Migrations

AnvilBase tracks schema changes as ordered SQL migration files. Each project database carries a _anvilbase_migrations table keyed on filename, so applying migrations is idempotent — already-applied files are skipped, and you can run db push as many times as you like.

Layout

Put .sql files in a migrations/ directory, named so they sort correctly:

migrations/
  001_init.sql
  002_add_todos.sql
  003_rls_policies.sql

Files are applied in filename order. A timestamp or zero-padded prefix keeps ordering stable.

-- migrations/002_add_todos.sql
create table todos (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null,
  task text not null,
  done boolean default false,
  created_at timestamptz default now()
);
alter table todos enable row level security;

Preview and apply

# Dry run — lists pending files and sizes, applies nothing
anvilbase db push --project <id>

# Apply — runs each file in its own transaction, prints [OK]/[FAIL]/[SKIP]
anvilbase db push --project <id> --apply

# Custom directory
anvilbase db push --project <id> --path ./db/migrations --apply

Each file runs in its own transaction. Already-applied or empty files are skipped. If a file fails, all subsequent files are skipped (so a broken migration doesn’t leave you half-applied across files).

Inspect applied state and drift

anvilbase migrations list --project <id>

This compares the server’s applied list against your local files and labels each:

• applied — applied and present locally
• applied (local file missing) — applied on the server but the file is gone locally (drift — investigate)
• pending — present locally, not yet applied

anvilbase migrations apply is an alias for db push --apply.

Under the hood (the API)

The CLI calls the management API:

# Apply ordered files
curl -X POST http://localhost:39001/api/v1/projects/<id>/db/push \
  -H "Authorization: Bearer $ANVILBASE_TOKEN" -H "Content-Type: application/json" \
  -d '{"migrations":[{"filename":"001_init.sql","sql":"create table t (id uuid primary key);"}]}'

# List applied migrations
curl http://localhost:39001/api/v1/projects/<id>/db/migrations \
  -H "Authorization: Bearer $ANVILBASE_TOKEN"

The push response reports { "applied": <n>, "results": [{filename, status, error?}] } with status ∈ applied | skipped | failed.

Resetting (development only)

db reset drops all tables in public and re-applies local migrations. It demands you type the project ID to confirm (or -y in CI). It is refused entirely against shared/real environments.

anvilbase db reset --project <id>          # prompts for the project ID
anvilbase db reset --project <id> --yes    # CI

Pulling the schema

# Dump reconstructable DDL (CREATE TABLE + FKs + indexes) to schema-<id>.sql
anvilbase db pull --project <id>

# Pull a bounded, RLS-scoped data sample to seed a LOCAL database
anvilbase --env prod db pull --data --yes   # writes seed-<project>.sql

db pull introspects the live schema and emits apply-able DDL, ordered FK-safe (tables, then foreign keys, then indexes) so pull → apply → introspect round-trips. db pull --data is strictly read-only and capped client- and server-side; load its output into your local Postgres only — never re-apply it to a real env.

Promoting across environments

Migrations are the unit of promotion. With named environments in anvilbase.toml:

anvilbase deploy staging --only migrations   # apply pending migrations to staging
anvilbase env diff staging prod              # non-zero exit if heads differ (CI gate)
anvilbase env diff staging prod --deep       # + structural drift (color-coded SQL diff)

See CLI Reference → deploy and Self-Hosting → Upgrades.

Best practices

• One logical change per file; never edit a file after it’s applied anywhere — add a new one. Released migrations are immutable and this is enforced in CI: a frozen migrations/CHECKSUMS.sha384 manifest + the migration_immutability_test guard fail the build if any released migration’s bytes change (even a comment — it flips the sqlx checksum and aborts existing deployments with VersionMismatch). To change schema, add a NEW migration file and append its checksum to the manifest.
• Capture functions, triggers, and RLS policies as migrations too (author them in the SQL Editor, then paste into a file).
• Keep migrations idempotent-friendly (create table if not exists, create or replace function) so partial states recover cleanly.
• Commit migrations/ to version control; let CI run deploy so promotion is reviewed.

Next: REST API → Overview.