The 'new' command

Now that you’ve initialized your repository and bootstrapped your existing database schema, you’re ready to start leveraging the power of MyBatis Migrations!

MyBatis Migrations are simple SQL script files (*.sql) that live in the scripts directory and follow a very strict convention. Since this convention is so important, we don’t want to leave it up to humans to try to get it right every time... so we let automation do what it does best, and keep things consistent.

The new command generates the skeleton of a migration script that you can then simply fill in. The command is simply run as follows:

/home/cbegin/testdb$ migrate new "create blog table"

The parameter that the new command takes is a comment describing the migration that you're creating. You don’t need quotes around it, but it helps keep the command readable.

When the command is run, it will create a file named something like the following:

20090807221754_create_blog_table.sql

This format is very important (which is why it’s generated). The number at the beginning plays three roles. First, it’s a practically unique identifier, meaning it’s highly unlikely that two people will generate the same one at the same time (it’s not a big deal to resolve if it does happen). Second, it’s a timestamp, indicating when the migration was created. Third, it is an ordinal index, formatted in a way that will keep the migrations sorted in the order in which they were created. The remainder of the filename is the comment you specified in the parameter. Finally, the suffix is .sql, indicating the file type that most editors will recognize.

Since version 3.2.1, you can change the default timestamp-based file prefix to number sequence by specifying --idpattern option. Assuming that the prefix of the latest existing script is '002', with the following command:

/home/cbegin/testdb$ migrate --idpattern=000 new "create blog table"

the name of the generated file will be:

003_create_blog_table.sql

Instead of specifying the pattern as a command line argument, you can set it in the configuration file $MIGRATIONS_HOME/migration.properties as follows.

# Example of migration.properties
idpattern=000

The contents of the migration script also follows a specific and required pattern. Here's the contents of the file we just generated:

-- // create blog table
-- Migration SQL that makes the change goes here.

-- //@UNDO
-- SQL to undo the change goes here.

Notice that your comment once again appears at the top of the file. You can add more comments beneath it and throughout the script if you like.

The section immediately following that comment is where you would put your DDL commands to create the blog table.

Then notice the

-- //@UNDO

-- // create blog table
CREATE TABLE BLOG (
  ID INT,
  NAME VARCHAR(255),
  PRIMARY KEY(ID)
);

-- //@UNDO
DROP TABLE BLOG;

Notice that the commands are terminated by a colon. This is also important, and you will receive a warning and likely a failure if you don't terminate the SQL statements with a colon.

Optionally, you can configure your own template to be consumed by the 'new' command. Configuration requires a file named migration.properties (in $MIGRATIONS_HOME). This file will contain the location of your template.

# Example of migration.properties

new_command.template=templates/new_template_migration.sql

Alternatively you can manually specify the location of your template as such:

migrate new --template=<path to template> "your description"

If neither of these are used, or valid, the default template shown on the previous page will be used.

So how do we run this script? Well, first it’s probably important to understand the current state of the database.