Migrations

A database's schema is a folder of .sql files, one change each. Opening the database applies the ones its file has not applied yet and checks the ones it has, so a program that starts has the schema its code expects, or it does not start.

migrations/
  0001_users.sql
  0002_orders.sql
  0003_orders_by_user.sql

const db = await store.database('shop', { migrations: `${import.meta.dirname}/migrations` })

db = await store.database("shop", migrations=Path(__file__).parent / "migrations")

db, err := sqldb.Open(ctx, store, "shop", sqldb.Migrations(os.DirFS("migrations")))

let db = store.database("shop").migrations("migrations").open()?;

#A migration file

create table orders (
    id         integer primary key,
    user_id    integer not null references users (id),
    total      integer not null,
    status     text not null check (status in ('pending', 'paid', 'refunded')),
    created_at integer not null
) strict;

create index orders_by_user on orders (user_id, id);

A file is plain SQLite: any number of statements, run in order. The name starts with its number, and the number decides the order: 0002_orders.sql runs after 0001_users.sql, whatever the rest of the name says. The numbers count from 0001 without a gap, and a name without one, 0001.sql, is refused.

#What opening does

The migrations that run at one open run in one transaction: the file has the old schema or the whole new one, never half (contract SQL-03). A statement that fails names its file and stops the open:

sql shop: …/sql/shop.db: migrations: 0004_refunds.sql: its statements: invalid: no such table: refund

#Change a schema

Add the next file. Never edit one that has run:

alter table orders add column note text;

There are no "down" migrations. To undo a change, write the next migration that undoes it. During development, before anything is deployed, deleting the database's file and starting again is the fastest undo.

#Rebuilding a table

SQLite's alter table adds, renames and drops columns. Anything else, a new check, another type or another key, is a new table, copied:

create table orders_new (
    id         integer primary key,
    user_id    integer not null references users (id),
    total      integer not null,
    currency   text not null default 'EUR',
    status     text not null check (status in ('pending', 'paid', 'refunded')),
    created_at integer not null
) strict;

insert into orders_new (id, user_id, total, status, created_at)
    select id, user_id, total, status, created_at from orders;
drop table orders;
alter table orders_new rename to orders;
create index orders_by_user on orders (user_id, id);

Foreign keys are off while migrations run, so that dropping orders does not cascade into the rows that point at it. They are checked once the migrations end: a migration that leaves a row without its parent is refused, and nothing it did is kept (contract SQL-04). Outside migrations, foreign keys are always on.

#What a migration may not do

A migration runs on the database's one writer, so it may not change that connection for everyone after it (contract SQL-02):

A pragma that only reads, pragma table_info(orders), is allowed.

#Migrations in the binary

A program shipped as one file carries its migrations inside it:

import users from './migrations/0001_users.sql' with { type: 'text' }
import orders from './migrations/0002_orders.sql' with { type: 'text' }

const db = await store.database('shop', {
	migrations: { '0001_users.sql': users, '0002_orders.sql': orders },
})

from importlib.resources import files

folder = files("shop") / "migrations"
db = await store.database("shop", migrations={f.name: f.read_text() for f in folder.iterdir() if f.name.endswith(".sql")})

//go:embed migrations/*.sql
var migrations embed.FS

dir, err := fs.Sub(migrations, "migrations")
db, err := sqldb.Open(ctx, store, "shop", sqldb.Migrations(dir))

let db = store
    .database("shop")
    .migrations([
        ("0001_users.sql", include_str!("../migrations/0001_users.sql")),
        ("0002_orders.sql", include_str!("../migrations/0002_orders.sql")),
    ])
    .open()?;

#Several programs, one database

Every program that opens a database passes its migrations, and each open checks them, so two versions of a program cannot disagree about a schema in silence. During a rolling deploy:

• The new version opens first, applies 0005, and works.
• An old version that opens the file later fails at start: the file has a migration it does not know. Stop the old versions before the new one starts, or make 0005 a change the old code does not notice, a new table or a new nullable column, and deploy it a version ahead.

tinystore migrate <dir> <name> <folder> applies a folder beside a running server, for a deploy that migrates before it starts the program.

#Limits

#See also

• SQL: the engine's overview.
• Writes and transactions: what runs against the schema.
• Messages in the Handbook writes a chat's schema.