Migrate CockroachDB Schemas with Liquibase

On this page

This page walks you through a series of simple database schema changes using the Liquibase command-line tool and the CockroachDB SQL shell.

For detailed information about using Liquibase, see the Liquibase documentation site.

Before you begin

Before you begin the tutorial, do the following:

Install CockroachDB, and start a secure cluster. When starting your cluster, make sure that you generate cluster certificates, create the bank database, and create the max user.
Download and install a Java Development Kit. Liquibase supports JDK versions 8+. In this tutorial, we use AdoptOpenJDK 8, but you can follow along with any JDK version 8+.

Step 1. Download and install Liquibase

To install the Liquibase binary on your machine:

Download the latest version of the Liquibase command-line tool. CockroachDB is fully compatible with Liquibase versions 4.2.0 and greater. We use the binary download of Liquibase 4.20, for macOS.

Note:

In this tutorial, we go through a manual installation, using a download of the binary version of the Liquibase command-line tool. If you are new to Liquibase, you can also use the Liquibase Installer to get started. The installer comes with some example properties and changelog files, an example H2 database, and a distribution of AdoptOpenJDK.
Make a new directory for your Liquibase installation:
```
$ mkdir liquibase-4.2.0-bin
```
Extract the Liquibase download to the new directory:
```
$ tar -xvf liquibase-4.2.0.tar.gz -C liquibase-4.2.0-bin
```
Append the full path of the liquibase binary (now located in the liquibase-4.2.0-bin folder) to your machine's PATH environment variable:
```
$ echo "export PATH=$PATH:/full-path/liquibase-4.2.0-bin" >> ~/.bash_profile
```
```
$ source ~/.bash_profile
```
Note:

If your terminal does not run .bash_profile at start-up, you can alternatively append the liquibase path to the PATH definition in .bashrc or .profile.

To verify that the installation was successful, run the following command:

$ liquibase --version

You should get output similar to the following:

