Table of Contents
- 1. Table methods
- 1.1. change_table
- 1.2. create_table
- 1.3. drop_table
- 1.4. rename_table
- 2. example_migration.rb
- 3. Database Mapping
- 4. Rake tasks
- 5. Directory structure
- 6. Shortcut methods
- 7. Column methods
- 7.1. add_column
- 7.2. change_column
- 7.3. rename_column
- 7.4. remove_column
- 8. Indexes
- 8.1. add_index
- 8.2. remove_index
- 9. Miscellaneous methods
- 9.1. execute
- 9.2. IrreversibleMigration
- 10. script/generate
- 11. Loading fixtures
- 11.1. customers.yml
- 11.2. migration.rb
1. Table methods
1.1. change_table
Provides a block that enables you to alter columns on an existing table using various shortcut methods.
change_table :table_name do |t|
t.change :column_name, :new_column_type
t.remove :column_name
end
1.2. create_table
Creates a table on the database. Creates a table called :table_name and makes the table object available to a block that can then add columns to it by specifying column types or utilising shortcut methods such as using belongs_to to specify foreign keys…
create_table :table_name, {table_options} do |t|
t.string :name, {column_options}
end
Table 1.2.1 {table_options}
| Key | Value | Description |
|---|---|---|
:force | true or false | if true, forces drop of an existing table of the same name before creation the new one |
:temporary | true or false | if true, creates a temporary table, one that goes away when the application disconnects from the database |
:id | true or false | if false, defines a table with no primary key, for example when you need to define a join table |
:primary_key | :symbol | overrides the default name of :id for the primary column. Use this to specify the name of the column in the database that Rails will use to store the primary key |
:options | "string" | pass raw options to your underlying database, e.g. auto_increment = 10000. Note that passing options will cause you to lose the default ENGINE=InnoDB statement |
1.3. drop_table
Destroys the specified table.
drop_table :table_name
1.4. rename_table
Renames the specified table.
rename_table :old_table_name, :new_table_name
2. example_migration.rb
class CreateCustomers < ActiveRecord::Migration
def self.up
create_table :customers, :primary_key => :customer_id, :options => "auto_increment = 10000" do |t|
t.integer :customer_id
t.string :name, :limit => 30, :null => false
t.integer :age
t.boolean :premium, :default => 0
t.binary :photo, :limit => 2.megabytes
t.timestamps
t.text :notes, :default => "No notes recorded"
end
add_column :customers, :surname, :string, :limit => 50
add_column :orders, :price, :decimal, :precision => 8, :scale => 2
Customer.create :name => "David", :surname => "Smith", :age => "32", :premium => "1", :notes => "One of our top customers!"
end
def self.down
drop_table :customers
end
end
3. Database Mapping
| Rails | db2 | mysql | openbase | Oracle |
|---|---|---|---|---|
:binary | blob(32678) | blob | object | blob |
:boolean | decimal(1) | tinyint(1) | boolean | number(10) |
:date | date | date | date | date |
:datetime | timestamp | datetime | datetime | date |
:decimal | decimal | decimal | decimal | decimal |
:float | float | float | float | number |
:integer | int | int(11) | integer | number(38) |
:string | varchar(255) | varchar(255) | char(4096) | varchar2(255) |
:text | clob(32768) | text | text | clob |
:time | time | time | time | date |
:timestamp | timestamp | datetime | timestamp | date |
| Rails | postgresql | sqlite | sqlserver | Sybase |
|---|---|---|---|---|
:binary | bytea | blob | image | image |
:boolean | boolean | boolean | bit | bit |
:date | date | date | datetime | datetime |
:datetime | timestamp | datetime | datetime | datetime |
:decimal | decimal | decimal | decimal | decimal |
:float | float | float | float(8) | float(8) |
:integer | integer | integer | int | int |
:string | * | varchar(255) | varchar(255) | varchar(255) |
:text | text | text | text | text |
:time | time | datetime | datetime | time |
:timestamp | timestamp | datetime | datetime | timestamp |
4. Rake tasks
db:createdb:create:all- Creates a single database specified in
config/databases.ymlfor the currentRAILS_ENVor creates all the databases db:dropdb:drop:all- Drops a single database specified in
config/databases.ymlfor the currentRAILS_ENVor drops all the databases db:fixtures:load- Load fixtures from
test/fixturesinto the current environment’s database db:migrate- Run all unapplied migrations
db:migrate:updb:migrate:down- Move forward to the next migration, or back to the previous migration
db:migrate VERSION=18- Migrate database to specific version
db:migrate RAILS_ENV=production- Use migrations to recreate tables in the testing or production databases
db:schema:dump- Create a
db/schema.rbfile that can be portably used against any database supported by ActiveRecord db:schema:load- Load a schema.rb file into the database
db:sessions:create- Create a sessions table for use with
CGI::Sessions::ActiveRecordStore db:sessions:clear- Clear the sessions table
db:structure:dump- Dump database structure to SQL file
db:reset- Drops the database, creates the database and then runs migrations against the database. Takes a
VERSIONargument as well asRAILS_ENV db:rollback STEP=4- Takes a STEP argument to determine how many version to rollback, the default being one version
db:test:prepare- Clone your database structure into the test database
db:version- Tells you the current version your database is at
5. Directory structure
RAILS_ROOT
/db
+ schema.rb
/migrate
+ 20081204220440_remove_sections_table.rb
+ 20081204220455_add_customers_table.rb
6. Shortcut methods
t.column t.change t.rename
t.remove t.change_default t.references
t.remove_references t.belongs_to t.remove_belongs_to
t.timestamps t.index t.remove_index
7. Column methods
7.1. add_column
Creates a new column on the specified table.
add_column :table_name, :column_name, :column_type, {column_options}
Table 7.1.1 {column_options}
| Key | Value | Description |
|---|---|---|
:null | true or false | if false, the underlying column has a not null constraint added by the database engine |
:limit | integer | set a limit on the size of the field |
:default | string | set a default value for the column |
:precision | integer | Specifies the precision for a :decimal column. |
:scale | integer | Specifies the scale for a :decimal column. |
7.2. change_column
Change the data type of the specified column
change_column :table_name, :column_name, :new_column_type
7.3. rename_column
Renames the specified column.
rename_column :table_name, :old_column_name, :new_column_name
7.4. remove_column
Removes the specified column.
remove_column :table_name, :column_name
8. Indexes
8.1. add_index
Creates an index for the specified column.
add_index :table_name, :column_name, :unique => true
8.2. remove_index
Remove an index from the specified column.
remove_index :table_name, :column_name
9. Miscellaneous methods
9.1. execute
Takes a single string identifying a valid SQL command to execute directly.
execute "alter table line_items add constraint fk_line_item_products foreign key (product_id) references products(id)"
9.2. IrreversibleMigration
Use in the down method of a migration file to raise an exception when the up methods of the same migration file can not be reversed, e.g. changing a column type from :integer to :string.
raise ActiveRecord::IrreversibleMigration
10. script/generate
script/generate migration new_migration_filename
field_name:column_type name:string age:integer date_of_birth:date
11. Loading fixtures
Fixtures contain data which can be loaded into your database using migrations. For example, to load data into a table named customers…
- Create a directory,
db/migrate/data - Create a file,
customers.yml, insidedb/migrate/data - Generate a new migration file:
ruby script/generate migration load_customers_data - Edit it to load data from the
customers.ymlfile into your customers table
11.1. customers.yml
melissa:
name: Melissa
age: 18
david:
name: David
age: 23
11.2. migration.rb
require 'active_record/fixtures'
class LoadCustomerData
def self.up
down
directory = File.join(File.dirname(__FILE__), "data")
Fixtures.create_fixtures(directory, "customers")
end
def self.down
Customer.delete_all
end
end

