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:
- Add UUID columns to tables
- Migrate associations
- 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
Top comments (1)
Is uuid optimal from performance perspective?