Migrations and seeding

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.

   zever db migrate --dry-run schema/app.zen

2. Apply it. The default adapter is sqlite.

   zever db migrate schema/app.zen --adapter=sqlite --dsn=data/app.db

   applied 4 statement(s) via sqlite

3. Seed the database.

   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.

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.

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).

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:<code> comment, or every rule with a bare -- zever:allow:

-- 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

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:

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:

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):

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.

zever db rollback --adapter=sqlite --dsn=data/app.db --dry-run   # print inverses
zever db rollback --adapter=sqlite --dsn=data/app.db -n 2

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.

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/.

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

Configuration

CLI reference