I work on a years-old non-Grails project with a manual database migration procedure. Essentially, all migrations are stored in uniquely named scripts, and when a script is run it’s recorded in a database table, such that we can query the database to learn what scripts have been run, compare that against the file system, and thus learn what scripts need to be run (Thanks Joe!).

The process works well and is fairly easy. Still, I wondered: how might you automate this?

Here’s one way.

Caveat

This is quick/dirty, investigative, un-budgeted automation. I didn’t want to change any existing processes or introduce any new frameworks. And, as always, time and budget are significantly constrained (i.e.: do it fast, and do it free).

One question guided me: What existing, well-known tools can I use to automate this migration, requiring no other changes?

The Scenario

I’ve got two databases:

1. The local dev database which is up to date — all migration scripts have been run against it
2. A target, un-migrated database
3. They live in the same environment (via other magic) and in this case can talk to one another
4. I have a file system of scripts
5. I’m using ANT

Automating the Migration

Big Picture

Using ANT’s SQL task:

1. Query against the two databases to arrive at a list of migration scripts which have not been run against the un-migrated database
2. Copy those scripts to another location and perform some transformations on them
3. Create a FileSet of those copied scripts, and run them against the un-migrated database

In Detail

Connecting to the database via ANT

I’m using SQL Server, and to connect to it via ANT I’m using the excellent jTDS driver. I put the .jar file into our build project’s lib directory, and then include it on the build classpath like so:

Using ANT’s SQL task is fairly straightforward, but the driver class and database URL always seem to confusticate people. Read the jTDS docs for all the details; here’s mine:

• sql.driver=net.sourceforge.jtds.jdbc.Driver
• sql.url=jdbc:jtds:sqlserver://${local.dbserver}

Note: you can suffix the URL with a database name to have the scripts run against a specific database, such as: jdbc:jtds:sqlserver://localhost/your_db

Finding scripts to run

To find scripts run against the migrated, but not unmigrated, database, I run a query which creates a list of files, output that list to a file, and read that file into an ANT property:

Transform scripts to run

For various reasons, we need to do some string replacements on these scripts prior to running them against the unmigrated database. Here’s a sample for doing so via ANT:

Running the migration scripts

ANT’s SQL task has two fantastic features which help us here:

1. Running a FileSet of scripts
2. A delimiter attribute for separating statements in a script

At this point, we have a directory of scripts to run, and it’s trivial to do so by passing a FileSet to the SQL Task.

In addition, our scripts often contain the word “GO”, which is a SQL Server specific directive used by Management Studio. We need to tell ANT to use that word as the delimiter, such that it doesn’t attempt to run it as SQL.

Output

When the SQL Task runs those files, you’ll see something like this in the console:

A Note on determining scripts to run

In my approach, I ran a query against the two databases to arrive at the scripts to run. You could certainly do this without that migrated database! In fact, my first approach was to simply query the un-migrated database for all of its scripts, and then use ANT, via scriptDef and javascript, to search the file system for files not in that list. It worked, it was fast, but because I have the luxury of having both databases able to communicate, I chose the simpler route.