The 'script' command

While we developers wish we had unlimited access to every environment, the unfortunate truth is that we don’t. Often it’s the very production environment that we’re targeting that we don’t have access to. Someone else, a DBA or Change Management team will need to apply any changes. However, we don’t want this to be an excuse to not use a good, automated change management tool.

MyBatis Migrations provides the script command to generate a script that can migrate a schema from one version to another. As mentioned above, this will likely always be in an upward direction, but the script command does also support generating undo scripts (just in case you’re so unfortunate that you don’t even have access to central development databases!).

The script command is quite simple. It takes two version numbers as parameters. Think of it as generating a script to apply all of the identified versions in the order specified (the lower version is exclusive and the higher version is inclusive). For example, given the following:

/home/cbegin/testdb$ migrate status
ID             Applied At          Description
20090802210445 2009-08-04 22:51:17 create changelog
20090804225207 2009-08-04 22:51:17 create author table
20090804225328 2009-08-05 24:55:23 create blog table
20090804225333    ...pending...    create post table

If we need to generate a “do” script to apply the last two versions above, we’d run the following command:

/home/cbegin/testdb$ migrate script 20090804225207 20090804225333 > do.sql

The script command outputs to stdout, so you can let it print to the console, or pipe it to a file or command.

To generate the corresponding “undo” script, simply specify the version numbers in the opposite order:

/home/cbegin/testdb$ migrate script 20090804225333 20090804225207 > undo.sql

To generate a script which includes the first version, specify '0' as the lower version:

/home/cbegin/testdb$ migrate script 0 20090804225207 > do.sql
/home/cbegin/testdb$ migrate script 20090804225207 0 > undo.sql

Since version 3.2.1, two handy shortcut parameters pending and pending_undo were added.

In the above example, only the last migration is pending, so the command below:

/home/cbegin/testdb$ migrate script pending

is equivalent to the following:

/home/cbegin/testdb$ migrate script 20090804225328 20090804225333

Similary, the command below:

/home/cbegin/testdb$ migrate script pending_undo

is equivalent to the following:

/home/cbegin/testdb$ migrate script 20090804225333 20090804225328