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.

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.

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>

  2. 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.

  3. 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 Liquibase properties file. For more information, see Specifying Properties in a Connection Profile.
    4. liquibase --changelog-file=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.

Supported Change Types

The following LiquibaseChange 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.

Related Links