Using UUID/GUID as Primary Key in Rails

A project I'm working on has a difficult requirement to meet. It needs to be able to support a multi-master database model. If you're already familiar with the concept, please forgive the following short introduction. This means that multiple systems need to be able to create records in their local database and sync up to other ones later. Connection among the systems is not guaranteed to be up 100% of the time. A prime example of this type of system is Microsoft's Active Directory.

Some of the key concerns when designing this solution are how to select primary keys that will avoid collision and how to keep relational data in tact when syncing data from one database to another. The method I follow for this is to use a UUID or GUID as the primary key for every table, just like Active Directory. When I initially looked at Rails for this project this was a major concern. ActiveRecord ties an auto-incrementing integer as the primary key for all tables/models and I was worried about my ability to override that. Well with a little searching, reading and a tiny amount of effort I was able to get Rails to do exactly what was needed. The following is how I did it.

The first thing you'll need to do is grab UUIDTools, which is a ruby gem written by Bob Aman. The preferred method for installing is through gems from the command line like so:

gem install uuidtools

Then go into your rails application and create uuid_helper.rb in your app/helpers directory. Make that file look like so:
Note: the following code is from this post and its comments:

require 'rubygems'
require 'uuidtools'
module UUIDHelper
  def before_create()
    self.id = UUID.timestamp_create().to_s
  end
end

All that's left in code is to add a line into your models that will use this functionality. The reason it's this incredibly easy is because of Ruby's Mixins (which should be the topic of another post entirely). So if we have a model named Product it would look like this:

class Product < ActiveRecord::Base
  include UUIDHelper
  # your module code...
end

The last part is the database itself. This will vary depending on what database you use. In my case it's MySQL. Since MySQL doesn't have a UUID field we use VARCHAR(36). I keep the field name the same (id) and everything is ready to go. In the comments of this post (mentioned earlier) Bob suggests the possibility of using a 128 bit integer as an alternative to a 36 character string. At first glance this doesn't seem possible in MySQL. MySQL's BigInt datatype doesn't seem to be able to store the range of values as mentioned in this thread.

So you can see that it's pretty simple to get ActiveRecord using UUIDs for the primary keys. Using Bob's UUIDTools gem we can design a solution that is platform and database independent.

What a great tip! I'm under

What a great tip!

I'm under the impression that your predicament is somewhat taken care of with MySQL 5.0, though I have yet to try out the master/slave stuff in 5.x yet.

Not quite

I'm running MySQL on the primary server, but there are client systems that won't be running it at all. I'm not sure what these will be running, possibly SQLLite or something along those lines. So I have to worry about all the syncing myself without utilizing replication. Also, this mentions that UUID doesn't work with replication but I have to admit to not knowing fully what that means.