# Migrate from Liquibase

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

Source: https://docs.ptah.run/v0.10.0/migrate-from/liquibase/

import { Tabs, TabItem } from '@astrojs/starlight/components';

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](../../start/install/) if necessary.
- A terminal and about eight minutes.

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

## Convert a formatted-SQL changelog

### Build the changelog

<Tabs syncKey="shell">
<TabItem label="Bash">

```bash
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
```

</TabItem>
<TabItem label="PowerShell">

```powershell
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
```

</TabItem>
</Tabs>

### Run the import

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

Expected output on standard output:

```text
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

<Tabs syncKey="shell">
<TabItem label="Bash">

```bash
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
```

</TabItem>
<TabItem label="PowerShell">

```powershell
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
```

</TabItem>
</Tabs>

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

Expected output on standard output:

```text
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

<Tabs syncKey="shell">
<TabItem label="Bash">

```bash
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
```

</TabItem>
<TabItem label="PowerShell">

```powershell
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
```

</TabItem>
</Tabs>

```console exits=2
ptah migrations import --from liquibase --source-dir ./typed/legacy --migrations-dir ./typed/migrations
```

Expected output on standard error:

```text
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:

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

Expected output on standard output:

```text
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:

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

Expected output includes, on standard output:

```text
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.

<Tabs syncKey="shell">
<TabItem label="Bash">

```bash
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
```

</TabItem>
<TabItem label="PowerShell">

```powershell
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
```

</TabItem>
</Tabs>

```console exits=2
ptah migrations import --from liquibase --source-dir ./conditional/legacy --migrations-dir ./conditional/migrations
```

Expected output on standard error:

```text
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](../../versioned/import/) 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

```console
ptah migrations validate --dir ./migrations
```

Expected output on standard output:

```text
OK: migrations directory matches ptah.sum
```

### Apply the converted directory

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

Expected output includes, on standard output:

```text
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

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

Expected output includes, on standard output:

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

## Clean up

<Tabs syncKey="shell">
<TabItem label="Bash">

```bash
cd ..
rm -rf ptah-from-liquibase
```

</TabItem>
<TabItem label="PowerShell">

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

</TabItem>
</Tabs>

## 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](../../start/adopt-an-existing-database/).

[Import an existing migration directory](../../versioned/import/) covers the
other source tools and the format table.