---
title: "Create SQLite generated columns in C#"
url: https://recursiveai.org/onelite/cookbook/create-sqlite-generated-column/
description: "Declare VIRTUAL and STORED SQLite generated columns, update the source value, and read the recalculated results from C#."
version: "1.0.0"
---

# 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.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/create-sqlite-generated-column/create-sqlite-generated-column.zip)

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

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onelite/cookbook/create-sqlite-generated-column/create-sqlite-generated-column.zip), then `dotnet run`.

Prints the doubled and tripled quantities after the update:

```text
8, 12
```

## The full program

```csharp
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`.

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

## Related recipes

- [Create a SQLite database in C#](https://recursiveai.org/onelite/cookbook/create-sqlite-database-csharp/)
- [Upsert a SQLite row in C#](https://recursiveai.org/onelite/cookbook/upsert-sqlite-row-csharp/)

## 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](https://www.sqlite.org/gencol.html#capabilities).

## 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](https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes). 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.
