Two teams run production workloads on a single binary Read more
Blog

Declarative Postgres migrations without migration files

· Co-Founder & CTO

How instancez migrates Postgres from one YAML file: diff and apply, previewing the plan, renamed_from for renames, a guard on drops and a lock timeout.

Declarative migrations mean you describe the schema you want and a tool works out the SQL to get there. In instancez, instancez.yaml is that description. inz dev and inz serve --migrate diff it against the live Postgres and apply only the delta. Renames are declared with renamed_from:, and drops are blocked in production unless you opt in.

This post walks through one table’s life: create it, preview a change, add a column, rename one without losing data, try to drop one, and hit a locked table. Every command and log line below comes from a run against Postgres 16.

What are declarative migrations?

Most migration tools are imperative. You write numbered files (0001_create_notes.sql, 0002_add_pinned.sql), the tool records which ones ran, and the schema is whatever you get by replaying them in order. To learn what a table looks like today, you either read every file or ask the database.

Declarative tools flip that. You keep one description of the end state, and the tool compares it with the database to generate the change.

Migration filesDeclarative (instancez)
Source of truthThe ordered list of filesinstancez.yaml
Adding a columnWrite a new fileAdd a field
Reading current schemaReplay files or inspect the DBRead one file
RenamesALTER ... RENAME in a filerenamed_from: on the field
Reviewing a changeReview the new fileReview the YAML diff

The second column is also easier for coding agents. An agent edits one file, and you review a diff of the schema itself, not a script that changes it. We cover that in why coding agents do better with a one-file backend.

How does instancez apply a schema change?

Here’s a starting instancez.yaml:

version: 1

tables:
  notes:
    rls_enabled: true
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: body
        type: text
        required: true
      - name: created_at
        type: timestamptz
        default: now()

inz validate checks it without touching a database:

$ inz validate
  ✓ Schema valid

In development, inz dev applies the schema on start and re-applies the diff every time you save the file. Run it with --embedded-pg and you don’t even need Postgres installed (local dev without Docker). In production, inz serve --migrate applies pending changes on startup. It needs a database URL and both API keys, which it reads from the environment or .production.env:

export INSTANCEZ_DATABASE_URL=postgres://postgres:password@db:5432/postgres
export INSTANCEZ_PUBLISHABLE_KEY=inz_publishable_...
export INSTANCEZ_SECRET_KEY=inz_secret_...
inz serve --migrate

The log reports migrations applied and the table is live behind the REST API. There’s no migration file anywhere in the project, and instancez never injects columns. If you didn’t declare updated_at, there isn’t one.

How do you preview a migration before it runs?

Pass a connection string to inz validate and it plans the migration against that database without applying it. Say we rename body to content and add a pinned flag:

      - name: content
        type: text
        required: true
        renamed_from: body
      - name: pinned
        type: boolean
        default: false
inz validate --use-dsn "$INSTANCEZ_DATABASE_URL"

The output starts with ! Migration plan (would run on next dev/serve): and then the SQL. It also includes idempotent statements instancez re-runs on every boot for its own auth schema, so here are just the lines for notes:

ALTER TABLE notes RENAME COLUMN body TO content;
ALTER TABLE notes ADD COLUMN pinned boolean DEFAULT FALSE;
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE notes FORCE ROW LEVEL SECURITY;

This is the step to put in code review or CI. The YAML diff shows intent, and the plan shows what Postgres will actually run. The CLI reference also covers --project, which previews against an instancez Cloud project instead.

What happens with additive changes?

They apply right away. New tables, columns, indexes and policies are added on the next run, with no flags. After inz serve --migrate with the YAML above, the row we inserted earlier reads back like this:

[{"content":"first note","id":1,"pinned":false}]

The new column picked up its default, and the old value survived the rename.

How do you rename a column without losing data?

A declarative tool only sees two versions of your schema. If body disappears and content appears, that looks exactly like dropping one column and adding another, which would throw away the data. So instancez doesn’t guess. You state the old name:

      - name: content
        type: text
        renamed_from: body

and it issues ALTER TABLE notes RENAME COLUMN body TO content, as the plan above showed. Tables take the same key (renamed_from: posts under the table). Renames run before anything else in the migration, so you can rename a table and its columns in one edit, or rename and change a type together.

