Columnas generadas en SQLite: VIRTUAL vs STORED

Una columna generada es una columna calculada

Una columna generada es una columna cuyo valor sale de una expresión, no de un INSERT. Defines la fórmula una sola vez en el CREATE TABLE y SQLite se encarga del resto. Nunca escribes en ella: si lo intentas, da error.

El ejemplo más corto posible:

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 nunca se insertó, pero aparece en todas las filas. SQLite la vuelve a calcular a partir de price + tax cada vez que lees la fila. Si actualizas cualquiera de las dos columnas, total se ajusta solo.

La cláusula GENERATED ALWAYS AS es obligatoria. El ALWAYS es una formalidad del estándar SQL — en SQLite no hay otra opción.

VIRTUAL vs STORED: los dos tipos de columna generada

Toda columna generada en SQLite es de uno de estos dos tipos. Por defecto es VIRTUAL:

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- se recalcula en cada SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- se calcula una vez y se guarda en disco
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

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

El modelo mental:

• VIRTUAL — no ocupa nada en disco, pero gasta CPU en cada lectura. Barata de añadir y barata de cambiar después.
• STORED — ocupa espacio en disco, pero leerla no cuesta nada extra. Vale la pena cuando la expresión es costosa o cuando lees la columna mucho más de lo que la escribes.

Si no pones la palabra clave, te queda VIRTUAL. Y casi siempre ese es el valor por defecto correcto.

¿Para qué sirven? Valores derivados que sí puedes indexar

La gran ventaja es que puedes crear un índice sobre una columna generada en SQLite. Eso te da búsquedas rápidas sobre valores derivados sin tener que reescribir todas tus consultas.

Imagina que quieres buscar emails sin distinguir mayúsculas y minúsculas:

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';

El índice cubre la versión en minúsculas. Una consulta que filtra por email_lower usa el índice de forma directa. SQLite también tiene índices sobre expresiones (CREATE INDEX ... ON users(lower(email))), pero una columna generada expone el valor derivado como una columna real: la puedes usar en SELECT, referenciarla en vistas y reutilizarla desde el código de la aplicación.

Extraer valores desde JSON

Las columnas generadas brillan cuando trabajas con JSON. El soporte de JSON en SQLite te ofrece ->> para extraer un valor escalar; envuélvelo en una columna generada y obtienes un campo tipado e indexable sobre 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 y kind se ven como columnas normales en tus consultas, pero los datos viven en payload. Si cambias el JSON, las columnas se actualizan solas. Y el índice sobre user_id hace que la búsqueda vuele.

Reglas y restricciones de las columnas generadas

Hay un par de cosas que SQLite impone — conviene tenerlas claras antes de toparte con ellas:

• La expresión tiene que ser determinista. random(), datetime('now') y demás funciones no deterministas no están permitidas. El valor debe poder reproducirse a partir de la fila.
• La expresión solo puede referenciar columnas de la misma fila. Nada de subconsultas, agregados ni otras tablas.
• No puedes hacer INSERT ni UPDATE directamente sobre una columna generada. INSERT INTO products (total) VALUES (5) da error.
• Las columnas STORED no se pueden añadir con ALTER TABLE ... ADD COLUMN. Solo las VIRTUAL se pueden agregar después.
• Las columnas generadas admiten restricciones NOT NULL, CHECK, UNIQUE e incluso FOREIGN KEY. A efectos prácticos, se comportan como cualquier otra columna.

Una demostración rápida de la regla de escritura:

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

La solución pasa por quitar la columna generada de la lista del INSERT y dejar que SQLite la calcule.

¿VIRTUAL o STORED? Cómo elegir en SQLite

La decisión casi siempre depende de la relación entre lecturas y escrituras, y de lo costosa que sea la expresión:

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Aritmética barata, no hay problema en recalcularla en cada lectura.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Expresión más costosa, consultada a menudo, escrita pocas veces → almacenarla.
    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;

Reglas prácticas:

• Por defecto, usa VIRTUAL. No cuesta nada en escritura y sirve para casi todo.
• Pásate a STORED cuando vayas a indexar la columna en una tabla con muchas escrituras (el índice ya necesita el valor persistido) o cuando la expresión sea realmente costosa.
• No le des demasiadas vueltas. El tipo forma parte del esquema, pero siempre puedes eliminar la columna y volver a crearla si cambias de opinión — al menos en el caso de VIRTUAL.

Columnas generadas vs vistas en SQLite

Hay solapamiento con las vistas: ambas exponen valores calculados sin almacenarlos (bueno, a veces sí). La separación suele ser así:

• Una columna generada pertenece a una fila y a una tabla. Te sirve para derivaciones por fila: formatear un email, extraer un campo de un JSON, calcular un total.
• Una vista es una consulta guardada. Úsala cuando el cálculo implique joins, agregaciones o filtrado entre varias filas.

Puedes combinarlas. Una vista puede hacer SELECT sobre una tabla que tenga columnas generadas y unir contexto adicional. Las columnas generadas viven en la capa de almacenamiento; las vistas, en la capa de consulta.

Lo que viene: ATTACH DATABASE

Las columnas generadas permiten que una tabla calcule sus propios valores. La siguiente página va en la dirección contraria: conectar varias bases de datos SQLite a la vez con ATTACH DATABASE, para que una sola consulta pueda abarcar varios archivos.