dcsimg
November 29, 2020
Hot Topics:

Manage Your MySQL Database with Rails' Migrations

  • By W. Jason Gilmore
  • Send Email »
  • More Articles »

Dropping a Table Using Migrations

So how do you cause the down method to execute? Because migrations operates much like a version control tool, you can roll the database back to an earlier version. Version numbers are identified by the migration script's prepended number; Because the 001_add_contacts_table.rb script's version number is 001, you can roll those changes back like so:

%>rake db:migrate VERSION=000
(in C:/ruby/addressbook)
== AddContactsTable: migrating ===============================
-- create_table(:contacts)
   -> 0.1250s
== AddContactsTable: migrated (0.1250s) ======================

Execute SHOW TABLES anew and you'll see the contacts table has been dropped!

Modifying a Table Using Migrations

Of course, developers will want an easy means to manage the database tables. Again, all you need to do is create a new migration script. For instance, suppose you want to add a city column to the contacts table:

%>ruby script/generate migration modify_contacts_add_city
       exists  db/migrate
       create  db/migrate/002_modify_contacts_add_city.rb

Open 002_modify_contacts_add_city.rb and modify the file so it looks like this:

class ModifyContactsAddCity < ActiveRecord::Migration
   def self.up
      add_column :contacts, :city, :string
   end

   def self.down
      remove_column :contacts, :city
   end
end

Execute describe contacts anew and you'll see the city column has been added to the table!

mysql> describe contacts;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | YES  |     | NULL    |                |
| email    | varchar(255) | YES  |     | NULL    |                |
| birthday | date         | YES  |     | NULL    |                |
| city     | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Notice I've also implemented the down method; it will remove the city column. To drop the column, just roll the migration back to version 001 like so:

C:\ruby\addressbook>rake db:migrate VERSION=001
(in C:/ruby/addressbook)
== ModifyContactsAddCity: reverting ===========================
-- remove_column(:contacts, :city)
   -> 0.1720s
== ModifyContactsAddCity: reverted (0.1720s) ==================

Conclusion

This tutorial introduced you to just a smattering of what migrations has to offer; it not only supports MySQL, but also popular databases such as SQLite, PostgreSQL, SQL Server, and Oracle. See the migrations documentation on the Rails web site for a complete breakdown of this wonderful tool's capabilities. Although migrations is a Rails-specific feature, there's nothing to prevent you from quickly creating a solution for managing MySQL no matter what web development language you settle upon.

About the Author

W. Jason Gilmore is Apress' Open Source Editor, and co-founder of IT Enlightenment. He's the author of several books, including the best-selling Beginning PHP and MySQL 5: Novice to Professional, Second Edition (Apress, 2006. 913pp.). Jason loves receiving e-mail, so don't hesitate to write him at wjATwjgilmore.com.





Page 2 of 2



This article was originally published on September 24, 2007

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.


Thanks for your registration, follow us on our social networks to keep up-to-date