Schema and migrations

With a declarative project, schema/ describes the database as it should be — complete CREATE TABLEs, functions, triggers, views — and the migrations are its history, generated from it.

pgzest.toml

[schema]
directory = "schema"
declarative = true

[migrations]
directory = "migrations"

The analysis and the editor then use the schema; the migrations are replayed separately and compared with it. As long as they do not reach it, the latest migration carries a schema-drift diagnostic.

Generating a migration

pgzest migrate create add_loyalty      # writes the migration, verified by replay
pgzest migrate check                   # CI: fails if the schema moved on alone
pgzest migrate diff old.sql new.sql    # between two schemas, no project

migrate create compares the state the migrations reach with the schema, object by object — schemas, extensions, enums, domains, types, sequences, tables, columns, constraints, views, routines, indexes, triggers, policies, row-level security, comments — and writes the migration in the directory’s layout: numbered files, Prisma (<timestamp>_<name>/migration.sql) or Diesel (<timestamp>_<name>/up.sql).

-- pgzest: postgres=18 extensions=citext@1.8,pg_trgm@1.6
-- Migration "search", generated by `pgzest migrate create`.

CREATE EXTENSION IF NOT EXISTS pg_trgm SCHEMA public VERSION '1.6';

ALTER TABLE customers
  ADD COLUMN loyalty integer DEFAULT 0 NOT NULL;

-- !! M006: index public.customers_name_trgm built without CONCURRENTLY: writes to public.customers blocked during the build
CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);

• Verified before it is written: the migration is replayed after the others, and the result must be exactly the schema.
• Ordered: what would block the changes first (policies, triggers, views to rebuild), then the creations sorted by dependency, the drops last.
• The simplest migration, adapted by you. It reaches the schema in the simplest way, even when that loses data (a rename becomes a drop and an add) or fails on a populated table (a NOT NULL column without default). Only you know the data: the !! TO ADAPT header lists what to review, and migrate check verifies the final state once adapted. pgzest never writes data-migration code.
• Nothing approximated: a change pgzest cannot express (an enum value removed, a domain changed…) is written as a TODO, and migrate check fails until it is written by hand.
• Risks on each statement concerned: M001 to M007, from a NOT NULL column without default to an index built without CONCURRENTLY (list).

The header records the context the migration was generated in — the PostgreSQL version, the extensions and their versions, the run log level of the routines — and the replay checks the file against it.

An existing project

Migrations of an existing project are read as they are, in their layout: a Prisma project plugs in without conversion. To move to a declarative schema, replay them once and write the result:

pgzest schema import prisma/migrations --layout prisma --out schema --baseline migrations/0000_baseline.sql

The import writes one <schema>/schema.sql per PostgreSQL schema and a baseline migration carrying its header; a replay error stops it (--force writes anyway).

Hand changes in production

pgzest db check --url postgres://…/myapp   # or DATABASE_URL

Compares a database with what its migrations produce, and reports what was changed in it by hand — a routine hotfixed in production, an index added, a trigger dropped. Exits 1 when it differs:

error: the database differs from its 5 migration(s), 3 object(s):
  extra    index orders_note_idx on public.orders: only in the database
  changed  routine order_total(bigint):
        …
      - AS $function$ SELECT coalesce(sum(quantity * unit_price), 0)::bigint …
      + AS $function$ SELECT coalesce(sum(quantity * unit_price), 0)::bigint * 2 …
  missing  trigger orders_touch on public.orders: in the migrations, not in the database

• The migrations compared are those the database received, as its migration tool recorded them (_prisma_migrations, __diesel_schema_migrations); the others are reported as not applied yet, and a recorded one the repository lacks is an error. Without a migrations table: all of them, or up to --until <name>.
• They are replayed in an empty database created on the same server and dropped afterwards (the CREATEDB privilege), or on another server of the same PostgreSQL version with --scratch-url.
• Both databases are described by PostgreSQL itself (pg_get_functiondef, pg_get_triggerdef, format_type…): a difference is one PostgreSQL sees. Column order and the names of NOT NULL constraints are not compared.