Version-Based Database Migration with Liquibase – Update an Existing Database

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

I showed you in the previous post of this series, how you can use Liquibase to create a new database. That’s a required first step but it’s not the one that provides you the most benefits. And there are several other ways to do the same, like JPA’s feature to execute SQL scripts at startup.

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:

1. You should create a backup and tag the current version of the database so that you can roll back all your changes if necessary.
2. While you implement your code changes, you should define a changeLog that describes the required changes of your database.
3. 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.

liquibase --driver=org.postgresql.Driver \
	--classpath=myFiles\postgresql-9.4.1212.jre7.jar \
	--changeLogFile=myFiles/db.changelog-1.0.xml \
	--url="jdbc:postgresql://localhost:5432/test_liquibase" \
	--username=postgres \
	--password=postgres \
	tag v1.00

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.

<databaseChangeLog
  xmlns""http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi""http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation""http://www.liquibase.org/xml/ns/dbchangelog/1.9
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
    <include file""myFiles/db.changelog-1.0.xml"/>
    <include file""myFiles/db.changelog-1.1.xml"/>
</databaseChangeLog>

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.

Add a Table

I already explained this changeSet in great detail in the 1st post of this series. So, I keep it short.

You can use a createTable tag to tell Liquibase to create a new database table. The follwing XML snippet creates the author table with the columns id, firstname, lastname and version.

<changeSet author="Thorben" id="1">
	<createTable tableName="publisher">
		<column name="id" type="BIGINT">
			<constraints nullable="false"/>
		</column>
		<column name="name" type="VARCHAR(255)"/>
		<column name="version" type="INT">
			<constraints nullable="false"/>
		</column>
	</createTable>
	<rollback>
		<dropTable tableName="publisher" />
	</rollback>
</changeSet>

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.

<changeSet author="Thorben" id="2">
	<renameTable  oldTableName="author" newTableName="book_author"/>
</changeSet>

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.

<changeSet author="Thorben" id="1">
	<dropTable tableName="publisher" />
</changeSet>

Be careful and create a database backup before you drop a table. Otherwise, you will not be able to restore any data that’s stored in the table.

Add a Column

Adding new columns to an existing table is another common update operation. Liquibase uses the addColumn tag to define this operation. It’s pretty similar to the createTable tag I showed you before.

You need to provide the tableName and one or more column tags. The column tag is the same as you used to create a new database table and it defines the database column you want to add to your table.

I use these tags in the following example to add the columns birthdate of type DATE and middlename of type VARCHAR(255) to the book_author table.

<changeSet author="Thorben" id="3">
	<addColumn tableName="book_author">
		<column name="dateofbirth" type="DATE"/>
		<column name="middlename" type="VARCHAR(255)"/>
	</addColumn>
</changeSet>

Liquibase can generate the rollback operation, so you only need to specify it if you want to override the generated statement.

Rename a Column

Sometimes, you also need to rename an existing database column. You can do that with the renameColumn tag. It requires the attributes tableName, oldColumnName and newColumnName.

The following XML snippet shows an example in which I rename the columns firstname, middlename and lastname to first_name, middle_name and last_name.

<changeSet author="Thorben" id="4">
	<renameColumn tableName="book_author" oldColumnName="firstname" newColumnName="first_name" />
	<renameColumn tableName="book_author" oldColumnName="middlename" newColumnName="middle_name" />
	<renameColumn tableName="book_author" oldColumnName="lastname" newColumnName="last_name" />
</changeSet>

You don’t need to provide a rollback tag for this operation. Liquibase generates the required statements.

Drop a Column

When you change your table model, you sometimes also need to remove columns that are no longer needed. You can do that with the dropColumn tag.

I use it in the example to remove the column middle_name from the book_author table.

<changeSet author="Thorben" id="5">
	<dropColumn tableName="book_author" columnName="middle_name" />
</changeSet>

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.

<changeSet author="Thorben" id="6">
	<mergeColumns tableName="book_author" finalColumnName="name" finalColumnType="VARCHAR(255)" column1Name="first_name" column2Name="last_name" joinString=" ">
</changeSet>

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.

<changeSet author="Thorben" id="1">
	<update tableName="book_author">
		<column name="name" value="Thorben Janssen"/>
		<where>name='Janssen'</where>
	</update>
</changeSet>

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.

liquibase --driver=org.postgresql.Driver \
	--classpath=myFiles\postgresql-9.4.1212.jre7.jar \
	--changeLogFile=myFiles/db.changelog.xml \
	--url="jdbc:postgresql://localhost:5432/test_liquibase" \
	--username=postgres \
	--password=postgres \
	updateSQL

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.

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: myFiles/db.changelog.xml
-- Ran at: 30.07.17 14:57
-- Against: postgres@jdbc:postgresql://localhost:5432/test_liquibase
-- Liquibase version: 3.5.3
-- *********************************************************************

-- Lock Database
UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = 'Laptop (10.0.75.1)', LOCKGRANTED = '2017-07-30 14:57:41.199' WHERE ID = 1 AND LOCKED = FALSE;

-- Changeset myFiles/db.changelog-1.1.xml::1::Thorben
CREATE TABLE public.publisher (id BIGINT NOT NULL, name VARCHAR(255), version INT NOT NULL);

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'Thorben', 'myFiles/db.changelog-1.1.xml', NOW(), 10, '7:caae5471cae7a496ee65c044163393e3', 'createTable tableName=publisher', '', 'EXECUTED', NULL, NULL, '3.5.3', '1419463287');

-- Changeset myFiles/db.changelog-1.1.xml::2::Thorben
ALTER TABLE public.author RENAME TO book_author;

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('2', 'Thorben', 'myFiles/db.changelog-1.1.xml', NOW(), 11, '7:01c6bbde8ef72e501bdf2e75c89173b7', 'renameTable newTableName=book_author, oldTableName=author', '', 'EXECUTED', NULL, NULL, '3.5.3', '1419463287');

...

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.

liquibase --driver=org.postgresql.Driver \
	--classpath=myFiles\postgresql-9.4.1212.jre7.jar \
	--changeLogFile=myFiles/db.changelog.xml \
	--url="jdbc:postgresql://localhost:5432/test_liquibase" \
	--username=postgres \
	--password=postgres \
	update

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.