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
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 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
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
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:
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
down method. You’ll likely be most interested in the
method, this is where you write code to implement your database
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.
up method contains the following call to
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
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
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
there is a variable
|t| which is an instance of
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:
- Wait. There’s no method on
what’s going on here? If you take a look at the code for
the Rails source take a look at line #499 (fancy, eh?).
defines the following shortcut methods: string, text, integer, float,
decimal, datetime, timestamp, time, date, binary, and boolean.
- 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
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
In this case, you can create a stand-alone migration by
$ 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
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
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
$ 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 <<EOF 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?
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.