James Ainslie

A derived value becomes a maintenance problem when applications can update it independently of the values that define it. An order line with a quantity, a unit price and a separately writable total admits inconsistent rows. Every writer must know the calculation and remember to apply it. SQLite 3.31.0, released on January 22, adds generated columns, which make that calculation part of the table definition. The release notes identify both virtual and stored forms as part of the new support.

The interesting change is the ownership of the value. A generated column has a name that queries can use, but an application cannot write its value directly. Changes to its input columns determine its result. This gives a derived attribute an explicit definition in the database, with the same definition for each writer that opens the file. It also introduces a compatibility decision, because older SQLite engines cannot interpret the new schema syntax.

I value SQLite for the amount of relational behavior it provides inside a file that an application can manage directly. Generated columns fit that preference when they remove duplicated calculation logic without requiring another process. Their usefulness depends on keeping the expression within the row whose value it describes.

Choosing when to calculate

The generated-column documentation available on the release date defines a generated column as a function of other columns in the same row. A virtual column computes its value when read. A stored column computes its value when the row is written and occupies space in the database file. The choice therefore changes when the calculation happens and whether the table stores its result.

An illustrative order-line table can express an integer calculation directly. Its ordinary columns contain the inputs, and the generated column declares the multiplication:

CREATE TABLE order_line (
    id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL,
    unit_price_cents INTEGER NOT NULL,
    line_total_cents INTEGER
        GENERATED ALWAYS AS (quantity * unit_price_cents) STORED
);

INSERT INTO order_line (quantity, unit_price_cents)
VALUES (3, 250);

SELECT quantity, unit_price_cents, line_total_cents
FROM order_line;

The selected total is 750 for these inputs. The insert supplies the ordinary columns and leaves SQLite to calculate the generated one. Updating the quantity changes the total under the same schema definition. The example uses small integer amounts to make the calculation explicit. It is a demonstration of a derived value, not a complete design for pricing or accounting.

Changing STORED to VIRTUAL leaves the SQL meaning of this column the same. The documentation describes both forms as producing the same query results. A stored value adds a representation to the database file and performs the calculation during writes. A virtual value avoids that stored representation and requires calculation during reads. The appropriate choice follows the calculation and the application's access pattern, without changing who owns the formula.

The syntax permits shorter declarations. GENERATED ALWAYS is optional, and omitting the final storage keyword selects VIRTUAL. Thus line_total_cents INTEGER AS (quantity * unit_price_cents) declares a virtual generated column. Explicit keywords can still help a schema reader recognize that an application must not supply the value. A shorter definition does not make the column an ordinary writable field.

There is also a migration difference between the two forms. ALTER TABLE ADD COLUMN can add a virtual generated column to an existing table, but it cannot add a stored generated column. That distinction matters before selecting stored computation merely because a value appears frequently in queries. The initial table definition and a later schema change offer different available operations.

A named expression can have constraints

Generated columns participate in more of the schema than a calculation in a particular SELECT. The release-date documentation permits indexes on them and permits NOT NULL, CHECK, UNIQUE and foreign key constraints. Those capabilities allow a derived attribute to have both a stable name and rules governing acceptable values. The application can query that name without reproducing the generating expression at each call site.

For the illustrative order-line table, a check can constrain the generated total. The following independent table definition keeps that check beside the expression it governs:

CREATE TABLE checked_order_line (
    id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL,
    unit_price_cents INTEGER NOT NULL,
    line_total_cents INTEGER
        GENERATED ALWAYS AS (quantity * unit_price_cents) VIRTUAL
        CHECK (line_total_cents >= 0)
);

CREATE INDEX checked_order_line_total
ON checked_order_line (line_total_cents);

This definition demonstrates independent capabilities described by the documentation. A generated value can carry a check, and an index can refer to the generated column. The check expresses a rule about the derived result. It does not establish all the rules a real order line needs, since a nonnegative product alone says too little about its individual inputs.

A generated column can also declare a datatype. SQLite attempts to transform the expression result according to the same affinity rules it uses for ordinary columns. A declaration of INTEGER therefore needs to be understood through SQLite's affinity behavior. It is not a separate promise that the inputs obey the application's intended domain rules. Those input rules still need their own representation in the schema or application.

I prefer this explicit division of responsibility. The generated expression defines a relationship between attributes, while constraints describe which resulting rows the database accepts. Combining them can remove an application-maintained copy of a value, but it does not remove the need to define the underlying data carefully.

The expression must remain local

The restrictions keep generated columns within a specific computational scope. Their expressions may reference constants and columns from the same row, and may use scalar deterministic functions. Subqueries, aggregates, window functions and table-valued functions are excluded. A generated column consequently cannot calculate an order's total by summing a separate table of order lines.

That limitation affects the model directly. The line total above depends on attributes in one row, so it fits. A customer balance that depends on many payment rows does not fit the same mechanism. Giving both values names in the application does not make their dependencies equivalent. The schema feature distinguishes calculations whose inputs are part of the row from calculations that require reading other rows.

Dependencies between generated columns are allowed, provided they do not form a cycle. One generated expression can refer to another generated column, which can make a longer calculation easier to describe. An indirect dependency back to the original column is still forbidden. Each dependency chain must ultimately reach ordinary inputs instead of requiring a column to determine its own value.

The documentation also excludes generated columns from primary keys and forbids a DEFAULT clause on them. A default would describe a value to use when an insert omits an input, but the generated expression already defines the column's value. Every table must retain at least one ordinary column. The expression cannot reference ROWID directly, although it can reference a declared INTEGER PRIMARY KEY column.

These rules make generated columns a bounded schema tool. They represent deterministic calculations over a row, with explicit dependency restrictions. They do not introduce a general query execution system inside a column declaration. That scope makes the order-line example understandable from its table definition, since the calculation does not require searching another relation.

Compatibility follows the schema syntax

The compatibility cost appears when another program opens the database with an older SQLite engine. According to the release-date documentation, an engine before 3.31.0 treats generated-column syntax as an error and reports a corrupt schema. Physical damage is not necessary for that diagnostic to appear. The older reader does not understand the declaration stored in the schema.

This is narrower than a blanket incompatibility between database files created by different releases. SQLite 3.31.0 can read and write databases created by earlier versions back to SQLite 3.0.0. Older engines can also work with databases created by the new release when the schema contains no unsupported features. Introducing a generated column is the relevant change here, even if the table otherwise looks familiar.

That distinction matters for an embedded engine because the database can have several consumers with different bundled SQLite versions. A schema change may affect a maintenance utility or an import program as well as the principal application. Selecting a new engine for one executable does not establish that every consumer understands the schema. The minimum supported reader becomes part of the decision to use generated columns.

Keeping an ordinary column with an application-maintained total preserves different compatibility properties, but also preserves the possibility that its value disagrees with its inputs. The generated declaration changes both properties together. That relationship belongs in the schema decision before a file containing the new declaration reaches another reader.

For my preference for SQLite's file-oriented design, this is the material tradeoff. A named, database-owned calculation can make a table easier to use correctly, while the new declaration establishes a newer minimum engine version. A virtual column avoids storing the calculated value, but it still uses the new syntax and therefore has the same older-reader concern. Storage choice does not remove the schema compatibility requirement.