Recursive AI

Create SQLite generated columns in C#

OneLite is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

Declare a formula with AS (expression). Choose VIRTUAL to compute on read or STORED to compute on write.

Terminal
dotnet add package RecursiveAI.OneLite

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
using RecursiveAI.OneLite;
 
await using var db =
    await Database.OpenAsync(":memory:");
db.ExecuteScript("""
    CREATE TABLE items (
      qty INTEGER,
      doubled INTEGER AS (qty * 2) VIRTUAL,
      tripled INTEGER AS (qty * 3) STORED
    );
    INSERT INTO items(qty) VALUES (2);
    UPDATE items SET qty = 4;
    """);
var row = await db.ScalarAsync<string>($"""
    SELECT doubled || ', ' || tripled
    FROM items
    """);
Console.WriteLine(row);

Unzip the example, then dotnet run.

Prints the doubled and tripled quantities after the update:

Text
8, 12

The full program

C#
using RecursiveAI.OneLite;
 
await using var db =
    await Database.OpenAsync(":memory:");
await db.ExecuteAsync($"""
    CREATE TABLE items (
      qty INTEGER,
      doubled INTEGER AS (qty * 2) VIRTUAL,
      tripled INTEGER AS (qty * 3) STORED
    )
    """);
await db.ExecuteAsync(
    $"INSERT INTO items(qty) VALUES (2)");
await PrintAsync();
await db.ExecuteAsync($"UPDATE items SET qty = 4");
await PrintAsync();
 
async Task PrintAsync()
{
    var row = await db.ScalarAsync<string>($"""
        SELECT qty || ', ' || doubled
                   || ', ' || tripled
        FROM items
        """);
    Console.WriteLine(row);
}
Text
2, 4, 6
4, 8, 12

The full program prints qty, doubled, and tripled before and after the update. It writes only qty; both generated columns reflect the new quantity.

Variations

Add a VIRTUAL column to the existing table. Append this to the program; selecting quadrupled for qty = 4 gives 16.

C#
await db.ExecuteAsync($"""
    ALTER TABLE items ADD COLUMN
        quadrupled INTEGER AS (qty * 4) VIRTUAL
    """);

The short answer

Declare column_name INTEGER AS (expression) in CREATE TABLE. Add VIRTUAL to calculate it on read or STORED to calculate and keep it on write. SQLite defaults to VIRTUAL if you omit both. In either case, insert and update the source column, then select the generated column by name.

What is the difference between VIRTUAL and STORED generated columns in SQLite?

A VIRTUAL column evaluates the expression when its value is read. A STORED column evaluates it when the row is written and keeps the result in the database. Both give values based on the current row. In the example, changing qty to 4 changes doubled to 8 and tripled to 12. The stored value isn’t a copy that your application has to refresh.

Use VIRTUAL when the expression is simple and there’s no reason to keep its value separately in the row. Consider STORED when you want to evaluate a more involved expression on write rather than on read, and can allocate the space in the file. Both kinds can be indexed; see SQLite’s generated-column reference.

Can you add a generated column with SQLite ALTER TABLE?

You can add a VIRTUAL generated column to an existing table with ALTER TABLE … ADD COLUMN. The example adds quadrupled after the table already contains a row. If you need to add a STORED generated column to a populated table, put the expression in the new table definition as part of a table rebuild. Copy the source columns into the new table, not the values from the generated columns.

The expression can use other columns in the same row and scalar deterministic functions, which return the same result for the same inputs. It can’t run a subquery or aggregate across rows. For a total across several rows, use a query rather than trying to embed the total in a generated column.

All onelite recipes