####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Get advanced features and support at          ##
##      liquibase.com/support                     ##
##                                                ##
####################################################
Starting Liquibase at 13:38:36 (version 4.2.0 #18 built at 2020-11-13 16:49+0000)
Liquibase Version: 4.2.0
Liquibase Community 4.2.0 by Datical
Running Java under /Library/Java/JavaVirtualMachines/adoptopenjdk-8.jdk/Contents/Home/jre (Version 1.8.0_242)

Step 2: Download the PostgreSQL JDBC driver

The Liquibase command-line tool uses the PostgreSQL JDBC driver to connect to CockroachDB as a Java application.

To install the driver for Liquibase:

Download the JDBC driver from the PostgreSQL website.
Place the driver in the lib directory of the Liquibase binary. For example:
```
$ cp ~/Downloads/postgresql-42.2.9.jar liquibase-4.2.0-bin/lib/
```

Tip:

If you are using Liquibase in the context of a separate Java application, we recommend that you use a dependency management tool, like Maven, to download the driver.

Step 3. Generate TLS certificates for the `max` user

When you started a secure CockroachDB cluster, you should have created a user max. You should have also given this user the admin role, which grants all privileges to all databases on the cluster. In this tutorial, Liquibase runs schema changes as the max user.

To authenticate connection requests to CockroachDB from the Liquibase client, you need to generate some certificates for max. Use cockroach cert to generate the certificates:

$ cockroach cert create-client max --certs-dir=certs --ca-key=my-safe-directory/ca.key --also-generate-pkcs8-key

The --also-generate-pkcs8-key flag generates a key in PKCS#8 format, which is the standard key encoding format in Java. In this case, the generated PKCS8 key will be named client.max.key.pk8.

Step 4: Create a changelog

Liquibase uses changelog files to manage database schema changes. Changelog files include a list of instructions, known as changesets, that are executed against the database in a specified order. Liquibase supports XML, YAML, and SQL formats for changelogs and changesets.

Let's define a changelog with the XML format:

Create a file named changelog-main.xml:
```
$ touch changelog-main.xml
```

Add the following to the blank changelog-main.xml file:

<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">

    <changeSet id="1" author="max" runInTransaction="false">
        <validCheckSum>ANY</validCheckSum>
        <sqlFile path="create.sql"/>
    </changeSet>

</databaseChangeLog>

This first changeset uses the sqlFile tag, which tells Liquibase that an external .sql file contains some SQL statements to execute.

Tip:

CockroachDB has limited support for online schema changes in transactions. To avoid running into issues with incomplete transactions, we recommend setting the runInTransaction attribute to "false" on all changesets.

In the same directory, create the SQL file specified by the first changeset:
```
$ touch create.sql
```

Add the following CREATE TABLE statement to the create.sql file:

create table account
(
    id      int            not null primary key default unique_rowid(),
    balance numeric(19, 2) not null,
    name    varchar(128)   not null,
    type    varchar(25)    not null
);

When Liquibase runs, the first changeset will execute the statements in create.sql, creating a table named account.

Now let's use the XML format to define the second changeset. Directly after the first changeSet element in changelog-main.xml, add the following:

<changeSet id="2" author="max" runInTransaction="false">
    <insert tableName="account">
        <column name="id">1</column>
        <column name="name">Alice</column>
        <column name="balance" valueNumeric="500.00"/>
        <column name="type">asset</column>
    </insert>
    <insert tableName="account">
        <column name="id">2</column>
        <column name="name">Bob</column>
        <column name="balance" valueNumeric="500.00"/>
        <column name="type">expense</column>
    </insert>
    <insert tableName="account">
        <column name="id">3</column>
        <column name="name">Bobby Tables</column>
        <column name="balance" valueNumeric="500.00"/>
        <column name="type">asset</column>
    </insert>
    <insert tableName="account">
        <column name="id">4</column>
        <column name="name">Doris</column>
        <column name="balance" valueNumeric="500.00"/>
        <column name="type">expense</column>
    </insert>
</changeSet>

This second changeset uses the Liquibase XML syntax to specify a series of sequential INSERT statements that initialize the account table with some values.

When the application is started, all of the queries specified by the changesets are executed in the order specified by their changeset id values.

Tip:

When possible, we recommend limiting each changeset to a single statement, per the one change per changeset Liquibase best practice. This is especially important for online schema changes. For more information, see Liquibase and transactions.

Step 5. Configure a Liquibase properties file

Liquibase properties are defined in a file named liquibase.properties. These properties define the database connection information.

Note:

You can also set Liquibase properties with the liquibase command-line tool.

To configure Liquibase properties:

In the same directory as changelog-main.xml, create a liquibase.properties file:
```
$ touch liquibase.properties
```

Add the following property definitions to the file:

changeLogFile: changelog-main.xml
driver: org.postgresql.Driver
url: jdbc:postgresql://localhost:26257/bank?sslmode=verify-full&sslrootcert=/full-path/certs/ca.crt&sslkey=/full-path/certs/client.max.key.pk8&sslcert=/full-path/certs/client.max.crt
username: max

Note:

For url, the SSL connection parameters must specify the full paths of the certificates that you generated.

Step 6. Run Liquibase

To run Liquibase from the command line, execute the following command from the directory containing your liquibase.properties and changelog-main.xml files:

$ liquibase update

You should see output similar to the following:

Liquibase Community 4.2.0 by Datical
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Get advanced features and support at          ##
##      liquibase.com/support                     ##
##                                                ##
####################################################
Starting Liquibase at 13:59:37 (version 4.2.0 #18 built at 2020-11-13 16:49+0000)
Liquibase: Update has been successful.

When the changelog is first executed, Liquibase also creates a table called databasechangelog in the database where it performs changes. This table's rows log all completed changesets.

To see the completed changesets, open a new terminal, start the built-in SQL shell, and query the databasechangelog table:

$ cockroach sql --certs-dir=certs

> SELECT * FROM bank.databasechangelog;

  id | author |      filename      |           dateexecuted           | orderexecuted | exectype |               md5sum               |                                              description                                               | comments | tag  | liquibase | contexts | labels | deployment_id
-----+--------+--------------------+----------------------------------+---------------+----------+------------------------------------+--------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+----------------
  1  | max    | changelog-main.xml | 2020-11-30 13:59:38.40272+00:00  |             1 | EXECUTED | 8:567321cdb0100cbe76731a7ed414674b | sqlFile                                                                                                |          | NULL | 4.2.0     | NULL     | NULL   | 6762778263
  2  | max    | changelog-main.xml | 2020-11-30 13:59:38.542547+00:00 |             2 | EXECUTED | 8:c2945f2a445cf60b4b203e1a91d14a89 | insert tableName=account; insert tableName=account; insert tableName=account; insert tableName=account |          | NULL | 4.2.0     | NULL     | NULL   | 6762778263
(2 rows)

You can also query the account table directly to see the latest changes reflected in the table:

> SELECT * FROM bank.account;

  id | balance |     name     |  type
-----+---------+--------------+----------
   1 |  500.00 | Alice        | asset
   2 |  500.00 | Bob          | expense
   3 |  500.00 | Bobby Tables | asset
   4 |  500.00 | Doris        | expense
(4 rows)

Note:

Liquibase does not retry transactions automatically. If a changeset fails at startup, you might need to restart the application manually to complete the changeset.

Step 7. Add additional changesets

Suppose that you want to change the primary key of the accounts table from a simple, incrementing integer (in this case, id) to an auto-generated UUID, to follow some CockroachDB best practices. You can make these changes to the schema by creating and executing an additional changeset:

Create a SQL file to add a new UUID-typed column to the table:
```
$ touch add_uuid.sql
```
Tip:

Using SQL files to define statements can be helpful when you want to execute statements that use syntax specific to CockroachDB.
Add the following SQL statement to add_uuid.sql:
```
/* Add new UUID-typed column */
ALTER TABLE account ADD COLUMN unique_id UUID NOT NULL DEFAULT gen_random_uuid();
```
This statement adds a new unique_id column to the accounts table, with the default value as a randomly-generated UUID.

In the changelog-main.xml file, add the following after the second changeSet element:

<changeSet id="3" author="max" runInTransaction="false">
    <sqlFile path="add_uuid.sql"/>
</changeSet>

Now create a SQL file to update the primary key for the table with the new column:
```
$ touch update_pk.sql
```
Add the following SQL statement to update_pk.sql:
```
/* Change primary key */
ALTER TABLE account ALTER PRIMARY KEY USING COLUMNS (unique_id);
```
This statement alters the accounts primary key to use the unique_id column.

In the changelog-main.xml file, add the following after the third changeSet element:

<changeSet id="4" author="max" runInTransaction="false">
    <sqlFile path="update_pk.sql"/>
</changeSet>

To update the table, run liquibase update again:

$ liquibase update

You should see output similar to the following:

Liquibase Community 4.2.0 by Datical
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Get advanced features and support at          ##
##      liquibase.com/support                     ##
##                                                ##
####################################################
Starting Liquibase at 14:26:50 (version 4.2.0 #18 built at 2020-11-13 16:49+0000)
Liquibase: Update has been successful.

To see the completed changesets, open a new terminal, start the built-in SQL shell, and query the databasechangelog table:

$ cockroach sql --certs-dir=certs

> SELECT * FROM bank.databasechangelog;

  id | author |      filename      |           dateexecuted           | orderexecuted | exectype |               md5sum               |                                              description                                               | comments | tag  | liquibase | contexts | labels | deployment_id
-----+--------+--------------------+----------------------------------+---------------+----------+------------------------------------+--------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+----------------
  1  | max    | changelog-main.xml | 2020-11-30 13:59:38.40272+00:00  |             1 | EXECUTED | 8:567321cdb0100cbe76731a7ed414674b | sqlFile                                                                                                |          | NULL | 4.2.0     | NULL     | NULL   | 6762778263
  2  | max    | changelog-main.xml | 2020-11-30 13:59:38.542547+00:00 |             2 | EXECUTED | 8:c2945f2a445cf60b4b203e1a91d14a89 | insert tableName=account; insert tableName=account; insert tableName=account; insert tableName=account |          | NULL | 4.2.0     | NULL     | NULL   | 6762778263
  3  | max    | changelog-main.xml | 2020-11-30 14:26:51.916768+00:00 |             3 | EXECUTED | 8:7b76f0ae200b1ae1d9f0c0f78979348b | sqlFile                                                                                                |          | NULL | 4.2.0     | NULL     | NULL   | 6764411427
  4  | max    | changelog-main.xml | 2020-11-30 14:26:52.609161+00:00 |             4 | EXECUTED | 8:fcaa0dca049c34c6372847af7a2646d9 | sqlFile                                                                                                |          | NULL | 4.2.0     | NULL     | NULL   | 6764411427
(4 rows)

You can also query the account table directly to see the latest changes reflected in the table:

> SELECT * FROM bank.account;

  id | balance |     name     |  type   |              unique_id
-----+---------+--------------+---------+---------------------------------------
   1 |  500.00 | Alice        | asset   | 3d2b7da4-0876-4ddd-8626-b980cef3323e
   2 |  500.00 | Bob          | expense | 8917ce09-c7d2-42a0-9ee4-8cb9cb3515ec
   3 |  500.00 | Bobby Tables | asset   | b5dccde6-25fe-4c73-b3a2-501225d8b235
   4 |  500.00 | Doris        | expense | f37dc62e-a2d5-4f63-801a-3eaa3fc68806
(4 rows)

> SHOW CREATE TABLE bank.account;

      table_name      |                     create_statement
----------------------+------------------------------------------------------------
  bank.public.account | CREATE TABLE account (
                      |     id INT8 NOT NULL DEFAULT unique_rowid(),
                      |     balance DECIMAL(19,2) NOT NULL,
                      |     name VARCHAR(128) NOT NULL,
                      |     type VARCHAR(25) NOT NULL,
                      |     unique_id UUID NOT NULL DEFAULT gen_random_uuid(),
                      |     CONSTRAINT "primary" PRIMARY KEY (unique_id ASC),
                      |     UNIQUE INDEX account_id_key (id ASC),
                      |     FAMILY "primary" (id, balance, name, type, unique_id)
                      | )
(1 row)

Liquibase and transactions

By default, Liquibase wraps each changeset within a single transaction. If the transaction fails to successfully commit, Liquibase rolls back the transaction.

CockroachDB has limited support for online schema changes within transactions. If a schema change fails, automatic rollbacks can lead to unexpected results. To avoid running into issues with incomplete transactions, we recommend setting the runInTransaction attribute on each of your changesets to "false", as demonstrated throughout this tutorial.

Note:

If runInTransaction="false" for a changeset, and an error occurs while Liquid is running the changeset, the databasechangelog table might be left in an invalid state and need to be fixed manually.

Transaction retries

When multiple, concurrent transactions or statements are issued to a single CockroachDB cluster, transaction contention can cause schema migrations to fail. In the event of transaction contention, CockroachDB returns a 40001 SQLSTATE (i.e., a serialization failure).

Liquibase does not automatically retry transactions. To handle transaction failures, we recommend writing client-side transaction retry logic. For more information about client-side transaction retries in CockroachDB, see Transaction Retries.

Liquibase integrations

You can run Liquibase in the context of a Java application framework, like Spring Boot. For examples of using Liquibase for schema management in a Spring Boot application built on CockroachDB, see Build a Spring App with CockroachDB and JDBC and Build a Spring App with CockroachDB and JPA.

For documentation on running Liquibase with other tooling, see the Liquibase documentation site.

Report Issues with Liquibase and CockroachDB

If you run into problems, please file an issue on the Liquibase issue tracker, including the following details about the environment where you encountered the issue:

CockroachDB version (cockroach version)
Liquibase version
Operating system
Steps to reproduce the behavior

Pricing

Contact us

Sign In

Migrate CockroachDB Schemas with Liquibase

Before you begin

Step 1. Download and install Liquibase

Step 2: Download the PostgreSQL JDBC driver

Step 3. Generate TLS certificates for the `max` user

Step 4: Create a changelog

Step 5. Configure a Liquibase properties file

Step 6. Run Liquibase

Step 7. Add additional changesets

Liquibase and transactions

Transaction retries

Liquibase integrations

Report Issues with Liquibase and CockroachDB

See Also

Migrate CockroachDB Schemas with Liquibase

Before you begin

Step 1. Download and install Liquibase

Step 2: Download the PostgreSQL JDBC driver

Step 3. Generate TLS certificates for the max user

Step 4: Create a changelog

Step 5. Configure a Liquibase properties file

Step 6. Run Liquibase

Step 7. Add additional changesets

Liquibase and transactions

Transaction retries

Liquibase integrations

Report Issues with Liquibase and CockroachDB

See Also

Step 3. Generate TLS certificates for the `max` user