Migrate from dbmate

A dbmate migration is one file with -- migrate:up and -- migrate:down sections, named with a fourteen-digit timestamp. The conversion does not keep those timestamps as version numbers, and it does keep transaction:false. Both are worth knowing before you run it.

What you need

• A ptah binary on your PATH. Install Ptah if necessary.
• A terminal and about five minutes.

No database server, Docker, or Go toolchain is required.

Convert the directory

Build the dbmate directory

The second migration disables the transaction, which is what a CREATE INDEX CONCURRENTLY needs on PostgreSQL. SQLite does not care, and the directive is the point here rather than the engine.

Bash

mkdir -p ptah-from-dbmate/legacy
cd ptah-from-dbmate
cat > legacy/20240101120000_create_users.sql <<'SQL'
-- migrate:up
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

-- migrate:down
DROP TABLE users;
SQL
cat > legacy/20240215093000_email_index.sql <<'SQL'
-- migrate:up transaction:false
CREATE UNIQUE INDEX users_email_idx ON users (email);

-- migrate:down transaction:false
DROP INDEX users_email_idx;
SQL

PowerShell

New-Item -ItemType Directory ptah-from-dbmate/legacy | Out-Null
Set-Location ptah-from-dbmate
@'
-- migrate:up
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

-- migrate:down
DROP TABLE users;
'@ | Set-Content legacy/20240101120000_create_users.sql
@'
-- migrate:up transaction:false
CREATE UNIQUE INDEX users_email_idx ON users (email);

-- migrate:down transaction:false
DROP INDEX users_email_idx;
'@ | Set-Content legacy/20240215093000_email_index.sql

Preview the conversion

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

Expected output on standard output:

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

Read those names before running anything. This is the part of a dbmate conversion that surprises people.

Run the import

ptah migrations import --source-dir ./legacy --migrations-dir ./migrations

Expected output on standard output:

Wrote 4 migration file(s) to ./migrations
Wrote ./migrations/ptah.sum
  0000000001_v20240101120000_create_users.up.sql
  0000000001_v20240101120000_create_users.down.sql
  0000000002_v20240215093000_email_index.up.sql
  0000000002_v20240215093000_email_index.down.sql

What the conversion changed

The timestamps are not the version numbers

20240101120000 became version 1, and 20240215093000 became version 2. The original timestamp is kept, in the description: v20240101120000_create_users.

The order is preserved, which is what a migration directory needs. What is not preserved is the number a database already recorded.

This matters for a database dbmate has already migrated. Its schema_migrations table holds the timestamps, and the converted directory counts from one, so the two do not line up on any row. Applying the converted directory to such a database would replay every migration it already has. Adopt an existing database is the procedure for recording the history as already applied; do that before the first ptah migrations up against anything that is not empty.

A fresh database has no such problem, which is what this page uses.

transaction:false survives

cat migrations/0000000002_v20240215093000_email_index.up.sql

Expected output on standard output:

-- +ptah no_transaction
CREATE UNIQUE INDEX users_email_idx ON users (email);

dbmate scopes the option to one direction and so does Ptah, so each side keeps what it asked for. A file that disables the transaction only on the way up converts to an up file carrying the directive and a down file that still runs inside one, which is what a multi-statement rollback wants: a failure partway through it rolls the whole thing back rather than leaving half the change behind.

Options the importer does not recognize are dropped from the SQL, as dbmate’s own directives are, and are not guessed at.

Apply the result

Validate the sealed directory

ptah migrations validate --dir ./migrations

Expected output on standard output:

OK: migrations directory matches ptah.sum

Apply the converted directory

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

Expected output includes, on standard output:

Current version: 0
Total migrations: 2
Pending migrations: 2

Progress records on standard error carry timestamps and correlation IDs, so this page does not copy those volatile fields.

Verify the recorded state

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

Expected output includes, on standard output:

Current Version: 2
Total Migrations: 2
Applied Migrations: 2
Pending Migrations: 0

The recorded versions are 1 and 2, not the timestamps. That is the record the converted directory and this database now share.

Clean up

Bash

cd ..
rm -rf ptah-from-dbmate

PowerShell

Set-Location ..
Remove-Item -Recurse -Force ptah-from-dbmate

Where this leaves you

dbmate also maintains db/schema.sql, a dump of the current schema. It has no destination in a migration directory, and the import does not read it. It is not useless: Ptah reads a .sql file as a desired-schema source, so it can become the input to compare and drift rather than something to delete.

Import an existing migration directory covers the other source tools and the format table.