Using Liquibase with CockroachDB

CockroachDB is a distributed database with standard SQL for cloud applications. You can run CockroachDB on your local machine or use a cloud cluster.

Supported versions

  • 22.X
  • 21.X
  • 20.X

Verification level

Foundational: All applicable Liquibase Open Source functionality and partial Liquibase Pro functionality works with this database. Verified by automated and real-world testing from Liquibase. The Liquibase support team provides best-effort usage and bug-fix support to Commercial users.

Note: A database's verification level indicates how well it works with different features in Liquibase and across different products, such as Liquibase Open Source and Liquibase Pro. For more information, see Liquibase Database Tutorials.

Prerequisites

Install drivers

To use Liquibase and CockroachDB, you need the JDBC driver JAR file (Maven download).

The latest version of Liquibase has a pre-installed driver for this database in the liquibase/internal/lib directory. Read more: Adding and Updating Liquibase Drivers.

If you use Maven, you must include the driver JAR as a dependency in your pom.xml file. Read more: Configuring Liquibase Attributes in your Maven POM File.

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.4.0</version>
</dependency>

Test your connection

  1. Ensure your CockroachDB is configured:
    1.  You can check its status depending on your cluster setup. For example, you can check basic network connectivity (ping), port connectivity (telnet), and certificate validity. See the Troubleshoot Cluster Setup for more details.
    2. Generate or check TLS certificates for the user that you created during a secure CockroachDB cluster setup. Use the cockroach cert command to generate the certificates:
    3. cockroach cert create-client user --certs-dir=certs --ca-key=my-safe-directory/ca.key --also-generate-pkcs8-key
  2. Specify the database URL in the Liquibase properties file. Liquibase does not parse the URL. You can either specify the full database connection string or specify the URL using your database's standard JDBC format:
  3. CockroachDB on-premises

    url:jdbc:postgresql://localhost:26257/database?ssl=true&sslmode=require&sslrootcert=/full-path/certs/ca.crt&sslkey=/full-ath/certs/client.user.key.pk8&sslcert=/full-path/certs/client.user.crt

    When using CockroachDB on-premises and specifying the URL, enter your IP address or host name, and then the port followed by the database name. An example of the format is: jdbc:postgresql://<IP OR HOSTNAME>:<PORT>/<DATABASE>.

    The SSL connection parameters to the full paths of the certificates that you generated are optional. A key in PKCS#8 format is the standard key encoding format in Java. As an alternative, you can use the URL without SSL connection parameters by specifying the username and password attributes:

    url: jdbc:postgresql://localhost:26257/dev
    username: root
    password: password

    CockroachDB cloud

    url: jdbc:postgresql://liquibase-3r8.aws-us-east-2.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=liquibase-ca.crt

    When using a CockroachCloud instance and specifying URL, enter a global host name and the port 26257 by referring to the CockroachCloud website. Also, add the database name with the SSL mode and the path to the CA certificate to your URL.

    Tip: To apply a Liquibase Pro key to your project, add the following property to the Liquibase properties file: liquibaseProLicenseKey: <paste code here>

  1. Create a text file called changelog (.xml, .sql, .json, or .yaml) in your project directory and add a changeset.
  2. Navigate to your project folder in the CLI and run the Liquibase status command to see whether the connection is successful:
  3. liquibase --username=test --password=test --changelog-file=<changelog.xml> status

    Note: You can pass arguments in the CLI or keep them in the Liquibase properties file.

  4. Inspect the SQL with the update-sql command. Then make changes to your database with the update command.
  5. liquibase --changelog-file=<changelog.xml> update-sql
    liquibase --changelog-file=<changelog.xml> update
  6. From a database UI tool, ensure that your database contains the test_table you added along with the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.

Note: CockroachDB has limited support for online schema changes in transactions. To avoid issues with incomplete transactions, you can set the runInTransaction attribute to false. However, take into account that if this attribute is set to false and an error occurs part way through running a changeset that contains multiple statements, the Liquibase DATABASECHANGELOG table will be left in an invalid state.

Related links