Migrate the way.

Yesterday I spent a couple of hours fathoming out how to use migration. I've read several articles and blogs saying that this is the best way to create your database tables in Rails, so thought I'd give it a go.

Firstly, the fact that it's possible to update your database schema, or rollback to a previous version seemed great. You simply type ruby script/generate migration SomeDescription and you have a file created in the db/migrate folder called something like 001_some_description.rb.

Then, next time you want to modify the database you type ruby script/generate migration AnotherDescription and another file called 002_another_description.rb would appear. So far so good.

But what happens if there's a problem in your migration code. For example, I accidentally had a field of type :booolean (with the extra 'o'). Then, when I ran rake migrate, there was a MySQL error and the migration stopped. Some of the migration had succeeded and one of the tables had been created, but the schema_info table which tracks which version is actually in the database still contained the previous version number. It seemed odd that if a migration fails, then it doesn't automatically call the self.down method to restore the database to the working version. Instead, I manually had to go into the MySQL command line, type UPDATE schema_info SET version = 2 (which was the version that failed) and then I could use rake migrate VERSION=1 to undo the partly updated tables. I was then able to correct my typo and re-rake the migration. Also noting that VERSION has to be in upper case which caught me out the first time I tried. I guess I could have manually undone the changes that Rails had made, but since I'd already defined these for the rollback, it seemed easier to use rake.

Another thing which I wasn't sure about was foreign keys. I'm a full time web developer and at work, whenever there are relationships, there are foreign keys. I was surprised to find that Rails doesn't have them! I knew about has_many and belongs_to – but these, to my surprise didn't enforce any data integrity. So I could have any value in one table that didn't exist in the id column of another. I wasn't happy about this. There had to be some way to stop this from happening. After many searches on Google, I found that it's possible to add validation to the model before data is inserted into the database:

class Site < ActiveRecord::Base
   belongs_to :category

   def validate
      errors.add_to_base "Error description" unless Category.exists?(category_id)
   end
end

Which did just what I wanted. Every row that was inserted into the sites table, would check that the category Id existed in categories and fail if it wasn't valid.

However, there's an even better way (as I see it at the moment). RedHill Consulting have created a Rails plugin (http://www.redhillconsulting.com.au/rails_plugins.html) which adds a foreign key to the database when you rake. All you have to do it run these two commands after making sure that SVN is installed on your computer (OSX users go here: http://www.codingmonkeys.de/mbo/):

script/plugin install svn://rubyforge.org//var/svn/redhillonrails/trunk/vendor/plugins/schema_defining

script/plugin install svn://rubyforge.org//var/svn/redhillonrails/trunk/vendor/plugins/foreign_key_migrations

Then, each time you migrate, the foreign keys are added to the database. I've only tried this using MySQL for now but from what I think I read, it should work for others too.

Discuss.

has_one or belongs_to?

One of the things that took a little more understanding than usual was the correct use of the ActiveRecord methods that define the relationships between your objects. I was often unsure whether to use has_one or belongs_to.

I have to write pub quizzes every so often. I had a quickly scaffolded together rails app to help me write them but it lacked all but the most basic features. So, I started a new one. I realised that I needed three tables: quizzes, questions and categories. I wrote the migrations and tried to set up the relationships in the created models. When I tried to test what I’d written in script/console. I could create quizzes and add questions to them but not assign the questions to a category. This baffled me for a while.

After going off and doing something else for a while I came back to the code and the database diagram and realised my error. While I’d correctly said that a question belonged_to a quiz, I’d not done the same with the categories and had thought that a question had a category. Looking again at the database structure I realised that the relationships work like this: The table with the foreign key in it belongs_to the table that that foreign key references. In my case my questions table had a quiz_id and a category_id. Therefore a Question has a belongs_to relationship with both the Quiz and Category models which both in turn have has_many relationships with Question.

I made the changes and it works now. It’s always good when something clicks into place.