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://app.db, containing two tables, customers and orders, so every command runs without a database daemon. Substitute your own database URL throughout.

If you do not have such a database, build one here. Start in an empty directory:

mkdir ptah-adopt
cd ptah-adopt

Save this as legacy.sql, standing in for the schema another tool built:

CREATE TABLE customers (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total       INTEGER NOT NULL
);

Create the database from it. Nothing after this step treats legacy.sql as the desired-schema source; it exists only to give the adoption something to adopt:

ptah schema apply --db-url "sqlite://app.db" --schema-file legacy.sql --auto-approve

Expected output on standard output:

Schema apply completed successfully.

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://app.db"

Expected output on standard output:

CREATE TABLE "customers" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT NOT NULL
);

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, so redirecting it is enough:

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

The redirection is the one step of this page that is not shell-neutral. A PowerShell redirect writes UTF-16 or a byte-order mark depending on the version, and Ptah’s SQL reader refuses either with expected SQL keyword, got Operator at position 0. On Windows, write the file with Set-Content -Encoding ascii or save the output from an editor. For the rest of this page, schema.sql holds exactly what the command printed:

CREATE TABLE "customers" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT NOT NULL
);

CREATE TABLE "orders" (
  "id" INTEGER PRIMARY KEY,
  "customer_id" INTEGER NOT NULL,
  "total" INTEGER NOT NULL
);

HCL

ptah schema inspect --db-url "sqlite://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://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://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://app.db"

HCL

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

DBML

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

Go annotations

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

Expected output on standard 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://empty.db" --migrations-dir ./migrations --name init

HCL

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

DBML

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

Go annotations

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

generate names the pair it wrote, with the timestamp it chose:

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

Seal the selected result:

ptah migrations hash --dir ./migrations

Expected output on standard output:

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://app.db" --migrations-dir ./migrations --dry-run

Expected output on standard output:

=== DRY RUN BASELINE ===
No metadata rows will be written.

The rest of that preview names the version it would write, which is the timestamp generate chose:

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://app.db" --migrations-dir ./migrations --shadow-db "sqlite://shadow.db"

The run ends by naming the version it recorded:

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.

A MySQL shadow needs global privileges

Ptah empties the shadow database before replaying into it, and on MySQL and MariaDB it refuses to empty one unless the connected account holds SELECT, DROP, ALTER, ALTER ROUTINE, EVENT, LOCK TABLES, PROCESS, SHOW VIEW, SHOW_ROUTINE and TRIGGER globally — ON *.*, not ON shadow_db.*. The refusal names the privileges:

error: baseline shadow check failed: drop all objects: mysql: refusing to clean
database "app_shadow": global SELECT, DROP, ALTER, ALTER ROUTINE, EVENT,
LOCK TABLES, PROCESS, SHOW_ROUTINE, and TRIGGER privileges are required to
prove complete metadata visibility and protect destructive DDL

A schema-scoped grant lets an account create and use the database and still leaves it unable to see every object in it, and emptying a database whose contents cannot all be seen is the destructive step this refuses. Point --shadow-db at an account that holds them, which need not be the one --db-url uses: the shadow is disposable and the target is not.

5. Verify the adoption

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

Expected output on standard output:

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

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

Expected output on standard output:

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

For the SQL path, schema.sql now reads in full:

CREATE TABLE customers (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total       INTEGER NOT NULL,
    note        TEXT
);

Generate the delta from the same source you adopted:

SQL

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

HCL

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

DBML

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

Go annotations

ptah migrations generate --root-dir ./models \
  --db-url "sqlite://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://app.db" --migrations-dir ./migrations --verify-sum

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

Expected output on standard output:

Pending Migrations: 0

Rerun the drift check from step 2:

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

Expected output on standard output:

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

That prints, for a directory holding two golang-migrate pairs:

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.