# Migrations and seeding Plan and apply schema migrations from .zen files, roll them back, and seed data deterministically. Zever compiles your `.zen` schema, diffs it against the live database, and applies the missing statements, recording each in `schema_migrations` so `zever db rollback` can undo it and re-running `migrate` is a no-op. ## Quick start 1. Preview the DDL without touching a database. ```sh zever db migrate --dry-run schema/app.zen ``` 2. Apply it. The default adapter is `sqlite`. ```sh zever db migrate schema/app.zen --adapter=sqlite --dsn=data/app.db ``` ```text applied 4 statement(s) via sqlite ``` 3. Seed the database. ```sh zever db seed ``` With no file arguments, `migrate` discovers `.zen` files under the schema directory; `-i` prompts for files, adapter, and DSN. `--dry-run` prints the diff against a live `--dsn`, else the full bootstrap DDL. ## Choose the database The CLI and the app share the same `db` settings. ```sh DB_ADAPTER=sqlite DB_PATH=app.db ``` sqlite defaults to `app.db`; `:memory:` forces a single connection even with `max_conns`. On postgres `min_conns` is a real pool minimum, on sqlite `SetMaxIdleConns`. See the [db options](https://zenta-dev.github.io/zever/reference/config/#db). ## Plan, review, and apply For each declared table: | Situation | Statement | |---|---| | Table is missing | `CREATE TABLE` and `CREATE INDEX IF NOT EXISTS` | | Field has `@renamed_from("old")` | `RENAME COLUMN` (keeps data) | | Column missing live | `ALTER TABLE ADD COLUMN` | | Column type changed | `ALTER COLUMN TYPE` (postgres); sqlite warns and skips | | Nullability changed | `ALTER COLUMN SET/DROP NOT NULL` (postgres); sqlite warns and skips | | Declared index or `@unique` missing | `CREATE [UNIQUE] INDEX` | | Live index no longer declared | `DROP INDEX`, only for indexes named by Zever's convention | | `belongs_to` or `has_one` foreign key missing | `ADD CONSTRAINT ... FOREIGN KEY` (postgres); sqlite warns and skips | | Live column no longer declared | `DROP COLUMN`, only with `--drop-columns` | `--drop-columns` destroys data, so snapshot first; an unannotated rename looks like a drop plus an add, so use `@renamed_from`. Removed live unique constraints and foreign keys are reported but never dropped. Plan in code or CI with `migrate.Plan` (no side effects). ```go title="migrate.go" import "github.com/zenta-dev/zever/orm/migrate" plan, _ := migrate.Plan(ctx, conn, desired, migrate.PlanOptions{}) for _, stmt := range plan.Statements() { fmt.Println(stmt) } applied, _ := migrate.Apply(ctx, conn, plan) // transaction where the driver supports it fmt.Println("applied", applied) ``` `PlanOptions{DropColumns: true}` emits `DROP COLUMN`; `DetectTypeChanges: true` compares live and declared types (the CLI always enables it). `Apply` uses `db.WithTx`, so failures roll back and nesting returns `ErrNestedTx`. ## Write safe migrations On a production database with live traffic, the dangerous part of a migration is rarely the DDL spelling: it is locks, rewrites, and destroyed data. Prefer expand/contract over in-place change. | Instead of | Do this | |---|---| | `DROP COLUMN` / `DROP TABLE` | Expand: add the replacement, dual-write, backfill, move reads, then drop. | | `ADD COLUMN ... NOT NULL` without a default | Add the column nullable, backfill existing rows, then `SET NOT NULL`. | | `ALTER COLUMN TYPE` | Add a new column of the new type, backfill, swap reads, drop the old column. | | Plain `CREATE INDEX` on postgres | `CREATE INDEX CONCURRENTLY` (see below). | `zever db migrate --lint` checks the plan for these shapes before anything runs and fails on error-severity findings. Waive one rule on one statement with a `-- zever:allow:` comment, or every rule with a bare `-- zever:allow`: ```sql -- zever:allow:drop_column ALTER TABLE "public"."users" DROP COLUMN IF EXISTS "legacy_flag"; ``` | Rule code | Severity | Flags | |---|---|---| | `drop_table`, `drop_column` | error | `DROP TABLE`, `DROP COLUMN` | | `not_null_without_default` | error | `ADD COLUMN ... NOT NULL` with no `DEFAULT` | | `type_rewrite` | error | `ALTER COLUMN ... TYPE`, `MODIFY COLUMN` | | `non_concurrent_index` | warning | postgres `CREATE INDEX` without `CONCURRENTLY` | ```sh zever db migrate --lint --dry-run schema/*.zen --adapter=postgres --dsn='postgres://localhost/db?sslmode=disable' ``` ## Backfill in batches A backfill (`UPDATE ... WHERE new_col IS NULL`) on a large table holds locks and bloats the table if done in one statement. Backfill in small batches with pauses between them, ideally from a job rather than inside the migration itself: migrate the schema first (new column nullable), run the batched backfill, then apply a second migration that adds the `NOT NULL` constraint once no nulls remain. Verify with `SELECT count(*) ... WHERE new_col IS NULL` returning zero before the second step. ## Build indexes without blocking writes A plain `CREATE INDEX` on postgres blocks writes to the table for the whole build. Pass `--concurrent-indexes` to build every new index as `CREATE INDEX CONCURRENTLY` instead: ```sh zever db migrate schema/app.zen --adapter=postgres --dsn='postgres://localhost/db?sslmode=disable' --concurrent-indexes ``` Concurrent builds run outside any transaction (postgres forbids `CONCURRENTLY` inside one), so each index build commits independently: a failure leaves earlier statements applied and the failed build to retry. `CREATE INDEX CONCURRENTLY` is postgres-only; other dialects render a plain `CREATE INDEX`. In code, set `migrate.PlanOptions{ConcurrentIndexes: true}` or `atlas.DDLOptions{ConcurrentIndexes: true}` for bootstrap rendering. ## Bound statement time with lock timeouts Every applied statement on postgres runs inside its own transaction, and `lock_timeout`/`statement_timeout` bound it via `SET LOCAL` without changing any session or database default: ```sh zever db migrate schema/app.zen --adapter=postgres --dsn='...' --lock-timeout=10s --statement-timeout=30s ``` Unset flags fall back to `db.options` from the config file (`DB_LOCK_TIMEOUT` / `DB_STATEMENT_TIMEOUT` in the environment): ```sh DB_ADAPTER=postgres DB_DSN=postgres://app:secret@db:5432/app DB_LOCK_TIMEOUT=10s DB_STATEMENT_TIMEOUT=30s ``` In code, set `migrate.PlanOptions{LockTimeout: 10 * time.Second, StatementTimeout: 30 * time.Second}`; the plan carries them into `Apply`. Zero disables each; negative fails `Plan` with `ErrInvalidTimeout`. A concurrent index build skips them (it runs outside a transaction, where `SET LOCAL` has no scope). ## Roll back a migration `zever db rollback` needs no `.zen` files; it reads `schema_migrations` and runs an inverse for the newest statements. `-n` counts statements, not runs, so undo a three-statement run with `-n 3`. ```sh zever db rollback --adapter=sqlite --dsn=data/app.db --dry-run # print inverses zever db rollback --adapter=sqlite --dsn=data/app.db -n 2 ``` ```text rolled back 2 statement(s) via the sqlite adapter ``` Rollback restores structure, not data. | Undoing | Result | |---|---| | `ADD COLUMN` | Drops the column and its values | | `DROP COLUMN` | Recreates the column empty | | `ALTER COLUMN TYPE` | Restores the type but not truncated values | | `RENAME COLUMN`, index changes, foreign keys | Lossless | | `ALTER ... NOT NULL` | Fails if a NULL was written meanwhile | | `CREATE TABLE` | Skipped; never dropped | Lossy statements warn before running; rows with no inverse are reported, skipped, and kept. ## Seed deterministically `zever db seed` runs the seed entrypoint (`project.seed_entry`, default `db/seed`) and passes arguments through. Write seeds as Go with the ORM in one transaction, idempotent via an `OnConflict` upsert. ```go title="db/seed/main.go" db.WithTx(ctx, conn, nil, func(ctx context.Context, tx db.Tx) error { return orm.InsertInto(Users). Values(orm.Set(UserName, "alice")). OnConflict(UserName.Col()).DoNothing(). Exec(ctx, tx) }) ``` Regenerate ORM builders after schema edits from the repo root; never hand-edit `generated/`. ```sh zever compile examples/pagination/schema/pagination.zen --backend=zenorm --out examples/pagination/generated ``` ## Troubleshooting
Plan shows unexpected DROP statements Cause: `--drop-columns` is set, or the schema lost a field. Fix: re-check the `.zen` source and keep the flag off unless intended.
Type or nullability change ignored on sqlite Cause: sqlite has no `ALTER COLUMN`. Fix: recreate the table or migrate the data manually.
`ErrNestedTx` during seed Cause: a seed helper opened a transaction inside `WithTx`. Fix: use one `WithTx` unit.
Postgres integration tests are skipped Cause: the gate is `POSTGRES_DSN`. Fix: export it for the live pass; sqlite runs without it.
Rollback does not restore data Cause: rollback reverses schema statements only. Fix: re-run `zever db seed` after rolling back.
## Related - [Query builder](https://zenta-dev.github.io/zever/guides/data/query-builder/) - [Configuration](https://zenta-dev.github.io/zever/guides/operate/configuration/) - [CLI reference](https://zenta-dev.github.io/zever/reference/cli/)