Implementing Agile Databases with Liquibase

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.

database-scripts-directoryWe 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.

liquibase_logoAs 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

automate-all-the-thingsNo 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.

Introducing ChatOps to my Workplace: Intro

slack_colour_rgbWhat is a ChatOps?

Last week my roommate talked about how he was building custom integrations for his Company’s Slack service. He was adding commands that would easily allow their client service team to extend the trial periods of their customers and other useful features. He was also in the process of working on being able to deploy their app to amazon via a single command.

All of these spells one thing: Productivity.

Trying to keep up with drinking as much of the new enterprise technology kool-aid, an excellent talk (slides) by Jesse Newland that I keep going back to every once in a while shows the power and benefit behind ChatOps.

Dissecting this buzzword, the true benefit behind this technology and culture is giving:

Visibility – Seeing what other people are doing and show what you’re doing to others to provide visibility and accountability into the operations that are performed. Sure, someone can say that they’re fixing the unresponsive server, but it’s not clear how they’re doing it and if its fixed yet without asking them. With all the operations used to fix such an issue available in a common chat room, its straightforward for others to read what was done in real time.

Learnability – Sure, you can have documentation and training to bring people on board, but nothing compares to seeing things done every day, most importantly their first day. In no time, a new employee can get up to speed faster than having to read through a lot of boring documentation.

Pairing – Allowing two or more people to solve a problem together. Much like pair programming, it is the practice of having more than one brain working on a problem or even passively observing. Pairing allows for more scenarios to be explored and better reasoning.

Automation – Simplifying tasks to the extreme. No need to log into a server, find and run a script. Just have a command in the chat room that does it. The command acts as a facade, presenting a clean api into your business activities. Being able to automate to the level of not having to switch out of the chat room is an amazing improvement in productivity.

Sneaky Implementation

On my self assigned task to improve productivity and culture at my workplace, many other developers and I are pinned in a rut of completing support tasks and fixing bugs for our software. Between tasks I’ve set up Slack and have been integrating our services into it. My goal is to ultimately reveal it slowly to other developers, gaining momentum until critical mass is reached, where the majority of my colleagues praise Slack and the power of its usefulness until it becomes the de facto messaging and team communication tool.

So far I have integrated our build server, Jenkins, and the source code repository, Gitlab, into Slack. Both of those are only dumb endpoints which dump data into Slack, but its already super interesting to have the visibility of just these services together. Nagios checks, and maybe even Hubot, a chat room robot, will come next.

The stream of messages created when someone pushes code, the code being built, and then the test results reported back. Its taking Continuous Integration and reporting all of the activities to a central location!


See the next post following the theme of ChatOps, this time about Hubot, the chatroom robot.