Using Liquibase as a Solution for Deploying and Tracking MySQL Schema Changes

Using Liquibase as a Solution for Deploying and Tracking MySQL Schema Changes

Database-as-code service is a new concept and gaining some popularity in recent years. As we already know, we have deployment solutions for application code. Managing and tracking application changes are quite easy with tools like Git and Jenkins.

Now this concept is applied in the database domain as well, assuming SQL as a code to manage database changes (DDL, DML) the same way that applications handle code. From a database standpoint, this allows tracing the history of modifications, allowing problems to be quickly detected and addressed.

What is Liquibase?

Liquibase is an open source, database-independent framework for deploying, managing, and tracking database schema changes. All the modifications or changes to the database are stored in text files (XML, YAML, JSON, or SQL) known as changesets. To specifically list database changes in order, Liquibase employs a changelog. The changelog serves as a record of changes and includes a list of changesets that Liquibase can execute on a target database.

Let’s see how we can set up Liquibase and perform some database changes with this tool.

Installing Liquibase

1. Download and extract Liquibase files.

2. Define the installation directory to the environment path and add the same in “~/.bashrc” file as well.

3. Validate the installation.

Note – Java(JDK) needs to be set up on your system for Liquibase to function.

How to use Liquibase with MySQL

1. To use Liquibase and MySQL, we need the JDBC driver JAR file. Copy the jar file in liquibase internal library.

2. First, initialize a project with the options below.

Let’s understand what these parameters are.

3. We can then create a manual file (“changelog.sql”) in the project location and define the changeset. Other formats for defining the deployment changes include (.xml, .json, or .yaml). 

4. Validate the connection to the database is successful. Inside the Liquibase project folder, run the below command.

5. Inspect the SQL before execution.

6. Finally, deploy the changes.

Output:

7. Validate the changes in the database. Additionally, you observe below extra tables in the same database which captures a few more stats of the execution.

a)  Table: DATABASECHANGELOG

Note – Each changeset is tracked in the table as a row and is identified by the id, author, and filename fields.

b) Table: DATABASECHANGELOGLOCK

Note – To guarantee that only one instance of Liquibase is running at once, Liquibase employs the DATABASECHANGELOGLOCK table.

8. By running the command “liquibase history” we can check the past deployments as well.

So here we have successfully deployed the changes in the target database. Next, we will see how we can track the changes and perform rollback operations.

In order to perform rollbacks with respect to certain DDL or  DML we need to add rollback changeset details in the .sql file.

Let’s see the steps to perform the rollback operations

1) Create the deployment file “deployment.sql” inside the project location with the below changeset details.

Option details:

AJ:1 => denotes author:id

labels:label1 => Specifies labels that are a general way to categorize changesets like contexts.

context:context1 => Executes the change if the particular context was passed at runtime. Any string can be used for the context name

2) Next, run the deployment. Run the below command inside the project location.

3) Now, add the tagging to manage rollback scenarios.

4) By default, the tagging will be added in the recent deployments. We can check the same in the below table.

5) Let’s roll back the executed changes. This command will revert all changes made to the database after the specified tag.

Output:

Note – Post this activity, we don’t see those tables anymore in the database (P1,P2). As a rollback operation these tables were dropped now.

Alternatively, we can perform the rollback activity on the basis of timestamps as well. The below command is used to revert all changes made to the database from the current date to the date and time you specify. 

Output:

Liquibase integration with Percona Toolkit (pt-osc)

A Liquibase extension is available to enable the pt-online-schema-change feature of the Percona Toolkit. With the use of pt-osc rather than SQL, this extension substitutes several default changes. With the aid of the pt-online-schema-change tool, you can upgrade a database without locking any tables.

Let’s see the steps to use pt-osc with Liquibase extension

1) Download the Percona Liquibase jar file.

2)  Copy the jar file to Liquibase internal library folder.

3) Add the below changeset in changelog.xml file which basically adds one column (“osc”) in table:liq1.

Note – here we mentioned using Percona=”true” which enable the DDL execution via pt-osc.

4) Validate the deployment changes before actual implementation. We can see below the pt-osc command reference which is going to be executed in the next phase. 

Output

5) Finally, run the deployment.

Summary

Apart from MySQL, Liquibase supports other popular databases like (PostgreSQL, Cassandra, and MongoDB). The developers benefit greatly from this since they can collaborate to write their own scripts that relate to the database and then commit them as part of their code. It maintains the versions of all the changes like any other version control change and supports branching and merging of SQL code.

Further reading