Agile database migrations with Liquibase: How I learned to love my inner DBA

Datetime:2016-08-23 03:32:59          Topic: Agile Development           Share

The database. We all know the importance of it. We know that we need its ability to let us perform multiple tasks with ease, manage data efficiently, and store a vast amount of information within a single application. But unless you’re a DBA or database developer, you’re not likely to immerse yourself intothe database world voluntarily.

My organization has highly skilled and professional DBAs, but given our agile work methodologies, frequent releases, and tightening schedules, we realized that when developing our new database application, any modifications made to our database would become our responsibility and ours alone.  With agile development practices , the releases are frequent and the schedules are tighter. We have to take care of the databases on our own, without a dedicated DBA.

Our DBAs have to always be aware of the latest emerging technologies and new design approaches and be able to effectively design, implement, and maintain the database system. As a result, we knew we needed a tool that at the very least would help us create a database schema and constantly migrate changes to the database.

And so our search began…

2016 Tech & Software Conferences 93-page Guide

Finding the right tool, and the right fit

There are several tools out there for continuous database migration. Finding one that provides a way for effectively managing and tracking schema changes is a must for any organization. For our purposes, we concentrated on finding a tool that would also best fit the following needs:

  1. Simplicity . We are not database experts. For our continuous deliveries and day-to-day work, we needed something easy and convenient that even the less experienced developers on our team could manage. And although we will likely face more challenging issues down the line, we know we have strong backup from our DBAs for these scenarios.
  2. Multiple database types . We use several databases, including Oracle, PostgreSQL, and HSQL. We needed a tool that would work in the same manner with all of them and support all of them seamlessly.
  3. Schema integrity . We wanted to be sure that the schema was correct and compatible with any modifications made thus far. 

One tool we looked into was Flyway , an open-source migration tool that favors simplicity and convention over configuration. The problem with Flyway for us was its reliance on raw SQL. We have several different database types, and we determined that it would be easier to write XML and let another tool write the proper SQL command for the different databases.

We looked at other tools as well, but after several inquiries and consultations with our DB experts, we came to the conclusion that for our needs, Liquibase  was the best solution. Liquibase supports multiple database types and gave us the ability to compare two database schemas and easily identify changes. Liquibase also synced well with our fast-moving agile environment, by simplifying our deployment and removing unneeded complexity.

Getting started with Liquibase

As promised in the Liquibase Quick Start Guide , we found that Liquibase was easy to implement and use. We started by running these steps:

  1. Add a Liquibase artifact as a dependency.
  2. Write a “Changelog” XML file that described our schema and our schema changes.
  3. Run Liquibase when our application started.

In the Changelog file, each section is called a “ChangeSet.” A change set can be a table creation, index creation, or other modification such as adding a column to an existing table.

Creating a table with an index and unique constraint is straightforward in Liquibase:

<span>&lt;changeSet id=&quot;4&quot; author=&quot;John Smith&quot; context=&quot;addon_tables&quot;&gt;<br /></span>&nbsp; &nbsp; &nbsp; &lt;preConditions onFail=&quot;MARK_RAN&quot;&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;not&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;tableExists tableName=&quot;APPLICATION&quot;/&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;/not&gt;<br />&nbsp; &nbsp; &nbsp; &lt;/preConditions&gt;<br /><br />&nbsp; &nbsp; &nbsp; &lt;createTable tableName=&quot;APPLICATION&quot;&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;column name=&quot;APPLICATION_ID&quot; type=&quot;varchar2(32)&quot;&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;/column&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;column name=&quot;APPLICATION_NAME&quot; type=&quot;varchar2(250)&quot;&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;constraints nullable=&quot;false&quot;/&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;/column&gt;<br />&nbsp; &nbsp; &lt;/createTable&gt;<br />&nbsp;&nbsp;&nbsp; &nbsp; &lt;createIndex tableName=&quot;APPLICATION&quot; indexName=&quot;APPLICATION_IX&quot;&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;column name=&quot;APPLICATION_ID&quot;&gt;&lt;/column&gt;<br />&nbsp; &nbsp; &nbsp; &lt;/createIndex&gt;&nbsp;<br /><br />&nbsp; &nbsp; &nbsp; &lt;addUniqueConstraint<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; columnNames=&quot;APPLICATION_NAME&quot;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; constraintName=&quot;APPLICATION_UK&quot;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tableName=&quot;APPLICATION&quot;/&gt;<br />&nbsp; &lt;/changeSet&gt;

