Version based database migration with Liquibase


Hi, I’m Thorben Janssen from thoughts-on-java.org and today, I want to talk about version-based database migration with Liquibase. Creating the database for your application seems to be easy as long as you don’t need to support multiple versions or work in huge teams. You just generate an SQL script from your database model or in the worst case, export the required statements from your test database. You can then execute it manually or use JPA to do that automatically. This approach doesn’t work as soon as you
need to support multiple versions of your application. And extracting the required changes from the test database becomes a huge mess when the size of your team and the number of changes increases. You can avoid some of these problems when you create the database update script while working on the next release, and store them alongside your code in git or whichever version control system you use. Whenever someone gets
the latest code changes, he will also get the required database changes. With the right tooling in place, you will even be able to apply these changes automatically. One tool that can help you with that is Liquibase. Liquibase is an open source tool released under APL 2.0. It provides you a set of tools to implement an automated, version based database migration for your application. Some of its benefits are the sequential execution of all required updates to get to the most recent database version, the automatic creation and execution of rollback operations for failed updates and the database-independent definition of the update operations. In this series of videos, I will show you how to use all of these benefits in your application. Let’s start with the creation of your database before I show you how to update it in the next video. The database change log is an XML, JSON, YAML
or SQL file which describes all changes that need to be performed to update the database. In most cases, you want to create 1 file for each release. Each file consists of one or more change sets. A changeSet describes a set of changes that Liquibase executes within one transaction. You can add as many changes to a set as you like. But to avoid any problems during a rollback, you shouldn’t define more than one logical change per set. Each changeSet gets identified by the name
of the author and an id. Liquibase stores this information together with the name of the change log file in the database changelog table to keep track on the executed change sets. Let’s have a look at 3 changeSets that create a new database table, a primary key, and a foreign key constraint. Liquibase allows you to do a lot more than that. You can change existing tables, define execution requirements, provide rollback commands to handle errors and load data. But for now, let’s focus on the creation of a new database. I will show you the other features in one of the following videos. Here you can see a changeSet that creates the table author with the columns id, firstname, lastname and version. The createTable element has to define the
name of the table you want to create. On top of that, you can specify other attributes,
like the name of the database schema or the tablespace. You also need to provide at least one column tag as a nested property. In this example, I use 4 of these tags, to define the 4 database columns of the author table. The attributes name and type are required to define a new column. Liquibase also supports a set of additional attributes which you can use to provide a default value, specify the encoding or set a comment. You can also use a constraints tag to define a primary key, not null, unique, foreign key or cascade constraint. I use it in this example to create a not null constraint for the id and version for the id and version columns. As you can see, the definition of the changeSet is simple and doesn’t require any SQL. As always, this can be a good and a bad thing. Liquibase will generate the SQL statements for the database you want to use. That allows you to create database-independent update scripts but puts you also at the risk of executing unexpected statements. You should therefore always test the generated SQL statements. I will show you how to export and run the SQL statements at the end of this video. If you didn’t define the primary key when you created the table, you can add the constraint with an addPrimaryKey tag. I use it here to add the primary key constraint to the id column of the book table. These two attributes are mandatory and you can provide additional ones to provide the name of the constraint, schema, catalog or tablespace. And you can add a foreign key constraint with an addForeignKeyConstraint tag. You then need to provide the name of the constraint, the baseColumnNames and baseTableName, that define the column and table to which you want to add the constraint, and the referencedColumnNames and referenceTableName, that define the column and table to which the foreign key points to. OK, you now understand the basic tags you can use to create the initial database for your application. But don’t worry, you don’t need to write this file yourself if you already have an existing database. You might want to add Liquibase to an existing application or you use a tool to model and export your table model. In these situations, it’s much easier to let Liquibase generate the required statements. You can do that by providing the connection information and the generateChangeLog command to the Liquibase command line client. Here you can see an example for my local PostgreSQL database. I tell the Liquibase client to use PostgreSQL’s JDBC driver to connect to the recipes schema
of my local database, to generate a changeLog that creates an identical database and to write it to the db.changelog-1.0.xml file in the myFiles directory. You can see the generated changeLog here. As you can see, Liquibase used my current username as the author and added ” generated” as a postfix and it also generated a prefix for the id. If you already have a database, generating the changeLog is a lot easier and faster than writing it yourself. But as always, don’t forget to review the generated changeSets so that you can be sure that they meet your expectations. Before you execute the changeLog, you should always export and review the generated SQL statements. Some database administrators also
need the script for their internal documentation or they plainly reject to perform any changes they
didn’t review themselves. In all these situations, you can use Liquibase’s command line client to generate the required SQL statements and write them to a file. Here you can see example in which I tell Liquibase to connect to my local PostgreSQL database, determine the required updates and generate the SQL statements. After you’ve created and checked the changeLog yourself or used the command line client to create it, you can choose between multiple options to execute it. I use the command line client in this video but you can also use a maven plugin to create the database as part of your build or deployment process or you can use a Servlet, Spring or CDI Listener to automatically create or update the database at application startup. Based on the changeLog, Liquibase create the tables and sequence in the test_liquibase schema. And as you can see here, Liquibase documented the execution of all changeSets in the databasechangelog table. It will use this information for future runs of the update process to determine which changeSets need to be executed. OK, that’s it for today. If you want to learn more about Hibernate,
you should join the free Thoughts on Java Library. It gives you free access to a lot of member-only content like an eBook about the Java 8 support in Hibernate 5 I’ll add the link to it to the video description below. And if you like today’s video, please give it a thumbs up and subscribe below.

Danny Hutson

Leave a Reply

Your email address will not be published. Required fields are marked *