Rails v. Liquibase: Creating Tables with Rails Migrations (Part 5)


When you need to create a new table in Rails, you have an important
decision to make: Do you want to create a model object and let Rails
create an associated migration? Or, do you want to create a
Stand-alone Migration and write the create_table code yourself?

In the previous post, I discussed the merits of separating your
database migrations from your application. If you are just starting a
simple Rails project, you will likely just want to go ahead and create
a model object with an associated create_table migration. If you are
using Rails to support a large, polyglot enterprise application,
you might just want to create a stand-alone migration and deal with
your model object in a separate project.

In this post, I’m going to present three different alternatives in
Rails for creating a new table: migrations w/ models, stand-along migrations, and migrations with raw SQL.

Creating a table associated with a model object

Note: I walked through the process of
creating a simple Rails project “dbtest” in a previous post. If you want to follow along
with this post, you’ll find instructions for setting up your database
connection here.

In your rails project, run the following command from your Rails project’s root directory:

$ rails generate model politician \
first_name:string last_name:string party:string
      invoke  active_record
      create    db/migrate/20101125161243_create_politicians.rb
      create    app/models/politician.rb
      invoke    test_unit
      create      test/unit/politician_test.rb
      create      test/fixtures/politicians.yml

When you use Rails to generate a model object it will take the
properties you passed to the generator and create a database
migration. Let’s take a closer look at this migration in
db/migrate/20101125161243_create_politicians.rb. First a few things
about the filename:

  • Rails creates a separate file for each database change.
  • Rails slaps a timestamp on the filename, this will become
    important later, so note the value.
  • Rails pluralized the name of our model object. Our model object
    is politician, but it wants to create a politicians table.

You will also note that Rails created a model object, a unit test, and
a test fixture. We’re not going to dwell on these objects, but very
quickly. the model object in app/models/politician.rb is a simple
class definition:

class Politician < ActiveRecord::Base
end

That’s it. The unit test does nothing but perform a simple check to
see if true is true, and the file in test/fixtures is where you would
define test data in a YAML format.

The database migration looks like this:

class CreatePoliticians < ActiveRecord::Migration
  def self.up
    create_table :politicians do |t|
      t.string :first_name
      t.string :last_name
      t.string :party

      t.timestamps
    end
  end

  def self.down
    drop_table :politicians
  end
end

Once you’ve generated all of these files, you can run the migration with the following command from your project’s root directory:

$ rake db:migrate
==  CreatePoliticians: migrating ==============================================
-- create_table(:politicians)
   -> 0.1760s
   -> 0 rows
==  CreatePoliticians: migrated (0.1770s)
=====================================

Once you run this, you’ll see that your database has a new table
“politicians” with the following columns:

Your migration said nothing of an “id” column, but this column is
created automatically every time you call create_table.

The details of this migration…

Take a closer look at the your migration in db/migrate. First, the
structure, a Rails Migration is a Ruby class which extends
ActiveRecord::Migration. ActiveRecord is the Rails ORM, and it is the
central concept around which the whole framework seems to be
assembled. Why do you use Rails? It makes writing an application
that babysits a RDBMs trivial. How does it do this? ActiveRecord.

ActiveRecord is what lets you get away with having that empty
“Politician” class which extends ActiveRecord::Base. Whenever you use
the Politician class, ActiveRecord is going to execute a bunch of SQL
to get a list of columns and figure out what properties are on the
class. In Rails, the structure of your model objects is the structure
of your tables (and yes, there are ways to customize this).

The next thing to point out is that this migration has two methods:
up and down. You can model database changes as a set of changes
executed in sequence – or points on a line. Moving forward on this
line executes the up method, and moving backwards on this line will
execute the down method. You’ll likely be most interested in the up
method, this is where you write code to implement your database
changes. The down method is important if you need to rollback
database changes. (without getting into the details, you would rollback by running “rake db:migrate VERSION=”, where version maches one of your migration’s timestamps).

Digging into Rails Migration Table Creation

Let’s take a quick survey of some of the methods that are being called
in this migration.

The up method contains the following call to create_table:

create_table :politicians do |t|
      t.string :first_name
      t.string :last_name
      t.string :party

      t.timestamps
end

What’s going on here? If you take a look at the create_table
method documentation
in the Rails API. You will see that we’re
calling create_table in what is known as “block form, with shorthand”.
create_table takes an argument :politicians which is really just the
name of the table (that table name is “politicians” not
“:politicians”, that colon is syntax for a Symbol, if you want to
understand the difference between a Symbol and a string read
this
).