When our application started, we simply invoked Liquibase, and our schema was ready to go.

Liquibase liquibase = new Liquibase(/*path to our changeset xml*/,

new ClassLoaderResourceAccessor(),

databaseConnections);

<span>liquibase.update(LIQUIBASE_CONTEXT);</span>

Modifying the Liquibase schema

After we launched our product, requirements from our project management team and feedback from the field started to flow. Some of these resulted in a need to modify the schema.

This was simple enough for us. In Liquibase it’s easy to perform database changes without DBA interventions. For example, in order to add a column to the table above, we simply created the following change set:

<span>&lt;changeSet id=&quot;20&quot; author=&quot;John Smith&quot; context=&quot;addon_tables&quot;&gt;<br /></span>&nbsp; &nbsp; &nbsp; &lt;addColumn &nbsp;tableName=&quot;APPLICATION&quot;&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;column name=&quot;COLOR&quot; type=&quot;number(20)&quot; &quot; defaultValue=&quot;16711680&quot;&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;constraints nullable=&quot;false&quot;/&gt;<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &lt;/column&gt;<br />&nbsp; &nbsp; &nbsp; &lt;/addColumn &gt;<br />&lt;/changeSet&gt;

The next time the application was launched, the column was added with a default value. This may sound trivial, but if you are not a DBA, this is a great help—especially on larger features that require many database changes (new tables, columns, constraints, etc.). Ours is a web application, and we prefer to concentrate on delivering content for our users rather than spending too much time on database migration issues.

The extensibility ecosystem

We found that Liquibase supported most of the databases that we use, out of the box. But there was one database that wasn’t supported. Fortunately, it’s easy to write Liquibase extensions, and there’s even a step-by-step tutorial . But before you roll out your own, check the Liquibase Extensions Portal first, since someone may have already written the extension and shared it for everyone else to use. Fortunately, we found that one of the extensions suited our needs.

Schema integrity and audit logs

Liquibase enabled us to enforce schema integrity while applying changes. When running a change log, Liquibase compares the schema to the change sets you already deployed and verifies that the schema fits them.

Liquibase records each change in a dedicated table (DATABASECHANGELOG) and verifies them on each application start:

Liquibase DATABASECHANGELOG Table

With the changelog XML and the database table, we are able to track the evolution of our database and troubleshoot related database issues. Our DBA can easily review our schema modifications as needed.

Aligning with backward compatibility

Database migration tools give you the ability to make many schema-related changes, including adding columns, removing columns, changing column types, renaming columns, etc. While all of these are possible using Liquibase, we decided to restrict ourselves only to changes that would be aligned with backward compatibility, such as adding tables, adding columns, and so on.

If for any reason we see that our binaries cause some type of regression, we can roll them back without worrying about changing the database. For example, if you remove a column and then discover that you need to roll back the binaries, the schema would have to be rolled back as well, since the old binaries are coupled to the removed column. We felt that this was a complexity that we could easily avoid.

Sharing the database responsibility

Until a better solution is found, databases are here to stay, and the need to maintain and manage them will continue. However, as applications and development processes evolve to be more agile and continuous integration and delivery (CI/CD) processes are adopted, more and more database-related development is done by feature teams, and DB work can no longer be an expertise practiced by only a selected few.

Given all this, we decided that Liquibase was the best choice for our database needs. Whichever tool you choose, what’s certain is that a good tool that can eliminate (or at least reduce) the stress of database modifications is something that developers today can’t live without.

2016 Tech & Software Conferences 93-page Guide





About List