migrate
The migrate package keeps a PostgreSQL or SQLite database's schema in step with your code. You write the changes as numbered .sql files, and migrate applies the ones the database doesn't have yet, in order, each in a transaction. It records what it applied in a tern_migrations table. It is a package: tern add migrate, then import migrate. The tern migrate command runs it without adding it.
import sqlite
import fs
import migrate
fn main() Result[int, str]:
dir = fs.temp_dir("migrations")
fs.write(dir + "/0001_create_users.sql", "-- migrate:up\nCREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);\n-- migrate:down\nDROP TABLE users;\n")
fs.write(dir + "/0002_add_email.sql", "ALTER TABLE users ADD COLUMN email TEXT;\n")
t = migrate.sqlite(sqlite.open(dir + "/app.db")) # or migrate.postgres(pg.pool(url, 8))
for m in t.up(dir)?:
print("applied", migrate.label(m))
print(len(t.up(dir)?), "the second time")
for s in t.status(dir)?:
print(s.state, migrate.label_of(s))
fs.remove_all(dir)
return Ok(0)applied 0001_create_users
applied 0002_add_email
0 the second time
applied 0001_create_users
applied 0002_add_emailMigration files
A migrations directory holds one file per change, named VERSION_NAME.sql:
migrations/
0001_create_users.sql
0002_add_email.sql
20260928120000_add_posts.sql (a timestamp works too; the version is the number)They run in version order. Other files (a README) are ignored; a .sql file without a version, or two files with the same version, is an error that names them.
A file is the SQL that applies the change. Marker lines split it into parts:
-- migrate:up
CREATE TABLE users (id bigserial PRIMARY KEY, name text NOT NULL);
CREATE INDEX users_name ON users (name);
-- migrate:down
DROP TABLE users;| Marker | Meaning |
|---|---|
-- migrate:up | (optional) the SQL that applies the migration starts here |
-- migrate:down | the SQL that undoes it starts here; without one, down refuses to roll it back |
-- migrate:no-transaction | run it outside a transaction (see below) |
If you come from golang-migrate, the pair 0001_create_users.up.sql and 0001_create_users.down.sql works as well.
migrate.create(dir, "add_posts") (or new add_posts from the command) writes the next file, numbered after the newest one, with the two markers in it.
Running migrations
fn postgres(p: pg.Pool) Target
fn sqlite(d: sqlite.Db) Target
fn open(url: str) Result[Target, str] postgres://... or postgresql://...; sqlite:PATH or a path
fn up(t: Target, dir: str) Result[[Migration], str] every pending migration
fn down(t: Target, dir: str, n: int) Result[[Migration], str] undo the newest n applied ones
fn status(t: Target, dir: str) Result[[Status], str]Each returns the migrations it ran, in the order it ran them. up is what an app calls as it starts; it does nothing when the database is current:
import migrate
import os
import pg
fn main() Result[int, str]:
p = pg.pool(os.env("DATABASE_URL", "postgres://app:secret@localhost/app"), 16)
if not p.ok():
return Err("can't connect: " + pg.error())
migrate.postgres(p).up("migrations")?
# ... serve requests with p
return Ok(0)A Status has version, name, applied_at ("2026-09-28T12:00:00Z", or "" when pending), and state:
| State | Meaning |
|---|---|
applied | in the database, and the file is unchanged |
pending | not applied yet; up will apply it |
changed | applied, but the file's up SQL is different now; up refuses to run |
missing | applied, but there is no file for it (older code, or a deleted file) |
What it guarantees
- All or nothing. A migration and its row in
tern_migrationscommit together. If a statement fails, the transaction rolls back, the error names the migration and gives the database's message (migrate: 0003_add_posts failed: ERROR 42P01: relation "user" does not exist), andupstops there. PostgreSQL rolls back schema changes too. SQLite does as well, but some PRAGMAs (foreign_keys) do nothing inside a transaction. - One run at a time. On PostgreSQL, a run holds an advisory lock on a connection of its own, so every instance of an app can call
upas it starts: one applies the migrations and the others wait, then find nothing to do. On SQLite, each migration takes the database's write lock and checks again that it's still pending. - Applied migrations don't change.
upcompares each applied migration's SHA-256 with its file and stops with an error on a difference (statechanged). Undo the edit, and put the change in a new migration. - Older code still starts. Migrations in the database without a file (state
missing) are allowed, so rolling back a deploy doesn't stop the old version atup. - Branches merge. A pending migration older than the newest applied one is applied like any other.
Outside a transaction
A few statements refuse to run in a transaction: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, VACUUM, ALTER SYSTEM. Mark their file:
-- migrate:no-transaction
CREATE INDEX CONCURRENTLY users_email ON users (email);
-- migrate:down
DROP INDEX CONCURRENTLY users_email;Its statements run one at a time, and it is recorded after the last one succeeds. If one fails, the ones before it stay done, so keep one statement per file (IF NOT EXISTS helps a retry). A CREATE INDEX CONCURRENTLY that fails leaves an invalid index behind; drop it before running again.
The migrate command
tern migrate works on ./migrations (or --dir DIR) and the database given by --db URL or $DATABASE_URL:
$ export DATABASE_URL=sqlite:app.db
$ tern migrate new add_posts
created migrations/0003_add_posts.sql
$ tern migrate up
applied 0003_add_posts
$ tern migrate status
applied 0001_create_users 2026-09-28T19:19:36Z
applied 0002_add_email 2026-09-28T19:19:36Z
applied 0003_add_posts 2026-09-28T19:20:02Z
$ tern migrate down 2
rolled back 0003_add_posts
rolled back 0002_add_emailIt is migrate.command(dir, url, args), which a program of your own can call too (migrate.cli(dir, url) takes the program's arguments):
import migrate
import os
fn main() Result[int, str]:
return migrate.cli("migrations", os.env("DATABASE_URL", "sqlite:app.db"))examples/sql/ in the Tern repository has two migrations, plus queries turned into typed functions by sqlgen.
Migrations in code
The directory is read when the program runs, so ship it next to the binary. To build migrations some other way, make them with parse and run them with the functions that take a list:
fn load(dir: str) Result[[Migration], str]
fn parse(version: int, name: str, text: str) Result[Migration, str]
fn apply(t: Target, ms: [Migration]) Result[[Migration], str] like up
fn rollback(t: Target, ms: [Migration], n: int) Result[[Migration], str] like down
fn report(t: Target, ms: [Migration]) Result[[Status], str] like status
fn label(m: Migration) str "0001_create_users"
fn label_of(s: Status) str
fn split_statements(sql: str) [str]A Migration has version, name, up, down ("" if none), transaction, and checksum (the up SQL's SHA-256, in hex).
import sqlite
import migrate
fn main():
ms = [
migrate.parse(1, "notes", "CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);").unwrap(),
migrate.parse(2, "tags", "CREATE TABLE tags (note INTEGER, tag TEXT);\n-- migrate:down\nDROP TABLE tags;").unwrap(),
]
t = migrate.sqlite(sqlite.open(":memory:"))
print(t.apply(ms).unwrap().map(fn(m) migrate.label(m)))
print(t.rollback(ms, 1).unwrap().map(fn(m) migrate.label(m)))
print(migrate.split_statements("INSERT INTO t VALUES ('a;b'); SELECT 1"))["0001_notes", "0002_tags"]
["0002_tags"]
["INSERT INTO t VALUES ('a;b')", "SELECT 1"]split_statements splits a script at the semicolons that end statements, leaving alone the ones in quotes, $$ function bodies, and comments.
Not available on WebAssembly (like sqlite and pg), or on Android (no SQLite in the NDK).
To call your queries as typed functions checked against the migrated schema, see sqlgen.