Applications often store values that can be calculated from other columns: an order-line total from quantity and unit price, a normalized search key from text, or a duration from two timestamps. The tempting approach is to calculate the value in application code and save both the inputs and the result.

That creates two sources of truth. If one code path updates the inputs but forgets to update the derived value, the row becomes internally inconsistent.

SQLite generated columns solve this class of problem inside the schema. A generated column has an expression instead of an independently writable value. SQLite derives its value from other columns in the same row, so application code cannot accidentally store a conflicting result.

The important design question is not only how to declare one. You also need to understand when SQLite computes it, which expressions are allowed, how indexing and constraints interact with it, and when an ordinary query expression is simpler.

Start with a value that should never be independent

Suppose an order line stores a quantity and a unit price in integer cents:

CREATE TABLE line_items (
    id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0)
);

Every time you need the line total, you could calculate:

SELECT
    id,
    quantity,
    unit_price_cents,
    quantity * unit_price_cents AS total_cents
FROM line_items;

That is already correct when only a few queries need the value. There is no requirement to put every calculation into the schema.

A generated column becomes useful when total_cents is part of the row’s reusable model: many queries reference it, you want to index it, or you want constraints to apply to the derived result.

Add it like this:

CREATE TABLE line_items (
    id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0),
    total_cents INTEGER
        GENERATED ALWAYS AS (quantity * unit_price_cents) STORED
);

Now the application writes only the independent inputs:

INSERT INTO line_items (quantity, unit_price_cents)
VALUES (3, 1250);

SELECT quantity, unit_price_cents, total_cents
FROM line_items;

The row reports a total_cents value of 3750.

The key mental model is:

ordinary columns are inputs; generated columns are derived outputs.

SQLite does not let an INSERT or UPDATE directly assign the generated column. Changing quantity or unit_price_cents changes the derived result instead.

VIRTUAL and STORED change when the work happens

SQLite supports two kinds of generated column:

value INTEGER AS (expression) VIRTUAL

and:

value INTEGER AS (expression) STORED

A VIRTUAL column is computed when its value is read. It does not occupy normal column storage in each row.

A STORED column is computed when the row is written, and the computed result is stored in the database file.

If you omit both keywords, SQLite uses VIRTUAL.

Both forms present the same logical relationship to SQL: the generated value is determined by its expression rather than written independently by the application. The trade-off is where the cost falls.

A VIRTUAL column can avoid storing repeated derived data, but reading it requires evaluating the expression. A STORED column uses additional database space and adds computation to writes, but reads do not need to recompute the expression from its source columns.

Do not choose STORED automatically because it sounds faster. For a cheap expression that is rarely queried, VIRTUAL may be simpler and smaller. For an expensive deterministic expression that is read frequently, STORED may be worth the extra write and storage cost. Measure the workload when the difference matters.

Updating source columns keeps the result consistent

With the earlier STORED definition:

INSERT INTO line_items (quantity, unit_price_cents)
VALUES (3, 1250);

the total is 3750.

If the quantity changes:

UPDATE line_items
SET quantity = 4
WHERE id = 1;

then total_cents becomes 5000.

Application code did not have to remember a second assignment. That is the main maintainability benefit: the dependency is declared once in the schema.

By contrast, storing total_cents as an ordinary column creates an invariant that every writer must maintain:

-- Risky design: application code must keep three values synchronized.
UPDATE line_items
SET
    quantity = 4,
    total_cents = 5000
WHERE id = 1;

One forgotten assignment, one buggy import, or one alternate write path can break that invariant.

A generated column removes that particular failure mode because SQLite owns the derived value.

Generated expressions are intentionally restricted

A generated column expression is not an arbitrary query. SQLite restricts it to values that can be derived from the same row in a predictable way.

The expression may reference constant literals, columns in the same row, and scalar deterministic functions. It cannot use subqueries, aggregate functions, window functions, or table-valued functions.

For example, this kind of same-row derivation fits:

CREATE TABLE rectangles (
    id INTEGER PRIMARY KEY,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    area INTEGER AS (width * height)
);

But a generated column cannot calculate “the number of orders for this customer” by querying another table. That value depends on other rows and therefore needs a query, trigger-maintained summary, materialized structure, or application-level design appropriate to the workload.

Generated columns may depend on other generated columns in the same row, but dependencies cannot form a direct or indirect cycle.

These restrictions are useful. They keep a generated value tied to the row that owns it instead of turning row writes into hidden multi-row queries.

Index a generated column when queries search by it

Generated columns can participate in indexes.

Suppose a service frequently finds expensive order lines:

SELECT id, total_cents
FROM line_items
WHERE total_cents >= 10000;

You can index the derived value:

CREATE INDEX line_items_total_idx
ON line_items (total_cents);

SQLite can then consider that index when planning predicates on total_cents.

This does not make the index free. The database still has to maintain the index when source-column changes alter the generated value. A STORED generated column plus an index therefore has both storage and write-maintenance costs.

Also distinguish a generated column from an expression index. If the only reason you want the derived value is to accelerate a particular expression in search conditions, an index directly on the expression may be enough:

CREATE INDEX line_items_total_expr_idx
ON line_items (quantity * unit_price_cents);

