Big Data/Analytics Zone is brought to you in partnership with:

We design and develop applications for the web using Ruby on Rails, as well as apps for mobile devices using html5, javascript, and phonegap, as well as iOS for iphones and iPads, and Android OS for android devices. Nick has posted 6 posts at DZone. You can read more from them at their website. View Full User Profile

Using SQL Instead of Ruby to Speed Migrations

09.06.2012
| 2678 views |
  • submit to reddit

This post originally authored by Reed Law.

When building a web app, there’s often a mountain of data that I need to move from one format to another. Most web application frameworks provide a mechanism for this called migrations. A migration is a snippet of code that creates a change in the database. Migrations are convenient because they can run inside each environment (e.g. development, staging, and production) and create the same results without having to dump and restore databases.

Usually when I think about doing a one-off data migration, I first think of how easy it is to do in Ruby. Programming in Ruby all day makes me think in Ruby, so I almost automatically come up with something like this:

Zone.all.map{|z|z.city + "," + z.state}.uniq.map{|c|City.create(name: c.gsub(/,.+$/), state: c.gsub(/^.+/))}

I needed to create cities for each unique city/state in the zones table. So I mapped each unique city plus state, to an array that then created a new city (with the help of a little regex to split the city and state). But with over forty thousand rows, this expression took several minutes before it overheated my laptop. Turns out this same migration can happen in under a second with a carefully crafted SQL statement. Here it is:

INSERT INTO cities ("name", "state", "created_at", "updated_at") SELECT DISTINCT city, state, current_timestamp, current_timestamp FROM zones;

Normally, to figure out the proper syntax for a query like the one above, I have to review the PostgreSQL manual. In this case, the time saved was clearly worth the extra effort. The next time you need a migration hopefully you’ll head for the right tool. After a while, you may begin thinking in SQL.

Published at DZone with permission of its author, Nick Jordan. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)