how-to

Evolve your database with migrations

Generate reviewable SQL from your schemas and apply it, the only step that changes the database.

A persistent schema (one with db:) models a table, but Marreta never changes your database on its own. Instead, you work in two clear steps:

  • marreta migrate generate compares your schemas against the database and writes reviewable SQL. It does not touch the database.
  • marreta migrate apply runs that SQL. It is the only step that changes the database.

Keeping those steps separate means every schema change lands as a reviewed, committed migration rather than a silent surprise at server boot.

Prerequisites

Generate a migration from your schema

Given this persistent schema:

marreta
export schema Product
    db: products

    id: integer
    sku: string
    name: string
    price: decimal

Generate a migration:

bash
marreta migrate generate

This writes a pair of SQL files under migrations/, an up (apply) and a down (revert). For the schema above, the up file is plain, readable SQL:

sql
CREATE TABLE products (
  id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  sku TEXT NOT NULL,
  name TEXT NOT NULL,
  price NUMERIC NOT NULL
);

And the down file reverses it:

sql
DROP TABLE products;

Nothing has changed in the database yet. These files are meant to be committed, so every environment applies the same change.

Review before you apply

Because generate only writes files, you can read the up file and confirm it does what you expect before anything runs. This is the review step. Commit the migration alongside the schema change.

Hand-written SQL

A migration file is plain SQL, so you can write one by hand when the schema surface does not cover what you need (an index, a data backfill, a CHECK constraint). apply runs it like any other migration. When diff and generate later replay your migrations to work out the current schema, they tolerate the statement classes that cannot change the table and column model and skip them during that replay:

  • index statements (CREATE INDEX, CREATE UNIQUE INDEX, DROP INDEX),
  • data statements (INSERT, UPDATE, DELETE, and a WITH ... query),
  • anything you mark with a -- marreta: skip-replay line directly above the statement (the general escape valve for schema-neutral SQL such as CREATE EXTENSION or GRANT).

What stays rejected is column-mutating DDL the replay cannot derive a schema from (ALTER TABLE ... DROP COLUMN, ALTER COLUMN ... TYPE, DROP TABLE, renames). Express those as a schema edit plus a generated migration, or, if a statement is genuinely schema-neutral, put the -- marreta: skip-replay marker above it. For example:

sql
-- marreta: skip-replay
CREATE EXTENSION IF NOT EXISTS pgcrypto;

Apply the migration

bash
marreta migrate apply

This runs the pending up files in order. It is the single step that mutates the database:

text
Applied 20260605_183253_create_products

Afterward, marreta migrate status reports a clean state:

text
Applied:
  20260605_183253_create_products
Pending:
  none
Database migration state is clean.

See what is pending

marreta migrate status compares your migrations against the database and groups them by state:

bash
marreta migrate status
text
Applied:
  none
Pending:
  20260605_183253_create_products
Changed:
  none
Missing local:
  none
Suggested actions:
  - apply:   marreta migrate apply
  - discard: marreta migrate discard 20260605_183253

marreta migrate list shows the same migrations as a compact table:

bash
marreta migrate list
text
VERSION           NAME                STATE
20260605_183253   create_products     pending

Run these before apply to know exactly what will change.

Preview and explain a state

marreta migrate diff compares your current db: schemas against the schema your local migration files already describe, and prints the SQL a new migration would contain. It does not read or change the database. It is a dry run of generate.

When a schema has a change no migration captures yet, diff shows the planned operations:

bash
marreta migrate diff
text
Planned migration operations:
  0 tables to create, 1 column to add, 0 foreign keys to add

ALTER TABLE products ADD COLUMN description TEXT NOT NULL;

When the migrations already capture every schema, there is nothing to plan:

text
Database schema is up to date.

Where diff looks at your schemas versus your migration files, status and list look at your migration files versus what is applied in the database.

When a migration is in a state you do not recognize, marreta migrate explain <state> describes it and tells you what to do. The states are pending, changed, missing_local, and workflow:

bash
marreta migrate explain pending
text
State: pending
Meaning:
  The migration exists locally in migrations/, but has not been applied to this database.
Recommended actions:
  - apply it:
      marreta migrate apply
  - discard the local pending migration:
      marreta migrate discard <version>

marreta migrate explain workflow prints the full state machine, from a new migration through applied, rolled back, and discarded.

Evolve a schema

When you change a persistent schema, generate again. Add a column:

marreta
export schema Product
    db: products

    id: integer
    sku: string
    name: string
    price: decimal
    in_stock: boolean
bash
marreta migrate generate
marreta migrate apply

The new migration captures only the delta (an ALTER TABLE adding in_stock), so your history is a readable trail of how the table evolved.

Changes migrations do not generate

Migrations are additive by design: generate writes new tables, new columns, and new foreign keys, never destructive or in-place changes. When you make a change it cannot express, such as changing a column’s type or nullability, deleting a field, or removing a schema entirely, diff and generate do not silently ignore it. They report it as drift and leave it for you to handle by hand:

text
Unsupported changes detected (migrations are additive-only, handle manually):
  products.price: type differs (history NUMERIC, schema BIGINT)
  products.old_sku: present in history, no longer in any schema

No migration is written for these. Apply the change yourself with a hand-written migration (see “Hand-written SQL” above) once you have decided how to handle the existing data.

Undo the last migration

bash
marreta migrate rollback

rollback runs the most recent migration’s down file, reverting that change:

text
Rolled back 20260605_183253_create_products

Discard a pending migration

If you generated a migration that is wrong and have not applied it yet, discard it and generate again. (Editing a generated file by hand is fine when you know the SQL you want, see “Hand-written SQL” above, but for a wrong generated migration discarding and regenerating keeps the history aligned with the schema):

bash
marreta migrate discard <version>

This deletes the local up and down files for that version (for example 20260605_191139). Use it only for a migration that is still pending. To undo a migration that is already applied, use rollback instead.

Try it

bash
docker compose up -d --wait
marreta migrate generate
marreta migrate status
marreta migrate apply

Result checkpoint

You should now have a migrations/ folder with a reviewable up and down pair, an applied table in your database, and a clear sense that generate only writes SQL while apply is the only command that changes the database.

Troubleshooting

  • relation "products" does not exist at runtime. You generated a migration but never applied it. Run marreta migrate apply.
  • You need to change an applied migration. Do not edit it in place. Change the schema and generate a new migration, so every environment applies the same ordered history.
  • An unapplied migration is wrong. Discard it with marreta migrate discard and generate again, rather than hand-editing.
  • unsupported statement '...' from diff or generate. A migration file contains column-mutating DDL the replay cannot derive a schema from (a DROP COLUMN, an ALTER COLUMN ... TYPE, a DROP TABLE, a rename). Either express the change as a schema edit plus a generated migration, or, if the statement is genuinely schema-neutral, put a -- marreta: skip-replay line directly above it. A hand-written CREATE INDEX or backfill does not hit this, those classes are tolerated automatically (see “Hand-written SQL”).

Next steps