# Adopt an existing database
Put a live database under Ptah management with introspect, baseline, and import, without recreating anything.
Source: https://docs.ptah.run/v0.8.0/start/adopt-an-existing-database/
import { Tabs, TabItem } from '@astrojs/starlight/components';
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](../install/)).
- 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:
```console
mkdir ptah-adopt
cd ptah-adopt
```
Save this as `legacy.sql`, standing in for the schema another tool built:
```sql
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:
```console
ptah schema apply --db-url "sqlite://app.db" --schema-file legacy.sql --auto-approve
```
Expected output on standard output:
```text
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](#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.
```console
ptah db read --db-url "sqlite://app.db"
```
Expected output on standard output:
```text
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:
```bash illustration
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:
```sql
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
);
```
```bash illustration
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.
```bash illustration
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.
```bash illustration
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
```console
ptah schema drift --schema-file schema.sql --db-url "sqlite://app.db"
```
```bash illustration
ptah schema drift --schema-file schema.hcl --db-url "sqlite://app.db"
```
```bash illustration
ptah schema drift --schema-file schema.dbml --db-url "sqlite://app.db"
```
```bash illustration
ptah schema drift --root-dir ./models --db-url "sqlite://app.db"
```
Expected output on standard output:
```text
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.
```console
ptah migrations generate --schema-file schema.sql --db-url "sqlite://empty.db" --migrations-dir ./migrations --name init
```
```bash illustration
ptah migrations generate \
--schema-file schema.hcl \
--db-url "sqlite://empty.db" \
--migrations-dir ./migrations \
--name init
```
```bash illustration
ptah migrations generate \
--schema-file schema.dbml \
--db-url "sqlite://empty.db" \
--migrations-dir ./migrations \
--name init
```
```bash illustration
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:
```text illustration
Generated migration files for sqlite://.../empty.db:
UP: .../_init.up.sql
DOWN: .../_init.down.sql
```
Seal the selected result:
```console
ptah migrations hash --dir ./migrations
```
Expected output on standard output:
```text
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:
```console
ptah migrations baseline --db-url "sqlite://app.db" --migrations-dir ./migrations --dry-run
```
Expected output on standard output:
```text
=== 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:
```text illustration
Baseline version:
Rows:
- version= description="Init"
```
Then baseline with a disposable [shadow database](../../concepts/database-urls-and-dev-databases/). Ptah replays the baselined migrations on it and verifies they reproduce the target schema before writing any rows:
```console
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:
```text illustration
Baselined 1 migration(s) through version 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.
:::caution[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:
```text illustration
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
```console
ptah migrations status --db-url "sqlite://app.db" --migrations-dir ./migrations
```
Expected output on standard output:
```text
Total Migrations: 1
Applied Migrations: 1
Pending Migrations: 0
Status: ✅ Database is up to date
```
```console
ptah migrations up --db-url "sqlite://app.db" --migrations-dir ./migrations --verify-sum
```
Expected output on standard output:
```text
✅ 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](../../versioned/overview/).
## 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 illustration
note TEXT
```
Place the column inside `CREATE TABLE orders (...)`, with a comma after the
preceding column.
```hcl
column "note" {
type = text
null = true
}
```
```text illustration
note text
```
Place the column inside `Table orders { ... }`.
```go
//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:
```sql
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:
```console
ptah migrations generate --schema-file schema.sql --db-url "sqlite://app.db" --migrations-dir ./migrations --name add_order_note
```
```bash illustration
ptah migrations generate --schema-file schema.hcl \
--db-url "sqlite://app.db" --migrations-dir ./migrations \
--name add_order_note
```
```bash illustration
ptah migrations generate --schema-file schema.dbml \
--db-url "sqlite://app.db" --migrations-dir ./migrations \
--name add_order_note
```
```bash illustration
ptah migrations generate --root-dir ./models \
--db-url "sqlite://app.db" --migrations-dir ./migrations \
--name add_order_note
```
The generated up file contains:
```sql illustration
ALTER TABLE "orders" ADD COLUMN "note" TEXT;
```
Seal and apply the new pair:
```console
ptah migrations hash --dir ./migrations
```
```console
ptah migrations up --db-url "sqlite://app.db" --migrations-dir ./migrations --verify-sum
```
```console
ptah migrations status --db-url "sqlite://app.db" --migrations-dir ./migrations
```
Expected output on standard output:
```text
Pending Migrations: 0
```
Rerun the drift check from step 2:
```console
ptah schema drift --schema-file schema.sql --db-url "sqlite://app.db"
```
Expected output on standard output:
```text
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:
```bash illustration
ptah migrations import \
--source-dir ./legacy \
--migrations-dir ./migrations \
--dry-run
```
That prints, for a directory holding two golang-migrate pairs:
```text illustration
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:
```text illustration
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](../../operate/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](../choose-a-workflow/).
- Run the full versioned lifecycle on the adopted directory: [Versioned migrations](../../versioned/overview/).
- Squash a long imported history into one checkpoint later: [Checkpoints](../../versioned/checkpoints/).