SQLite Generated Columns: VIRTUAL vs STORED with Examples

A Generated Column Is a Computed Column

A generated column is a column whose value comes from an expression, not from an INSERT. You declare the formula once in CREATE TABLE, and SQLite handles the rest. You never write to it — trying to is an error.

The shortest possible example:

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 was never inserted, but it shows up in every row. SQLite recomputes it from price + tax whenever you read the row. Update either column and total follows.

The keyword phrase GENERATED ALWAYS AS is required. The ALWAYS is a SQL standard formality — there's no other option in SQLite.

VIRTUAL vs STORED

Every generated column is one of two flavors. The default is VIRTUAL:

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- recomputed on every SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- computed once and saved to disk
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

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

The mental model:

• VIRTUAL — costs zero bytes on disk, costs CPU on every read. Cheap to add, cheap to change later.
• STORED — costs disk space, costs nothing extra to read. Pays off when the expression is expensive or the column is read far more often than written.

If you don't write a keyword, you get VIRTUAL. That's almost always the right default.

Why Bother? Indexable Derived Values

The killer feature is that you can put an index on a generated column. That gives you fast lookups on derived values without rewriting every query.

Say you want case-insensitive email lookups:

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

The index covers the lowercased form. A query that filters on email_lower uses the index directly. SQLite even has expression indexes (CREATE INDEX ... ON users(lower(email))), but a generated column makes the derived value visible as a real column you can SELECT, reference in views, and reuse from application code.

Pulling Values Out of JSON

Generated columns shine on top of JSON. SQLite's JSON support gives you ->> to extract a scalar; wrap that in a generated column and you've got a typed, indexable field on top of a flexible blob.

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 and kind look like regular columns to your queries, but the data lives in payload. Change the JSON, and the columns update. The index on user_id makes the lookup fast.

Rules and Constraints

A few things SQLite enforces — worth knowing before you hit them:

• The expression must be deterministic. random(), datetime('now'), and other non-deterministic functions are not allowed. The value has to be reproducible from the row.
• The expression can only reference columns in the same row. No subqueries, no aggregates, no other tables.
• You can't INSERT or UPDATE a generated column directly. INSERT INTO products (total) VALUES (5) is an error.
• STORED columns can't be added with ALTER TABLE ... ADD COLUMN. Only VIRTUAL can be added after the fact.
• Generated columns can have NOT NULL, CHECK, UNIQUE, and even FOREIGN KEY constraints. They behave like any other column for those purposes.

A quick demo of the write rule:

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

The fix is to drop the generated column from the INSERT list and let SQLite compute it.

Choosing VIRTUAL or STORED

The decision usually comes down to read/write ratio and expression cost:

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Cheap arithmetic, fine to recompute every read.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Heavier expression, queried often, written rarely → store it.
    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;

Rules of thumb:

• Default to VIRTUAL. It's free at write time and fine for almost anything.
• Switch to STORED when you index the column on a write-heavy table (the index needs the value persisted anyway), or when the expression is genuinely expensive.
• Don't agonize over it. The flavor is part of the schema, but you can drop and recreate the column if you change your mind — at least for VIRTUAL.

Generated Columns vs Views

There's overlap with views: both expose computed values without storing them (well, sometimes). The split is usually:

• A generated column belongs to one row and one table. Use it for per-row derivations — formatting an email, extracting a JSON field, computing a total.
• A view is a saved query. Use it when the computation involves joins, aggregation, or filtering across rows.

You can mix them. A view can SELECT from a table that has generated columns and join in extra context. Generated columns sit at the storage layer; views sit at the query layer.

Next: ATTACH DATABASE

Generated columns let one table compute its own values. The next page goes the other direction: connecting multiple SQLite databases at once with ATTACH DATABASE, so a single query can span files.