santoku-sqlite-migrate

versioned, idempotent SQL migrations for santoku-sqlite: one function that applies missing migrations in version order, forward-only, in a single transaction

santoku-sqlite-migrate is versioned schema migration in one function: pass a santoku-sqlite handle and a table mapping names to SQL strings, and it applies the ones that have not run yet, in version order, inside a single transaction, recording each in a bookkeeping table. There is no down direction: migrations are forward-only, append a new numbered one and never edit an applied one, and when a change is too deep to express as SQL over live data, wipe and rebuild under a schema epoch instead, shown at the end. The first five examples run live in this page against an in-memory database; the consumer patterns after them touch files or build machinery and are shown for reading.

the whole API: migrate(db, migrations)

requiring santoku.sqlite.migrate returns one function. Keys name the migrations, values are SQL scripts. It ensures a migrations(id integer primary key, filename text not null) table, applies what is missing in version order, and records each name; id preserves application order.

local sqlite = require("santoku.sqlite.db")
local sql = require("santoku.sqlite")
local migrate = require("santoku.sqlite.migrate")
local db = sql(sqlite.open_memory())
migrate(db, {
  ["0.0.1"] = "create table items (id integer primary key, name text);",
  ["0.0.2"] = "alter table items add column done integer not null default 0;",
})
db.runner("insert into items (name) values (?)")("write docs")
local applied = db.all("select id, filename from migrations order by id", true)()
for i = 1, #applied do
  print(applied[i].id, applied[i].filename)
end
return db.getter("select count(*) from items")()

idempotent: re-running is a no-op

Applied names are skipped, so calling migrate again with the same table does nothing, even when a migration has side effects like inserting rows. That makes it safe to run unconditionally at every startup.

local sqlite = require("santoku.sqlite.db")
local sql = require("santoku.sqlite")
local migrate = require("santoku.sqlite.migrate")
local db = sql(sqlite.open_memory())
local m = {
  ["0.0.1"] = "create table c (n integer); insert into c (n) values (1);",
}
migrate(db, m)
migrate(db, m)
migrate(db, m)
return db.getter("select count(*) from c")()

forward-only: append, never edit

A later call with a superset applies only the new keys. Since applied migrations are matched by name alone, editing the body of an already-applied migration changes nothing on existing databases: new schema work is always a new numbered entry.

local sqlite = require("santoku.sqlite.db")
local sql = require("santoku.sqlite")
local migrate = require("santoku.sqlite.migrate")
local db = sql(sqlite.open_memory())
local first = "create table d (n integer); insert into d (n) values (1);"
migrate(db, { ["0.0.1"] = first })
migrate(db, {
  ["0.0.1"] = first,
  ["0.0.2"] = "insert into d (n) values (2);",
})
local rows = db.all("select n from d order by n", true)()
for i = 1, #rows do
  print(rows[i].n)
end
return #rows

version-aware ordering

Keys sort with a comparator that compares digit runs as numbers, so 0.0.2 < 0.0.10 < 0.1.0 rather than the lexical 0.0.10 first.

local sqlite = require("santoku.sqlite.db")
local sql = require("santoku.sqlite")
local migrate = require("santoku.sqlite.migrate")
local db = sql(sqlite.open_memory())
local function log (v)
  return "create table if not exists log (seq integer primary key, name text);" ..
    " insert into log (name) values ('" .. v .. "');"
end
migrate(db, {
  ["0.0.2"] = log("0.0.2"),
  ["0.0.10"] = log("0.0.10"),
  ["0.1.0"] = log("0.1.0"),
})
local rows = db.all("select name from log order by seq", true)()
for i = 1, #rows do
  print(rows[i].name)
end
return #rows

one transaction: all or nothing

The whole batch runs inside one db.transaction. A failing migration rolls back every change from that call, including migrations earlier in the same batch and their bookkeeping rows, so a database is never left half-migrated. A non-table migrations argument raises immediately.

local sqlite = require("santoku.sqlite.db")
local sql = require("santoku.sqlite")
local migrate = require("santoku.sqlite.migrate")
local err = require("santoku.error")
local db = sql(sqlite.open_memory())
migrate(db, { ["0.0.1"] = "create table a (n);" })
local ok = err.pcall(migrate, db, {
  ["0.0.1"] = "create table a (n);",
  ["0.0.2"] = "create table e (n);",
  ["0.0.3"] = "this is not valid sql;",
})
print("batch applied:", ok)
print("table e exists:", err.pcall(function ()
  db.exec("insert into e (n) values (1)")
end))
print("recorded:", db.getter("select count(*) from migrations")())
return err.pcall(migrate, db, "not a table")

migrations are scripts, not statements

Each value goes through db.exec, which runs multi-statement scripts, so one migration can create tables, build indexes, backfill data, and drop what it replaced. Below, 0.0.2 adds a parent column and its index, and 0.0.6 replaces the sequence table with durable hybrid-logical-clock state and seeds it.

local migrate = require("santoku.sqlite.migrate")
migrate(db, {
  ["0.0.2.sql"] = [=[
    alter table records add column parent_id text;
    create index records_parent on records(parent_id);
  ]=],
  ["0.0.6.sql"] = [=[
    drop table hlc_seq;
    create table hlc_state (
      id integer primary key check (id = 1),
      pt integer not null,
      c integer not null
    );
    insert into hlc_state values (1, 0, 0);
  ]=],
})