Legacy Databases with ActiveRecord

So Close, But Yet, So Far

2008-03-17

I loved working with Ruby on Rails, I still do. I bought books, I read blogs, and I made a fair few sites (both personal and for clients). I thought Ruby on Rails was the be-all, end-all for web development. I was very wrong.

Ruby on Rails is fantastic. Especially when you create a new application and design and create that web application with Rails, and its way, in mind. However, if, like myself, you have to work with the database from an application that originated nearly 20 years ago, you may hit some snags. Just as I did.

The design of a database to be used with Ruby on Rails (specifically ActiveRecord) must adhere to some very stringent "best" practices. These are not always the absolute, best practices in design but, they provide some fairly standard basic practices. One such example, though fairly advanced comes with polymorphic associations. In defining the type in one such association, ActiveRecord uses the class name of the type instead of a secondary table listing all possible types. This breaks any possible use of a foreign-key constraint.

Some basics in Ruby on Rails database design, which is often generated without any specification from the developer (when using model generators), include: tables are named the pluralized class name (e.g. User becomes users), every table has an "id" field that is an auto-incremented integer, and a foreign-key is tablename_id.

What must we do if we are using an existing database, and it does not comply to these standards? I'll go through each instance of non-standard database design from the existing database and show how ActiveRecord did or did not handle the problem.

What to do if a Table is not Named after a Class? Easy. ActiveRecord provides a simple method set_table_name that allows you to do just that. If the tables are named after a class but are not pluralized, you can simply set the environment to not use the pluralized versions.

What to do if a Table does not have Primary Key named 'id'? Easy, too. Simply use set_primary_key to set the primary key. This does not, however, provide for a case in which a table does not have a primary key (or otherwise usable unique key) in any way, shape, or form. The simple answer is to add an 'id' field that is not used by the legacy application. This is difficult or impossible to do if you are not the person in control of the database. It is also difficult if, like me, the database exists on hundreds of servers distributed around the country. Making any change in this case is not a simple undertaking.

What to do if the Primary Key is not an auto-incremented integer field? Here, we find the most difficult problem of all. ActiveRecord can ignore the fact that you don't have auto-incremented values but, if your key is not an integer ActiveRecord (as of this posting) hits the proverbial wall. In the case of my legacy database, the primary keys were text fields, five digits in length. This was a carry-over from an even older system in which no relational database was involved.

My only options here became extremely complicated and were only possible because I was using Postgresql (editable views). That was the end of the road for this project and Ruby on Rails. Thankfully, a helpful suggestion led me to use Merb for my framework and DataMapper for my database ORM. Watch for my next post in which I discuss the solutions to these problems, and a few nice perks, found in Merb and DataMapper.