Docs executáveis de SQLite: Generated Columns

Q: Qual a diferença entre generated columns VIRTUAL e STORED?

Uma coluna VIRTUAL é recalculada a cada leitura e não ocupa espaço em disco — é o padrão. Já uma STORED é calculada uma única vez no momento da escrita e gravada no banco, o que deixa as leituras mais baratas em troca de uma gravação um pouco mais cara. As duas podem ser indexadas, mas a STORED costuma fazer mais sentido quando a expressão é pesada ou quando a coluna é lida muito mais do que escrita.

Q: Dá para criar índice em uma generated column no SQLite?

Dá sim. O CREATE INDEX funciona normalmente em generated columns, tanto VIRTUAL quanto STORED. Esse é justamente o principal motivo de usar esse recurso: você indexa um valor derivado (como lower(email) ou um campo de JSON extraído com ->>) e o planejador de consultas passa a aproveitar esse índice sem precisar reescrever cada query.

Q: Posso usar ALTER TABLE para adicionar uma generated column?

Pode, mas só para colunas VIRTUAL. O comando ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL funciona sem problema. Já adicionar uma coluna STORED via ALTER TABLE não é suportado — nesse caso, você precisaria recriar a tabela. Então, se você quer colunas armazenadas em tabelas que já existem, é melhor planejar isso desde o início.

Coluna gerada é uma coluna computada

Uma coluna gerada (ou coluna computada) é uma coluna cujo valor vem de uma expressão, e não de um INSERT. Você declara a fórmula uma única vez no CREATE TABLE e o SQLite cuida do resto. Não dá para escrever nela — qualquer tentativa resulta em erro.

O exemplo mais simples possível:

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 foi inserido, mas aparece em toda linha. O SQLite recalcula esse valor a partir de price + tax toda vez que você lê o registro. Atualize qualquer uma das colunas e total acompanha.

A expressão GENERATED ALWAYS AS é obrigatória. O ALWAYS é só uma formalidade do padrão SQL — no SQLite não existe outra opção.

SQLite virtual vs stored: qual usar?

Toda coluna gerada no SQLite tem um de dois sabores. O padrão é VIRTUAL:

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- recalculado em cada SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- calculado uma vez e salvo em disco
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

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

O modelo mental:

VIRTUAL — não ocupa nenhum byte em disco, mas gasta CPU a cada leitura. Barata de criar e fácil de alterar depois.
STORED — ocupa espaço em disco e não tem custo extra na leitura. Vale a pena quando a expressão é cara ou quando a coluna é lida muito mais do que escrita.

Se você não especificar nada, o SQLite assume VIRTUAL. E esse costuma ser o padrão certo na grande maioria dos casos.

Por que usar? Valores derivados indexáveis

O grande trunfo é que dá pra criar um índice em coluna gerada. Isso te dá buscas rápidas em valores derivados sem precisar reescrever consulta nenhuma.

Imagine que você queira buscar e-mails ignorando maiúsculas e 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';

O índice cobre a versão em minúsculas. Uma consulta que filtra por email_lower usa o índice direto. O SQLite até tem índices em expressão (CREATE INDEX ... ON users(lower(email))), mas uma coluna gerada deixa o valor derivado visível como uma coluna de verdade, que você pode usar no SELECT, referenciar em views e reaproveitar no código da aplicação.

Extraindo valores de JSON

As colunas geradas brilham quando o assunto é JSON. O suporte a JSON no SQLite oferece o operador ->> para extrair um valor escalar; embrulhe isso numa coluna gerada e você ganha um campo tipado e indexável em cima de um blob flexível.

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 e kind aparecem como colunas normais nas suas queries, mas o dado real fica em payload. Se você mudar o JSON, as colunas se atualizam sozinhas. E o índice em user_id deixa a busca rápida.

Regras e restrições

Tem algumas coisas que o SQLite impõe — vale conhecer antes de esbarrar nelas:

