Abdullah Barrak
May 24, 2019

Using UUIDs as Primary Key for Active Record Models

Using universally unique identifiers (UUIDs) for exposed resource identifiers is more secure, and convenient for database distribution. It is relatively simple to configure Active Record to generate UUID primary keys in migrations.

Setup

Here are the steps:

  1. Setup the default generation inside config/application.rb:

     # config/application.rb

 module SampleApp
   class Application < Rails::Application
     ...
     # Change the primary key
     # default type to UUIDs.
     config.generators do |g|
       g.orm :active_record,
             primary_key_type: :uuid
     end
   end
 end

  2. Enable the database support:

  • PostgresSQL:

    Enable uuid-ossp extension for random :uuid generation at the DB level.

     # db/migrations/xxxxx_enable_uuid_ossp_extension.rb.rb

 class EnableUuidOsspExtension <
       ActiveRecord::Migration[5.2]
   def change
     enable_extension 'uuid-ossp' unless extension_enabled?("uuid-ossp")
   end
 end

    For new Postgres versions ( >= 9.4), pgcrypto extension can be used alternatively.

  • SQLite:

    As UUID is not naively available in SQLite.

    A workaround is to utilize generic varchar or blob(16) columns instead. Some people have reported they needed to load the adapter file in order for it to work.

  • SQL Server:

    Has :uuid native support (through uniqueidentifier column type) with the generator.


Now newly generated tables will use :uuid by default for primary keys.

# db/migrations/xxxxx_create_customers.rb

class CreateCustomers <
      ActiveRecord::Migration[5.2]
 def change
   create_table :customers,
                id: :uuid do |t|
     t.string :full_name,
              null: false, index: true
     t.string :email, index: true

     t.uuid :ceeated_by, null: false
     t.uuid :updated_by, null: true

     t.timestamps
   end
   add_foreign_key :customers, :users,
                   column: :created_by
   add_foreign_key :customers, :users,
                   column: :updated_by
 end
end

The snippet above shows :uuid type usage for other non-primary key columns too.

In case you don’t require UUID key type, it’s possible to get the integer or bigint types back again:

create_table :cities,
             id: :integer do |t|
  t.string :name
  t.float :population
end

Ordering Results

A drawback of querying UUID-based tables is that ordering is not inferred as with the sequential keys. We have to set it up using default ordering scope, easily:

# app/models/customer.rb

class Customer < ApplicationRecord
 ...
 default_scope -> {
   order(created_at: :asc)
 }
end

Ensure that indices on created_at columns already added for boosted performance.

# db/migrations/xxxxx_add_created_at_indices.rb

class AddCreatedAtIndices < ActiveRecord::Migration[5.2]
 def change
   add_index :customers, :created_at
   add_index :surveys, :created_at
   add_index :more_info, :created_at
 end
end

Keep in mind the additional storage cost of :uuid keys compared to sequential ones, which requires balancing the trade-offs when designing your data models.

Comments