Recipe 3.2. Programmatically Defining Database Schema

Problem

You are developing a Rails application for public distribution and you would like it to work with any database that supports Rails migrations (e.g., MySQL, PostgreSQL, SQLite, SQL Server, and Oracle). You want to define your database schema in such a way that you don't need to worry about the specific SQL implementation of each database.

Solution

From your application's root, run the following generator command:

$ ruby script/generate migration create_database             

This command creates a new migration script named 001_create_database.rb. In the script's up method, add schema creation instructions using Active Record schema statements, such as create_table. For the down method, do the reverse: add statements to remove the tables created by up.

db/migrate/001_create_database.rb:

class CreateDatabase < ActiveRecord::Migration   def self.up     create_table :products do |t|       t.column :name, :string, :limit => 80       t.column :description, :string      end     create_table(:categories_products, :id => false) do |t|       t.column :category_id, :integer       t.column :product_id, :integer     end     create_table :categories do |t|       t.column :name, :string, :limit => 80     end   end   def self.down     drop_table :categories_products     drop_table :products     drop_table :categories   end end

Then instantiate your database with this migration by running:

$ rake db:migrate             

Discussion

Inspecting the database shows that the tables were created correctly, just as if you had used pure SQL.

mysql> desc categories; +-------+-------------+------+-----+---------+----------------+ | Field | Type        | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+ | id    | int(11)     |      | PRI | NULL    | auto_increment | | name  | varchar(80) | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> desc products; +-------------+--------------+------+-----+---------+----------------+ | Field       | Type         | Null | Key | Default | Extra          | +-------------+--------------+------+-----+---------+----------------+ | id          | int(11)      |      | PRI | NULL    | auto_increment | | name        | varchar(80)  | YES  |     | NULL    |                | | description | varchar(255) | YES  |     | NULL    |                | +-------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> desc categories_products; +-------------+---------+------+-----+---------+-------+ | Field       | Type    | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+-------+ | category_id | int(11) | YES  |     | NULL    |       | | product_id  | int(11) | YES  |     | NULL    |       | +-------------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)

We've set up a database with a many-to-many relationship between products and categories, and a categories_products join table. Unlike the other tables, the join table doesn't have a primary key. We suppressed the creation of a primary key, which Rails creates by default, by passing @:id => false as an option to create_table when creating categories_products.

create_table takes a block that contains calls to the column method, defining the columns of the table. column is passed the name of the column, followed by the type (for example, :primary_key, :string, :text, :integer, :float, :datetime, :timestamp, :time, :date, :binary, :boolean). Finally, you can pass options to column that define the maximum width, default value, and whether null entries are allowed. For example:

t.column :name, :string, :limit => 80 t.column :role, :string, :default => 'admin' t.column :status, :string, :default => 'pending', :null => false

See Also

• Section 3.3" for the preferred way to develop a database in Rails with migrations.