TechPortal article on database version control

techPortalLast year I spoke at different conferences throughout Europe about database version control. However, a while ago I decided that I did the talk often enough and that it’s time to move on. Therefor I wrote a big wrap-up article that summarizes everything I told (and learned) during these events. I’m proud to announce that this article was published on ibuildings’ techPortal site today!

You can find the article here:
http://techportal.ibuildings.com/2011/01/11/database-version-control/

  1. W ….. Here Is My Silver Bullet!
    Dzo ( http://sourceforge.net/projects/dzo ) is a tool that compares a file with native sql (Sql Source Code) with the actual database schema and generates (or executes) the needed sql to transform the database schema to the objects in sql source code (drops objects that is not in sql source code but in the database schema, creates objects that exists in sql source code but not in the database schema and alters objects that differs).

    In Dzo, the Sql Source Code contains ”create table ( …)”, ”create view …”, ”create trigger …” and so forth for all database objects, and this file is the artefact to put under revision control.
    This file (or in practice a xml-representation of the content) is also distributed with the deployable application artefact, because the application is the natural owner of the database objects.

    A developer uses ant/maven plugins or a command-line tool to update developers database schema.
    When deploying to other test environments there is a command-line tool that uses the sql source code in the application to update the environment database schema.

    With this approach the following happens:
    - The application controls database structure so the application automatically gets the right database content.
    - You can update the database schema with databasetools (like dbvisualizer/toad) which is impossible with tools like Liquibase and dbdeploy.
    - The sql source code that is put under revision control is usable when comparing different revisions because it directly reflects the actual database changes. In tools like Liquibase and dbdeploy the ”deltas”/”migrations scripts” must be ”puzzled together” over time to see the actual changes in the database.
    - No need to know the current/actual database schema.
    A database change is ”finished” when it is checked in.
    Update of test environments can be fully automated.
    - Branch/merge is handled in the same way (and with the same problems) as handling branch/merge in application source code.
    - You are applying DRY (Dont Repeat Yourself), DIE (Duplication Is Evil) and SSOT (Single Source Of Truth), there is only one representation of the database schema and this is the Sql Source Code.

    And what about the problem with renaming tables/columns? Dzo have an object called Converter that adress this issue. For example, column ”name” is renamed to ”surname” in the table ”person”, Dzo sql source code contains:
    #convert person U20110507 cond=(created(person.surname) AND dropped(person.name))
    #sql begin
    update person set surname = name
    runTheSql
    #sql end

    This means, execute the sql between ”#sql begin” and ”#sql end” if and only if the column person.surname is added and the column person.name are going to be dropped.

    Dzo also have a notion that handles referencedata (table content like lookup-tables which never updates from the application):
    #referencedata country key=(countrycode)
    insert into country(countrycode,country)values(‘SE’, ‘Sweden’);
    insert into country(countrycode,country)values(‘NO’, ‘Norway’);

    Dzo is a java-application and has support for:
    generate sql source code from jpa-annotated entity beans
    web-applications (war-files) with a ”deploy-console” to control update of the applications database schema.
    database engines HSQLDB, MySql, Oracle, Postgresql, SqlServer

  2. Thanks Ulf for your extensive reply! I must admit I had never heard of Dzo before, but it definitely looks promising. If I find the time I will definitely play around with it for a bit (I get the best idea of what things do when I’m using them) and blog about my findings.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>