Liquibase with Spanner

Stay organized with collections Save and categorize content based on your preferences.

This page describes how to use the open source Spanner Liquibase Extension to manage database schema changes with Liquibase.

Liquibase is an open-source database-independent library for tracking, managing, and applying database schema changes. It supports SQL as well as declarative formats such as XML, YAML, and JSON. With the Spanner Liquibase Extension you can enable Liquibase to target Spanner. All Spanner features, with the exception of some limitations, are supported.

Installing Liquibase

Follow the instructions in the Liquibase documentation to install and configure Liquibase.

Installing the extension

  1. Navigate to the Spanner Liquibase Extension releases page on GitHub and select the latest release.

  2. Select and download the JAR file with the name liquibase-spanner-x.y.z-all.jar, where x.y.z represents the extension version number. For example, liquibase-spanner-1.0.2-all.jar.

  3. Place the downloaded JAR file in the Liquibase lib directory. The JAR file includes the extension, the Spanner SDK, and the Spanner JDBC driver driver.

  4. Configure the URL for Liquibase in the format jdbc:cloudspanner:/projects/<project>/instances/<instance>/databases/<database>

Using the extension

The example changelog.yaml included with the Spanner Liquibase Extension demonstrates many of the features of Liquibase and how to use them with Spanner.

Before you begin

  • Make sure you have completed the preceding steps to install the extension.

  • Make sure you have created a Spanner instance.

  • Make sure you have created a Spanner database.

  • Give the extension temporary use of your own Spanner user credentials for API access by running the following gcloud command:

    gcloud auth application-default login

Creating a changelog.yaml

  1. Enter the following YAML into your favorite editor.

    databaseChangeLog:
  - preConditions:
     onFail: HALT
     onError: HALT

  - changeSet:
     id: create-singers-table
     author: spanner-examples
     changes:
       - createTable:
          tableName: Singers
          columns:
            -  column:
                name:    SingerId
                type:    BIGINT
                constraints:
                  primaryKey: true
            -  column:
                name:    Name
                type:    STRING(255)

    This YAML defines a table called Singers with a primary key SingerId and a column called Name to store the singer's name.

  2. Save your changes as changelog.yaml.

Running Liquibase

Execute these commands against Liquibase using the following command, replacing <URL> with the JDBC connection string, in the format jdbc:cloudspanner:/projects/<project>/instances/<instance>/databases/<database>.

  liquibase --changeLogFile changelog.yaml --url <URL> update

Verifying your changes

The updates in the preceding step caused three new tables to be added to your database: Singer, DATABASECHANGELOG, and DATABASECHANGELOGLOCK.

You can verify the existence of these tables through the Google Cloud console or gcloud CLI. For example, running the SQL query SELECT * FROM INFORMATION_SCHEMA.TABLES returns a list of all tables in your database.

  gcloud spanner databases execute-sql <DB>  --instance=<INSTANCE> \
  --sql='SELECT * FROM INFORMATION_SCHEMA.TABLES'

You can see a record of the changes that were applied by querying the contents of DATABASECHANGELOG.

What's next