Using Liquibase with Cloud Spanner

Cloud Spanner is a fully managed relational database with unlimited scale and strong consistency. It optimizes performance by automatically sharding the data based on request load and size of the data.

Note: For more information, see the Cloud Spanner documentation.

You can use the Cloud Spanner Liquibase Extension to manage database schema changes with Liquibase. With the Cloud Spanner Liquibase extension, you can enable Liquibase to target Cloud Spanner. All Cloud Spanner features, with the exception of some limitations, are supported.

Additionally, the example changelog.yaml included with the Cloud Spanner Liquibase extension demonstrates Liquibase features and how to use them with Cloud Spanner.

Supported Change Types

The following Liquibase Change Types are supported: createTable, dropTable, addColumn, modifyDataType, addNotNullConstraint, dropColumn, createIndex, dropIndex, addForeignKeyConstraint, dropForeignKeyConstraint, dropAllForeignKeyConstraints, addLookupTable.

The following data DML Change Types are supported: insert, update, loadData, loadUpdateData.

Driver Information

To use Liquibase and Cloud Spanner, you need to have the Cloud Spanner Liquibase extension file, which includes the extension, the Cloud Spanner SDK, and the Cloud Spanner JDBC driver:

  1. Go to the liquibase-spanner repository.
  2. Download the latest released extension liquibase-spanner-version-all.jar file.
  3. Place the liquibase-spanner-version-all.jar file in the liquibase/lib install directory.

Testing Your Connection

  1. Ensure you have created the Cloud Spanner instance and database.
  2. Give the extension temporary use of your own Cloud Spanner user credentials for API access by running the following gcloud command:
  3. gcloud auth application-default login
  1. Configure URL in the format:
  2. jdbc:cloudspanner:/projects/<project>/instances/<instance>/databases/<database>
  3. Create a changelog.yaml file, enter the following information, and save the changes:

    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)

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

  4. Run the Liquibase update command, replacing <URL> with the JDBC connection string, in the format jdbc:cloudspanner:/projects/<project>/instances/<instance>/databases/<database>. You can also specify the database URL in your liquibase.properties file. For more information, see Creating and configuring a liquibase.properties file.
  5. liquibase --changeLogFile=changelog.yaml --url= <URL> update
  1. Verify your changes. After running the first update, you will see three new tables added to your database: Singer, DATABASECHANGELOG, and DATABASECHANGELOGLOCK. You can verify the existence of these tables through the Cloud Console or gcloud tool. 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'
    Also, you can see a record of the changes that were applied by querying the contents of DATABASECHANGELOG.

Related Links