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:
- Install CockroachDB, and start a secure cluster. When starting your cluster, make sure that you generate cluster certificates, create the
bank
database, and create themax
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. 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.
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 theliquibase-4.2.0-bin
folder) to your machine'sPATH
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 theliquibase
path to thePATH
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 latest JDBC driver from the PostgreSQL website.
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.
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 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.In the same directory, create the SQL file specified by the first changeset:
$ touch create.sql
Add the following
CREATE TABLE
statement to thecreate.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 namedaccount
.Now let's use the XML format to define the second changeset. Directly after the first
changeSet
element inchangelog-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 theaccount
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.
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.
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 aliquibase.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)
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 theaccounts
table, with the default value as a randomly-generated UUID.In the
changelog-main.xml
file, add the following after the secondchangeSet
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 theunique_id
column.In the
changelog-main.xml
file, add the following after the thirdchangeSet
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 doesn't guarantee the atomicity of 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.
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