Migrate CockroachDB Schemas with Liquibase

On this page

This page guides 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:

1. 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.
2. 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:

1. Download the latest version of the Liquibase command-line tool. CockroachDB is fully compatible with Liquibase versions 4.2.0 and greater. This tutorial uses the binary download of Liquibase 4.2.0 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.

2. Make a new directory for your Liquibase installation:

   $ mkdir liquibase-4.2.0-bin
   

3. Extract the Liquibase download to the new directory:

   $ tar -xvf liquibase-4.2.0.tar.gz -C liquibase-4.2.0-bin
   

4. 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.

5. 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:

1. Download the latest JDBC driver from the PostgreSQL website.

2. Place the driver in the lib directory of the Liquibase binary. For example:

   $ cp ~/Downloads/postgresql-{version}.jar liquibase-4.2.0-bin/lib/
   

Where {version} is the latest stable version.

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:

1. Create a file named changelog-main.xml:

   $ touch changelog-main.xml
   

2. 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 doesn't guarantee the atomicity of online schema changes in transactions with multiple statements. To avoid running into issues with incomplete transactions, we recommend setting the runInTransaction attribute to "false" on all changesets.

3. In the same directory, create the SQL file specified by the first changeset:

   $ touch create.sql
   

4. 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.

5. 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.

Step 5. Configure a Liquibase properties file

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

To configure Liquibase properties:

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

   $ touch liquibase.properties
   

2. 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)

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:

1. 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.

2. 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.

3. 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>
   

4. Now create a SQL file to update the primary key for the table with the new column:

   $ touch update_pk.sql
   

5. 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.

6. 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>
   

7. 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 doesn't guarantee the atomicity of schema changes within transactions with multiple statements. 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.

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

See Also

• Liquibase documentation
• Liquibase issue tracker
• Client connection parameters
• Third-Party Database Tools
• Learn CockroachDB SQL
• Build a Spring App with CockroachDB and JDBC
• Build a Spring App with CockroachDB and JPA

Was this helpful?