Tech-Angels

Rails migrations are great. They allow to migrate and rollback, and maintain the DB in a consistent state. Thanks to Rails DSL (+ some plugins like rails_on_pg), they are even easy to read and write. Fine.

Enters our beloved friend language SQL. Whilst views (or functions, or whatever) creation is made easy, updating existing code can become a real nightmare, especially with long and complex queries.

Let’s illustrate our problem :

In a rails app, I’d like to create a view for confirmed users. Let’s leave rails_on_pg plugin for now, but the problem would be exactly the same. (Note: examples bellow are based on rails 2.3, they would be a little different in rails 3, but the problem is still there).

script/generate migration create_confirmed_user_view

In the migration file, you would add your code as :

Right, now your run your migration process :

rake db:migrate

and everything goes fine.

Later, you realize that the view was incorrect, you should have filtered also on the disabled boolean column. Now the migration will be something more :

Here comes the problem. You have to copy / paste your previous definition in the migration, each time you want to update a view or a function.

In this example, our view is 2 lines only. With a big fancy function of 20 lines, you won’t be able to notice differences easily (or at all).

Enters Git

To solve our problem, we can create a sql folder in our project tree, and put all functions and complex queries in there.

Now we can write our first implementation of the confirmed_users view :

Add the file to git :

git add sql/confirmed_users_view.sql

And commit it :

git ci

The file has been “saved” in git, at least in its original version. We can easily use git to manage the history of the file. Do your changes, and commit the file again (you know, git add, git ci).

Let’s spice up our migration now. First, grab the last commit ids from git :

git log --oneline -n 2

2d308a6 Update view confirmed users
1596b6d Create view confirmed users

We can use these 2 ids in our migration :

Migration files will be certainly harder to read, but you get a valuable tool with git diff :

git diff -p 2d308a6

[...]
CREATE OR REPLACE VIEW confirmed_user AS
-  SELECT * FROM users WHERE confirmed_at IS NOT NULL;
+  SELECT * FROM users WHERE confirmed_at IS NOT NULL
+  AND disabled IS FALSE;

Of course, this implies you have Git installed on your production servers, but why won’t you ? Especially if you’re using capistrano.