A expressão precisa ser determinística. Funções como random(), datetime('now') e outras não-determinísticas não são permitidas. O valor tem que ser reproduzível a partir da linha.
A expressão só pode referenciar colunas da mesma linha. Nada de subqueries, agregações ou outras tabelas.
Você não pode fazer INSERT nem UPDATE direto em uma coluna gerada. INSERT INTO products (total) VALUES (5) dá erro.
Colunas STORED não podem ser adicionadas via ALTER TABLE ... ADD COLUMN. Só dá pra adicionar VIRTUAL depois que a tabela já existe.
Colunas geradas aceitam NOT NULL, CHECK, UNIQUE e até FOREIGN KEY. Para essas restrições, elas se comportam como qualquer outra coluna.

Uma demonstração rápida da regra de escrita:

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

A solução é tirar a coluna gerada da lista do INSERT e deixar o SQLite calcular o valor sozinho.

VIRTUAL ou STORED: qual escolher?

Na prática, a escolha depende da relação entre leituras e escritas e do custo da expressão:

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Aritmética barata, tudo bem recalcular a cada leitura.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Expressão mais pesada, consultada com frequência, escrita raramente → armazene.
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'congelando'
            WHEN celsius < 15 THEN 'fresco'
            WHEN celsius < 25 THEN 'ameno'
            ELSE 'quente'
        END
    ) STORED
);

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

Regras práticas:

Por padrão, vai de VIRTUAL. Não custa nada na escrita e dá conta do recado em quase tudo.
Mude para STORED quando você for criar índice na coluna em uma tabela com muita escrita (o índice já precisa do valor persistido de qualquer jeito), ou quando a expressão for realmente cara de calcular.
Não fique agonizando com isso. O tipo faz parte do schema, mas dá para dropar e recriar a coluna se você mudar de ideia — pelo menos no caso de VIRTUAL.

Coluna computada no SQLite vs views

Existe uma sobreposição com views: as duas expõem valores calculados sem armazená-los (bom, às vezes). A divisão costuma ser assim:

Uma coluna gerada pertence a uma linha e uma tabela. Use quando a derivação é por linha — formatar um email, extrair um campo JSON, calcular um total.
Uma view é uma query salva. Use quando o cálculo envolve joins, agregação ou filtros entre várias linhas.

Dá para combinar as duas. Uma view pode fazer SELECT em uma tabela que tem colunas geradas e ainda dar join com contexto extra. Colunas geradas vivem na camada de armazenamento; views vivem na camada de consulta.

A seguir: ATTACH DATABASE

As colunas geradas permitem que uma tabela calcule seus próprios valores. A próxima página vai no sentido oposto: conectar vários bancos SQLite ao mesmo tempo com ATTACH DATABASE, para que uma única query possa percorrer arquivos diferentes.

Perguntas frequentes

O que é uma generated column no SQLite?

É uma coluna cujo valor é calculado a partir de uma expressão envolvendo outras colunas da mesma linha. Você declara usando GENERATED ALWAYS AS (expressão) no CREATE TABLE. Não dá para escrever nela diretamente — o SQLite calcula o valor sozinho, seja na hora de ler ou de gravar a linha.

Qual a diferença entre generated columns VIRTUAL e STORED?

Uma coluna VIRTUAL é recalculada a cada leitura e não ocupa espaço em disco — é o padrão. Já uma STORED é calculada uma única vez no momento da escrita e gravada no banco, o que deixa as leituras mais baratas em troca de uma gravação um pouco mais cara. As duas podem ser indexadas, mas a STORED costuma fazer mais sentido quando a expressão é pesada ou quando a coluna é lida muito mais do que escrita.

Dá para criar índice em uma generated column no SQLite?

Dá sim. O CREATE INDEX funciona normalmente em generated columns, tanto VIRTUAL quanto STORED. Esse é justamente o principal motivo de usar esse recurso: você indexa um valor derivado (como lower(email) ou um campo de JSON extraído com ->>) e o planejador de consultas passa a aproveitar esse índice sem precisar reescrever cada query.

Posso usar ALTER TABLE para adicionar uma generated column?

Pode, mas só para colunas VIRTUAL. O comando ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL funciona sem problema. Já adicionar uma coluna STORED via ALTER TABLE não é suportado — nesse caso, você precisaria recriar a tabela. Então, se você quer colunas armazenadas em tabelas que já existem, é melhor planejar isso desde o início.

Conceitos relacionados

Generated Columns no SQLite: VIRTUAL vs STORED