Creating a Project to Manage Database Change with Liquibase (Part 3)


In the previous two posts, I dicussed the challenges of supporting an
enterprise database with Rails Migrations
and we quickly set up a
simple Rails application
to use as a foundation for exploration
and comparison. In
this post, we’re going to walk through the process of setting up a
simple project stub to use for Liquibase experiments.

This simple project will use Maven as a foundation for the project.
Maven is the most sophisticated project and build tool available, and
Liquibase has a mature Maven plugin to support development.

Prerequisites and Assumptions

You are running a Java 6 SDK. Either OpenJDK or a JDK from Oracle.
You should also be running a relatively recent MySQL 5.1
distribution, and you should have administrative access to it.

You are also running Apache Maven 3. If you don’t have Maven yet,
download it from apache, unpack
it, point the M2_HOME variable at the directory and add M2_HOME/bin to your PATH.

Step 1: Create a Project

$ mvn archetype:generate

This command will generate a list of archetypes. (When this blog
post was posted) That archetype list contains 303 archetypes and the
default archetype (#82 maven-archetype-quickstart) is selected by
default. Select the default, and choose the latest version (currently
1.1). Supply the following values for groupId, artifactId,
version:

  • groupId: com.discursive.migrate
  • artifactId: liquibase-test
  • version: 1.0-SNAPSHOT
  • package: com.discursive.migrate

Running the archetype plugin will create a barebones Maven project
in the directory liquibase-test.

Step 2: Add the Liquibase Plugin to Your POM

Add the following XML to your new project’s Maven POM directly
after the dependencies section:

  
    
      org.liquibase
      liquibase-plugin
      1.9.5.0
      
        src/main/db/liquibase.properties
      
      
        
          mysql
          mysql-connector-java
          5.1.13
        
      
    
  

This is going to add the Liquibase Maven plugin to your project and
point your build at a properties file for configuration. You will
also notice that I’m adding the MySQL Connector/J JDBC driver directly
as a plugin dependency.

Step 3: Create a Liquibase Properties File

$ mkdir -p src/main/db
$ emacs src/main/db/liquibase.properties

This properties file should contain the following variables:

changeLogFile src/main/db/db-changelog.xml 
driver com.mysql.jdbc.Driver
url jdbc:mysql://localhost/liquitest_development
username root
password 
verbose true 
dropFirst false

Step 4: Create a Database Change Log

In Liquibase, all changes to your database are going to be tracked in
an XML file called a Change log. Put the following, empty Change Log
XML into a file in ${basedir}/src/main/db/db-changelog.xml:

 


This change log isn’t particular interesting (it doesn’t do
anything), but it will form a foundation for future posts about
liquibase.

Step 5: Create Your Database

Create a mysql database. For this sample project, we are going to
use the same conventions that are used in rails. Your development
database will be named liquitest_development.

$ mysqladmin -u root -p create
liquitest_development

Step 6: Run Liquibase for the First Time

From your project’s root, run the following:

$ mvn liquibase:update
[INFO] Scanning for projects...
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building liquibase-test 1.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- liquibase-plugin:1.9.5.0:update (default-cli) @ liquibase-test ---
[INFO] ------------------------------------------------------------------------
[INFO] Parsing Liquibase Properties File
[INFO]   File: src/main/db/liquibase.properties
[INFO] ------------------------------------------------------------------------
[INFO] Executing on Database: jdbc:mysql://localhost/liquitest_development
Nov 23, 2010 1:36:07 PM liquibase.database.template.JdbcTemplate comment
INFO: Create Database Lock Table
Nov 23, 2010 1:36:08 PM liquibase.database.template.JdbcTemplate comment
INFO: Lock Database
Nov 23, 2010 1:36:08 PM liquibase.lock.LockHandler acquireLock
INFO: Successfully acquired change log lock
Nov 23, 2010 1:36:08 PM liquibase.database.template.JdbcTemplate comment
INFO: Create Database Change Log Table
Nov 23, 2010 1:36:08 PM liquibase.database.AbstractDatabase checkDatabaseChangeLogTable
INFO: Creating database history table with name: `DATABASECHANGELOG`
Nov 23, 2010 1:36:09 PM liquibase.database.AbstractDatabase getRanChangeSetList
INFO: Reading from `DATABASECHANGELOG`
Nov 23, 2010 1:36:09 PM liquibase.database.template.JdbcTemplate comment
INFO: Release Database Lock
Nov 23, 2010 1:36:09 PM liquibase.lock.LockHandler releaseLock
INFO: Successfully released change log lock
Nov 23, 2010 1:36:09 PM liquibase.database.template.JdbcTemplate comment
INFO: Release Database Lock
Nov 23, 2010 1:36:09 PM liquibase.lock.LockHandler releaseLock
INFO: Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS

There, if you look at your database, your liquitest_development
database should have two tables.

There it is, you haven’t really used Liquibase yet, but you have a
working Maven project with a properties file and a changelog. In the
next post, I’m going to focus on what it takes to create tables in
both Rails and Liquibase.

Liquibase Recommendations

You’ll note that my advice differs from the standard configuration
recommended in the Liquibase documentation. I’m not a big fan of
putting database management tasks in the middle of the project
lifecycle. I don’t view these operations as part of the regular
development cycle. My recommendation is that you not configure
liquibase update as a regular, automatic part of your project’s
build.

If your project is going to grow over a few years, I also recommend
that you create a project dedicated to your Liquibase configuration
and change log. Project and applications tend to come and go, but
databases grow in importance over time. If you hook your database
change scripts to a web application, it will seem odd in a few years
once you have a constellation of related applications. New Dev: “Why are our
database scripts in the customer-web application?” You: “That’s a
legacy from back when we only had one developer and one application.