Lone Badger Tech

SQLite

SQLite database files, with the SQLite engine built in: nothing else to install.

trek init --provider sqlite
trek add create-product
trek deploy
trek.yaml
provider: sqlite
sqlite:
  minVersion: "3.26"                    # the oldest SQLite that opens these databases (default 3.26)
defaultEnvironment: local
environments:
  local:
    database: shop.db                   # relative to the folder you run trek in
  prod:
    database: /srv/shop/data/shop.db
    protected: true
    connectionProperties:               # any Microsoft.Data.Sqlite connection-string keyword
      Foreign Keys: true
      Default Timeout: 60

database is the file. A relative path starts at the folder you run trek in, as with the sqlite3 shell, and ~/ is your home folder. A file that does not exist yet is created. You can give connectionString: Data Source=… instead; database wins when both are set. {db} is the file name without its extension.

Everything else is the same as on the other providers: the plan, hash certification, revert from stored scripts, environments, variables, bundles, diff-schema, generated changes (add --from-diff, diff-schema --script), rework and repair, and imports.

What does not apply

SQLite has no server, so host, port, user, password, authentication, tls and ssh are errors in an SQLite environment. An in-memory database (:memory:, Mode=Memory) is refused too: it disappears when the connection closes, and the journal with it. Encrypted (SQLCipher) databases are not supported.

SQLite versions

trek always runs the SQLite built into it, the same recent version on every machine. But your application opens the same file with the SQLite it was built with, and that is often years older: programs that use the system library get what the operating system ships, such as 3.26 on RHEL 8, 3.34 on RHEL 9, 3.37 on Ubuntu 22.04, 3.40 on Debian 12 and 3.45 on Ubuntu 24.04. Every SQLite 3 reads every SQLite 3 file, but an older engine rejects SQL it does not know, and a table that uses a newer feature (a STRICT table or a generated column) cannot be opened by an engine without it.

sqlite.minVersion in trek.yaml is the oldest SQLite that opens your databases; the default is 3.26, the oldest trek supports. Write it in quotes ("3.30"): unquoted, YAML reads 3.30 as 3.3.

  • Changes trek writes (add --from-diff, diff-schema --script) use only SQL that version understands. Below 3.35 a dropped column is a table rebuild rather than ALTER TABLE … DROP COLUMN.
  • Lint warns (TREK008) when a script uses SQL newer than minVersion: NULLS FIRST/LAST and FILTER (3.30), generated columns (3.31), iif() (3.32), UPDATE … FROM (3.33), DROP COLUMN, RETURNING and MATERIALIZED CTEs (3.35), STRICT tables (3.37), ->/->>, unixepoch() and format() (3.38), RIGHT/FULL JOIN and IS DISTINCT FROM (3.39), unhex() (3.41), timediff() and octet_length() (3.43), concat() and string_agg() (3.44), and JSONB functions (3.45).
  • If every program that opens the database has a newer SQLite (it bundles its own, as many drivers do), raise minVersion to match.

trek's tests run against the SQLite of each of those distributions, from 3.26 up, as well as the built-in engine on Windows, macOS and Linux.

On Linux the built-in engine needs glibc 2.33 or later. On older systems (RHEL 8, Ubuntu 20.04, Debian 11) trek uses the system's libsqlite3 instead, when it is 3.26 or later: install sqlite-libs or libsqlite3-0 if it is missing. To use a particular library, set TREK_SQLITE_LIBRARY to its path. trek doctor shows which engine is in use.

Statements and transactions

trek splits scripts into statements the way the sqlite3 shell does: at semicolons, except inside strings, quoted names, comments and CREATE TRIGGER … BEGIN … END; bodies. A change runs in one transaction (BEGIN IMMEDIATE), and SQLite's DDL is transactional, so a failure leaves nothing behind. Errors name the line of the statement that failed.

ALTER TABLE only renames tables and columns, adds a column and (from SQLite 3.35) drops a simple one. For other table changes, rebuild the table (trek add --from-diff writes the rebuild for you). If other tables have foreign keys to the table you rebuild, switch foreign keys off for it, which only works outside a transaction:

changes/product-price-check/deploy.sql
PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE new_product (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price NUMERIC NOT NULL CHECK (price >= 0));
INSERT INTO new_product (id, name, price) SELECT id, name, price FROM product;
DROP TABLE product;
ALTER TABLE new_product RENAME TO product;
CREATE INDEX product_name_idx ON product (name);
COMMIT;
PRAGMA foreign_keys = ON;
trek.plan
product-price-check [create-product] !notx

Repeatables: SQLite has no CREATE OR REPLACE, so write DROP VIEW IF EXISTS v; and then CREATE VIEW v … (and the same for triggers). A verify script selects from what must exist, for example SELECT email FROM customer LIMIT 0;: SQLite raises "no such column" when it is missing.

Linter

CodeSeverityFinding
TREK001ErrorVACUUM, ATTACH, DETACH, PRAGMA journal_mode and PRAGMA foreign_keys (which does nothing inside a transaction) in a change that is not !notx
TREK003ErrorBEGIN, COMMIT, END or ROLLBACK in a transactional change: it would end trek's transaction. (SAVEPOINT and ROLLBACK TO are fine, and so is BEGIN … END in a trigger.)
TREK005Errorsqlite3 shell dot-commands such as .read, .mode and .import: they are not SQL
TREK008WarningSQL that needs a newer SQLite than sqlite.minVersion (SQLite versions)

Schema fingerprint

The fingerprint covers tables (column by column, with their constraints, WITHOUT ROWID and STRICT), virtual tables, indexes, views and triggers. Definitions are compared as written, ignoring layout, comments and identifier quoting, so a column added with ALTER TABLE fingerprints the same as the same column written in CREATE TABLE. Left out: SQLite's own tables (sqlite_*), the shadow tables of virtual tables, and the trek journal.

Journal and lock

SQLite has no schemas, so the journal tables are prefixed instead: trek_changes, trek_repeatables, trek_events, trek_meta and trek_schema_snapshots (the prefix is stateSchema). SQLite's own locks last one transaction and a deploy is many, so trek holds an exclusive lock on a file next to the database, shop.db.trek-lock, for the whole run. The file stays after the run (deleting it could let two deployers lock different files); add *.trek-lock to .gitignore. The lock works between processes on one machine; it is not reliable on network file shares, where SQLite itself is not either.

trek doctor shows the SQLite version, the file, the journal mode, the encoding and whether foreign keys are on. The SQLite built into trek enforces foreign keys unless the connection turns them off (connectionProperties: { Foreign Keys: false }).