DB migrations

Published on and tagged with ant  cakephp  configuration  database

Some days ago Joel Moss released a new version of Cake DB Migrations. As this version also supports advanced installations, I tested it a bit. After fixing some small glitches it worked fine. But, well, it seems that it doesn’t fit to my working style. As I do a lot of tiny steps to build a database, the overhead when using Migrations is rather high: for each change there is a migration file. The format of those migration files is rather “talkative”, and I miss a bit of magic, i.e. if I use create_table in the UP section, I would expect that automagically a drop_table is done in the DOWN section. Last, but not least, the migration tool works similar to the bake script, which means you have to select several options each time you want to apply a migration. And that is a pain! But test it yourself to see whether it fits to your working style.

And so I am back to Apache Ant and the good old SQL scripts ;-) My approach follows a simple pattern after a modification of the SQL scripts: drop, create and insert. Here is the Ant script I use:

<?xml version="1.0"?>
<project name="myproject" default="default">
  <property name="driver" value="com.mysql.jdbc.Driver"></property>
  <property name="url" value="jdbc:mysql://localhost/myproject?characterEncoding=UTF-8"></property>
  <property name="user" value="root"></property>
  <property name="password" value=""></property>
  <target name="default" >
    <sql driver="${driver}" password="${password}" url="${url}" userid="${user}" src="drop.sql" encoding="UTF-8"></sql>
    <sql driver="${driver}" password="${password}" url="${url}" userid="${user}" src="create.sql" encoding="UTF-8"></sql>
    <sql driver="${driver}" password="${password}" url="${url}" userid="${user}" src="insert.sql" encoding="UTF-8"></sql>

This script I can then execute with one mouse click directly from my IDE. Simple and fast.

3 comments baked

  • Joel Moss

    Thanks for the mention. I understand your concerns over migrations and there are some good comments. Please post on my Switchboard forum with your thoughts and suggestions, then I can get it developed further.

    But you gotta admit that migration files are easier and faster to work with than SQL queries and XML with Ant? But let me know on my Switchboard if you disagree.

    And by the way, I just released v2.1 which fixes all known bugs and introduces Fixtures. Again, I would appreciate your comments: http://joelmoss.info/switchboard/blog/2002:Migrations_V21__Now_with_Fixtures

    Thanks again.


  • cakebaker

    @Joel Moss: Thanks for your comment.

    What do you mean with “my Switchboard forum”? Do you mean the comment section of your blog post?

    Well, personally, it is easier for me to write SQL queries than YAML files, as I am more used to SQL than YAML. And usually I do not have to write any XML, as I can copy my template, and the only thing I have to edit in the Ant file is the database name.

  • Joel Moss

    Aswell as hosting my blog, my Switchboard at http://joelmoss.info also has an integrated forum.

    Just click “Start a Discussion” to create a forum post.

    By the way, I just released v2.2 which fixes a few things and works on your comments that you made. Please take a look at http://joelmoss.info/switchboard/blog/2021:Migrations_v22

© daniel hofstetter. Licensed under a Creative Commons License