SQLite

Status: partially supported. SQLite backs a minority of the stores and is not a replacement for Postgres, which remains the only backend able to run the whole system.

Where SQLite is used

Three stores accept Sqlite as their implementation: app_store, mailer_store and twinoid_store. Every other store only accepts Memory or Postgres.

[backend]
store = "Memory"
mailer_store = "Sqlite"

[sqlite]
file = "./eternaltwin.dev.sqlite"
max_connections = 50

The [sqlite] section has two keys:

Key	Default	Role
`file`	`./eternaltwin.<profile>.sqlite`	Database file
`max_connections`	`50`	Size of the connection pool

The path must either be relative (starting with ./ or ../) or a file: URL.

SQLite is also what powers those three stores under Memory: MemAppStore, MemMailerStore and MemTwinoidStore are the SQLite stores running against an in-memory database. This is why SQLite is a workspace dependency even when the configuration never mentions it.

Limits

There is no migration chain for SQLite. A single script, crates/db_schema/sqlite/create/latest.sql, creates the latest state, and the backend applies it to the configured file on startup. Treat a SQLite database as disposable: it is re-created rather than upgraded.
The SQLite app store is a stub. get_app returns NotFound and the outbound app event handlers return not implemented.
The SQLite schema only covers the outbound email tables and the Twinoid archive. Users, authentication, the forum, OAuth, Dinoparc and Hammerfest have no SQLite tables at all.

Porting queries from Postgres

SQLite is missing a few features compared to Postgres. The sections below provide guidance when adapting SQL queries written for Postgres so they are compatible with SQLite. They are the reference the SQLite schema points at.

UUID

-- Postgresql
create table t (
  "foo" uuid not null
)

-- SQLite
create table t (
  "foo" blob not null check (length(foo) = 16)
)

Range, Period

Eternaltwin uses ranges extensively. The period types in particular are defined as ranges of timestamps and used to track when values are valid. For SQLite, replace columns of type range with a pair of columns with the _start and _end suffixes and a check for the order.

PeriodLower

-- PostgreSQL
create table t (
  "foo" period_lower not null
)

-- SQLite
create table t (
  "foo_start" text not null check (foo_start = strftime('%FT%T+00:00', foo_start)),
  "foo_end" text null check (foo_end = strftime('%FT%T+00:00', foo_end)),
  constraint foo_ck check (foo_start < foo_end)
)

Membership

-- PostgreSQL
select ... where range @> item;

-- SQLite
select ... where range_start <= item and item < range_end;

Overlap

-- PostgreSQL
select ... where range0 && range1;

-- SQLite
select ... where range0_start < range1_end and range1_start < range0_end;
-- variant with parameters
select ... where ? < range1_end and range1_start < ?;

Enum

Use a text field with checked values.

Timestamps

Postgres instant columns become text columns holding the RFC 3339 form, checked with strftime('%FT%T+00:00', value) so that lexicographic order matches chronological order.

Tables

Declare tables strict so that SQLite enforces the declared column types, as the existing SQLite schema does.