What's a UUID?

UUID stands for Universally unique identifier. It's a string that is supposed to be randomly generated with low risks of collision. It looks like 4c4f8f1e-d199-4a3d-a5b5-92dd7465145e.

UUIDs can be used as an alternative to autoincremental IDs for primary keys generation.

When may I need them?

As all tools, they can potentially have dozens of uses. Here are a few common ones:

Privacy: When exposing autoincremental IDs (on URLs, in forms, etc.), you disclose how many records you have on a given table. Example: when a user creates an account and sees they are assigned the user ID 121, they know how few users you have, which can be bad for business.
Security: The more an attacker knows about your application, the more chance they have to break into it. Autoincremental IDs help an attacker guess existing IDs and run attacks on them (bruteforcing, phishing, etc.).
Sharding: Sharding is when your database writes are dispatched across multiple servers (usually to reduce response latency). Using autoincremental IDs means your shards must agree on some kind of rule to preserve primary keys uniqueness across all shards (calling a third-party dedicated ID generation service, distributing ID ranges per shard, etc) which induces complexity. As an alternative, one can use UUIDs.
External requirement: You might simply need to use a library that requires UUIDs (gem friendly_id for instance).

Any downsides to using UUIDs?

Yes. The main one when coming from autoincremental IDs is loosing the order of records creation. With IDs 1, 2, 3, ... you can do .order(id: :desc), with UUIDs this sorting wouldn't have the same meaning.

In this article, I'm talking about UUIDv4 specifically but you can take a look at UUIDv6 to avoid ordering issues.

Who'll generate UUIDs: Rails or PostgreSQL?

In this article, we're focusing on having UUID primary keys so it's better to let PostgreSQL be in charge. You could generate UUIDs in Ruby (require 'securerandom'; SecureRandom.uuid) and feed them to your database but it really wouldn't be practical nor efficient.

Does PG know how to generate UUIDs out of the box?

No, that's why we'll need to enable the PG extension pgcrypto which provides the gen_random_uuid() function.

How to enable pgcrypto

Within an ActiveRecord migration

class EnablePgcrypto < ActiveRecord::Migration[6.1]
  def up
    enable_extension('pgcrypto') unless extensions.include?('pgcrypto')
  end

  def down
    disable_extension('pgcrypto') if extensions.include?('pgcrypto')
  end
end

In case you're curious about the methods available within a migration to manage PG extensions, where's an extract from activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:

def enable_extension(name)
  exec_query("CREATE EXTENSION IF NOT EXISTS \"#{name}\"").tap {
    reload_type_map
  }
end

def disable_extension(name)
  exec_query("DROP EXTENSION IF EXISTS \"#{name}\" CASCADE").tap {
    reload_type_map
  }
end

def extension_available?(name)
  query_value("SELECT true FROM pg_available_extensions WHERE name = #{quote(name)}", "SCHEMA")
end

def extension_enabled?(name)
  query_value("SELECT installed_version IS NOT NULL FROM pg_available_extensions WHERE name = #{quote(name)}", "SCHEMA")
end

def extensions
  exec_query("SELECT extname FROM pg_extension", "SCHEMA").cast_values
end

Wait a minute... #enable_extension already says IF NOT EXISTS, so why still check whether extensions.include?('pgcrypto')?

Great question! I came to this because I ran into a production error. Although everything was fine in localhost, during deployment in production I got the following error: PG::InsufficientPrivilege: ERROR: permission denied to create extension "pgcrypto".
My database user wasn't granted the required privileges... From there I had two choices:

Granting more privileges to the user; it solves this problem but introduces security risks (if ever stolen, this database account might allow an attacker to do more damage)
Enabling the extension manually using a privileged user account.

I chose the latter.

Manually in PostgreSQL interactive terminal (psql)

SSH into your remote server, then:

$ psql -d <production database name> -U <privileged database user> -W
Password:

postgres=# CREATE EXTENSION pgcrypto;

Don't forget to specify the right database name, otherwise you'll loose time not understanding why the extension is successfully created but doesn't seem to work back in your Rails application.

Create a model with a UUID primary key

All you need to do is to give the id: :uuid argument to #create_table in the migration:

class CreateStudents < ActiveRecord::Migration[6.1]
  def change
    create_table :students, id: :uuid do |t|
      t.string :name
      t.integer :grade

      t.timestamps
    end
  end
end

Done.

How about a regular UUID column?

Whitin a migration, you can also add a regular UUID column (not a primary key):

add_column :my_table, :my_column, :uuid, null: false, default: 'gen_random_uuid()'

Beware, however, that contrarily to primary keys, you need to reload after creating it to retrieve its UUID.

# Let's say the User model has a :token column created as we just did previously in the migration.
user = User.create(email: 'whatever@you.like')
user.token # => nil
user.reload
user.token # => "ffcc73b8-e85e-4461-a995-a8e5053fdfe6"

This is because an INSERT returns the id of the record (no matter whether it's an integer or a UUID) so upon record creation, the ActiveRecord object knows its id but doesn't know the value of any other column that is filled by the database until it's reloaded.

Thank you for reading!
Younes SERRAJ

Table of contents

How to have UUID primary keys with PostgreSQL