Category: Code

We have an inconvenient problem. Our development databases are all snowflakes – snowflakes in the sense that each developer’s database has been hand updated and maintained at the leisure of the developer so that no two databases are alike.

We version our database changes into scripts with the creation date included in the name. But that’s where the database script organization and automation ends. There’s nothing to take those scripts and apply it to a local developer’s database. Just plain old copy and pasting to run new scripts. Adding to the pain is that the database scripts don’t go back to day 1 of the database. Instead, the development databases are passed around and copied whenever someone breaks their database and needs a new one or a new employee comes on board and needs to set up their development environment.

Manually updating our personal development database is problematic. Forgetting to run scripts can result in unknown side effects. Usually we don’t bother updating our database with the latest scripts until we really have to. That happens whenever we launch our app. Once the app starts complaining about missing tables or fields we’re on the hunt searching for the one script out of hundreds that would fix the problem.

As you can see, it is a system that is wasting the productivity of all developers, not to mention the frustration that happens when catching up after being behind for a long time. For a while now we’ve acknowledged that it’s a problem and should be fixed. A few of us looked into the problem and talked about using FlywayDB or Liquibase, but Liquibase seemed to be the best choice for us since it is more feature complete. Since that discussion one of our team members started experimenting with Liquibase and pushed that code to a branch, but it’s remained dormant for a while. I wouldn’t say integrating Liquibase into our development environment was abandoned because it was tough to do, rather I’m realizing that it is a common trend for developer tooling and continuous improvement to make way for feature development, bug fixing and level 3 support. Maybe our development team is just too small and busy to tackle these extra tasks or our bosses don’t realize the productivity sinkholes as significant and don’t allocate any time for improving it. I would like to spur some discussion around this.

Anyways, on with the rest of the post.

Look! The Proof of Concept is Working!

I spent the greater part of my Good Friday working on getting Liquibase working with our app. Partway through the day I got the production database schema into the Liquibase xml format and checked into source control. A few more hours were put into fixing minor SQL vs. MySQL issues with Liquibase’s import. (Who knew the BIT(1) type could have an auto increment? Liquibase disagrees).

Some time was spent creating a script at script/db (in the style of GitHub) for bootstrapping the Liquibase command with the developer database configuration.

Next I’ll mention some of the incompatibilities that I ran into while generating a Liquibase change log from the existing production database.

Generating a Change Log From an Existing Database

Liquibase offers a very helpful feature: being able to take an existing database schema and turn it into an xml change log that it can work with. The Liquibase website has documentation on the topic, but it doesn’t mention the slight incompatibilities that you may run into, particularly with data types.

Once the production database schema was converted into a liquibase change log, I pointed Liquibase to a fresh MySQL server running locally. Running the validate and update commands on the change log resulted in some SQL errors when executing. All of them were related to data type conversions. These problems were fixed by modifying the change log xml file manually.

The first issue was that the NOW() function wasn’t being recognized. Simple enough, just replace it with CURRENT_TIMESTAMP.

Next was Liquibase turning all of the timestamp data types into TIMESTAMP(19). Doing a search and replace for TIMESTAMP(19) to TIMESTAMP did the trick.

The same issue as above happened to all datetime data types. Doing a search and replace for datetime(6) to datetime worked as expected.

In the production database one table had a primary key with the data type of TINYINT(1). When Liquibase read this it converted the data type to BIT. It’s a known issue at the moment, but the fix is simple: change the type in the change log to some other data type like TINYINT (or TINYINT UNSIGNED). Make sure if this is a primary key that you update the foreign keys in the other tables, otherwise you’ll get errors when the foreign keys get applied.

This one was the weirdest. In the production database an index existed on a column of type mediumtext with no explicit length. The index was defined as a FULLTEXT. When Liquibase would create the database, it would fail when creating this index. After some googling it appears that the FULLTEXT index requires a length when operating on mediumtext. In the end, adding a (255) or however long to your FULLTEXT index data type fixes it.

Lastly, the tables from the production database were set to use the UTF-8 encoding and the InnoDB engine, but Liquibase doesn’t pick this up. The workaround for this was to append the following to every table definition in the Liquibase change set xml:

Next Steps

Because we provide a multitenancy SaaS offering, we drive a lot of behaviour of our app from the database. Whether it’s per customer feature toggles, a list of selectable fields, or email templates, a lot of data needs to be prepopulated in the database for the app to fully function.

The next bit of work involved with moving towards an agile database is to find all of the tables that contain data which are needed for the app to function. Liquibase offers methods of loading this data into the database by either loading data from a CSV file or by specifying the data in a change log.

Another important part of the database that needs to be checked in with Liquibase is the triggers and procedures. Liquibase doesn’t automatically extract the triggers and procedures so you’ll have to locate and export them manually.

Additionally, improving the developer experience by simplifying the number of things they have to do and know eases adoption and can make them more productive. Things like the configuration needed to run Liquibase, creating a new change log from a template and documentation of usage and best practices are all things that can bring a developer up to speed and make their life easier.

Lastly, there exists a Liquibase plugin for the Gradle build tool which makes it straightforward to orchestrate Liquibase with your  Gradle tasks. This would come in handy when Gradle is used to perform integration and any other form of automated testing in an environment which uses the database. Test data could be loaded in and cleaned up based on the type of testing.

Conclusion

No developer likes to perform repetitive tasks, therefore minimize the pain by automating all the things. Developer tooling can be often overlooked. As a developer do yourself and your colleagues a favour and automate the tedious tasks into oblivion. As a manager, realize the inefficiencies and prioritize fixing it. Attack the tasks that take the most time or would provide the most value if automated, then just start picking at it piece by piece.

Liquibase was discussed and acknowledged as the solution to our developer database woes. Following through with integrating Liquibase into our developer environment and going a few steps further with making it easy to use leads to more time saved for actual work. Delaying the implementation of the solution results in losing out on the productivity gains that you’re well aware of. Any productivity increase is better for both the developer’s productivity, the developer’s happiness and the business as a whole.