In previous post, we saw how we can install and configure Liquibase. In this blog post, we’ll see how we can leverage Liquibase to deploy changes on the Microsoft SQL Server.

Download JDBC driver for SQL Server

For this, we first need to download the JDBC driver for the SQL Server. This can be downloaded from the Microsoft using this link. The download is available in both .exe format for Windows and .tar.gz format for Mac OS X or Linux. By default, it will extract the files in the same path as the zip resides. Note down the path where driver is extracted as we’ll need to refer this path.

Create Changelog files (Database Changes)

With all the setup of Liquibase, we are ready to execute the database changes using Liquibase. However, we first need to prepare the changes. As discussed alreay, the changelog files can be created using either YAML, XML, JSON or native SQL. Below is one example of the changelog file where we want to create a table with couple of columns in xml format:

Note how the change log uses a predefined XSD schema and how we are defining attributes for the changeset (id, author etc.). The actual change is contained within the changeset tag. We can also say which database and schema this is applicable to in the changeset as well using attributes for same as described in below samples.

The same change can be written in JSON as below:

And in YAML as below:

Formatted SQL Changelogs

You can also choose to write this in traditional SQL format. These are also known as ‘Formatted SQL files’ in liquibase terminology. Formatted SQL files use comments to provide Liquibase with metadata. Each SQL file must begin with the following comment:

--liquibase formatted sql

Each changeset in a formatted SQL file begins with a comment of the form

--changeset author:id attribute1:value1 attribute2:value2 [...]

So, our SQL equivalent for above change is like below:

Running Liquibase to deploy Changelog

Once the changesets and changelog is defined, we are ready to apply it to the database. We are going to mention this for SQL database, but the same can be applied to any Database Platform provided its in the supported list of databases. Since Liquibase is a command line tool, we’ll need to run it from the command line (in windows OS) and terminal (in case of Linux / Macintosh). The liquibase syntax to run command is as below:

java -jar liquibase.jar [options] [command]

We can safely skip the java -jar part if we have set it up as described in the previous post. There are a ton of options and commands available for the Liquibase. At a very minimum, the following details are required:

1. changeLogFile: This specifies the path and file name of the changelog
   username: The username Liquibase will connect to the database with
   password: password for the account Liquibase will connect to the database with
2. url: JDBC URL for the database instance and the database
3. driver: The JDBC driver class name. We would also need the location where driver is extracted and saved.
4. username: Username to establish database connection
5. password: Password to establish database connection

Note that the windows integrated authentication would not work. We can then use the below command to deploy the changelog:

We have broken down the command in separate lines for clarity’s purpose only. It needs to be passed in one line. You’ll need to replace the command with appropriate values in your case. Also note that the Liquibase is case sensitive in nature. If the deployment is successful, you should see an output like below:

Verify ChangeLog is deployed

By default, you would see only minimum output in latest versions. If we now go and expand created tables in the Database, we should be able to see our table created fine:

As we discussed previously, there will be two liquibase specific tables: DATABASECHANGELOG and DATABASECHANGELOGLOCK table. These two tables are automatically generated by Liquibase the first time it runs and is updated in every subsequent run.

Minimize liquibase command using liquibase.properties file

We do not need to type the lengthy command every time we need to deploy changes using Liquibase. Typing such a lengthy command can be very error prone. For this, we can save default necessary information in a special text file known as liquibase.properties in the directory where we are running Liquibase from. Again, the name of the file is case-sensitive in nature. Below is one of the examples of liquibase.properties file:

Note how the class path has been defined with relative to the current directory. This is because we are running Liquibase from C:\Liquibase directory. Also the path format uses the Java’s way of referring windows paths. Now we can deploy changelog using just below command:

How Liquibase Manages Database Integrity

As mentioned previously, in subsequent runs, Liquibase skips over the changeset which are already applied and does not try to recreate the table. That’s because at run time it compares the changeset’s id, author and the changelog file name with what’s stored in the DATABASECHANGELOG table. If there is an entry in the table, it checks the MD5 checksum stored in the table against the MD5 checksum computed from the file. If they are the same, Liquibase knows the changeset has already been applied, so it does not run it again. If the MD5 checksums are different, Liquibase throws an error and exits. If there is no entry in the table for that changeset, Liquibase applies the changeset.

To illustrate the point, we have modified the existing changeset in our changelog file for the customer table. We have decided to include a new column named ‘CustomerAddress’ here:

Let’s go ahead and apply this change. We should see an error like below:

Above error clearly demonstrates that MD5 Checksum of the file has been modified and that’s why it fails to proceed.

In next blog post, how we can leverage Liquibase to export DDL and DML information from existing databases.