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.
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?
I’ve got two databases:
- The local dev database which is up to date — all migration scripts have been run against it
- A target, un-migrated database
- They live in the same environment (via other magic) and in this case can talk to one another
- I have a file system of scripts
- I’m using ANT
Automating the Migration
Using ANT’s SQL task:
- Query against the two databases to arrive at a list of migration scripts which have not been run against the un-migrated database
- Copy those scripts to another location and perform some transformations on them
- Create a FileSet of those copied scripts, and run them against the un-migrated database
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:
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:
- Running a FileSet of scripts
- 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.
When the SQL Task runs those files, you’ll see something like this in the console:
A Note on determining scripts to run