Update: Aug, 13 2016. Update the article to include Datical DB under commercial Tools.
A couple of years ago I wrote a blog post about Database Versioning and how it was ignored over source code versioning. Originally posted on my other website , it got quite a bit of attention and sparked some very interesting discussion. As a result of the discussions, I got to know quite a bit about database version control tools. After about two years I thought I would visit the topic again and see what new tools and methods are available for versioning databases. Judging from the speed at which technology moves, I was expecting a very different landscape from where I had left off. To my surprise, there has not been much new developments in this field at all.
Why Database Versioning ?
Versioning your database is different than simply checking in the scripts that make changes to your database to version control. The theory is that every change made to your database, specifically a DDL change but perhaps a DML change too, assigns a version number to your database thus allowing you to roll forward or backwards to a specific version safely. Just like you would with source code. For a full argument read the article above. Such versioning could go hand in hand with your code release or could go out all by itself. This makes changes to your database more manageable, reproducible and portable. Below I have compiled a list of tools that allow you to version your database
Commercial Tools for Database Version Control
1) Red Gate
Red Gate Database Lifecycle Management is available for mysql, Oracle, and Azure. It is an integrated tool set that helps with Version Control, Continuous Integration,Release Management and Monitoring of all database changes. Each process can be used individually or can be chained together in your DevOps toolchain. What I like about Red Gate toolkit is their support for and excellent GUI as well as good command line client.
DBMaestro Teamwork is available for Oracle and MSSql. DBMaestro Teamwork positions itself as DevOps for databases with rich feature set which includes and IDE, audit trail, permissions management, object locking prevention etc. DBMaestro will also perform an intelligent 3 way, baseline aware analysis of your database against any code dump you may have to figure out where configuration drift has occurred so you can get your automation under way in the right manner.
Delphix provides advanced DaaS (Database as a Service) tools that go beyond just versioning of database. Using Delphix’s tools you can bookmark your database, fast rewind, forward, reset, branch and share your environments with other teams. This also allows for faster testing and identifying of errors. Delphix also integrates a data masking tool that allows for masking of sensitive data and data governance with full audit trail.
4) Dev Modeler
Dev Modeler by Databazoo does have a free version for download that supports DB Visualization, Change tracking, data modeling, code completing etc. However I could not locate any meaningful documentation. Nor could I figure out how to reverse engineer an existing database although their website claims you can do so.
Datical is the commercial company behind the Open Source tool Liquibase. However Datical DB is more than just a commercial version of Liquibase. For starters it comes bundled with a nice GUI as opposed to Liquibase’s pure CLI. Datical is also a tool aimed squarely at the Enterprise with feature like Policy Management and enforcement of Regulatory Standards. It also integrates with wide variety of products for toolchain automation like Puppet, Chef, Bamboo, Jenkins etc. You can find more information on Datical DB here.
Open Source Tools
Liquibase is by far the best DIY database versioning tool out there. For a brief primer on how to use liquibase look here.
FluentMigrator is to .Net what Active Record is to Ruby on Rails. FluentMigrator promotes writing the changes to the database as classes in C# which are then translated to database changes via migrations. These changes can be checked in to a version control system just like any code changes. This also allows you to run any version control operation on the change like diff, merge, roll back and forward etc.
DBGeni feels like ActiveRecord on steroids. Probably because it is. Written in Ruby, DBGeni provides full support for scaffolding your database and automates various tasks like generating migrations, generating relevant code, etc. It also provides the ability to write hooks for your database in Ruby.
Flyway is a java based utility and is a favorite among the java crowd. It provides a command-line interface, an API, and hooks into Maven, Gradle, and Ant. It has support for migrations, baselining, validating, and repairing of databases. Although it will baseline an existing database, it is more like a bookmark. I could not find any way or reverse-engineering an existing database with Flyway. If you do decide to use Flyway to version your database, you may want to conduct api testing to make sure everything is working to your standards.
Sqitch is written in Perl. The unique thing about sqitch is it does not enforce any rules of its own on the user regarding scripting language or numbering scheme. sqitch also has all the standard features that you would expect from a command line database version control tool. Beyond that sqitch is the only tool I found that has support for docker and firebase.
Schema Zen is a small but very well maintained utility for versioning changes to MS Sql database. This command line utility is under active development and was brought to my attention in one of the comments on my previous blog posts. Schemazen can reverse engineer a database to its script. Can create a database from its scripts and can compare two databases. It is a small and fast utility well suited for automation.
So which tools do you think I missed ? Which ones do you use ? Let me know in the comments below. Also let me know if you would like me to test out a particular tool or write a primer for one.
Nilesh has 17 years of professional experience out of which 5 years he worked as a System and Network Administrators and 13 years as Change and Release manager. He has worked all of his career in the Financial industry for major Financial Institutions, Banks, Hedge Funds and other Super Villains.
He dreams of retiring on a beach with a beer and a banana.