Cheatsheets





ABOUT 1 YEAR AGO
Tomomi
Very Beneficial
Very Beneficial resource code.
ABOUT 1 YEAR AGO
tom
migrate
beneficial
ABOUT 1 YEAR AGO
Joe Lewis
Very nice - one small bug
Thank you for the organized approach to looking at migrations - much appreciated. One small item - I noticed in the PDF version, the rename_column item uses rename_table in the code example (rename_table :old_column_name, :new_column_name).
11 MONTHS AGO
Cwelle
Very nice, buy one type is missing
Very nice, buy one type is missing. It's :text. Representing TEXT in mysql.
11 MONTHS AGO
Dougle
Drops?
What about drops for migrating down
11 MONTHS AGO
txarli
Cortesy
what ahout a little bit of courtesy? So, when you find an excelent guide to migrations, the only thing to say is "What about drops for migrating down" pff...
11 MONTHS AGO
Dougle
Drops?
"So, when you find an excelent guide to migrations, the only thing to say is "What about drops for migrating down" pff..." Exactly what i mean.. it would be an "excellent" guide if it had drop syntax, that's not discourteous at all.
10 MONTHS AGO
Gideon
rename_table args reversed
Thanks for this very useful resource. The args for rename_table should be :old_table, :new_table (and rename_table is actually listed twice too).
10 MONTHS AGO
Ryan Shillington
rename_column is wrong too
You're missing the table argument from the rename_column syntax as well.
9 MONTHS AGO
mosquete
Minor mistake
In "Fixtures" section, item 4, "migration.rb" was swapped with "customers.yml".
7 MONTHS AGO
shikha
Limit is not working with integer or bigint
i have tried so many times but :limit is not working. code: add_column(:table, :field, :bigint, {:limit => 13})
6 MONTHS AGO
Richard Patching
Limit is not working with integer or bigint
To create a bigint you need to set the integer limit to be between 5 and 8 bytes. The following example would give you a bigint with length 20: t.integer :foo, :limit => 8
2 MONTHS AGO
Manasi Vora
Yes this works
this works perfectly well. Another issue, I came across in my project was ruby supports extended inserts (i.e sql file with insert statements for every row) but not single insert (1 sql statement for all rows). I think it is something with the mysql adapter. I am on ruby 1.8.5
2 MONTHS AGO
Fern
Ichy
Thanks! it is very good
2 MONTHS AGO
Soro
Great Guide!
Thank you very much.. Great new Migration guide
2 MONTHS AGO
Loan
Yeah !
Thanks a lot ! It saves me a lot of time !
6 DAYS AGO
JezC
Fixtures?
Great guide - but for one niggle. I can't use the Fixtures cheat in 11.2; the migration.rb code does a "down", which fails on the first attempt at migration, as there is no previous table with contents for the delete_all. I get a SQLite3 error when I try to use it.