SQLite

Connect

Whole app on a file:

SOLI_DB_ADAPTER=sqlite
DATABASE_URL=sqlite://db/app.sqlite3
# optional; default 5, forced to 1 for :memory:
SOLI_DB_POOL_SIZE=5

Aliases: sqlite, sqlite3.

A named connection:

[connections.analytics]
adapter = "sqlite"
url = "sqlite://db/analytics.sqlite3"

class Event < Model
  connection "analytics"
end

URL forms

A query suffix (?mode=rwc) is stripped from the path. Every connection opens in WAL mode with a 10-second busy timeout and foreign keys on. A transaction is BEGIN IMMEDIATE so it cannot fail to upgrade a read into a write. :memory: pins the pool to one connection — a second one would be a second, empty database.

Create and drop

soli db:create              # create the file and its parent directory
soli db:migrate up
soli db:schema:dump
soli db:drop                # delete the file plus -wal / -shm sidecars

db:drop removes the -wal and -shm files too: leaving them would resurrect committed data into the next file of the same name. :memory: needs no file — create/drop is a no-op. Both accept --connection NAME.

Document tables

table <collection>
  _key  TEXT PRIMARY KEY
  doc   TEXT NOT NULL        # JSON, read with json1

SQLite has no JSON type. Soli stores JSON as text and reads it with json1 (->>, json_patch, json_set), which every write goes through. String equality in .where({ "status": "open" }) compares on the text extract, which is what a document index holds. Placeholders in raw SQL are ?.

Indexes

index "status" (or soli db:indexes) creates an expression index:

CREATE INDEX IF NOT EXISTS idx_posts_status ON posts ((doc ->> '$.status'))

The planner uses that index when the predicate is the same expression. Multi-field and unique: work the same way. Numbers and booleans keep JSON comparison and are not covered by that index.

Background jobs

The queue lives in _jobs in the same file. SQLite has no SKIP LOCKED, so a claim takes the database write lock (BEGIN IMMEDIATE) for the length of the claim — exclusive by construction. Leases, retries, backoff, and single-winner cron firing behave like the other adapters. See Jobs. A busy write path across many processes belongs on Postgres: WAL lets readers run during a write, but writers serialize.

Schema dump

soli db:schema:dump         # writes db/schema.sql
soli db:schema:load         # recreate a fresh database from that file

The dump is sqlite_master (CREATE TABLE / CREATE INDEX as originally executed), plus a -- versions: header so load records applied migrations without replaying every file.

Column-aware models

class Order < Model
  table "orders"
end

Introspection uses PRAGMA table_info. An INTEGER PRIMARY KEY is generated (it aliases the rowid, with or without AUTOINCREMENT); a key of any other type must be supplied.

SQLite enforces no types. It applies affinity to the declared type, and any column can hold any value. Soli reads the declared type to decide how to convert, and reads each value by what it actually is — so an INTEGER stored in a TEXT column still comes back as a number. A DATETIME column holding a unix timestamp (seconds or milliseconds) is converted to RFC 3339.

There is no native ILIKE. Hash .where({ "email": { "ilike": "%@x.com" } }) compiles to LOWER("email") LIKE LOWER(?). Migrations cannot add a UNIQUE or NOT NULL-without-default column to an existing table — SQLite says so, with the way around it.

Backups

Copy a live WAL database with .backup or VACUUM INTO, not cp. A plain copy can miss frames still in the WAL.

Honest limits

• One writer at a time. Fine for a single node; not a substitute for Postgres under multi-process write load.
• No exact numeric type. See above.
• No graph, vector, fulltext, geo, columnar, timeseries, or raw SDBQL. Those stay on SoliDB. Use Model.find_by_sql for SQL the portable surface cannot express.
• grouped {} read-coalescing is SoliDB-only.
• Composite primary keys are refused at boot on column-aware models.
• encrypts and STI work in column mode: encrypted fields must be text columns; STI subclasses need a string type column.

The shared portable surface is documented under SQL document backends.

See also