March 11, 2008

Create A New Migration Script

Filed under: migration, model, rails — oomoo @ 1:19 pm


  1. Generate the script
  2. Edit the script
  3. Test the Script
  4. Apply the Script
  5. Roll back a script
  6. Apply the Script… again
  7. Check the “schema.rb” that gets generated
    NewbieNote*  The Database.YML file dictates the database upon which your code (and rake commands) operate.  For example, this snippet from a database.yml file will execute all commands/code on the “mybd_development” database. 
      adapter: mysql
      database: mydb_development
      username: root
      host: localhost 


Generate the scripts…

    For a new Table/Model…      “ruby script/generate model order”
          This command also creates the following files:
             app/models/ order.rb
             test/unit/ order_test.rb
             test/fixtures/ orders.yml 
    For a new Table/Model (just the migration script)…   “ruby script/generate migration create_orders_table”
    For making modifications…      “ruby script/generate migration this_is_the_name_of_my_migration”
    For existing Tables (legacy data)…      …see the related topic ‘Create Migration For Existing Tables’ 
    (you should use explicit names, describing what the migration will do, since the generator will create a file with this same name and overwrite any previously existing one) 
    Scripts are Stored In:  \db\migrate\ 
    Scripts are Sequentially Numbered…  001_create_orders_table.rb, 002_create_second_table.rb, etc. 
    Next Number that needs to be applied, stored in table “schema_info” field named “version” 
    “db:migrate” will apply the scripts in sequential order, starting with the next number after the one stored in “schema_info.version”. 

Edit the script file (see below) 

    You probably want to include the “:force => true” parameter when creating a new table. 
    Make sure you have properly coded the “self.down” method in case you need to roll back to a prior version! 

Test the Migration Scrips…   “rake db:migrate –dry-run –trace” 

    If “nothing” happens, then your migration was not applied.  The most obvious reason is that the “schema_info.version” field already
    contains a number equal to, or higher than, the migration you are trying to apply. 

Apply the Migration Scripts…  “rake db:migrate”

    Did an error occur?  See the “troubleshooting” section below. 
    If “nothing” happens, then your migration was not applied.  The most obvious reason is that the “schema_info.version” field already
    contains a number equal to, or higher than, the migration you are trying to apply. 

