Tern

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)
Output
applied 0001_create_users
applied 0002_add_email
0 the second time
applied 0001_create_users
applied 0002_add_email

Migration 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;
MarkerMeaning
-- migrate:up(optional) the SQL that applies the migration starts here
-- migrate:downthe SQL that undoes it starts here; without one, down refuses to roll it back
-- migrate:no-transactionrun 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:

Compiles (not run here)
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:

StateMeaning
appliedin the database, and the file is unchanged
pendingnot applied yet; up will apply it
changedapplied, but the file's up SQL is different now; up refuses to run
missingapplied, 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_migrations commit 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), and up stops 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 up as 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. up compares each applied migration's SHA-256 with its file and stops with an error on a difference (state changed). 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 at up.
  • 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_email

It is migrate.command(dir, url, args), which a program of your own can call too (migrate.cli(dir, url) takes the program's arguments):

Compiles (not run here)
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"))
Output
["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.

Source: docs/packages/migrate.md. Every example on this page is compiled and run by tern x test docs.