README
sqlkit
Helpers on top of SSPUR's built-in SQLite (db) for the parts of database code every service writes again: a query builder for common CRUD that only ever produces SQL text plus parameters, keyset (seek) pagination with opaque cursors, upserts, batched inserts that stay under SQLite's parameter limit, versioned up/down migrations with checksums and dry runs, and helpers for tests (an in-memory database with migrations applied, fixtures, and a transaction that is always rolled back).
Values never enter the SQL text: every value is a ? parameter, and every table and column name is checked (ASCII letters, digits and underscores, not in SQLite's sqlite_ namespace) and double-quoted, so reserved words such as order work as column names. The builder refuses an UPDATE or DELETE without a condition unless you call update_all or delete_all.
Install
$ sspur add sqlkit
Example
use sqlkit.{vint, vtext, eq, like_substr, asc, desc, values_all, columns, select_where, order_by, update_where}
type Book = {id: Int, title: Str, year: Int}
fn schema() -> List[sqlkit.Migration]
= [sqlkit.migration(1, "books", "create table books (id integer primary key, title text not null, year integer not null)", "drop table books"), sqlkit.migration(2, "by year", "create index books_year on books (year, id)", "drop index books_year")]
fn book_key(b: Book) -> List[sqlkit.Value]
= [vint(b.year), vint(b.id)]
fn main() -> Unit ! db, log, fail[DbErr], fail[sqlkit.SqlkitErr], fail[sqlkit.MigrateErr]
= do
c = db.open(":memory:")
ran = sqlkit.migrate_latest(c, schema())
log("applied: {ran.map(s => s.name)}")
titles = ["Dune", "Neuromancer", "Hyperion", "Foundation", "Snow Crash", "The Dispossessed"]
years = [1965, 1984, 1989, 1951, 1992, 1974]
n = sqlkit.insert_batch(c, sqlkit.insert("books", ["title", "year"]).values_all((0..6).map(i => [vtext(titles[i]), vint(years[i])])))
q = sqlkit.select_sql(sqlkit.select("books").columns(["title"]).select_where(like_substr("title", "an")).order_by([asc("title")]))
log(q.sql)
log("{db.query[Str](c, q.sql, q.params)}")
up = sqlkit.update_sql(sqlkit.update("books", [("title", vtext("Dune (1965)"))]).update_where(eq("title", vtext("Dune"))))
log("updated {sqlkit.exec(c, up)}")
k = sqlkit.keyset(sqlkit.select("books"), [desc("year"), asc("id")], 4)
first = sqlkit.keyset_sql(k, none)
p1 = sqlkit.keyset_page(k, none, db.query[Book](c, first.sql, first.params), book_key)
log("page 1: {p1.items.map(b => b.year)}")
next = sqlkit.keyset_sql(k, p1.after)
p2 = sqlkit.keyset_page(k, p1.after, db.query[Book](c, next.sql, next.params), book_key)
log("page 2: {p2.items.map(b => b.year)}, more: {p2.after.is_some}")
log("dry run to 1: {sqlkit.plan(c, schema(), 1).map(s => s.sql)}")
$ sspur run main.ssp
applied: ["books", "by year"]
SELECT "title" FROM "books" WHERE "title" LIKE ? ESCAPE '\' ORDER BY "title" ASC
["Neuromancer"]
updated 1
page 1: [1992, 1989, 1984, 1974]
page 2: [1965, 1951], more: false
dry run to 1: ["drop index books_year"]
The builder functions read best as methods (select(...).columns(...)), which works for the names you import with use.
How values are bound
db.query(c, sql, params) accepts a list literal of mixed types, but a list built at run time has one element type, so a builder can't hand over Int, Str and Bytes values as they are. sqlkit sends every value as text (Query.params is a List[Opt[Str]]) behind a typed placeholder, so each column receives exactly the type a native binding gives:
| Value | Placeholder | Parameter |
|---|
vint(n) | CAST(? AS INTEGER) | decimal text (all of Int, including the minimum) |
vreal(x) | CAST(? AS REAL) | shortest round-trip text; infinities and NaN are refused |
vtext(s) | ? | the text |
vbool(b) | CAST(? AS INTEGER) | 1 or 0 |
vtime(t) | ? | ISO 8601 with milliseconds, as the stdlib binds a Time (text order is time order) |
vblob(b) | unhex(?) | lower-case hex |
vdec(d) | ? | the decimal's text, as the stdlib binds a Dec |
vnull() | NULL | none |
The tests check the round trip of every type (typeof included), exact F64 round trips (0.1, 1/3, 5e-324, the largest double), and that an indexed column compared with a cast placeholder still uses its index (explain query plan).
API
Values
Value (SqlNull, SqlInt, SqlReal, SqlText, SqlBool, SqlTime, SqlBlob, SqlDec): a parameter value.vint, vreal, vtext, vbool, vtime, vblob, vdec, vnull: make one. vopt(o): none as NULL.placeholder(v): the placeholder SQL and parameter of one value.
Queries and errors
Query{sql, params}: pass to db.query[T](c, q.sql, q.params), db.one, db.opt or exec.exec(c, q): run a statement and return the rows changed.SqlkitErr: BadIdent{name, why}, BadQuery{msg} (malformed or unsafe statement), BadCursor{msg}.
Identifiers
ident_problem(s), is_ident(s): whether s is a valid plain identifier, and why not.is_keyword(s): whether s is one of SQLite's 147 keywords.quote_ident(s): s as a quoted identifier, for SQL you write by hand.name_sql(s): a checked name or qualifier.name. column_sql(s): a checked select-list column (*, t.*, name, t.name, ... as alias).
Conditions
Cond, Op (OpEq, OpNe, OpLt, OpLe, OpGt, OpGe).eq, ne, lt, le, gt, ge: compare a column with a value. cols_compare(a, op, b): two columns (excluded.n > t.n).row_compare(cols, op, values): SQLite row values, (a, b) > (?, ?).in_list, not_in: membership; an empty list matches nothing (in_list) or everything (not_in).like, not_like: a pattern you wrote, with ESCAPE '\'. escape_like(s): escape %, _ and \. like_substr, like_prefix, like_suffix: literal text matches.is_null, not_null, between, not_between.all_of, any_of, negate: AND (empty is true), OR (empty is false), NOT.raw(sql, args): SQL you wrote with bare ? placeholders (each becomes the argument's typed placeholder; ?NNN, :name and unbalanced quotes are refused).cond_sql(c): the SQL of a condition.
SELECT
Select, select(table), columns, distinct, select_where (joins with AND), order_by, limit, offset.Order, Nulls, asc, desc, nulls_first, nulls_last.select_sql, count_sql (count of the rows, ignoring order and limit), exists_sql.
INSERT and upserts
Insert, insert(table, cols), values, values_all, insert_returning, insert_sql.Conflict (ConflictFail, DoNothing{target}, DoUpdate{target, from_excluded, assign, only_if}), on_conflict_nothing, on_conflict_update, upsert(table, cols, row, key).insert_chunks(i, max): split rows into statements of at most max parameters. max_params(): 32766, SQLite's limit. insert_batch(c, i): insert any number of rows in one transaction.
UPDATE and DELETE
Update, update(table, sets), update_where, update_all, update_returning, update_sql.Delete, delete(table), delete_where, delete_all, delete_returning, delete_sql.
Keyset pagination
Keyset, keyset(base, keys, page_size): pages of base ordered by keys (mixed directions allowed; end with a unique column).keyset_sql(k, after), keyset_before_sql(k, before): the query for the next or previous page (one extra row tells whether more follow). A uniform direction uses a row-value comparison that an index on the keys serves; mixed directions expand to (a < ?) OR (a = ? AND b > ?).keyset_page, keyset_page_before: the KeyPage{items, after, before} from the rows.cursor(k, key), decode_cursor(k, c): opaque base64url cursors bound to the keyset (a cursor from another ordering is BadCursor). Cursors are not signed: a forged one only selects another page of the same query, through parameters.
Migrations
Migration{version, name, up, down}, migration(...): versions increase along the list, gaps allowed (timestamps work); down is "" when a step can't be undone.migrate_latest(c, ms), migrate_to(c, ms, target) (up or down; 0 undoes everything), rollback(c, ms, n): run and return the Steps, each step in its own transaction with its record. The whole plan is checked first, so an error changes nothing; a step another process ran meanwhile is skipped.plan(c, ms, target): the dry run. status(c, ms): each migration's MigrationState (Applied, Pending, Changed, Missing). current_version(c), latest(ms), validate(ms), checksum(m) (SHA-256 of up), migrations_table() (sqlkit_migrations, separate from db.migrate's sspur_migrations).Step, Direction (StepUp, StepDown), MigrationStatus, MigrateErr: DuplicateVersion, UnorderedVersion, ChangedMigration (an applied up was edited), UnknownApplied (applied but not in the list), OutOfOrder (a pending version older than the newest applied one), Irreversible, NoSuchTarget.
Tests
test_db(ms): a fresh in-memory database with the migrations applied.load_fixtures(c, inserts): insert fixture rows in one transaction.rolled_back(c, f): run a test body in a transaction that is always rolled back, and return its Bool.row_count(c, table).
Notes
- A migration's SQL runs inside a transaction, so statements SQLite refuses there (
VACUUM, PRAGMA foreign_keys) don't belong in one. - SQLite's
LIKE ignores ASCII case only; not_in with a NULL in the list is never true, as in SQL. rolled_back returns a Bool rather than any type: db.tx can't yet be called from a function that is generic in its result type.
Out of scope
Joins, grouping, subqueries and window functions in the builder (write that SQL by hand, with raw conditions or db.query directly); schema introspection and diffing; databases other than SQLite; reading rows generically (db.query[T] needs a concrete T, so the caller reads rows).
License
MIT
API
pub type Value = SqlNull | SqlInt{v: Int} | SqlReal{v: F64} | SqlText{v: Str} | SqlBool{v: Bool} | SqlTime{v: Time} | SqlBlob{v: Bytes} | SqlDec{v: Dec} derive Eq, Show
A value bound to a ? placeholder. db.query needs one element type in a non-literal parameter list, so every value is sent as text (Opt[Str]) behind a typed placeholder: CAST(? AS INTEGER) for SqlInt and SqlBool, CAST(? AS REAL) for SqlReal, unhex(?) for SqlBlob, ? for text, times (ISO 8601 with milliseconds, as the stdlib binds them) and decimals, and NULL for SqlNull. The column receives the same type as with a native binding.
fn vint(n: Int) -> Value
fn vreal(x: F64) -> Value
A real value. Infinities and NaN can't be stored by SQLite and are rejected when the query is built.
fn vtext(s: Str) -> Value
fn vbool(b: Bool) -> Value
A boolean value, stored as 0 or 1.
fn vtime(t: Time) -> Value
A time, stored as ISO 8601 text with milliseconds (2026-10-15T09:30:00.000Z), so text order is time order, exactly as the stdlib binds a Time.
fn vblob(b: Bytes) -> Value
fn vdec(d: Dec) -> Value
A decimal, stored as its text (12.50), as the stdlib binds a Dec.
fn vnull() -> Value
fn vopt(o: Opt[Value]) -> Value
none as NULL, some(v) as v: vopt(email.map(vtext)).
pub type SqlkitErr = BadIdent{name: Str, why: Str} | BadQuery{msg: Str} | BadCursor{msg: Str} derive Eq, Show
Why a statement could not be built: an identifier that is not allowed, a statement that is not well formed (or is unsafe, such as an UPDATE without WHERE), or a pagination cursor that does not belong to the query.
pub type Query = {sql: Str, params: List[Opt[Str]]} derive Eq, Show
SQL text and its parameters, ready for db.query[T](c, q.sql, q.params), db.one, db.opt or db.exec.
fn placeholder(v: Value) -> Query ! fail[SqlkitErr]
The placeholder and parameter for a value; see Value.
fn is_keyword(s: Str) -> Bool
Whether s is one of SQLite's 147 keywords, in any case. The builder quotes every identifier, so a column named order or key works; use this when you write SQL by hand.
fn ident_problem(s: Str) -> Opt[Str]
Why s is not allowed as a plain identifier, or none: 1 to 128 ASCII letters, digits and underscores, not starting with a digit, and not in SQLite's reserved sqlite_ namespace.
fn is_ident(s: Str) -> Bool
Whether s is a valid plain identifier (see ident_problem).
fn quote_ident(s: Str) -> Str
s as a quoted SQLite identifier: double quotes around it, with each " doubled. This is safe for any text; the builder uses it only on identifiers that passed ident_problem.
fn column_sql(s: Str) -> Str ! fail[SqlkitErr]
The SQL for one select-list column, checked: *, t.*, name, t.name, or any of those names followed by as alias.
fn name_sql(s: Str) -> Str ! fail[SqlkitErr]
The SQL for a table or column name, name or qualifier.name, checked and quoted.
Op type
pub type Op = OpEq | OpNe | OpLt | OpLe | OpGt | OpGe derive Eq, Show
pub type Cond = Compare{col: Str, op: Op, v: Value} | CompareCols{a: Str, op: Op, b: Str} | RowCompare{cols: List[Str], op: Op, vs: List[Value]} | InList{col: Str, vs: List[Value], negated: Bool} | Like{col: Str, pattern: Str, negated: Bool} | NullTest{col: Str, negated: Bool} | Between{col: Str, lo: Value, hi: Value, negated: Bool} | AllOf{cs: List[Cond]} | AnyOf{cs: List[Cond]} | Negate{c: Cond} | RawCond{sql: Str, args: List[Value]} derive Eq, Show
A WHERE condition. Build them with the functions below (eq, in_list, all_of, ...); every value becomes a parameter.
fn eq(col: Str, v: Value) -> Cond
col = v. Comparing with NULL is never true in SQL: use is_null for that.
fn ne(col: Str, v: Value) -> Cond
fn lt(col: Str, v: Value) -> Cond
fn le(col: Str, v: Value) -> Cond
fn gt(col: Str, v: Value) -> Cond
fn ge(col: Str, v: Value) -> Cond
fn cols_compare(a: Str, op: Op, b: Str) -> Cond
a op b for two columns, such as excluded.version > items.version in an upsert.
fn row_compare(cols: List[Str], op: Op, vs: List[Value]) -> Cond
(c1, c2, ...) op (v1, v2, ...), SQLite's row-value comparison (lexicographic), which can use an index on those columns.
fn in_list(col: Str, vs: List[Value]) -> Cond
col IN (v1, v2, ...). An empty list matches nothing.
fn not_in(col: Str, vs: List[Value]) -> Cond
col NOT IN (v1, v2, ...). An empty list matches every row. As in SQL, a NULL in the list makes the test unknown (false) for every row.
fn like(col: Str, pattern: Str) -> Cond
col LIKE pattern ESCAPE '\' with a pattern you wrote, where % and _ are wildcards (escape literal text with escape_like). SQLite's LIKE ignores ASCII case.
fn not_like(col: Str, pattern: Str) -> Cond
col NOT LIKE pattern ESCAPE '\'.
fn escape_like(s: Str) -> Str
s with the LIKE wildcards % and _ and the escape character \ escaped, so it matches itself literally.
fn like_substr(col: Str, s: Str) -> Cond
Rows whose col contains s literally (ASCII case ignored).
fn like_prefix(col: Str, s: Str) -> Cond
Rows whose col starts with s literally (ASCII case ignored).
fn like_suffix(col: Str, s: Str) -> Cond
Rows whose col ends with s literally (ASCII case ignored).
fn is_null(col: Str) -> Cond
fn not_null(col: Str) -> Cond
fn between(col: Str, lo: Value, hi: Value) -> Cond
col BETWEEN lo AND hi, both ends included.
fn not_between(col: Str, lo: Value, hi: Value) -> Cond
col NOT BETWEEN lo AND hi.
fn all_of(cs: List[Cond]) -> Cond
Every condition holds; an empty list is true.
fn any_of(cs: List[Cond]) -> Cond
At least one condition holds; an empty list is false.
fn negate(c: Cond) -> Cond
fn raw(sql: Str, args: List[Value]) -> Cond
An escape hatch: SQL you wrote, with one bare ? per argument (outside quoted text), such as raw("lower(email) = ?", [vtext(e)]). Each ? becomes the argument's typed placeholder. Never build sql from input; that is what the arguments are for.
fn cond_sql(c: Cond) -> Query ! fail[SqlkitErr]
The SQL and parameters of a condition.
pub type Nulls = NullsDefault | NullsFirst | NullsLast derive Eq, Show
pub type Order = {col: Str, desc: Bool, nulls: Nulls} derive Eq, Show
fn asc(col: Str) -> Order
fn desc(col: Str) -> Order
fn nulls_first(o: Order) -> Order
The same order with NULLs first.
fn nulls_last(o: Order) -> Order
The same order with NULLs last.
pub type Select = {table: Str, column_list: List[Str], is_distinct: Bool, where_cond: Opt[Cond], order_terms: List[Order], row_limit: Opt[Int], row_offset: Opt[Int]} derive Eq, Show
A SELECT. Build one with select and the functions after it, or with a literal and with.
fn select(table: Str) -> Select
fn columns(s: Select, cols: List[Str]) -> Select
The columns to select (see column_sql); an empty list is *.
fn distinct(s: Select) -> Select
fn select_where(s: Select, c: Cond) -> Select
Adds a condition, joined with AND to any condition already there.
fn order_by(s: Select, os: List[Order]) -> Select
Adds ORDER BY terms after those already there.
fn limit(s: Select, n: Int where _ >= 0) -> Select
fn offset(s: Select, n: Int where _ >= 0) -> Select
Skips the first n rows (LIMIT -1 OFFSET n when there is no limit).
fn select_sql(s: Select) -> Query ! fail[SqlkitErr]
The SQL and parameters of a SELECT.
fn count_sql(s: Select) -> Query ! fail[SqlkitErr]
SELECT count(*) over the rows s selects, ignoring its order, limit and offset; read it with db.one[Int].
fn exists_sql(s: Select) -> Query ! fail[SqlkitErr]
SELECT EXISTS (...) over the rows s selects, 0 or 1; read it with db.one[Bool].
pub type Conflict = ConflictFail | DoNothing{target: List[Str]} | DoUpdate{target: List[Str], from_excluded: List[Str], assign: List[(Str, Value)], only_if: Opt[Cond]} derive Eq, Show
What an INSERT does when a row conflicts with a unique key: fail (the default), do nothing, or update. DoUpdate sets each of from_excluded to the value the insert proposed (excluded.col), each pair of assign to a value, and applies only where only_if holds.
pub type Insert = {into: Str, cols: List[Str], rows: List[List[Value]], on_conflict: Conflict, returning_cols: List[Str]} derive Eq, Show
An INSERT of one or more rows.
fn insert(table: Str, cols: List[Str]) -> Insert
INSERT INTO table (cols...) with no rows yet.
fn values(i: Insert, row: List[Value]) -> Insert
Adds a row; it must have one value per column.
fn values_all(i: Insert, rows: List[List[Value]]) -> Insert
fn on_conflict_nothing(i: Insert, target: List[Str]) -> Insert
ON CONFLICT (target) DO NOTHING; an empty target is any uniqueness conflict.
fn on_conflict_update(i: Insert, target: List[Str], set_cols: List[Str]) -> Insert
ON CONFLICT (target) DO UPDATE SET c = excluded.c for each of set_cols.
fn upsert(table: Str, cols: List[Str], row: List[Value], key: List[Str]) -> Insert
The upsert of row into table keyed by key: inserts it, or updates every other column of the existing row.
fn insert_returning(i: Insert, cols: List[Str]) -> Insert
RETURNING these columns (see column_sql); read the rows with db.query[T] or db.one[T].
fn insert_sql(i: Insert) -> Query ! fail[SqlkitErr]
The SQL and parameters of an INSERT.
fn insert_chunks(i: Insert, max_params: Int where _ > 0) -> List[Insert] ! fail[SqlkitErr]
The insert split into inserts of consecutive rows, each with at most max_params parameters (SQLite's limit is 32766). A single row with more parameters is an error.
fn max_params() -> Int
SQLite's limit on parameters in one statement.
fn insert_batch(c: Db, i: Insert) -> Int ! db, fail[DbErr], fail[SqlkitErr]
Inserts all the rows of i in one transaction, in statements under SQLite's parameter limit, and returns the number of rows changed.
pub type Update = {table: Str, set_list: List[(Str, Value)], where_cond: Opt[Cond], returning_cols: List[Str], all_rows: Bool} derive Eq, Show
An UPDATE. Without a condition it is refused unless all_rows is set (update_all).
fn update(table: Str, sets: List[(Str, Value)]) -> Update
UPDATE table SET col = value, ....
fn update_where(u: Update, c: Cond) -> Update
Adds a condition, joined with AND to any condition already there.
fn update_all(u: Update) -> Update
Allows the update to change every row (no WHERE).
fn update_returning(u: Update, cols: List[Str]) -> Update
fn update_sql(u: Update) -> Query ! fail[SqlkitErr]
The SQL and parameters of an UPDATE.
pub type Delete = {table: Str, where_cond: Opt[Cond], returning_cols: List[Str], all_rows: Bool} derive Eq, Show
A DELETE. Without a condition it is refused unless all_rows is set (delete_all).
fn delete(table: Str) -> Delete
fn delete_where(d: Delete, c: Cond) -> Delete
Adds a condition, joined with AND to any condition already there.
fn delete_all(d: Delete) -> Delete
Allows the delete to remove every row (no WHERE).
fn delete_returning(d: Delete, cols: List[Str]) -> Delete
fn delete_sql(d: Delete) -> Query ! fail[SqlkitErr]
The SQL and parameters of a DELETE.
fn exec(c: Db, x: Query) -> Int ! db, fail[DbErr]
Runs a statement that returns no rows (db.exec with its parameters) and returns the number of rows changed.
pub type Keyset = {base: Select, keys: List[Order], page_size: Int where _ > 0} derive Eq, Show
Keyset (seek) pagination over base ordered by keys: each page starts after (or before) the key of a row instead of at an offset, so pages are stable under inserts and cost the same deep into the table. The keys must identify a row (end them with the primary key) and must not be NULL; directions may be mixed. base gives the table, columns and filter, and must not set its own order, limit or offset.
fn keyset(base: Select, keys: List[Order], page_size: Int where _ > 0) -> Keyset
Keyset pagination of base by keys, page_size rows a page.
pub type KeyPage[T] = {items: List[T], after: Opt[Str], before: Opt[Str]} derive Eq, Show
A page: its rows in key order, the cursor for the page after it (after, when there may be more rows) and the cursor for the page before it (before, when this page was reached through a cursor).
fn cursor(k: Keyset, key: List[Value]) -> Str ! fail[SqlkitErr]
The opaque cursor for a row whose key columns hold key (one value per key, in order). It is base64url JSON, not signed: a changed cursor can only select another page of the same query.
fn decode_cursor(k: Keyset, c: Str) -> List[Value] ! fail[SqlkitErr]
The key values in a cursor, checked against the keyset it must belong to.
fn keyset_sql(k: Keyset, after: Opt[Str]) -> Query ! fail[SqlkitErr]
The query for the page after after (the first page for none), ordered by the keys, with one row more than a page so keyset_page can tell whether more follow.
fn keyset_before_sql(k: Keyset, before: Opt[Str]) -> Query ! fail[SqlkitErr]
The query for the page before before (the last page for none). Its rows come in reverse key order; keyset_page_before puts them back.
fn keyset_page[T](k: Keyset, after: Opt[Str], rows: List[T], key: T -> List[Value]) -> KeyPage[T] ! fail[SqlkitErr]
The page from the rows keyset_sql(k, after) returned; key gives a row's key values.
fn keyset_page_before[T](k: Keyset, before: Opt[Str], rows: List[T], key: T -> List[Value]) -> KeyPage[T] ! fail[SqlkitErr]
The page from the rows keyset_before_sql(k, before) returned, in key order.
pub type Migration = {version: Int where _ > 0, name: Str, up: Str, down: Str} derive Eq, Show
One versioned schema change: up applies it and down undoes it ("" when it can't be undone). Each may hold several statements. Versions are positive and increase along the list; they need not be consecutive (timestamps work).
fn migration(version: Int where _ > 0, name: Str, up: Str, down: Str) -> Migration
pub type MigrateErr = DuplicateVersion{version: Int} | UnorderedVersion{version: Int} | ChangedMigration{version: Int, name: Str} | UnknownApplied{version: Int} | OutOfOrder{version: Int} | Irreversible{version: Int, name: Str} | NoSuchTarget{version: Int} derive Eq, Show
Why migrations can't run: the list is not in order or repeats a version; an applied migration was edited since (its checksum differs) or is no longer in the list; a pending one is older than one already applied; a step down has no down; or the target version is not in the list.
pub type Direction = StepUp | StepDown derive Eq, Show
Whether a step applies or undoes its migration.
pub type Step = {version: Int, name: Str, direction: Direction, sql: Str} derive Eq, Show
One step of a plan: the migration, which way, and the SQL that runs.
pub type MigrationState = Applied{at: Str} | Pending | Changed{at: Str} | Missing{at: Str} derive Eq, Show
Where a migration stands in a database: applied (with the time it was applied), pending, applied but edited since, or applied but missing from the list.
pub type MigrationStatus = {version: Int, name: Str, state: MigrationState} derive Eq, Show
fn migrations_table() -> Str
The table sqlkit records applied migrations in (db.migrate uses its own, sspur_migrations).
fn checksum(m: Migration) -> Str
The SHA-256 of a migration's up SQL, in hex: what is recorded when it is applied and compared afterwards. Editing down later is allowed; editing up is ChangedMigration.
fn validate(ms: List[Migration]) -> Unit ! fail[MigrateErr]
Checks that versions increase strictly along the list.
fn latest(ms: List[Migration]) -> Int
The newest version in the list, or 0 for none.
fn current_version(c: Db) -> Int ! db, fail[DbErr]
The newest applied version, or 0 for none.
fn status(c: Db, ms: List[Migration]) -> List[MigrationStatus] ! db, fail[DbErr]
Each migration of the list with its state, then applied versions the list doesn't have.
fn plan(c: Db, ms: List[Migration], target: Int where _ >= 0) -> List[Step] ! db, fail[DbErr], fail[MigrateErr]
The steps that would bring the database to target (0 undoes everything), without changing anything: the dry run. Up steps run oldest first, down steps newest first.
fn migrate_to(c: Db, ms: List[Migration], target: Int where _ >= 0) -> List[Step] ! db, fail[DbErr], fail[MigrateErr]
Brings the database to target: applies or undoes migrations, each step in its own transaction with its record, and returns the steps that ran. The whole plan is checked before the first step, so an error changes nothing. A step another process ran meanwhile is skipped.
fn migrate_latest(c: Db, ms: List[Migration]) -> List[Step] ! db, fail[DbErr], fail[MigrateErr]
Applies every pending migration.
fn rollback(c: Db, ms: List[Migration], n: Int where _ >= 0) -> List[Step] ! db, fail[DbErr], fail[MigrateErr]
Undoes the newest n applied migrations.
fn test_db(ms: List[Migration]) -> Db ! db, fail[DbErr], fail[MigrateErr]
A fresh in-memory database with every migration applied.
fn load_fixtures(c: Db, fixtures: List[Insert]) -> Int ! db, fail[DbErr], fail[SqlkitErr]
Inserts fixture rows in one transaction and returns the number of rows changed.
fn row_count(c: Db, table: Str) -> Int ! db, fail[DbErr], fail[SqlkitErr]
The number of rows in table.
fn rolled_back[e](c: Db, f: () -> Bool ! e) -> Bool ! db, fail[DbErr], e
Runs a test body inside a transaction that is always rolled back, and returns its result, so tests can share one database without seeing each other's writes. Nested db.tx calls in f become savepoints. (The result is a Bool, what a test needs: db.tx can't yet be wrapped by a function generic in its result type.)
