A look back at our open-source database change management application’s 7-year journey
Today iovation released v1.0.0 of Sqitch, our open-source database change management application for framework-free development and dependable deployment. The culmination of seven years of development and production operation, this release marks a significant milestone in our commitment to developer efficiency and database consistency. Sqitch 1.0.0 has been put through its paces at iovation and by a community of outside contributors to reach a level of stability and maturity today that we believe worthy of broad distribution and adoption by organizations worldwide.
Database change management systems operationalize the development, deployment and tracking of database schema changes, which might otherwise be ad-hoc and difficult to track. For diverse teams practicing agile development, and for busy service reliability engineers requiring dependable deployments, an application such as Sqitch greatly reduces complexity, improves quality and assures consistency of practice and deployment.
Database engineers and admins on a mass scale can now enjoy the flexibility, safety and independence of Sqitch. The path to get here has been rewarding, if lengthy. We consider it time well spent.
A History of Sqitch
Back in 2012, as I was starting my career at iovation, we wanted to migrate off of our Oracle database. I was hired to help facilitate the move by designing our Postgres infrastructure. We planned to replace a monolithic Oracle cluster with many smaller Postgres clusters and databases (and eventually a few very large Cassandra clusters). Since we would need a way to effectively manage changes to those databases, I started digging into our process for deploying changes to Oracle, to see what could be wrangled for the new architecture. The process I found to create a new migration was:
- Reserve an update version by editing a Wiki page (‼️)
- Write update and undo SQL*Plus scripts
- Use iovation-specific SQL*Plus configuration variables
- Run the updateSchema script to execute the update scripts
The updateSchema script kept track of the last deployed version in a single row in an application-specific table so it would know where it left off and where to start on a new deployment. Otherwise, it provided virtually no metadata. It worked well enough as it grew naturally out of our single big database, but wasn’t going to be applicable beyond that database. There was just too much of the business app baked into the assumptions of the script.
Meanwhile, in practical use by a busy engineering team, reserving a migration ID by editing a wiki proved particularly challenging (thus the above ‼️). The IDs were incremental, and if two or three teams worked on database changes concurrently, it was difficult to keep track of the numbers — especially if there were dependencies between them.
While updateSchema was far from the worst migration system I’ve ever seen, I knew database change management could be better. In January of 2012, I started sketching out some ideas in a series of blog posts on tagging and dependencies, VCS change tracking, and idempotent changes. By April work on Sqitch, as I started calling it, began in earnest. As I explored a number of different ideas and approaches and drafted a manual, I developed the following goals:
- Independence. Sqitch would be a standalone database change management application, not part of any framework, ORM or platform.
- Database fluency. Whatever your preferred database engine, Sqitch would let changes be written in its native SQL, using its native command-line client syntax.
- Dependency resolution. Sqitch changes could require other Sqitch changes — even changes in other projects. Deployment would fail on unsatisfied dependencies.
- Planning. Through a process of iterative development, a database administrator or engineer would add and order changes in a plan file. There would be no reliance on a magical file naming convention.
- No numbering. Sqitch would not assign numbers to changes, but rely on the order of declaration in the plan file.
- Quality control. In addition to deployment scripts for each change, Sqitch would encourage the creation of reversion scripts for painless deployment rollbacks. It later also encouraged the creation of scripts to verify the deployment of each change.
- Packaging and distribution. Sqitch needed to provide a straightforward method to tag a project and bundle it into a release for distribution and deployment.
- Minimal duplication. Where possible, Sqitch would simplify object management to track only the bits that change, rather than duplicating full declarations for every little change. This advantage applied to objects supporting CREATE OR REPLACE and not ALTER statements, such as procedures and views.
- Iterative development. A database developer should be able to efficiently iterate on database development, and Sqitch should facilitate that work, but otherwise stay out of the way.
- Reliable deployment. If something gets out of order, Sqitch should refuse to deploy changes to a working system. This reduces the chances of unexpected or unauthorized changes to production systems.
- Revision history. Sqitch should keep track of the current state of the system, of course, but also the complete history of changes. This metadata serves as both a compliance-fulfilling audit history and a path to accountability.
Over the next few months, development proceeded swiftly, as we tried and discarded or folded in different ideas and approaches. We released numerous development versions as work progressed, eagerly welcoming feedback from interested followers of this open-source software. In August 2012, Sqitch v0.90 went out as the first stable release; the first commits to iovation-internal database projects using Sqitch started in September.
By December 2012, it was time to prove Sqitch’s utility. We developed, released and successfully deployed our first Sqitch database package to manage reporting configuration data. A number of other projects followed throughout 2013 and the ensuing years. Today, Sqitch manages 26 different database projects at iovation, and those databases store 10 terabytes of business, configuration, reporting and analytics data.
Over the years, Sqitch has added support for numerous popular database engines:
Adoption has been broad outside iovation, as well. The Sqitch project has racked up 1720 stars on GitHub, where a search also turns up over 520 plan files, indicating broad experimentation and uptake among open source developers. The introduction of an improved Homebrew tap for Mac users, as well as a lightweight Docker image, has proven ideal for kicking the Sqitch tires, CI/CD pipelines and efficient production deployments.
We’re gratified that all this work, dating back seven years, culminating in the release of Sqitch v1.0.0 today. We hope you give it a try.