Colonnes générées SQLite : VIRTUAL ou STORED

Une colonne générée, c'est une colonne calculée

Une colonne générée SQLite est une colonne dont la valeur provient d'une expression, et non d'un INSERT. Vous écrivez la formule une seule fois dans le CREATE TABLE, et SQLite s'occupe du reste. Impossible d'y écrire directement — toute tentative déclenche une erreur.

Voici l'exemple le plus court possible :

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    price   REAL NOT NULL,
    tax     REAL NOT NULL,
    total   REAL GENERATED ALWAYS AS (price + tax)
);

INSERT INTO products (price, tax) VALUES (100.0, 8.5);
INSERT INTO products (price, tax) VALUES (49.99, 4.25);

SELECT id, price, tax, total FROM products;

total n'a jamais été inséré, et pourtant il apparaît dans chaque ligne. SQLite le recalcule à partir de price + tax à chaque lecture de la ligne. Modifiez l'une des deux colonnes et total suit automatiquement.

Le mot-clé GENERATED ALWAYS AS est obligatoire. Le ALWAYS est une formalité du standard SQL — SQLite ne propose pas d'autre option de toute façon.

SQLite VIRTUAL vs STORED

Chaque colonne générée existe sous l'une de ces deux variantes. Par défaut, c'est VIRTUAL :

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- recalculé à chaque SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- calculé une seule fois et enregistré sur le disque
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

INSERT INTO orders (qty, unit) VALUES (3, 19.99);
SELECT * FROM orders;

Le modèle mental :

• VIRTUAL — zéro octet sur le disque, mais du CPU à chaque lecture. Pas cher à ajouter, pas cher à modifier plus tard.
• STORED — occupe de l'espace disque, mais ne coûte rien de plus à la lecture. Rentable quand l'expression est coûteuse ou que la colonne est lue beaucoup plus souvent qu'elle n'est écrite.

Si vous ne précisez pas le mot-clé, vous obtenez VIRTUAL. Et c'est presque toujours le bon choix par défaut.

L'intérêt : indexer des valeurs dérivées

L'atout maître, c'est qu'on peut poser un index sur une colonne générée. Résultat : des recherches rapides sur des valeurs dérivées, sans avoir à réécrire toutes vos requêtes.

Imaginons que vous vouliez faire des recherches d'e-mails insensibles à la casse :

CREATE TABLE users (
    id           INTEGER PRIMARY KEY,
    email        TEXT NOT NULL,
    email_lower  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);

CREATE INDEX users_email_lower ON users(email_lower);

INSERT INTO users (email) VALUES ('Ada@Example.com'), ('boris@example.com');

SELECT id, email FROM users WHERE email_lower = 'ada@example.com';

L'index couvre la version en minuscules. Une requête qui filtre sur email_lower utilise directement l'index. SQLite propose aussi des index sur expression (CREATE INDEX ... ON users(lower(email))), mais une colonne générée a l'avantage d'exposer la valeur calculée comme une vraie colonne : on peut la SELECT, la référencer dans des vues et la réutiliser depuis le code applicatif.

Extraire des valeurs depuis du JSON

Les colonnes générées SQLite prennent tout leur sens avec du JSON. Le support JSON de SQLite fournit l'opérateur ->> pour extraire une valeur scalaire ; encapsulez-la dans une colonne générée et vous obtenez un champ typé et indexable par-dessus un blob flexible.

CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL,
    user_id  INTEGER GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
    kind     TEXT    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);

CREATE INDEX events_user_id ON events(user_id);

INSERT INTO events (payload) VALUES
    ('{"user_id": 1, "kind": "login"}'),
    ('{"user_id": 2, "kind": "purchase"}'),
    ('{"user_id": 1, "kind": "logout"}');

SELECT id, kind FROM events WHERE user_id = 1;

user_id et kind se comportent comme des colonnes normales dans vos requêtes, mais les données vivent en réalité dans payload. Modifiez le JSON, et les colonnes se mettent à jour automatiquement. L'index sur user_id rend la recherche rapide.

Règles et contraintes

Quelques points que SQLite impose — autant les connaître avant de tomber dessus :

• L'expression doit être déterministe. random(), datetime('now') et autres fonctions non déterministes sont interdites. La valeur doit pouvoir être reproduite à partir de la ligne.
• L'expression ne peut référencer que les colonnes de la même ligne. Pas de sous-requêtes, pas d'agrégats, pas d'autres tables.
• Impossible de faire un INSERT ou un UPDATE directement sur une colonne générée. INSERT INTO products (total) VALUES (5) renvoie une erreur.
• Les colonnes STORED ne peuvent pas être ajoutées via ALTER TABLE ... ADD COLUMN. Seules les colonnes VIRTUAL peuvent être ajoutées après coup.
• Les colonnes générées acceptent les contraintes NOT NULL, CHECK, UNIQUE et même FOREIGN KEY. De ce point de vue, elles se comportent comme n'importe quelle autre colonne.

Une petite démo de la règle d'écriture :

sqlite> INSERT INTO products (price, tax, total) VALUES (10, 1, 999);
Runtime error: cannot INSERT into generated column "total"

La solution : retirez la colonne générée de la liste du INSERT et laissez SQLite la calculer.

Choisir entre VIRTUAL et STORED

Le choix se joue généralement sur le ratio lecture/écriture et le coût de l'expression :

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Arithmétique peu coûteuse, on peut la recalculer à chaque lecture.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Expression plus lourde, lue souvent, écrite rarement → on la stocke.
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'freezing'
            WHEN celsius < 15 THEN 'cool'
            WHEN celsius < 25 THEN 'mild'
            ELSE 'hot'
        END
    ) STORED
);

INSERT INTO measurements (celsius) VALUES (-3), (10), (22), (31);
SELECT celsius, fahrenheit, bucket FROM measurements;

Quelques règles pratiques :

• Par défaut, prends VIRTUAL. Ça ne coûte rien à l'écriture et ça suffit dans la grande majorité des cas.
• Passe à STORED quand vous posez un index sur la colonne d'une table très sollicitée en écriture (l'index doit de toute façon persister la valeur), ou quand l'expression est vraiment coûteuse à calculer.
• Ne te prends pas trop la tête. Le type fait partie du schéma, mais rien ne t'empêche de supprimer puis recréer la colonne si vous changez d'avis — du moins pour les VIRTUAL.

Colonnes générées ou vues : que choisir ?

Le recouvrement avec les vues est réel : les deux exposent des valeurs calculées sans forcément les stocker. Voici comment on tranche en général :

• Une colonne générée appartient à une seule ligne d'une seule table. Elle sert aux dérivations ligne par ligne — formater un e-mail, extraire un champ JSON, calculer un total.
• Une vue, c'est une requête sauvegardée. Tu la sors quand le calcul implique des jointures, des agrégations ou des filtres sur plusieurs lignes.

Et vous pouvez combiner les deux. Une vue peut très bien faire un SELECT sur une table qui contient des colonnes générées, puis joindre d'autres données autour. Les colonnes générées vivent au niveau du stockage ; les vues, au niveau de la requête.

La suite : ATTACH DATABASE

Avec les colonnes générées, une table calcule ses propres valeurs. La page suivante prend le chemin inverse : connecter plusieurs bases SQLite en même temps avec ATTACH DATABASE, pour qu'une seule requête puisse traverser plusieurs fichiers.