You really benefit from a version-based database migration tool and process when you update an existing database. And that’s what I want to show you in this post. It allows you to evolve your database together with the code and to perform the required update operations when you install a new version of your application.

As I explained in the previous post, the general idea is to create a changeLog script for each software release. This script consists of one or more changeSets. Each of these changeSets describes one logical change that needs to be performed to adapt the database to the new software version.

OK, let’s update the test database to version 1.1. The update process consists of 3 parts:

You should create a backup and tag the current version of the database so that you can roll back all your changes if necessary.

While you implement your code changes, you should define a changeLog that describes the required changes of your database.

And when you install your update, you need to execute the database migration and roll it back if any error occurs.

Tag the current database

The tag is not required to roll back your changes. Liquibase can also roll back the executed changeSets one by one without referencing a tag. But I prefer to tag my database before I perform any updates. That gives me a defined state to which I can go back easily if anything goes wrong.

You can create a tag with Liquibase’s command line client by calling the tag command with the name of the tag. And as you can see in the following code snippet, you also need to provide the connection information for your database.

You now have a tag called “v1.00” which defines the database state before you performed any update operations.

As I will explain later, Liquibase’s rollback mechanism uses a set of DDL statements to execute the inverse operations for each changeSet. That is a good approach as long as you can define the reverse operation in an SQL statement.

You most often can’t do that when you remove or change any records in your database. So, you should create a backup before you execute any update. Please check your database documentation to learn more about creating and restoring a backup.

OK, let’s define a changeLog that describes a set of database changes.

Define the Update ChangeLog

That is an ongoing task which you should perform while you implement your changes. It’s one of the benefits of a version-based database migration that you can implement the required update scripts together with your source code. That helps you to update your test and development systems and makes sure that you don’t miss any changes.

I explained the structure of the changelog file in more detail in the first post of this series. If you’re not already familiar with it, you should take a look at it before you continue to read this post.

There is just one thing I want to add. I wrote in the first post that you should have 1 changelog file for each software update. So, you have 1 file for version 1.0 and another one for version 1.1. As soon as you have more than 1 changelog file, you should add a master changelog that includes all other files. So, for this series of posts, I have a db.changelog.xml file which includes the files db.changelog-1.0.xml and db.changelog-1.1.xml.

You can then provide the master changelog to the Liquibase client. It will iterate through the included files and check which changeSets need to be executed to update the database to the latest version.

OK, now you just need to describe the required update operations. Here are a few examples of the most important ones.

As you can see, this changeSet also contains a rollback tag. That’s because Liquibase doesn’t generate a rollback operation when you create a new database table. If you want to remove the table when you perform a rollback, you need to use the rollback tag to provide your own rollback operation. You can use it with all other Liquibase tags, or you can provide SQL statements that shall be executed. In this example, I use the dropTable tag which I will explain in more detail in a later section of this post.

Rename a Table

The next example renames the author table to book_author. You can do that with a renameTable tag. It requires 2 attributes: the oldTableName and the newTableName. You can also define the catalogName and schemaName, if you like.

You don’t need to provide the rollback operation when you rename a table. Liquibase can generate the required statement. But you can use the rollback tag to override the generated statement.

Drop a Table

When you create and rename database tables, you might also need to drop a table that you no longer need. You can do that with the dropTable tag. As you can see in the following code snippet, you just need to provide the tableName as an attribute.

Before you drop a database column, you should create a backup of your database. Liquibase can’t generate the rollback operation. You can, of course, use the rollback tag to create the dropped column. But in most cases, you will need a database backup to recreate the deleted data.

Merge 2 Columns

OK, there is one more column related update operation that I want to show you. You can also merge 2 table columns into 1. This operation creates a new table column, sets the concatenated value of the 2 old columns as the value of the new one and drops the 2 old table columns.

That sounds like a complex operation but its definition is pretty simple. You just need a mergeColumn tag and provide:

the tableName

the finalColumnName and its finalColumnType

the names of the 2 old columns as column1Name and column2Name

and an optional joinString.

The following XML snippet shows you an example that joins the columns first_name and last_name into the new column name. All columns are of type VARCHAR(255) and I use a ” ” as the joinString.

A merge of 2 database columns is another operation that Liquibase can’t rollback automatically. I always recommend to create a database backup before you perform this operation and to use that instead of a set of rollback operations.

But if you can define the reverse operations as a set of SQL statements, you can, of course, also specify them in a rollback tag. But keep in mind that you not only need to create the old table columns, you also need to split the merged data. And that is almost always the bigger challenge.

Update Database Records

That is the last update operation I want to show you. When you migrate your database, you often not only need to change the structure of it, you also need to update its data.

You can do that with Liquibase’s update tag. It requires the tableName on which you want to perform the update operation and you can provide one or more column tags to define the update operation. If you don’t want to perform the update on all records in the table, you can add a where tag to specify the WHERE clause of the SQL UPDATE statement.

Similar to the previously described merge operation, Liquibase is not able to generate the rollback statements. You should, therefore, create a database update before you perform an update operation. Or you can define the statements for the rollback operation in a rollback tag.

Execute the Update

As I explained in the beginning, you should use a master changelog file which includes all changelogs for your application. You can then provide this file to the Liquibase client. It will check all included changeLog files and determine which changeSets need to be executed. The client will then generated the required SQL statements and either export or execute them. I always prefer to export the statements first so that I can take a look at them myself and provide them to one of the database administrators.

The following snippet shows an example in which I tell Liquibase to connect to my local PostgreSQL database, to determine the required changes and to generate the SQL statements.

I provided the master changelog file to the client. As you can see in the output, it recognizes that the changeSets in db.changelog-1.0.xml were already executed. It only generates the SQL statements for the changeSets in db.changelog-1.1.xml.

Each update operation consists of 2 parts. These are 1 or more SQL statements to perform the update and an additional SQL INSERT statement to document the execution of the changeSet.

After you reviewed the generated statements, you can call the update command with the same parameters. Liquibase will then find and execute the required changeSets to migrate the database to the latest version.

Summary

After we created a new database in the first post of this series, we now added a master changelog file and an additional changelog to update the database to version 1.1.

As you have seen, Liquibase provides several XML tags for defining the required update operations. You can use them in changeSets which you can organize in one or more changelog files. Liquibase identifies the changeSets it needs to execute and performs the required update operations.

When an error occurs, Liquibase rolls back the changeSets one by one. So, please make sure that each changeSet groups the required operation to perform only one logical change.

And keep in mind that you can’t roll back all migration steps. If you delete any data, it’s most often impossible to reproduce it from the existing information. So, better create a database backup before you start the migration.