Getting Started

This functionality is easy to enable in a mavenized project. First you add the Carbon Five public plugin repository:

<pluginRepositories>
  <pluginRepository>
    <id>c5-public-repository</id>
    <url>http://mvn.carbonfive.com/public</url>
  </pluginRepository>
</pluginRepositories>

And then you configure the migration plugin:

...
<build>
   ...
  <plugin>
    <groupId>com.carbonfive.db-support</groupId>
    <artifactId>db-migration-maven-plugin</artifactId>
    <version>RELEASE</version>
    <configuration>
      <url>jdbc:mysql://localhost/myapp_test</url>
      <username>dev</username>
      <password>dev</password>
    </configuration>
    <dependencies>
      <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
      </dependency>
    </dependencies>
  </plugin>
</build>
...

We've hard-coded values for url, username, and password for the sake of example. These would most likely be properties that can be overridden for numerous environments (dev, staging, production) via maven profiles (see below).

The plugin doesn't have dependencies on any specific JDBC drivers, so you'll have to include the dependencies which include your specific driver.

Lastly, you drop in your migration scripts into the src/main/db/migrations directory, naming them using the pattern YYYYMMDDHHMMSS_name.sql, where YYYYMMDDHHMMSS is a fourteen digit timestamp. Some examples:

• 20080115200312_create_users_table.sql
• 20080518092345_add_default_users.sql
• 20080718214051_add_lastvisit_column.sql
• 20080802134142.sql

The name is optional and isn’t used for anything, it’s just there so that other developers can get an idea of what a script does without having to open it.

From the command line, you can run the migration plugin like this:

$ mvn db-migration:migrate

If you want the plugin to also create the database, you can invoke the plugin like this:

$ mvn db-migration:create db-migration:migrate

Sometimes you want to blow away the database and start fresh:

$ mvn db-migration:reset

You can also just check to see if the database is up-to-date:

$ mvn db-migration:validate

When it's time to create a new migration script, you can do so by:

$ mvn db-migration:new -Dname=users_and_roles

Which results in a new, empty file called: src/main/db/migrations/yyyyMMddHHmmss_users_and_roles.sql (assuming default settings).

I’ve created a simple, complete sample that shows off this functionality, it’s located on the Carbon Five public subversion repository here. Check it out from subversion and then read the readme.txt at the top of the project.

Maven Plugin Goal Reference

The maven plugin has several goals:

Notes:

1. Must have create and drop database privileges.
2. Must have schema update privileges.

Maven Plugin Configuration

General Settings

Here is the exhaustive list of configuration options and the goal to which they apply.

Notes:

1. Where %s is the name of the database, as extracted from the JDBC connection URL.

Handling Multiple Environments

In any real world application, a number of environments must be supported. At the very least, there are unit testing and application databases for each developer. And then most likely, a staging and production database. This can be handled using a native maven feature, build profiles.

Consider the following plugin configuration...

<build>
  <plugins>
    ...
    <plugin>
      <groupId>com.carbonfive.db-support</groupId>
      <artifactId>maven-db-migration-plugin</artifactId>
      <version>RELEASE</version>
      <configuration>
        <url>${jdbc.url}</url>
        <username>${jdbc.username}</username>
        <password>${jdbc.password}</password>
      </configuration>
      ...
    </plugin>
  </plugins>
  ...
</build>

The three placeholders can be resolved to values that come from a number of locations. Additionally, they could be defined in one or more maven build profiles. Sometimes our developers share a common database server and in other cases (when possible) we run the database on our development machines. I'll tackle each in turn:

Local Database (localhost)

...
<properties>
  <!-- Default to localhost but allow for simple overriding. -->
  <jdbc.host>localhost</jdbc.host>
</properties>
...
<profiles>
  <profile>
    <id>test</id>
    <activation>
      <activeByDefault>true</activeByDefault>
    </activation>
    <properties>
      <jdbc.url>jdbc:mysql://${jdbc.host}/myapp_test</jdbc.url>
      <jdbc.username>dev</jdbc.username>
      <jdbc.password>dev</jdbc.password>
    </properties>
  </profile>
  <profile>
    <id>development</id>
    <properties>
      <jdbc.url>jdbc:mysql://${jdbc.host}/myapp_development</jdbc.url>
      <jdbc.username>dev</jdbc.username>
      <jdbc.password>dev</jdbc.password>
    </properties>
  </profile>
</profiles>
...

Shared Development Database Server

...
<properties>
  <!-- Default to staging but allow for simple overriding. -->
  <jdbc.host>staging-db</jdbc.host>
</properties>
...
<profiles>
  <profile>
    <id>test</id>
    <activation>
      <activeByDefault>true</activeByDefault>
    </activation>
    <properties>
      <jdbc.url>jdbc:mysql://${jdbc.host}/${env.USERNAME}_myapp_test</jdbc.url>
      <jdbc.username>dev</jdbc.username>
      <jdbc.password>dev</jdbc.password>
    </properties>
  </profile>
  <profile>
    <id>development</id>
    <properties>
      <jdbc.url>jdbc:mysql://${jdbc.host}/${env.USERNAME}_myapp_development</jdbc.url>
      <jdbc.username>dev</jdbc.username>
      <jdbc.password>dev</jdbc.password>
    </properties>
  </profile>
</profiles>
...

Note that jdbc.username and jdbc.password could be defined in each developer's settings.xml if greater security was desired.

Staging and Production

...
  <profile>
    <id>staging</id>
    <properties>
      <jdbc.url>jdbc:mysql://192.168.2.122/myapp_staging</jdbc.url>
      <jdbc.username>myapp_user</jdbc.username>
      <jdbc.password>8H6%gj8n</jdbc.password>
    </properties>
  </profile>
  <profile>
    <!-- This would likely only be present in settings.xml for whoever pushed to production. --> 
    <id>production</id>
    <properties>
      <jdbc.url>jdbc:mysql://10.4.5.341/myapp_production</jdbc.url>
      <jdbc.username>myapp_user</jdbc.username>
      <jdbc.password>gH7Ga5jw</jdbc.password>
    </properties>
  </profile>
...

Profile Location and Activation

Profiles can be defined in the pom, a parent pom, individual developer's settings.xml, and an external profiles.xml. This gives you a few options for where best to define a specific profile. And with resolution mechanism for properties (e.g. ${jdbc.url), you can support just about anything you might need.

Profiles can be manually activated using the mvn -Pdevelopment <goal> syntax. They can also be activated automatically when certain criteria are met.