Once applied, renamed_from: does nothing on later runs. You can leave it or clean it up. If the old name never existed, the field is treated as a new column. If both names exist, nothing is renamed, so it never overwrites a live column. Renaming is the one change you declare; everything else is inferred (schema docs).

What happens when you drop a column?

Removing a field from the YAML drops the column along with its data. inz serve refuses to do that by default. We deleted pinned and restarted; this is the warning from the JSON log, trimmed:

WARN destructive change blocked; applied additive provisioning only
reason: destructive migration: this change drops notes.pinned.
If you meant to rename it, declare the old name with renamed_from: so the data moves instead of being dropped.
To drop it for real, pass --allow-destructive (or set INSTANCEZ_ALLOW_DESTRUCTIVE=true)

The server still starts and keeps serving on the last applied config. It also logs config drift: source has unapplied changes until the drop goes through. The message names exactly what would be lost and suggests the most likely mistake, a rename without renamed_from:.

To drop it on purpose:

inz serve --migrate --allow-destructive
WARN applying destructive migration drops=notes.pinned

inz dev is different on purpose. Rebuilding the schema is the normal dev loop, so it applies drops without asking but logs each one. Delete a field while inz dev is watching and you’ll see:

level=WARN msg="applying destructive migration" drops=notes.tags

Because dev re-applies on every save, a field you delete by accident is dropped on save. Point inz dev at a scratch database, not one with data you care about.

What if a table is locked during a migration?

ALTER TABLE needs a strong lock. If a long-running query holds the table, a migration that waits forever would also queue every request behind it. instancez caps the wait: each inz serve migration statement waits at most --migrate-lock-timeout (default 5s) for a table lock.

We tested it by holding a lock on notes from psql in an open transaction, then starting inz serve --migrate with a new column in the YAML. About five seconds later:

ERROR config drift: source has unapplied changes
reason: migrate exec: database tx_exec: ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)

The server came up anyway and answered requests on the previous schema. The next boot, or the next reload under --watch, applies the change once the lock is gone. Set --migrate-lock-timeout 0 to wait without limit.

instancez also takes a Postgres advisory lock before it migrates (schema docs).

How does this compare to Supabase declarative schemas?

Supabase has a declarative option too (docs). You write SQL files under supabase/schemas/, run supabase db schema declarative sync to generate a migration file into supabase/migrations, then apply and push it. The schema files are the source of truth, and the generated files are still versioned migrations.

Supabase declarative schemasinstancez
You editSQL files in supabase/schemas/instancez.yaml
GeneratesMigration files to commitNothing; the diff is applied at boot or on save
PreviewThe generated migration fileinz validate --use-dsn
Auth and storagePlatform-managed, outside the diffDeclared in the same YAML

The bigger difference is scope. In instancez the same file also holds RLS policies, auth settings, storage buckets, RPC and functions, so one diff covers the whole backend change. See row-level security as YAML for the policy side.

What does a safe workflow look like?

  1. Edit instancez.yaml locally with inz dev watching it.
  2. Commit the YAML. The pull request diff is the schema change.
  3. In CI, run inz validate, and inz validate --use-dsn against staging to print the plan.
  4. Deploy with inz serve --migrate. Add --allow-destructive only for a release that’s meant to drop something.

The same file moves from dev to staging to prod; that post covers env vars and secrets per environment. If you’d rather not run Postgres yourself, inz cloud deploy shows a diff and asks before applying (pricing). Coming from Supabase migrations? The migration guide translates your existing tables and columns into instancez.yaml.

FAQ

Do I need migration files with instancez? No. instancez.yaml is the schema. instancez diffs it against the database and applies only the change.

How do I roll back a schema change? Revert the YAML and redeploy. instancez diffs the reverted file like any other change. Undoing a rename needs its own renamed_from:, and undoing a drop brings the column back empty, which is why inz serve blocks drops by default.

Can I see the SQL before it runs? Yes. inz validate --use-dsn <dsn> prints the plan and never applies it.

Does inz dev protect against drops? No, it applies them and logs a warning for each. Only inz serve needs --allow-destructive.