07 October 2012
Having worked as a developer on projects with (Oracle) databases at their core for around 10 years, I have seen quite a number of releases that needed to change database objects.
Database changes are different from application server changes, in that when you build a new version of the app, it completely replaces the old version, even if only a couple of lines of code changed. In many cases you can even have the old and new versions of the application live at the same time, maintaining service through an upgrade.
With databases, it is not so simple - each release builds on the existing database, generally by applying a script to modify it's structure. The hard part of database releases is how to produce this script, and many teams do it in different ways, from diffing databases, to exporting code and doing manual comparisons, and everything in between.
Quite a few years ago now, Ruby on Rails appeared on the scene, and with it came a new-to-me way to manage database changes with migration scripts. The idea was simple, starting with an empty database, you should be able to take a series of scripts, arranged in date stamped order, and apply each in turn to the database to get the current version. These migration scripts are version controlled with the application code, and so when the code is branched or tagged, they can be run against an empty database to produce the database version the application depends on. In fact, given version 1 of the application and database, the additional scripts to move the database to version 2 can be applied, especially if the migrations that were already applied are logged in the database.
What Rails didn't need to care about is stored procedures, but they are easier to handle than tables and indexes, because when they are changed, they can be completely replaced, just like with application code.
In my then day job, I took the ideas from Rails, and applied them to a broken build process, creating an installer to manage changes in a massive PLSQL application developed by a team of over 100 people.
This first version of the installer was pretty horrible. I used it to teach myself Ruby, and what I produced was Rubyized Perl code - ie not very Object Orientated, and worse, it was tightly coupled to our application, but it was better than what we had before, and it transformed the release process. I later left that job and joined a new company, and it turned out the database release process was just as bad there.
From this, DBGeni was born - the DataBase GENeric Installer. DBGeni is a Ruby gem that provides a simple command line interface to apply (and rollback) database migrations and stored procedure code.
Do I think DBGeni is my path to internet fame and riches? Probably not, however coding it was a fun project. It allowed me to build a non trivial Ruby application, package it in a gem, employ a TDD approach to development, hone my OO skills on a green field project and generally improve my Ruby knowledge.
I also took the time to create a simple website and a fairly complete manual, which currently sees very little traffic (more on that later), but it let me see how much non-coding work is involved in producing something other people can use!
After starting on DBGeni, I came across DBDeploy which does something very similar to DBGeni, but in a slightly different way. This didn't put me off working on DBGeni, but further reinforced my believe that managing database changes in this way is a good idea. DBDeploy seem to have at least a few users, so there is potential for DBGeni to get some adoption too.
Right now, I am fairly certain that I am the sole user of DBGeni. The website is not getting much organic traffic from Google, and to be honest, I haven't promoted it at all, except on my own Oracle blog and this one.
At one point, I had visions of charging for DBGeni, but now I am not so sure. First of all, I would need to get a few teams to use the tool to see if they like it and give it some thorough field testing, which means spending some time on promotion.
Right now, I am happy to have produced a tool as a side project and I think it solves a real problem. At the very least, I can use it as an example of my work, and going forward I will spend some time on promotion and see if I can get anyone to use it.