Integrate Spanner with Liquibase

This page describes how to manage Spanner database schema changes with Liquibase for GoogleSQL-dialect databases and PostgreSQL-dialect databases.

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.

Liquibase can target Spanner databases. It supports all Spanner features, with some limitations.

• To see general limitations, see limitations.
• To see additional information for PostgreSQL-dialect databases, such as Liquibase requirements, supported change types, and limitations, see PGAdapter and Liquibase.

Install Liquibase

To use Liquibase with GoogleSQL-dialect databases, you have to install the Spanner Liquibase extension. For PostgreSQL-dialect databases, Liquibase can use its built-in PostgreSQL support in conjunction with PGAdapter.

 jdbc:cloudspanner:/projects/PROJECT/instances/INSTANCE/databases/DATABASE
 

Your `liquibase.properties` configuration file can contain only this
property. Other properties are optional.

  jdbc:postgresql://localhost:5432/DATABASE_NAME?options=-c%20spanner.ddl_transaction_mode=AutocommitExplicitTransaction
  

Review the Liquibase samples

Liquibase quickstart

This quickstart shows you how to use Liquibase to add a Singers table to a database.

Before you begin

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

• Create a Spanner instance.

• Create a GoogleSQL-dialect database or PostgreSQL-dialect database.

• For PostgreSQL-dialect databases only, ensure that PGAdapter is started and running on the same machine as your Liquibase installation. For more information, see Start PGAdapter.

• For PostgreSQL-dialect databases only, use the create_database_change_log.sql script to create the databasechangeloglock and databasechangelog metadata tables. You must create these tables to override the tables that Liquibase creates automatically in your database. This is to ensure that the correct PostgreSQL data types for Spanner are used in these tables.

  You can run the script with the following command:

  psql -h localhost -d DATABASE_NAME -f create_database_change_log.sql
  

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

  gcloud auth application-default login
  

Create 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
                     primaryKeyName: pk_Singers
               -  column:
                   name:    Name
                   type:    VARCHAR(255)
   

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

   For PostgreSQL-dialect databases, we recommend using all lower case for table and column names. For more information, see PostgreSQL case sensitivity.

   Note that the createTable change set must include a primary key constraint, and the name of the primary key constraint must be pk_table_name.

2. Save your changes as changelog.yaml.

Run Liquibase

Apply the changeset in changelog.yaml by executing the following command:

liquibase --changeLogFile changelog.yaml update

Liquibase uses the URL that you defined in the liquibase.properties file. You can override the value in the file by adding the following argument to the preceding command:

--url URL

Verify your changes

The updates in the preceding step caused the Singer table to be added to your database. Also, the DATABASECHANGELOG and DATABASECHANGELOGLOCK tables were added (GoogleSQL-dialect database) or updated (PostgreSQL-dialect database).

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 DATABASE_NAME --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

• For more documentation, visit the Spanner Liquibase Extension GitHub repository.

• To learn more about Liquibase, see Getting Started with Liquibase.