Notes concernant l'activation des contraintes sur clé étrangère (FK) sur les bases de type SQLite3 utilisées en backend via Flask-SQLAlchemy.

Une contrainte de type FK¹⁾ est définie dans notre modèle mais ne semble pas s'appliquer. Ci-dessous la définition ayant présenté la problématique :

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship
from sqlalchemy import Integer, String, Text, ForeignKey
 
class Base(DeclarativeBase):
  pass
 
db = SQLAlchemy(model_class=Base)
 
class Category(db.Model):
    __tablename__ = "categories"
 
    id = mapped_column(Integer, primary_key=True,)
    label = mapped_column(String(50), nullable=False)
    description = mapped_column(Text, nullable=True)
    parent_id = mapped_column(Integer, ForeignKey("categories.id"), nullable=True,)
 
    children = relationship("Category", back_populates="parent",
                            cascade="all, delete-orphan", uselist=True)
    parent = relationship("Category", back_populates="children", remote_side='Category.id')
 
    def __init__(self, label):
        """Constructeur """
        self.label = label

La classe Category utilise un modèle(pattern) bien connu dit en “liste adjacente” (adjacency list pattern) qui se présente comme une table se référençant elle même (ici via la création d'une Foreign Key sur le champ parent_id ligne 16).

Ce modèle permet de représenter des structures hiérarchiques dans des tables et dans ce cas de créer des sous catégories dans des catégories. Pour une sous catégorie, le champ parent_id contient l'identifiant de la catégorie parente et une catégorie racine aura une valeur nulle dans son champ parent_id.

Lorsqu'on teste cet objet via la commande flask shell on constate que la contrainte définie dans la classe ne s'applique pas car il est possible de créer un sous-catégorie avec un parent_id aléatoire ne correspondant à aucun identifiant existant dans la table(relation) categories.

>>> # Depuis flask shell
>>> db.create_all()
 
>>> # Ce comportement est correct
>>> rc = Category("root")
>>> rc.children.append( Category( "r1-A") )
>>> rc.children.append( Category( "r1-B") )
>>> 
>>> db.session.add(rc)
>>> db.session.commit()
>>>
>>> del rc
>>>
>>> # On récupère la catégorie racine depuis la base de données
>>> root_cat = Category.query.filter(Category.label == 'root').first()
>>> # root_cat est bien un objet de type Category
>>> type(root_cat)
<class 'app.Category'>
>>>
>>> # la catégorie racine contient bien les deux sous-catégories
>>> for c in root_cat.children :
...   print(c.label)
... 
r1-A
r1-B
>>> # Les sous-catégories ont pour parent_id l'identifiant de la catégorie root
>>> >>> root_cat.id
1
>>> root_cat.children[0].parent_id
1
>>> root_cat.children[1].parent_id
1
>>>
>>> # Le comportement suivant est anormal : 
>>> # On crée une catégorie avec un parent_id non existant
>>> cat_test = Category("une catégorie invalide")
>>> cat_test.parent_id = 666
>>> db.session.add(cat_test)
>>> db.session.commit()
>>> # Ici le commit a fonctionné sans lever d'exception
>>> quit()

via le client sqlite3 en ligne de commande, on peut afficher le contenu de notre fichier base de données. On constate ainsi que la table existe bien avec la contrainte et que les enregistrements ont été créés :

sqlite3 instance/project.db

sqlite> -- Affiche la structure de la table
sqlite> .schema categories
CREATE TABLE categories (
        id INTEGER NOT NULL, 
        label VARCHAR(50) NOT NULL, 
        description TEXT, 
        parent_id INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(parent_id) REFERENCES categories (id)
);
sqlite>
sqlite> -- affiche les valeurs nulles et les entêtes
sqlite> .nullvalue NULL
sqlite> .headers on
sqlite>
sqlite> -- Affiche le contenu de la table categories
sqlite> SELECT * FROM categories;
id|label|description|parent_id
1|root|NULL|NULL
2|r1-A|NULL|1
3|r1-B|NULL|1
4|une catégorie invalide|NULL|666

Ici on voit bien que la catégorie d'identifiant 4 a pour parent_id une valeur 666 alors que la contrainte est bien présente dans la définition de la table.

La requête d'insertion suivante est également accepté :

-- la requête suivante passée depuis sqlite3 ne retourne pas d'erreur
INSERT INTO categories (label,parent_id) VALUES ("un autre test", 777 );

On affiche la table categories, on peut voir que le nouvel enregistrement a été créée :

sqlite> SELECT * FROM categories;
id|label|description|parent_id
1|root|NULL|NULL
2|r1-A|NULL|1
3|r1-B|NULL|1
4|une catégorie invalide|NULL|666
5|un autre test|NULL|777

En affichant la configuration du fichier de base de données on peut voir une option enable_fkey positionnée à off par défaut :

sqlite> .dbconfig
          defensive on
            dqs_ddl on
            dqs_dml on
        enable_fkey off
        enable_qpsg off
     enable_trigger on
        enable_view on
     fts3_tokenizer on
 legacy_alter_table off
 legacy_file_format off
     load_extension on
   no_ckpt_on_close off
     reset_database off
  reverse_scanorder off
    stmt_scanstatus off
        trigger_eqp off
     trusted_schema off
    writable_schema off

sqlite> .dbconfig enable_fkey on
        enable_fkey on
sqlite> INSERT INTO categories (label,parent_id) VALUES ("encore un test", 888 );
Runtime error: FOREIGN KEY constraint failed (19)

C'est bien une variable de configuration pour notre base de données SQLite3 qui permet d'activer la prise en compte des contraintes FK existantes.

Le code ci-dessous proposé sur la plateforme github permet d'obtenir le comportement attendu sous Flask-SQLAlchemy en activant de la même manière la prise en compte des contraintes de type FK lors de l'ouverture de la base de données.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
 
def create_app(config: str=None):
    app = Flask(__name__, instance_relative_config=True)
    if config is None:
        app.config.from_pyfile('dev.py')
    else:
        logger.debug('Using %s as configuration', config)
        app.config.from_pyfile(config)
 
    db.init_app(app)
 
    # Ensure FOREIGN KEY for sqlite3
    if 'sqlite' in app.config['SQLALCHEMY_DATABASE_URI']:
        def _fk_pragma_on_connect(dbapi_con, con_record):  # noqa
            dbapi_con.execute('pragma foreign_keys=ON')
 
        with app.app_context():
            from sqlalchemy import event
            event.listen(db.engine, 'connect', _fk_pragma_on_connect)

Après modification du code d'initialisation de l'application Flask, on obtient bien le résultat attendu dans le shell flask :

>>> cat_must_fail = Category("categorie non valide")
>>> cat_must_fail.parent_id = 1789
>>> db.session.add(cat_must_fail)
>>> db.session.commit()
Traceback (most recent call last):
  File "/home/yoann/dev/poc/categories/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/home/yoann/dev/poc/categories/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 951, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

• Imposer les contraintes FK pour les bases SQLite avec Flask-SQLAlchemy
• Usage des contraintes avec auto-référence dans SQLAlchemy (stackoverflow.com)