How to migrate primary keys from bigint to UUID in Rails

Rails uses bigint as a default primary key type for PostgreSQL databases. This is a solid option, but you may consider using UUID as primary keys. There is and endless debate about pros and cons of each of them, so I'm not going to repeat the arguments here. However, if you've decided to migrate to UUIDs, here is one possible solution.

Let's say we have two tables in our database - users and comments:

create_table :users do |t|
  # ...
end

create_table :comments do |t|
  t.references :user, null: false, foreign_key: true
  # ...
end

A possible way to migrate these tables to UUIDs is:

1. Add UUID columns to tables
2. Migrate associations
3. Drop ID columns, rename UUID to ID and use them as primary keys

This migration process is in the following example, which is pretty self-explanatory.

class MigrateToUuid < ActiveRecord::Migration[5.2]
  def up
    # Add UUID columns
    add_column :users,    :uuid, :uuid, null: false, default: -> { "gen_random_uuid()" }
    add_column :comments, :uuid, :uuid, null: false, default: -> { "gen_random_uuid()" }

    # Add UUID columns for associations
    add_column :comments, :user_uuid, :uuid

    # Populate UUID columns for associations
    execute <<-SQL
      UPDATE comments SET user_uuid = users.uuid
      FROM users WHERE comments.user_id = users.id;
    SQL

    # Change null
    change_column_null :comments, :user_uuid, false    

    # Migrate UUID to ID for associations
    remove_column :comments, :user_id
    rename_column :comments, :user_uuid, :user_id

    # Add indexes for associations
    add_index :comments, :user_id    

    # Add foreign keys
    add_foreign_key :comments, :users    

    # Migrate primary keys from UUIDs to IDs
    remove_column :users,    :id
    remove_column :comments, :id
    rename_column :users,    :uuid, :id
    rename_column :comments, :uuid, :id
    execute "ALTER TABLE users    ADD PRIMARY KEY (id);"
    execute "ALTER TABLE comments ADD PRIMARY KEY (id);"

    # Add indexes for ordering by date
    add_index :users,    :created_at
    add_index :comments, :created_at
  end

  def down
    raise ActiveRecord::IrreversibleMigration
  end

The gen_random_uuid() function is from pgcrypto extension, so make sure it's enabled in your databse before running this migration:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

You can also easily write the down method to make the migration reversible.

---

Original article: How to migrate primary keys from bigint to UUID

Comments

[egalicia]

Is uuid optimal from performance perspective?