Rails vs. Liquibase: Working with SQL (Part 7)


It has been a month since the last installment of this Rails vs. Liquibase comparison.   The last two posts compared table creation with Rails Migrations to table creation with Liquibase.   To review, both Rails and Liquibase offer similar featuresets.    Both offer something of a “DSL” for table creation, and both also allow you to simply define a migration as a SQL script.

Changing a Database with SQL

A SQL changeset in Liquibase looks like this:

<changeSet id="create_election" author="tobrien">
  <comment>Creating the Election Table</comment>
  <sql>
    CREATE TABLE election (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    election_date DATE,
    name VARCHAR(128)
    ) TYPE=innodb;
  </sql>
  <rollback>
    <sql>DROP TABLE election;</sql>
  </rollback>
</changeSet>

And, a SQL migration in Rails looks like this:

class CreateElection < ActiveRecord::Migration
  def self.up
    execute <<EOF
      CREATE TABLE election (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        election_date DATE,
        name VARCHAR(128)
      ) TYPE=innodb;
EOF
  end
 
  def self.down
    execute <<EOF
      DROP TABLE election;
EOF
  end
end

So, there’s no appreciable difference between the two approaches to SQL changesets or migrations. In both, there is a simple “envelope” which surrounds the SQL. In the case of Liquibase, you are writing SQL within a very simple XML envelope, and in Rails migrations, your SQL exists as a here document in a Ruby class.   While using SQL in a migration is often very useful, in the most complex systems it becomes the norm.   Especially if you are starting to use more advanced features of a database (triggers, views, stored procedures, custom types, etc.) you are likely going to find it preferable to just create a new create table statement in raw SQL.

I’ve blogged this particular topic in previous parts of this series, but as soon as a real DBA shows up, they are very likely going to prefer these raw SQL migrations to anything that uses an abstraction layer to hide database complexity from developers.

Generating SQL for a Database Change

If you use Liquibase, it is trivial to create a SQL script containing a change script.   Using the Liquibase Maven plugin and running the updateSQL goal, you can just run:

mvn liquibase:updateSQL

Then, in target/liquibase/migrate.sql you’ll have the following SQL output:

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: src/main/db/db-changelog.xml
-- Ran at: 1/22/11 5:45 PM
-- Against: root@jdbc:mysql://localhost/liquitest_development
-- LiquiBase version: 1.9.5
-- *********************************************************************
 
-- Create Database Lock Table
CREATE TABLE `DATABASECHANGELOGLOCK` (`ID` INT NOT NULL, `LOCKED` TINYINT(1) NOT NULL, `LOCKGRANTED` DATETIME, `LOCKEDBY` VARCHAR(255), CONSTRAINT `PK_DATABASECHANGELOGLOCK` PRIMARY KEY (`ID`));
 
....
 
-- Changeset src/main/db/db-changelog.xml::create_politician::tobrien::(MD5Sum: 3f4754f5380463b451b6a6b1aa3cbc0)
-- Creating the Politician Table
CREATE TABLE `politician` (`id` INT AUTO_INCREMENT  NOT NULL, 
`first_name` VARCHAR(255), `last_name` VARCHAR(255), `party` VARCHAR(255), 
`created_at` DATETIME, `updated_at` DATETIME, CONSTRAINT `PK_POLITICIAN`
 PRIMARY KEY (`id`));
 
INSERT INTO `DATABASECHANGELOG` (`DATEEXECUTED`, `AUTHOR`, `LIQUIBASE`, 
`DESCRIPTION`, `COMMENTS`, `MD5SUM`, `ID`, `FILENAME`) VALUES (NOW(), 'tobrien', 
'1.9.5', 'Create Table', 'Creating the Politician Table', '3f4754f5380463b451b6a6b1aa3cbc0', 
'create_politician', 'src/main/db/db-changelog.xml');
 
-- Changeset src/main/db/db-changelog.xml::create_election::tobrien::(MD5Sum: 8ccd45a2acad2cda2035e29528dc16e7)
-- Creating the Election Table
CREATE TABLE election (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      election_date DATE,
      name VARCHAR(128)
      ) TYPE=innodb;
 
INSERT INTO `DATABASECHANGELOG` (`DATEEXECUTED`, `AUTHOR`, 
`LIQUIBASE`, `DESCRIPTION`, `COMMENTS`, `MD5SUM`, `ID`, `FILENAME`) VALUES 
(NOW(), 'tobrien', '1.9.5', 'Custom SQL', 'Creating the Election Table', 
'8ccd45a2acad2cda2035e29528dc16e7', 'create_election', 'src/main/db/db-changelog.xml');
 
...

Can you do the same thing in Rails?

While you can use a plugin, the migration_sql_generator, to achieve a similar effect for a Rails migration, this particular plugin isn’t guaranteed to work across all databases, and it doesn’t generate a single migration file containing a set of changesets ala Liquibase.   In other words, I wouldn’t trust this plugin, it isn’t a standard part of the Rails experience, someone stumble upon a need for it, created a plugin, but I don’t get the sense that this is a very frequently visited part of the Rails ecosystem.

Most Rails developers are going to be executing Rails migrations directly against test, development, and production networks likely using tools like Capistrano which were specifically designed to develop Rails deployment workflows.   If your system isn’t solely comprised of Rails applications and if you need to work with external groups of sysadmins (who are often uninterested in using the latest development tool/framework), you may have to produce a SQL file for a given push to production.

In this this area, Liquibase comes out as the clear winner.   If you work in an enterprise and you want to make sure that you can pass a SQL file to an operations group and not require them to run some custom database tool – use Liquibase.