Java migrations tools

Wow, it’s been a while. If you’re interested in good links follow me on Twitter: http://twitter.com/anderssv . I usually update there these days.

My talk on “Agile deployment” got accepted for JavaZone this year! I’m extremely happy, but a bit scared too. :) I’ll be talking about rolling out changes in a controlled manner, and one of the things that are usually neglected in this scenario is the database side. I’ll cover stuff like packaging and deploy of the application too, but that’s probably the area where I know the least. The database side of things are really sort of my expertise.

I have written some blog posts on this already, and in relation to the talk and things at work I did a quick search for Java migration tools. DBDeploy I have used earlier, but there are now a couple of other contenders. Here’s my list so far of tools that work on sql deltas that can be checked into SCM:

  • DBDeploy – Tried, few features but works well. Ant based.
  • DbMaintain – Probably has the most features. Ant based.
  • c5-db-migration – Interesting alternative, similar to DBDeploy. Maven based.
  • scala-migrations – Based on the Ruby on Rails migrations. Interesting take.
  • migrate4j – Similar to Scala Migrations, but implemented in Java.
  • Bering – Similar to Scala Migrations, and looks a lot like Migrate4J

I’ll definitely be looking into DbMaintain and c5-db-migration soon. DbMaintain looks promising, or I migh just contribute to DBDeploy some features. I’ll let you know how it went. :)

(updated with scala-migrations, bering and migrate4j after first post)

