Tables STRICT en SQLite : typage strict des colonnes

Pourquoi les tables STRICT existent

SQLite, par défaut, est très permissif avec les types. Déclarez une colonne en INTEGER, insérez la chaîne "hello" : SQLite hausse les épaules et stocke la chaîne sans broncher. Cette souplesse était un choix de conception assumé dans les années 90, mais elle déroute les développeurs venant de Postgres ou MySQL — et surtout, elle masque des bugs.

Les tables STRICT, introduites dans SQLite 3.37, corrigent ce comportement. Vous activez le mode strict table par table, et à partir de là, les types de colonnes veulent vraiment dire ce qu'ils annoncent.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
) STRICT;

INSERT INTO users (name, age) VALUES ('Ada', 30);
SELECT * FROM users;

Le mot-clé STRICT se place juste après la parenthèse fermante. Tout le reste ressemble à un CREATE TABLE classique. La différence saute aux yeux dès que vous tentez d'insérer une valeur du mauvais type dans une colonne.

Ce que le mode STRICT impose vraiment

Dans une table classique, l'affinité de type tente de convertir la valeur vers le type déclaré, et si ça échoue, elle la stocke telle quelle. Dans une table STRICT, le moindre écart de type déclenche une erreur.

CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance INTEGER
) STRICT;

INSERT INTO accounts (balance) VALUES (100);     -- ok
INSERT INTO accounts (balance) VALUES ('100');   -- ok, '100' se convertit proprement
INSERT INTO accounts (balance) VALUES ('oops');  -- erreur

Faites le même essai sur une table non‑STRICT et la troisième insertion passe sans broncher : SQLite range tranquillement la chaîne 'oops' dans une colonne que vous aviez pourtant déclarée INTEGER. Quelques mois plus tard, une requête d'agrégation vous sort un résultat aberrant et vous y passez l'après‑midi pour comprendre d'où ça vient. Avec le mode STRICT, l'erreur saute aux yeux dès l'insertion, là où vous pouvez encore la corriger.

Voici l'erreur que vous obtiendrez :

Runtime error: cannot store TEXT value in INTEGER column accounts.balance

Clair, direct, impossible à louper.

Les cinq types autorisés

Une table STRICT n'accepte que cinq noms de types :

• INTEGER — nombres entiers.
• REAL — nombres à virgule flottante.
• TEXT — chaînes de caractères.
• BLOB — octets bruts.
• ANY — n'importe quel type, sans conversion.

Et c'est tout. Les alias laxistes que SQLite tolère habituellement — VARCHAR(255), DOUBLE, BOOLEAN, DATETIME, INT — déclenchent tous une erreur dans une table STRICT :

CREATE TABLE bad (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255)
) STRICT;

The error:

Erreur d'analyse : type de données inconnu pour bad.name : "VARCHAR(255)"

La solution, c'est d'utiliser l'un des cinq noms canoniques. VARCHAR(255) devient TEXT, DATETIME devient TEXT aussi (de toute façon, SQLite stocke les dates sous forme de chaînes ISO), et BOOLEAN devient INTEGER (avec 0 et 1).

La porte de sortie : la colonne ANY

ANY est le seul type qui autorise une colonne STRICT à contenir des valeurs hétérogènes. Pratique, par exemple, pour une colonne value générique dans une table clé/valeur :

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value ANY
) STRICT;

INSERT INTO settings VALUES ('max_users', 100);
INSERT INTO settings VALUES ('site_name', 'Coddy');
INSERT INTO settings VALUES ('ratio', 1.5);

SELECT key, value, typeof(value) FROM settings;

ANY est un cas particulier dans les tables STRICT : les valeurs y sont stockées sans la coercition de type que ce même mot-clé déclencherait ailleurs. Une chaîne '100' reste une chaîne, et un entier 100 reste un entier. Les appels à typeof() dans la requête précédente le confirment.

Dans une table non-STRICT, une colonne d'affinité ANY convertirait les chaînes ressemblant à des nombres en nombres. Le mode STRICT, lui, conserve le type d'origine à l'identique.

STRICT et PRIMARY KEY

Voici une subtilité à connaître : dans une table classique, INTEGER PRIMARY KEY a un statut particulier — il devient un alias de rowid et n'accepte que des entiers. Les autres déclarations de clé primaire sont nettement plus permissives.

Dans une table STRICT, le type de la colonne est appliqué qu'il s'agisse ou non de la clé primaire :

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL
) STRICT;

INSERT INTO products VALUES ('A-001', 'Bidule', 9.99);
INSERT INTO products VALUES (42, 'Machin', 14.99);  -- 42 est-il converti en '42' ? Non — erreur.

SELECT * FROM products;

Le second INSERT échoue. Dans une table non-STRICT, la valeur 42 aurait été stockée silencieusement dans la colonne TEXT qui sert de clé primaire. Ici, SQLite te le signale clairement.

Mélanger des tables STRICT et non-STRICT

Le mode STRICT s'applique table par table, pas à toute la base. Rien ne t'empêche d'avoir une table users en mode strict et une table events plus permissive dans le même fichier. Les clés étrangères fonctionnent entre les deux exactement comme d'habitude.

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    payload   -- aucun type déclaré ; les règles d'affinité s'appliquent
);

INSERT INTO users (name) VALUES ('Boris');
INSERT INTO events (user_id, payload) VALUES (1, 'connexion');
INSERT INTO events (user_id, payload) VALUES (1, 42);

SELECT id, payload, typeof(payload) FROM events;

La table events n'a pas de STRICT ni de type déclaré sur payload, donc elle accepte n'importe quoi. Pratique parfois, risqué par défaut. Réservez le stockage sans typage aux cas où vous avez réellement besoin d'une colonne fourre-tout.

Quand utiliser le mode STRICT

Pour un nouveau schéma, la réponse est « presque toujours ». Le coût est minime : un mot-clé par table, plus le fait de retenir les cinq noms de types canoniques. En contrepartie, les bugs qui se glisseraient normalement dans vos données apparaissent dès l'INSERT fautif.

Vous pouvez vous passer de STRICT dans ces cas :

• Vous maintenez une vieille base SQLite dont le schéma repose sur le typage souple.
• Vous visez une version de SQLite antérieure à 3.37 (octobre 2021) — le mot-clé n'existe pas.
• Vous voulez vraiment qu'une colonne accueille des types mixtes ; et même là, mieux vaut une table STRICT avec une colonne ANY qu'une table non-STRICT, parce que tout le reste reste contrôlé.

Une petite checklist pour convertir une table classique en STRICT :

• Remplacez VARCHAR, CHAR, NVARCHAR par TEXT.
• Remplacez DOUBLE, FLOAT, NUMERIC par REAL.
• Remplacez BOOLEAN, BIT, TINYINT par INTEGER.
• Remplacez DATETIME, TIMESTAMP, DATE par TEXT (ou INTEGER si vous stockez des timestamps unix).
• Ajoutez STRICT après la parenthèse fermante.

La suite : les clés primaires

Les tables STRICT verrouillent la façon dont les colonnes stockent leurs données. La prochaine chose à verrouiller, c'est quelle colonne identifie chaque ligne — et les clés primaires de SQLite ont quelques particularités (notamment autour d'INTEGER PRIMARY KEY et de rowid) qu'il vaut mieux connaître avant de concevoir un vrai schéma.