1 of 13

BigQuery Schema Management using Liquibase (CICD for Databases)

Cloud Track

Samrat Priyadarshi

Cloud Engineer

Google

github.com/SamPriyadarshi

linkedin.com/in/sampriyadarshi

@SamPriyadarshi

2 of 13

Agenda

Why CI/CD for Databases
Liquibase
Key Concepts and Features
Demo
Q&A

3 of 13

Why CI/CD for Databases

4 of 13

Database schema migrations are an essential task for every software project. There are several different reasons why updates to the database are required, some examples are:

New features require new attributes in existing tables or entirely new tables
Bug fixes may lead to changes in names or data types in the database
Performance issues that require additional indexes in the database

Even in organizations that have adopted DevOps, manual rework is the norm when it comes to database schema and stored procedure changes. In fact, the faster the application release cycle, the more database professionals had to rework database changes.

5 of 13

Liquibase

Open-source database schema change management solution

6 of 13

Key Concepts

7 of 13

Changeset

A changeset is a unit of change that Liquibase runs on a database. A changeset is uniquely tagged by both an author and an id attribute (author:id), as well as the changelog file path. The id tag is only used as an identifier, it does not direct the order in that changes are run and does not have to be an integer. If you do not know or do not want to save the real author, use a placeholder value such as UNKNOWN. To execute the changeset, you must have both author and id.

Changelog

The changelog file is the source of all Liquibase changes. The changelog includes a sequential list of all database changes (changesets). Liquibase uses this changelog record to audit the database and enforce any changes that are not yet applied to the database. Changelogs can be in XML or JSON or YAML or SQL format and even mixing and matching distinct types of changelogs is also allowed.

8 of 13

DATABASECHANGELOGLOCK table

The DATABASECHANGELOGLOCK table assures that only one instance of Liquibase is running at a time. The DATABASECHANGELOG_ACTIONS table follows the object state and the SQL statements run during the deployment.

DATABASECHANGELOG table

This table is utilized by liquibase to track which changesets have been run. The table tracks each changeset as a row, specified by a combination of the “id”, “author”, and “filename” columns.

There is no primary key on the table. This is to avoid any database-specific constraints on key lengths. The “id”, “author”, and “filename” is unique across every row of the table.

10 of 13

Rollback:- The capability to undo changes is one of Liquibase’s most useful basic features. This can be taken out automatically or with the aid of a predefined SQL script. It is likely to use the automatically created rollback for commands like “create table”, “create view”, “add column”, and others. However, a few changes, like the “drop table” cannot be reversed. In that circumstances, rollback must be manually defined.

Update/Rollback SQL Output:- Rather than executing updates or rollbacks directly against the database, you can generate the SQL that would be executed for inspection and/or manual execution.

Future Rollback Output:- Before applying an update to a database, you can generate the SQL you would require to run in order to bring the database back to the state it is in now for inspection.

ChangeLog and ChangeSet preconditions:- Preconditions can be added to the changeLog or individual changeSets to inspect the state of the database before trying to execute them.

DBDoc:- You are able to generate Javadoc-style documentation for your existing schema and its history.

ChangeSet Contexts:- ChangeSets can be allocated “contexts” in which to run. Contexts are selected at runtime and can be utilized to have changesets that only run in test instances or other unique events.

ChangeSet checksums:- Whenever a changeSet is executed, Liquibase stores a checksum and can fail or change execution if it notices a change between the original definition of a changeSet when it was run and the current definition.

Diff Support:- Although Liquibase is built to use database comparisons for change management, there is help for it in Liquibase which is useful in many cases such as performing sanity checks between databases.