16 thoughts on “Java migrations tools

  1. Thomas Ferris Nicolaisen

    I really enjoyed your previous talk at XP Meetup, and I think you’ll deliver a great talk for JZ too, (unfortunately, I won’t make it there this year).

    We are also currently playing around with migration tools. Thanks for your small overview here, and I hope to learn more from your experiences in the future ;)

    Reply
  2. Anders Post author

    Thanks, I hope to do well.

    I’ll update when I get a chance to test the stuff. Not sure I’ll get to test on my current project though.

    Reply
  3. Viggo

    Hi,

    have you evaluated more of the migration tools, like c5-db-migration and DbMaintain? If you’re planning to run migration from Maven in your development environment, how will you do this when deploying to a stage/system/production environment?

    Reply
  4. Anders Post author

    Hey Viggo,

    Sorry, havn’t had the opportunity to test any more tools. You are quite right, a Maven integration makes everything a bit harder. One solution could be to have a dedicated project for the migrations, but this is also somewhat of an unchartered territory.

    With DbMaintain you could do a packaging of the whole project including Ant into a ZIP file, and then execute the contents after downloading from the Maven repo. Scala migrations you could probably just execute from public void main.

    Check out http://github.com/anderssv/agile-deploy for some ideas on how to use DbDeploy with a deployer. I’m using the deployer in my current project, but without the DB part sadly. So that’s pretty much early alpha.

    Let me know if there’s anything else. :)

    Reply
  5. Viggo

    Thanks for (very!) quick reply, must be because we’re both in Norway/Oslo;)

    Some background info:
    My current project is a web application (plain war) which integrates with a database through Hibernate and is built using Maven. We have some custom maven plugins, one for the creation of the database and one for the deployment of the war to an application server (JBoss at the moment). We have created an installer using the maven-assembly-plugin which is used to setup the application in other environments like test,stage,production. The installer bundles a build.xml with a few targets to achieve the same things we do in Maven in our development environment. The home-grown utilities for creating the database and deploying to the appserver is used in all environments, from Maven in dev-env, from Ant in all other envs.

    I want to replace our custom utilities for database creation/migration AND deployment with some more standard things, where the DBDeploy (or c5-db-migration) could be used for the database-related stuff.

    Challenges:
    1. Database-related tasks: Is there a tool that can be utilised from both Maven and Ant to accomplish the database creation/migration? Right now it seems that DBDeploy would be the primary tool, since we can execute ant tasks from Maven. Input?
    2. Appserver/deployment related tasks: Currently we have a home-grown maven plugin which takes care of creating a JBoss directory structure and deploying the war file, as well as starting it in our development environment. In test/stage/production we have Ant targets in the installer which does the same. I’ve looked at maven-cargo-plugin and finds it interesting, since it can both download an app-server and deploy/redeploy war files to it by configuration in pom.xml. Well, I would like to have a common approach for installing/configuration/deployment of an appserver in dev-env/test/stage/prod. The installer which is deployed to the maven repo should be environmental-neutral, right?! So, will the agile-deploy facilitate my needs?

    Reply
  6. Anders Post author

    If you’re looking for both Ant and Maven support I guess DBDeploy would be the easiest. On the other hand, writing Ant tasks or Maven plugins isn’t that hard. So if any of the other tools has a decent API, writing support for them shouldn’t be very hard. I’d choose the ones that match your needs best and figure out the technical problems after that.

    Well, agile-deploy works by using the same Maven artifact from the Maven repository for all environments, so yeah it has to be env neutral. Currently it only supports ZIPed self contained apps. Contributions are very welcome. :)

    One last option you could consider is running the DB upgrade on application startup, which could be a good solution for many.

    Reply
  7. Viggo

    I’m thinking about the following approach for solving my db-related tasks:
    1. In dev-env use c5-db-migration to create migration scripts and to reset/create and migrate a database by utilizing the maven goals.
    2. In test/stage/prod/etc run the migration scripts using Ant. The migration scripts will be bundled with the installer which is made by the maven-assembly-plugin.

    What do you think? This will give the developer the same tool (=Maven) for
    building code (maven-compile-plugin, maven-war-plugin, maven-ear-plugin etc)
    database-related tasks (c5-db-migration)
    deploying to appserver (maven-cargo-plugin)

    while reusing the same migration scripts in all other environments.

    Question: You’ve said in another post that you preferred Sqlplus over Ant SQL tasks. Could you tell me more about the reasons for this? Does SqlPlus/Ant SQL tasks have a way to specify the order to run the migration scripts created by c5-db-migration?

    Reply
  8. Anders Post author

    My reason earlier for not using Ant from the command line was to avoid having the developers as well as host machines install it. Maven was already installed. But that’s really no biggie.

    I think the stuff you are saying makes sense. Just a couple of things that would make everything ideal:
    * All artifacts for deploy to anything else than local dev is available in central Maven repo. Even with cargo plugin.
    * Have separate tasks that lets you create/copy/dump databases easily for everyone.

    I think you probably have most of it covered, and as I say it would just make everything ideal. :)

    When it comes to Sqlplus vs. Ant it was just a question of which operations are available through JDBC. When you’re doing some more advanced DB refactorings you might need some of the Sqlplus features. But there’s really no problem using Ant sql until you need the sqlplus stuff. Just make sure none of your developers grinds to a halt because they don’t know what to do when they hit the limit of JDBC operations. :)

    Reply
  9. Eirik Rosvold Larsen

    > When it comes to Sqlplus vs. Ant it was just a question of which operations are available through JDBC.

    You can also get most sqlplus operations (such as PL/SQL stored procedures) available through Java with the sqldeveloper jars which comes bundled with sqldeveloper. The small differences between sqlplus and sqldeveloper is also well documented on the oracle web site.

    Reply
      1. Eirik Rosvold Larsen

        Unfortunately they’re not in a maven repo AFAIK, and also there might be licensing issues. sqldeveloper 1.5 came with an undocumented bat/sh script that does the job, it’s called “sqlcli”

        Reply
  10. Tiziano Mengotti

    hi, there is also http://www.deltasql.org as an Open Source tool to mantain incremental changes to databases. It is based on a server side architecture. Serverside is PHP/mySQL, available clients are in ant, Java and bash scripts. It is productively used by companies in Switzerland and India.

    I would be interested in your comments.

    Reply

Leave a Reply

Your email address will not be published.

You may 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>