Test migrations and schemas

Ptah can run declarative test cases that apply your migrations or desired schema to a throwaway database, load fixtures, run SQL, and assert on the result — so migration and schema behavior is exercised in CI without a bespoke harness.

Two commands share one test-case format:

• ptah migrations test applies a migration directory (to a version, or all the way up) and asserts against the migrated database.
• ptah schema test applies one of the desired-schema inputs below once, then asserts against it.

Source	Selector	Source-specific limitation
SQL file	--schema-file ./schema.sql	Uses Ptah’s DDL parser subset for the selected dialect.
YAML file	--schema-file ./schema.yaml	Uses Ptah YAML schema objects and their documented dialect overrides.
HCL file	--schema-file ./schema.hcl	Uses the Atlas-compatible HCL subset plus named Ptah extensions.
DBML file	--schema-file ./schema.dbml	Covers DBML tables, columns, keys, indexes, relationships, and notes, but not every Ptah object.
Go annotations	--root-dir ./models	Uses the native Go annotation model and requires a Go source tree.
OCI artifact	--schema-file oci://registry.example/app:v1	Reads the artifact’s lossless canonical HCL document.
Live database	--source-db-url sqlite://source.db	The database reader measures the live catalog; the destination must be throwaway and a non-SQLite source needs an explicit matching --db-url.

The live database is the desired input; --db-url still names the separate throwaway database where the assertions run.

Atlas keeps migrate test and schema test in its proprietary Pro build (an Atlas account and the closed-source binary). Ptah provides both as MIT, local, no-account, embeddable capabilities. Scripts written against the Atlas Pro CLI can run them through the Atlas-compatible surface too: migrate test and schema test in the ptah-compat drop-in binary forward to the native runners with Atlas-shaped flags (--dir / -u --url, --dev-url, --run, --config/--env/--var) and the native exit codes, with Ptah-native YAML test files as the executable payload — see Atlas compatibility overview.

Test-case format

A test file is a YAML document with a top-level cases: list. Each case is a named, ordered list of steps, and each step performs exactly one action:

• migrate_to — migrate the database to a target version: a non-negative integer, latest (migrate up to the newest migration), or 0 (roll everything back). Valid in migration tests only.
• apply_schema — reapply the selected desired schema. Write apply_schema: true. Schema tests already converge this schema before a case; the step rechecks live state and repairs supported drift. Migration tests use the Go annotations under their --root-dir for this optional step.
• exec — run raw SQL against the database.
• seed — apply environment-scoped SQL seed files from a directory (the seeder’s NNN_description.env.sql convention: files matching env plus .all.sql). Set dir on the step, or provide a shared default with --seed-dir.
• assert — run a query and check exactly one condition: row_count, scalar (the first column of the first row, compared as text), or error_contains (the query is expected to fail with a message containing the substring).

cases:
  - name: users table accepts rows
    steps:
      - migrate_to: latest
      - apply_schema: true
      - seed: { dir: ./seeds, env: test }
      - exec: INSERT INTO users (name) VALUES ('ada')
      - assert: { query: SELECT id FROM users, row_count: 2 }
      - assert: { query: SELECT name FROM users ORDER BY id LIMIT 1, scalar: seeded }
      - assert: { query: SELECT * FROM does_not_exist, error_contains: does_not_exist }

Running tests

# Migration tests: apply the migrations directory, then assert.
ptah migrations test --dir ./tests --migrations-dir ./migrations --seed-dir ./seeds

# Schema tests: apply a static desired schema, then assert.
ptah schema test --dir ./tests --schema-file ./schema.sql --seed-dir ./seeds

# HCL variables reach an HCL desired schema through repeatable --var values.
ptah schema test --dir ./tests --schema-file ./schema.hcl --var tenant=test

# Go annotations.
ptah schema test --dir ./tests --root-dir ./models --seed-dir ./seeds

# A live database, introspected. --db-url stays the throwaway target.
ptah schema test --dir ./tests --source-db-url sqlite://source.db \
  --db-url sqlite://throwaway.db

One selector names the desired schema, and each says what it takes:

Selector	Source
--root-dir	a directory of Go schema annotations
--schema-file	a .sql, .yaml, .yml, .hcl, or .dbml file, or an OCI artifact (repeatable)
--source-db-url	a database URL whose live schema is introspected

Naming two of them is refused before the throwaway database is provisioned. --source-db-url is separate from --db-url because that one names the throwaway database the cases run against; a database source must share its dialect.

Both commands load every *.yaml/*.yml file under --dir, run the cases, print a report, and exit non-zero if any case fails — so they slot straight into a CI gate. --run accepts a Go regular expression and selects matching case names. --seed-dir is the default directory for seed steps that omit their own dir. --report selects the output format: text (default), json (for CI tooling), or html. A migrate_to step is rejected in a schema test (there are no migrations), and reported as a failed step rather than silently skipped.

On ptah-compat schema test, an explicit --url uses the run’s --var values. A source selected through data.hcl_schema uses only that block’s vars; even an empty block scope prevents the run-wide values from leaking in.

Read migration test reports

The HTML format keeps summary counts, each case, and every step result in one portable artifact. It is self-contained: opening it fetches no stylesheet, font or script, so it reads the same attached to a review as it does on a machine with no network, and it follows the reader’s light or dark preference. The passing and failing reports below come from the same migration directory; only the selected assertion changes.

A real passing migration test report beside the corresponding failing assertion.

What to notice: The summary distinguishes pass from fail immediately, while the case row names the exact step and observed row count that decided the result.

Open full sizeDownloadView source

Reproduce this output

ptah migrations test --dir docs/site/fixtures/product-output/migration-tests --migrations-dir docs/site/fixtures/product-output/migrations --run '^products accept a row$' --report html > migration-test-pass.html

Failing assertion: expected 2 rows, got 0

DownloadSource

Read schema test reports

Schema tests use the same report shape but converge the desired schema before the case instead of applying a migration directory.

A real passing schema test report beside a failing row-count assertion.

What to notice: The report names itself as a schema test, and the failing variant shows the stable assertion message that CI can route back to the case.

Open full sizeDownloadView source

Reproduce this output

ptah schema test --dir docs/site/fixtures/product-output/schema-tests --schema-file docs/site/fixtures/product-output/schema.sql --run '^product schema accepts a row$' --report html > schema-test-pass.html

Failing assertion: expected 2 rows, got 0

DownloadSource

Database isolation

By default — no --db-url — each case runs against its own fresh ephemeral SQLite database, so state created by one case is never visible to another. For schema tests the desired schema is applied to each of those fresh databases.

Pass --db-url to run against a specific throwaway database (for example to exercise a real PostgreSQL or MySQL dialect). All cases then share that one database — it is provisioned once, cases accumulate state, and keeping them independent is the caller’s responsibility. Never point --db-url at a real database: tests mutate schema and data, and seed steps bypass the seeder’s protected-environment guards.

Desired-schema application is additive at object boundaries: unrelated objects created by migrations remain in place, while declared objects are planned with the live target’s capabilities and identifier semantics. Roles and grants are not accepted because their security effects can be cluster-scoped.

See Database test commands for the exact flags, step scopes, assertions, reports, and exit contract.

Embedding

The runner is exported as go.5x5.cz/ptah/migration/dbtest (RunMigrationTest / RunSchemaTest, with the Case/Step/Assertion model), so migration and schema tests can be driven directly from Go — no CLI, no account, no cloud.