A generated column is more useful when the derived concept deserves a stable name that queries, constraints, and application code can reference consistently.

Put constraints on the derived result when the rule belongs in the schema

SQLite allows generated columns to have constraints such as NOT NULL, CHECK, UNIQUE, and foreign-key constraints where their normal rules are satisfied.

For example, suppose a username is stored with a normalized lowercase key used for uniqueness:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    username_key TEXT
        GENERATED ALWAYS AS (lower(username)) STORED
        UNIQUE
);

The database now rejects two rows whose generated username_key values conflict under that definition.

This can be useful, but the expression must actually match the business rule. lower() is not a universal Unicode identity or locale-aware username policy. If your application needs a specific normalization or collation contract, define and test that contract rather than assuming lowercase conversion is equivalent.

The general lesson is that generated columns can help enforce a rule only when the generating expression faithfully represents the rule.

Know what the declared type does

SQLite generated columns can have a declared type:

total_cents INTEGER AS (quantity * unit_price_cents) STORED

SQLite applies the column’s type affinity to the generated result in the same general way it does for ordinary columns.

The expression itself does not determine the generated column’s declared type or collation. Those come from the column definition.

This matters when refactoring. Two expressions that appear to produce similar values can behave differently if the generated columns have different declared affinities or collations.

Keep the declaration explicit when callers depend on comparison or sorting behavior.

Schema changes are different for VIRTUAL and STORED columns

SQLite documents an important migration difference: ALTER TABLE ... ADD COLUMN can add a VIRTUAL generated column, but not a STORED generated column.

For example, adding a VIRTUAL value can be written as:

ALTER TABLE products
ADD COLUMN final_price_cents INTEGER
    GENERATED ALWAYS AS (price_cents - discount_cents) VIRTUAL;

If an existing populated table needs a new STORED generated column, plan a table-rebuild migration rather than relying on ADD COLUMN. A typical SQLite rebuild creates the desired replacement schema, copies compatible data, recreates indexes and other schema objects, and swaps the tables inside an appropriate migration procedure.

Treat that as an operational cost of choosing STORED. The choice affects future schema evolution, not just runtime reads and writes.

Use table_xinfo when inspecting generated columns

There is a small introspection trap in SQLite tooling.

PRAGMA table_info(table_name) does not include generated columns. PRAGMA table_xinfo(table_name) does.

For schema inspection code that must see the complete column set, use:

PRAGMA table_xinfo(line_items);

This distinction matters for migration tools, schema validators, and code generators. If they rely only on table_info, they can mistakenly conclude that a generated column does not exist.

Avoid copying environment-dependent logic into the schema

A generated expression becomes part of the database schema. That makes portability and determinism more important than they are for a one-off query.

SQLite permits deterministic scalar functions in generated expressions. If an application registers its own deterministic function and uses it in the schema, the database now depends on compatible implementations of that function being available whenever the schema is used.

That can be reasonable in a controlled application, but it raises deployment and migration costs. A database opened by another tool may not know the function. A future implementation change can also create compatibility concerns for values or indexes derived under earlier behavior.

Prefer built-in, stable expressions when they are sufficient. Use application-defined functions only when you can own that compatibility contract.

Common mistakes come from treating derived data as independent

Three mistakes are especially common.

First, do not include a generated column in application writes:

INSERT INTO line_items (
    quantity,
    unit_price_cents,
    total_cents
)
VALUES (2, 500, 1000);

SQLite rejects attempts to write generated columns directly. Send only the source values.

Second, do not use a generated column to hide expensive logic without measuring it. A complex VIRTUAL expression used across a large scan still has to be evaluated for rows where its value is needed. STORED moves that computation to writes but consumes space and may make migrations harder.

Third, do not expect generated columns to maintain cross-row or cross-table summaries. They are row-local derivations, not a replacement for aggregation queries, triggers, or carefully designed summary tables.

When a generated column is the right tool

Use a generated column when all of these are broadly true:

  • the value is a deterministic function of other columns in the same row;
  • callers benefit from a stable schema-level name for that value;
  • eliminating duplicate write logic reduces inconsistency risk;
  • the VIRTUAL or STORED cost fits the actual read/write workload.

Use a normal query expression when the calculation is simple and only needed in a small number of queries. Use an expression index when you mainly need lookup performance and do not need a named derived field. Use other mechanisms when the value depends on multiple rows, other tables, external state, or business logic that cannot be expressed safely as a deterministic row-local expression.

Generated columns are most valuable when they make the data model more truthful: the database stores the independent facts and declares how a dependent value follows from them.

Keep one source of truth

The practical reason to use generated columns is not syntax convenience. It is ownership.

If a value is always determined by other fields in the same row, letting every writer calculate and persist its own copy creates an avoidable synchronization problem. A generated column puts that dependency in the schema, where all writers share it.

Choose VIRTUAL when computing on read is acceptable and storage should stay minimal. Choose STORED when shifting that deterministic work to writes is worth the extra storage and migration cost. Index or constrain the generated value when the workload and data rules justify it.

The result is a schema with fewer independent facts to keep synchronized—and fewer opportunities for application code to disagree with the database.

Database SQLite SQL Schema Design