dcsimg
June 25, 2018
Hot Topics:

Manage Your MySQL Database with Rails' Migrations

  • September 24, 2007
  • 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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

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

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date