tutorial

Migrate from Liquibase

Convert a Liquibase changelog, and learn which of your changesets carry SQL and which do not.

What decides whether a Liquibase changeset converts is not the file format. It is what the changeset carries.

A changeset that carries SQL converts, whether you wrote it as formatted SQL or as <sql> inside XML. A changeset that carries a typed change — <createTable>, <addColumn>, and the rest of Liquibase’s database-independent vocabulary — has no SQL yet. Liquibase writes that SQL when it runs, for the database it is pointed at. Ptah writes it during the import, for the dialect you name with --dialect, and refuses the changeset by name when you name none.

Converting the changes is necessary and not sufficient. context, contexts, labels, dbms and preConditions decide at run time whether a changeset applies, and runAlways and runOnChange make Liquibase run it again. A migration directory cannot express either, so a changeset carrying one is refused as well. Importing it would turn a conditional history into an unconditional one, which is a worse outcome than a refusal.

This page converts what converts and reads both refusals.

What you need

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

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

Convert a formatted-SQL changelog

mkdir -p ptah-from-liquibase/legacy
cd ptah-from-liquibase
cat > legacy/001-users.sql <<'SQL'
--liquibase formatted sql

--changeset alice:1
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
--rollback DROP TABLE users;

--changeset alice:2
CREATE UNIQUE INDEX users_email_idx ON users (email);
--rollback DROP INDEX users_email_idx;
SQL

New-Item -ItemType Directory ptah-from-liquibase/legacy | Out-Null
Set-Location ptah-from-liquibase
@'
--liquibase formatted sql

--changeset alice:1
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
--rollback DROP TABLE users;

--changeset alice:2
CREATE UNIQUE INDEX users_email_idx ON users (email);
--rollback DROP INDEX users_email_idx;
'@ | Set-Content legacy/001-users.sql

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_alice_1.up.sql
  0000000001_alice_1.down.sql
  0000000002_alice_2.up.sql
  0000000002_alice_2.down.sql

Each changeset became a migration, named from its author and id. The --rollback line became the down file, which is what it already was.

What an XML changelog does

A changeset carrying SQL converts

Bash
PowerShell

mkdir -p xml/legacy
cat > xml/legacy/changelog.xml <<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
  <changeSet id="1" author="alice">
    <sql>CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);</sql>
    <rollback>DROP TABLE users;</rollback>
  </changeSet>
</databaseChangeLog>
XML

New-Item -ItemType Directory xml/legacy | Out-Null
@'
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
  <changeSet id="1" author="alice">
    <sql>CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);</sql>
    <rollback>DROP TABLE users;</rollback>
  </changeSet>
</databaseChangeLog>
'@ | Set-Content xml/legacy/changelog.xml

ptah migrations import --from liquibase --source-dir ./xml/legacy --migrations-dir ./xml/migrations

Expected output on standard output:

Wrote 2 migration file(s) to ./xml/migrations
Wrote ./xml/migrations/ptah.sum
  0000000001_alice_1.up.sql
  0000000001_alice_1.down.sql

XML is read. Nothing about the format stops the conversion.

A changeset carrying a typed change needs a dialect

Bash
PowerShell

mkdir -p typed/legacy
cat > typed/legacy/changelog.xml <<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
  <changeSet id="1" author="alice">
    <createTable tableName="users">
      <column name="id" type="int">
        <constraints primaryKey="true"/>
      </column>
      <column name="email" type="varchar(255)">
        <constraints nullable="false"/>
      </column>
    </createTable>
  </changeSet>
</databaseChangeLog>
XML

New-Item -ItemType Directory typed/legacy | Out-Null
@'
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
  <changeSet id="1" author="alice">
    <createTable tableName="users">
      <column name="id" type="int">
        <constraints primaryKey="true"/>
      </column>
      <column name="email" type="varchar(255)">
        <constraints nullable="false"/>
      </column>
    </createTable>
  </changeSet>
</databaseChangeLog>
'@ | Set-Content typed/legacy/changelog.xml

ptah migrations import --from liquibase --source-dir ./typed/legacy --migrations-dir ./typed/migrations

Expected output on standard error:

error: parse liquibase source: liquibase changeset alice_1 in "changelog.xml" uses <createTable>, which is not SQL text; Ptah renders it for one target dialect, so pass --dialect to choose it, or rewrite it as a `sql` change

The message names the changeset, the file and the element. A typed change is database-independent, so turning it into SQL means choosing a database, and Ptah does not choose one on your behalf. Name it:

