i360 uses Liquibase to easily manage updates for thousands of databases in Amazon Aurora

Company

Founded in 2009, i360 helps its customer base of political organizations and commercial clients understand and leverage data to its full potential, taking it from informative to actionable for results they can count on. Since they are growing quickly, they recently decided to move from MS SQL to PostgreSQL on Amazon Aurora to more easily manage their growing footprint.

Challenge

i360 uses templated databases repeated over and over for each client. That means literally thousands of databases need to be updated and synchronized. In order to manage the task of database migrations, i360 turned to Liquibase. “In the PostgreSQL world, Liquibase is a star player in database migrations,” said Robert Harrison, Technical Director at i360.

“We are migrating from a world where I was basically going into each database and manually doing a compare from the model and then manually updating. It was tedious but we also didn’t have any accountability for where these changes came from. If something failed, we didn’t know who to send it to,” said Bill Foshay, Senior Software Development Lead at i360. Database deployments happened weekly due to the manual process.

Solution: Liquibase Pro

i360 now uses Liquibase Pro to help track, order, and deploy database migration scripts that propagate to literally thousands of Postgres databases in Amazon Aurora. Here’s how they set it up with Evolutionary Database Design in mind.

For a given database, developers initially clone the corresponding database repo from GitLab. Then they use Liquibase database migration software installed on their machine to run the migration scripts against their personal database to bring it up to date. As developers work on features and bugs, the work is performed against their personal database.

Developers write migration scripts and commit migration scripts to their local Git repo at any time without affecting anyone else. They can focus on their work in their own sandbox. To bring their database up-to-date, they pull the latest changes from GitLab and run Liquibase. Once the feature is complete, the application code and database migration scripts are checked into GitLab. If there are conflicts with the latest migrations in source control, the developer must reconcile the changes on their personal database first, adjust their migration scripts, and then push their changes.

Workflow

• Developers create database scripts, rollback scripts, and add comments about the bug or feature ID in their Liquibase changeset that match what they have in TFS for tracking.
• Developers run both their migration and rollback script through Liquibase to verify they are valid, run successfully, and are idempotent.
• Developers check the script and Liquibase changelog into GitLab source control.
• Includes the author name as their email address for notifications for script success and failure.
• A record is added to the approval table in Postgres. 
• A copy of the script and changelogs are put into S3 so that Fargate can have access to running those scripts. 
• The migration runs and kicks off a build.

Managing changelogs

There's a master changelog file at the root of each database repository. Under that, there are folders containing SQL migrations created during each quarter.

Individual .sql files and each sibling db-changelog.xml file referencing all scripts in the quarterly folder. Quarterly folders prevent the master-changelog.xml file from growing too large. It provides a means of breaking it up into a smaller series of dependent changelog files.

Detecting database drift

Liquibase Pro’s database drift detection feature allows the i360 team to automatically detect differences between database schemas using JSON output. Since they are using source control as their source of truth, conflicts are detected when a migration fails. The team developed a GUI around this JSON output to make it easy to compare and resolve database drift.

• Drift Conflict A: The drift is valid and should be applied to all other databases. The developer should write a migration script and check it into the trunk so that the drift is propagated everywhere.
• Drift Conflict B: The drift isn’t valid and should be removed.

Results

By using Liquibase Pro, the i360 team instantly knows if there’s a problem with a database script and the person who needs to address the issue is notified immediately. “When a developer submits a script and it fails, the developer who checked it in gets a notification about where the script failed and why,” says Harrison. “We see the error logs very quickly. The developer corrects the script and it runs again and everything is fine.” 

“It’s not that we no longer have failed scripts,” continues Harrison. “The cost of failure is greatly reduced because we know sooner and can more easily address it. With Liquibase Pro, we know exactly where every change came from and when it was made, which is a huge advantage.”

“I learned a lesson,” says Foshay. “Bill was always pushing for migration-based database changes. I was always a little skeptical. I now see the benefits of incremental updates, where you're doing incremental changes to the databases as they're coming in as opposed to comparing one database to another and just checking it whole.” This is especially true as i360 grows. Each of their clients has eight databases so literally thousands of databases need to be updated and synchronized without developer code collisions. Now, their developers can focus on delivering innovative features instead of the drudgery of manually troubleshooting database scripts.