Modern SQLite: Generated columns

This post is part of the Modern SQLite series, where I write about handy SQLite features you might not have heard about.

Generated columns are calculated based on other columns in the same table. For example, we can calculate the failure rate based on the number of queries:

create table stats (
    date text,
    n_total int,
    n_failed int,
    fail_perc as (n_failed*100.0 / n_total)
);

insert into stats values
('2024-05-01', 100, 15),
('2024-05-02', 150, 27),
('2024-05-02', 110, 22);

select date, fail_perc from stats;

┌────────────┬───────────┐
│    date    │ fail_perc │
├────────────┼───────────┤
│ 2024-05-01 │ 15.0      │
│ 2024-05-02 │ 18.0      │
│ 2024-05-02 │ 20.0      │
└────────────┴───────────┘

Another common use case is to extract a certain JSON path into a separate column and optionally index it:

create table events (
    id integer primary key,
    event blob,
    etime text as (event ->> 'time'),
    etype text as (event ->> 'type')
);

create index events_time on events(etime);

insert into events(event) values
(jsonb('{"time": "2024-05-01", "type": "credit"}')),
(jsonb('{"time": "2024-05-02", "type": "debit"}')),
(jsonb('{"time": "2024-05-03", "type": "close"}'));

select etime, etype from events;

┌────────────┬────────┐
│   etime    │ etype  │
├────────────┼────────┤
│ 2024-05-01 │ credit │
│ 2024-05-02 │ debit  │
│ 2024-05-03 │ close  │
└────────────┴────────┘

This allows using SQLite as a document database:

Store the document as a JSON blob.
Extract fields as generated columns as needed.
Build indexes on extracted fields for faster queries.

Generated columns can be computed on the fly or stored on disk. Stored are rarely used in practice.

Available since SQLite 3.31 (Jan 2020).

Documentation

──

Interactive examples in this post are powered by codapi — an open source tool I'm building. Use it to embed live code snippets into your product docs, online course or blog.

★ Subscribe to keep up with new posts.