ptah migrations import --from liquibase --source-dir ./typed/legacy --migrations-dir ./typed/migrations --dialect sqlite

Expected output on standard output:

Wrote 2 migration file(s) to ./typed/migrations
Wrote ./typed/migrations/ptah.sum
  0000000001_alice_1.up.sql
  0000000001_alice_1.down.sql

The up file holds SQL for SQLite and for no other database, so name the dialect of the database the history will run on. The changeset declares no rollback, so the down file holds the one Liquibase would derive: it drops the table.

Apply the result, which is how you find out whether the server accepts the SQL:

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

Expected output includes, on standard output:

Database is now at version: 1

These typed changes convert: createTable, dropTable, addColumn, dropColumn, createIndex, dropIndex, addPrimaryKey, addForeignKeyConstraint, renameTable and renameColumn. sqlFile converts as well, and needs no dialect because the file it names is SQL already. Any other change type, and any attribute a converted change sets that Ptah does not read, is refused by name. Rewrite such a changeset as a sql change in Liquibase first, where liquibase update-sql shows the SQL it would have run.

A changeset carrying a selector is refused too

The changeset below carries SQL, so the rule above stops short of deciding it. context="staging" is what makes the difference.

Bash
PowerShell

mkdir -p conditional/legacy
cat > conditional/legacy/changelog.xml <<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
  <changeSet id="1" author="alice" context="staging">
    <sql>CREATE TABLE users (id INTEGER PRIMARY KEY);</sql>
  </changeSet>
</databaseChangeLog>
XML

New-Item -ItemType Directory conditional/legacy | Out-Null
@'
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
  <changeSet id="1" author="alice" context="staging">
    <sql>CREATE TABLE users (id INTEGER PRIMARY KEY);</sql>
  </changeSet>
</databaseChangeLog>
'@ | Set-Content conditional/legacy/changelog.xml

ptah migrations import --from liquibase --source-dir ./conditional/legacy --migrations-dir ./conditional/migrations

Expected output on standard error:

error: parse liquibase source: liquibase changeset alice_1 in "changelog.xml" is conditional on context; a migration directory has no equivalent, so importing it would turn a conditional history into an unconditional one -- split the changelog or import it by hand

contexts, labels, dbms and preConditions are refused the same way, and so is dbms on a sql or sqlFile change. Split the changelog per environment or per database in Liquibase first, where the selector still means something, and convert each result separately.

A changelog split by dbms alone has a shorter way through: name the database the history ran on, in Liquibase’s own spelling, with --liquibase-dbms, and the import keeps what Liquibase ran there and names the rest on standard error. --dialect cannot stand in for it: the name Liquibase gives some databases depends on how it connected, so Ptah cannot tell which changesets ran on yours. Import from another tool says more.

runAlways="true" and runOnChange="true" are refused as well, because Liquibase can run such a changeset again on a later update and a Ptah migration runs once. The value false is the default and imports. So are the changeset attributes a migration has no form for, such as failOnError="false" and runOrder, and any attribute Ptah does not read.

A changeset with runInTransaction="false" becomes a no-transaction migration, and one with ignore="true", which Liquibase never runs, is left out of the import and named on standard error. In formatted SQL, the lines an --ignoreLines directive skips are left out as well.

In formatted SQL, a /* liquibase rollback block becomes the down file the way a --rollback line does, and --rollback empty or --rollback not required becomes a down file that runs nothing. Liquibase joins the lines of a block with nothing between them, so DELETE FROM t and WHERE id = 1; on two lines run as DELETE FROM tWHERE id = 1;. A rollback that Liquibase would run as different SQL from what is written is refused by name; rewrite it as --rollback lines, which Liquibase keeps apart.

A property reference such as ${schema} is refused. Liquibase fills it in when it runs, and an environment variable or command-line parameter of that name wins over the changelog’s own property, so the changelog does not say what ran. Write the value in before you import.

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:

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

Clean up

Bash
PowerShell

cd ..
rm -rf ptah-from-liquibase

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

Where this leaves you

Contexts, labels, dbms and preconditions have no destination in a migration directory, which is why a changeset carrying one is refused rather than imported without it. Deciding what they meant is work that belongs in Liquibase: a context is usually the reason a changeset ran in one environment and not another, a dbms the reason it ran on one database and not another, and once the changelog is split along that line each half converts.

If a database already has Liquibase’s DATABASECHANGELOG table, do not run ptah migrations up against it. Record the history as already applied first: see Adopt an existing database.

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