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.
2) DBMaestro
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.
3) Delphix
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.
5) Datical
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
1) liquibase
Liquibase is by far the best DIY database versioning tool out there. For a brief primer on how to use liquibase look here.
2) FluentMigrator
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.
3) DBGeni
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.
4) Flyway
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.
5) Sqitch
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.
6) Schemazen
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.
I don’t care much about whether the schema is kept in version control or not… generally speaking, the schema in PROD is the baseline, since any changes need to be compatible with it.
So I only worry about deployment (and rollback) scripts.
But since scripts deploy schema changes, they need to be tracked. Personally, I prefer to store them in the database, and refer to them with database deployments.
It should also be noted that scripts are version controlled, but that a scripts’ version control is NOT the same as a schema version, since I might make a dozen changes (and checkins) to the rollout script, just to get the schema to v2.0
I wrote some PowerShell scripts and such to handle such deployments with Octopus Deploy, and I did a brief write-up at http://www.sbrickey.com/Tech/Blog/Post/OctoPackPlus_DBDeploy_Improving_Database_Deployments_with_Octopus
I saw your post. Excellent write up. I did something similar at a place I used to consult at. However for us the base line was the PROD schema at certain point in time plus a list of deltas. Thus over time the list of deltas used to grow very long and force us to refresh the baseline to cut down on the build time. The plus side was we could stand up a brand new environment from within a build anytime, which the automated build did to run the functional tests every time.
How about a case where you have multiple prod environments, with slightly diverging (for a good reason) schemas?
I know also about these:
Datical (http://www.datical.com/ )
DBMain – more a modelisation tool but as it can generate the code (http://www.db-main.eu/?q=en)
So both keep the model track, but also the code to get this model version.
Doh ! I don’t know how I missed Datical. Thanks for pointing that out. In reality datical and liquibase used to be the same. Now datical has become the biggest contributor to the liquibase open source project.
We’re glad you recognize that Datical has come a long way since we began building on top of Liquibase, but we’re more than just the largest contributor to the project. In fact enterprises all around the world are adopting Datical DB to seamlessly map development-driven database change management into their continuous integration (CI) workflows
Datical DB integrates with a wider variety of database types than our competitors and even works with and complements Delphix’s DBaaS platform. After three years of massive growth, we think Datical DB has earned its place on the list of top commercial db versioning tools.
You said it yourself: don’t know how you missed Datical! Would you mind updating the list to include Datical DB among the Commercial Tools? You can look through the Datical DB product details here, and we’re happy to answer any questions you might have to round out your understanding of our product.”
Datical doesn’t support MySQL, difference from Liquibase
Hello, thank you for mentioning Databazoo Dev modeler! We are currently rebranding and working on documentation; we are about to release those features within few weeks.
However, we are still working day night at improving our solution – get latest release at devmodeler.com and send me an e-mail for enterprise edition key. We would love to hear some feedback from the community.
Cheers,
Jay, Product manager
We have used Flyway both in CLI and with Jenkins. We used Ruby scripts to customize our solution to add the flexibility to rollback provided the developers create idempotent rollout and rollback scripts. Our solution is working flawlessly and for any group within our company interested in using this, I actually bundled all the training into a VM so they don’t even need software or hardware to train. about 2 to 3 days of training and they can start using it for their projects. Cost is literally restricted to how much time the development group invests in learning to adopt this solution
Hi nimkar,
I’m the creator of yuniql (yu-nee-kel); an open source Database DevOps tool for SqlServer, PostgreSql, and others. With yuniql, we can deploy fresh databases fast, seed lookup tables and test data from CSV and run run environment-specific migrations. yuniql is available as stand-alone .exe app (no CLR needed!), Azure DevOps Pipelines Tasks, .NET Core nuget package and Docker container.
P.S. Please support by clicking GitHub Star! For a young project like this, a star can help capture more user experiences and improve the tool in its early stage 🙂
https://github.com/rdagumampan/yuniql
https://yuniql.io
Thanks thanks!
rdagumampan