Creating a Table

My first step to creating a hello-world style Rails application involves using a database. Using PHP on a regular basis and being the type of person that tries to use a tool without reading the instructions first, I jump into a MySQL console and create a table to work with.

mysql> create table newsletter_subscribers (
email varchar(150) primary key,
created timestamp default now()
);

After reading several Rail database tutorials, there’s a better way of doing this. Rails provides a Migration class which will do the database dirty work for you.

First create a project to work with. I called my project newsletter.

rails newsletter

This creates a bunch of files and places it in a directory called newsletter. Now, I want to programmatically create a database table called subscribers.

cd newsletter
ruby script/generate model subscriber

The last line of output that Ruby generates is of particular interest.

create test/unit/subscriber_test.rb
create test/fixtures/subscribers.yml
create db/migrate
create db/migrate/001_create_subscribers.rb

Editing the 001_create_subscribers.rb file, I’ll add the columns that I want. Keep in mind that this doesn’t match the CREATE TABLE MySQL example that I used at the beginning on this entry.

class CreateSubscribers < ActiveRecord::Migration
  # Initial table setup in this migration
  def self.up
    # Create a subscribers table
    # Most documentation recommends making naming
    # tables using the plural form of the word
    create_table :subscribers do |table|
      # Declare an email field as a string
      table.column :email, :string,
                   # Restrict the size to 150 characters
                   :limit => 150,
                   # The field cannot have a null value
                   :null => false
      # Declare a join_timestamp as a datetime value
      table.column :join_timestamp, :datetime,
                   :null => false
    end

    # Create an index on the email field
    # Enforce that email addresses are unique
    add_index :subscribers, :email, :unqiue => true
  end

  # Undo this migration
  def self.down
    drop_table :subscribers
  end
end

Personally, I never encountered using an object in a programming language to create a table before. The idea behind this from what I’ve read allows incremental table, or schema, changes. If I wanted to rename a column, this infrastructure supports it. I’m not going to delve into that at this time since I’m trying to create my first table.

I’ll create a database in MySQL for this project. Ruby will programmatically create the table for me, but it will not create the database for the table. The convention that Ruby uses is project name_development for the development version of this database. Since I called this project newsletter, I’ll create a newsletter_development database.

mysqladmin -u root -p create newsletter_development

Next, I’ll use the rake command (where do they come up with these names), to migrate the changes.

rake db:migrate

The table is created, generating the following output.

== CreateSubscribers: migrating ===============================
– create_table(:subscribers)
-> 0.0149s
– add_index(:subscribers, :email, {:unqiue=>true})
-> 0.0196s
== CreateSubscribers: migrated (0.0349s) ======================

Jumping into the MySQL console, let’s see definition of the subscribers table.

mysql> use newsletter_development;
mysql> describe subscribers;

The two columns that I defined in the CreateSubscribers object are in this table. There is an additional column, id, which I did not define. To quote my Rails book: “Rails really doesn’t work too well unless each table has a numeric primary key…My strong advice is to go with the flow and let Rails have its id column.”

In the event that I wanted to undo the first step of migration, I would revert to version zero by typing.

rake db:migrate VERSION=0

Ruby will revert to the original state, dropping the table that I created in my first revision.

== CreateSubscribers: reverting ===============================
– drop_table(:subscribers)
-> 0.0035s
== CreateSubscribers: reverted (0.0037s) ======================