Roll back a script…  “rake db:migrate VERSION=004”    (substitute the desired “version #” for the number “4”) (Use the number “0” to drop the tables) 

    If you manually “dropped” the table yourself, you need to add the table back (just add one field), so the rollback will work.
    OR, you could manually change the number stored in “schema_info.version” to a number prior to when the table existed. 
    It is a good idea to “roll back” EVERY script, just to make sure your “self.down” works properly (before the next \db\migrate\ script gets created). 

Apply the Migration Script…Again  “rake db:migrate”   (After it rolls back successfully, just “rake db:migrate” again to put your migration back into effect.) 

Check the “schema.rb” file to check your new model.  Can be run manually by…  “rake db:schema:dump” 



Table/Model Naming Conventions: 

    Model / Class – ( singular, first letter Capitalized, CamelCase for models like SteeringWheel)
    Table / schema (in database) – ( plural, with underscores instead of spaces between words, like steering_wheels)  
    Model/Class       Table/Schema
    Order                 orders
    LineItem             line_items
    Person                people
    Address             addresses
    Legacy               legacies
    Mouse               mice 



Migration Data Types





      float :limit => 25   (yields a double)







      decimal, :precision => 15, :scale => 10   
               (precision = total digits, scale = digits right of decimal point)

      t.column :, :string, :limit => 0, :default => , :null => false

      t.column :, :integer, :default => ‘0’ :null => false

      t.column :, :decimal, :precision => 10, :scale => 0 :default => ‘0’ :null => false

      t.column :, :date, :default => ‘0000-00-00’, :null => false 

      *Force the ordCustPK field to be a Foreign Key (FK) referencing Customers.CustPK:

      t.column :ordCustPK, :integer, :default => ‘0’ :null => false,  
=> [:customers, :CustPK] 

      *Force the Primary Key (PK) of the customers table to be the CustPK field:

      create_table(:customers, :force => true :primary_key => ‘CustPK’) 



Some Additional Migration Syntax

      add_index “activitylog”, [“logcreatedate”], :name => “logcreatedate”

      add_constraint :projects, :unique => :name

      add_constraint :projects, :foreign_key => :owner_id, :references => :users

            OR… execute ALTER TABLE posts ADD CONSTRAINT fk_posts FOREIGN KEY(discussion_id) REFERENCES discussions(id) 

      say_with_time “Updating salaries…”

            …Inserts your own messages/benchmarks by using the #say_with_time method 

Available transformations

  • create_table(name, options) Creates a table called name and makes the table object available to a block that can then add columns to it, following the same format as add_column. See example above. The options hash is for fragments like “DEFAULT CHARSET=UTF-8” that are appended to the create table definition.
  • drop_table(name): Drops the table called name.
  • rename_table(old_name, new_name): Renames the table called old_name to new_name.
  • add_column(table_name, column_name, type, options): Adds a new column to the table called table_name named column_name specified to be one of the following types: :string, :text, :integer, :float, :decimal, :datetime, :timestamp, :time, :date, :binary, :boolean. A default value can be specified by passing an options hash like { :default => 11 }. Other options include :limit and :null (e.g. { :limit => 50, :null => false }) — see ActiveRecord::ConnectionAdapters::TableDefinition#column for details.
  • rename_column(table_name, column_name, new_column_name): Renames a column but keeps the type and content.
  • change_column(table_name, column_name, type, options): Changes the column to a different type using the same parameters as add_column.
  • remove_column(table_name, column_name): Removes the column named column_name from the table called table_name.
  • add_index(table_name, column_names, index_type, index_name): Add a new index with the name of the column, or index_name (if specified) on the column(s). Specify an optional index_type (e.g. UNIQUE).
  • remove_index(table_name, index_name): Remove the index specified by index_name.



Primary Key:

The Rails convention is for every table to have a field named “id” which is an auto-incrementing integer primary key.  



Specify Your Own Primary Key:

The Rails convention is for every table to have a field named “id” which is an auto-incrementing integer primary key

(that the Rails migration code will automatically insert into your tables). 

If you are working with an existing database, you probably don’t want to redefine all the primary key fields to suit Rails. 

In your Migration code, just use the “:primary_key =>” parameter in your create_table method call, like this: 

class CreateAppTables < ActiveRecord::Migration

  def self.up

    create_table “employees”, :force => true, :primary_key => “empPK” do |t|

      t.column “empFirstName”,    :string,    :limit => 100, :default => “”,   :null => false

      t.column “empLastName”,     :string,    :limit => 100, :default => “”,   :null => false

      t.column “empActive”,       :boolean,                  :default => true, :null => false

      t.column “empDateCreate”,   :timestamp,                                  :null => false

      t.column “empLastLogon”,    :datetime,                                   :null => false

      t.column “empCustomerMgr”,  :integer,   :limit => 5,   :default => 0,    :null => false



Note – Since you are specifying the primary key, DO NOT also list the primary key as a field !!!

Notice above that since “empPK” is the primary key field, no “t.column” line exists for the field “empPK”. 

In your Model code, you must also tell Rails not to use the standard “id” primary key. 

Include a statement like this in your model code: 

class Employee ActiveRecord::Base 

  set_primary_key “empPK” 




(see also) http://redhillconsulting.com.au/rails_plugins.html 
If you use any engines, etc. that have their own migrations,  

You will need to edit them to keep the “Row Version Migration” plugin from inserting the extra columns. 

In the create table statement, include  :row_version => false 

You will also need to edit the field definitions for any field that ends with “id” to keep the “Foreign Key Migration” plugin

from trying to force foreign key constraints on the table. 

Add the following to the end of the each “id” field definition  :references => nil 




This migration will add a boolean flag to the accounts table and remove it again:

    class AddSsl < ActiveRecord::Migration
        def self.up
          add_column :accounts, :ssl_enabled, :boolean, :default => 1
        def self.down
          remove_column :accounts, :ssl_enabled

Example of a more complex migration that also needs to initialize data:

  class AddSystemSettings < ActiveRecord::Migration

    def self.up

      create_table :system_settings do |t|

        t.column :name,     :string

        t.column :label,    :string

        t.column :value,    :text

        t.column :type,     :string

        t.column :position, :integer


      SystemSetting.create :name => “notice”, :label => “Use notice?”, :value => 1


    def self.down

      drop_table :system_settings




class CreateRepairs < ActiveRecord::Migration

  def self.up

    execute “DROP TABLE IF EXISTS `repairs`” 

      create_table(:repairs, :force => true :options => ‘ENGINE=MyISAM’) do |t|

            t.column :ordPK, :integer, :null => false, :auto_increment => true

            t.column :ordCustPK, :integer, :null => false

            t.column :ordCustPONum, :string, :limit => 20, :default =>

            t.column :ordTotal, :decimal, :precision => 10, :scale => 2 :default => ‘0.00’



  def self.down

    drop_table :repairs




class CreateRepairs < ActiveRecord::Migration

  def self.up

      create_table(:repairs, :force => true) do |t|

            t.column :ordPK, :integer, :default => 0, :null => false

            t.column :ordCustPONum, :string, :limit => 20, :null => false

            t.column :ordFinalDisposition, :string, :limit => 30, :null => false

            t.column :orDateOrderTaken, :date, :null => false  

            t.column :ordItemWeight, :decimal, :precision => 10, :scale => 0, :default => 0

            t.column :ordShippingWeight, :float, :limit => 25, :default => 0, :null => false



  def self.down

    drop_table :repairs



Not all migrations change the schema. Some just fix the data:

  class RemoveEmptyTags < ActiveRecord::Migration

    def self.up

      Tag.find(:all).each { |tag| tag.destroy if tag.pages.empty? }


    def self.down

      # not much we can do to restore deleted data

      raise IrreversibleMigration



Others remove columns when they migrate up instead of down:

  class RemoveUnnecessaryItemAttributes < ActiveRecord::Migration

    def self.up

      remove_column :items, :incomplete_items_count

      remove_column :items, :completed_items_count


    def self.down

      add_column :items, :incomplete_items_count

      add_column :items, :completed_items_count



And sometimes you need to do something in SQL not abstracted directly by migrations:

  class MakeJoinUnique < ActiveRecord::Migration

    def self.up

      execute “ALTER TABLE `pages_linked_pages` ADD  
               UNIQUE `page_id_linked_page_id` (`page_id`,`linked_page_id`)”


    def self.down

      execute “ALTER TABLE `pages_linked_pages` DROP INDEX `page_id_linked_page_id`”





The Next “Migration Number” that needs to be applied, is stored in table “schema_info” field named “version”


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: