Adopt an existing database

You have a live database whose schema was built outside Ptah — hand-run SQL, an ORM, or another migration tool — and you want Ptah to manage it without recreating existing objects. Choose the desired-schema representation first. This how-to verifies that representation against the database, generates an initial migration for fresh environments, and records it as already applied on the adopted database.

Prerequisites:

• A ptah binary on your machine (Install Ptah).
• The URL of the database you are adopting.

The examples use a local SQLite file, sqlite://$PWD/app.db, containing two tables, customers and orders, so every command runs without a database daemon. Substitute your own database URL throughout.

If the database was migrated with golang-migrate, Goose, Flyway, or Liquibase and you want to keep that history, jump to Keep history from another tool. Otherwise follow the numbered steps.

1. Choose and write the desired-schema source

SQL, HCL, DBML, and Go annotations have direct database-to-source commands. Do not route the static formats through Go. The examples below all describe the same live database, but their expressiveness boundaries differ.

SQL

ptah db read --db-url "sqlite://$PWD/app.db" > schema.sql

Keep schema.sql when reviewed DDL is the desired-schema source. db read writes SQL to stdout; connection diagnostics stay on stderr and do not enter the file.

HCL

ptah schema inspect --db-url "sqlite://$PWD/app.db" > schema.hcl

HCL is the default schema inspect output. It is the broadest static representation for Ptah and Atlas-shaped schema objects.

DBML

ptah schema inspect \
  --db-url "sqlite://$PWD/app.db" \
  --format dbml > schema.dbml

Choose DBML when the schema fits its table, column, key, index, relationship, and note subset. The verification step below catches an object that the format could not represent.

Go annotations

ptah introspect \
  --db-url "sqlite://$PWD/app.db" \
  --out ./models \
  --package models

Expected output includes Generated 2 Go file(s) and Imported 2 table(s). This path requires Go source because the generated files are annotated structs.

There is no direct database-to-YAML command. Use SQL, HCL, DBML, or Go for the adoption, or author YAML separately and prove it with the same drift check. From this point, edit the selected source rather than the database.

2. Confirm the source matches the database

SQL

ptah schema drift --schema-file schema.sql --db-url "sqlite://$PWD/app.db"

HCL

ptah schema drift --schema-file schema.hcl --db-url "sqlite://$PWD/app.db"

DBML

ptah schema drift --schema-file schema.dbml --db-url "sqlite://$PWD/app.db"

Go annotations

ptah schema drift --root-dir ./models --db-url "sqlite://$PWD/app.db"

Expected output:

No schema drift detected.

This is the round-trip check. Do not proceed on a non-empty result: it means the chosen representation omitted or changed something Ptah measures.

3. Generate the initial migration

Point ptah migrations generate at an empty throwaway database. The difference between that database and the selected source is the whole schema, so the generated migration creates a fresh environment.

SQL

ptah migrations generate \
  --schema-file schema.sql \
  --db-url "sqlite://$PWD/empty.db" \
  --migrations-dir ./migrations \
  --name init

HCL

ptah migrations generate \
  --schema-file schema.hcl \
  --db-url "sqlite://$PWD/empty.db" \
  --migrations-dir ./migrations \
  --name init

DBML

ptah migrations generate \
  --schema-file schema.dbml \
  --db-url "sqlite://$PWD/empty.db" \
  --migrations-dir ./migrations \
  --name init

Go annotations

ptah migrations generate \
  --root-dir ./models \
  --db-url "sqlite://$PWD/empty.db" \
  --migrations-dir ./migrations \
  --name init

Seal the selected result:

ptah migrations hash --dir ./migrations

Expected output includes:

Generated migration files for sqlite://.../empty.db:
UP:   .../<timestamp>_init.up.sql
DOWN: .../<timestamp>_init.down.sql

Wrote ./migrations/ptah.sum
2 migration file(s) hashed

4. Record the migration as applied

The adopted database already has this schema, so the initial migration must never run against it. ptah migrations baseline writes revision-table rows only — it does not execute migration SQL on the target. Preview the rows first:

ptah migrations baseline \
  --db-url "sqlite://$PWD/app.db" \
  --migrations-dir ./migrations \
  --dry-run

Expected output includes:

=== DRY RUN BASELINE ===
No metadata rows will be written.
...
Baseline version: <timestamp>
Rows:
- version=<timestamp> description="Init"

Then baseline with a disposable shadow database. Ptah replays the baselined migrations on it and verifies they reproduce the target schema before writing any rows:

ptah migrations baseline \
  --db-url "sqlite://$PWD/app.db" \
  --migrations-dir ./migrations \
  --shadow-db "sqlite://$PWD/shadow.db"

Expected output ends with:

Baselined 1 migration(s) through version <timestamp> in "schema_migrations"

Keep --shadow-db for SQL, HCL, DBML, and separately authored YAML sources. Without it, baseline prints No --shadow-db provided; using weaker entity drift verification. and can only compare the Go annotations selected by --root-dir with the target database. The shadow replay is the format-neutral proof that the migration directory reproduces the adopted schema.

5. Verify the adoption

ptah migrations status \
  --db-url "sqlite://$PWD/app.db" \
  --migrations-dir ./migrations

ptah migrations up \
  --db-url "sqlite://$PWD/app.db" \
  --migrations-dir ./migrations \
  --verify-sum

Expected output includes:

Total Migrations: 1
Applied Migrations: 1
Pending Migrations: 0
Status: ✅ Database is up to date

✅ Database is already up to date!

The database is adopted: up has nothing to do, and every future change follows the regular versioned loop — edit the selected desired-schema source, generate (which writes only the delta), hash, up — described in Versioned migrations.

6. Apply the first subsequent change

Prove the adopted database can continue through the regular loop. Add one nullable note column to the orders declaration in the selected source:

SQL

note TEXT

Place the column inside CREATE TABLE orders (...), with a comma after the preceding column.

HCL

column "note" {
  type = text
  null = true
}

DBML

note text

Place the column inside Table orders { ... }.

Go annotations

//ptah:schema:field name="note" type="TEXT"
Note string

Place the field on the annotated Order struct.

Generate the delta from the same source you adopted:

SQL

ptah migrations generate --schema-file schema.sql \
  --db-url "sqlite://$PWD/app.db" --migrations-dir ./migrations \
  --name add_order_note

HCL

ptah migrations generate --schema-file schema.hcl \
  --db-url "sqlite://$PWD/app.db" --migrations-dir ./migrations \
  --name add_order_note

DBML

ptah migrations generate --schema-file schema.dbml \
  --db-url "sqlite://$PWD/app.db" --migrations-dir ./migrations \
  --name add_order_note

Go annotations

ptah migrations generate --root-dir ./models \
  --db-url "sqlite://$PWD/app.db" --migrations-dir ./migrations \
  --name add_order_note

The generated up file contains:

ALTER TABLE "orders" ADD COLUMN "note" TEXT;

Seal and apply the new pair:

ptah migrations hash --dir ./migrations
ptah migrations up \
  --db-url "sqlite://$PWD/app.db" \
  --migrations-dir ./migrations \
  --verify-sum
ptah migrations status \
  --db-url "sqlite://$PWD/app.db" \
  --migrations-dir ./migrations

Expected status includes Pending Migrations: 0. Rerun the matching drift command from step 2; it prints No schema drift detected. The adopted source, migration history, and live database now agree after a real follow-up change.

Keep history from another tool

ptah migrations import converts a golang-migrate, Goose, Flyway, or Liquibase migration directory into Ptah’s native format. The source tool is auto-detected; --from overrides the detection. Preview first:

ptah migrations import \
  --source-dir ./legacy \
  --migrations-dir ./migrations \
  --dry-run

Expected output includes:

Dry run: would write 4 migration file(s) to ./migrations
  0000000001_init.up.sql
  0000000001_init.down.sql
  0000000002_orders.up.sql
  0000000002_orders.down.sql

Rerun without --dry-run to write the files:

Wrote 4 migration file(s) to ./migrations
Wrote ./migrations/ptah.sum

The source tool already applied these changes to the database, so finish with the same baseline and verification as steps 4–5, pointing --migrations-dir at the imported directory. ptah migrations status then reports every imported migration as applied and none pending.

Failure modes

• ptah migrations baseline exits 2 with error: schema migrations table is not empty; rerun with force to baseline anyway: the database already has migration metadata. Add --force only when you intend to overwrite that record deliberately.
• ptah migrations baseline exits 2 with error: baseline drift verification failed: schema drift detected; findings: [...]: the migration directory does not reproduce the live schema — usually the schema changed after step 1. Re-introspect and regenerate, then retry.
• ptah schema drift exits 1 with findings such as columns_removed: 1 (destructive): the live database and your desired-schema source have diverged. Findings describe what applying the desired schema would change, so columns_removed means the database has a column the source lacks.

For symptoms outside this flow, see Troubleshooting.

Limitations

• ptah introspect writes annotated Go models only. Use schema inspect for direct HCL or DBML output and db read for direct SQL output. Ptah does not have a direct database-to-YAML conversion.
• A source format can preserve only the objects it represents. The drift check in step 2 is required; choose HCL or SQL when DBML omits an object the database uses.
• ptah migrations baseline records revision rows; it never creates, alters, or drops schema objects on the target.

Next steps

• Decide how future changes reach this database: Choose a workflow.
• Run the full versioned lifecycle on the adopted directory: Versioned migrations.
• Squash a long imported history into one checkpoint later: Checkpoints.