You could have just stopped with the first parameter. Calling
"create_table :politicians" would have been enough. It would have
created a table with a single "id" column. This migration takes it a
step further and defines a Block, this Block contains a group of
statements which are executed when you call create_table. (If you
*really* want to know what a Block is and how it is invoked read
this
.) The contents of the block fall between do and end, and
there is a variable |t| which is an instance of TableDefinition.

What’s a TableDefinition? The TableDefinition class is Rails’ abstract
for table SQL it contains a number of methods like column which allow
you to define new columns, here’s
the API doc
. Let’s explore how we’re using this class:

t.string :first_name
Wait. There’s no method on TableDefinition named string,
what’s going on here? If you take a look at the code for
TableDefinition in
the Rails source
take a look at line #499 (fancy, eh?). TableDefinition
defines the following shortcut methods: string, text, integer, float,
decimal, datetime, timestamp, time, date, binary, and boolean.
t.timestamps
Again, check out the source for TableDefinition
and you will see on line 514-515, that this simply adds two datetime
columns “created_at” and “updated_at”.

There are a number of other methods on TableDefinition which we will
talk about later, but if the Rails defaults are what you want, it is
pretty straightforward to create a simple schema using just these
functions.

Alternative: Create a Stand-along Migration

Now, if you are just creating a simple Rails application, creating
a model object makes sense, but what if you are just using Rails only for
migrations? What if you don’t really care about tests or
fixtures?

In this case, you can create a stand-alone migration by
running:

$ rails generate migration CreateParty
      invoke  active_record
      create    db/migrate/20101125172938_create_party.rb

This is more appropriate if you have a separate rails project to
manage database schema, or you are using Rails migrations in the
context of a larger project. You notice that Rails, again, slaps a
timestamp at the front of the file name, and it also transforms your
CamelCase migration name to lowercase with underscores. Here are the
contents of the migration:

class CreateParty < ActiveRecord::Migration
  def self.up
  end

  def self.down
  end
end

Since this migration does nothing, it is up to you to add the
appropriate calls to create_table. Try putting the following code
into the up method:

class CreateParty < ActiveRecord::Migration
  def self.up
    create_table :party	do |t|
      t.string :name
      t.string :code
    end
  end

  def self.down
    drop_table :party
  end
end

And, run the migration:

$ rake db:migrate
(in /Users/Tim/Library/Code/discursive/blog/rails-v-liquibase/dbtest)
==  CreateParty: migrating ====================================================
-- create_table(:party)
   -> 0.0680s
   -> 0 rows
==  CreateParty: migrated (0.0690s)
===========================================

And, your database will have a table named “party” with the columns
“id”, “name”, “code”.

Note: The party table isn’t plural. Plural vs. Singular
table names is a religious debate (and I’m firmly on the Singular side
of the fence).
While Rails is great tool for writing web
application, it also makes a few assumptions that don’t always do well
outside of Railsland. If you are using Rails simply for migrations,
you don’t have to go with the Rails convention.

Alternative: Creating a Table with SQL

If you are really taking your database to its limits (using
Partitions in Oracle or MySQL, tuning query cache sizes for specific
tables, etc), you are likely going to start executing very DB-specific
DML statements. In this case, you are probably not going to want to
deal with all of the TableDefinition, ColumnDefinition options. In
these cases (toward the maturation of a project), you also might want
to switch to SQL to make it a bit easier on your DBAs (who know
nothing of Rails).

To create a migration that uses SQL, create a stand-alone migration
like this:

$ rails generate migration CreateElection
      invoke  active_record
      create    db/migrate/20101125174532_create_election.rb

Then edit, your new migration and use the following syntax (with HERE
docs) to define raw SQL statements for CREATE and DROP:

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 <&ltEOF
      DROP TABLE election;
EOF
  end
end

When you execute this migration, you will see that Rails just
executes the SQL directly.

Note: If you are solely using Rails Migrations as a hook to
execute RAW SQL you might want to consider some alternative
tools. Maybe you’d benefit from taking a look at Liquibase?

Conclusion

For the simpliest cases, Rails is a fairly flexible system when used as a stand-alone tool to manage database changes. This post covered only the basic, when you start to get into foreign keys, custom types, and all the good stuff that tends to show up in a custom database, Rails needs some basic customization. In this series, we’re starting slow, comparing only the most straightforward table creation cases to get a quick sense of the differences between Liquibase and Rails. Look for a more advanced post on table creation and a comparison between the two tools later on.