Add UUID Primary Key in Rails and MySQL

Question

I have a table without ID. Now that I know it's a wrong design, I want to add a primary key ID to that table. Here's the script:

def up
  add_column :players, :id, :primary_key, first: true

  change_table :players do |t|
    t.change :id, :uuid
  end
end

def down
  remove_column :players, :id
end

The thing is that script generate integer value for UUID column. How to properly add primary key UUID to the table?

Edit: It's already in production. I can add ID column, but it will be filled with integer, not UUID as I expected.

You should work towards fixing your design if you say it's a wrong design! — vee, Apr 25 '15 at 19:39
possible duplicate of [Add Id column in a migration](http://stackoverflow.com/questions/12548746/add-id-column-in-a-migration) — max, Apr 25 '15 at 19:40
It's already in production. I can add ID column, but it will be filled with integer, not UUID as I expected. — shankshera, Apr 25 '15 at 19:43

score 1 · Answer 1 · answered Apr 25 '15 at 19:55

In the database environment, a table with a column id as primary key would be an unique identifier of each row of the table. It's usually an integer value with autoincrement (but not necessary) and usually a 32bit or 64bit integer is used. There are no 2 rows in the table with the same id.

In Rails, UUID is a global unique identifier. It can be used to identify a user, a session, anything. And the id is for all categories the same. It's created with a random generator, so to reduce the chance of having the same UUID for 2 different creations it's a 128bit value.

There are ways to use the UUID as primary key, but it's likely a bad design idea since the database engine has to lookup rows based on it and since it's so big the work to find rows might be harder. Often the DB will have a translation of UUIDs to an internal table row id.

I'm guessing you need both a primary key and a separate UUID column, but that depends on the context.

score 1 · Answer 2 · answered Aug 01 '19 at 03:02

It's totally doable, and in many cases it is a good design choice. Creating a globally unique ID makes things much easier when sharding, with multi-master writes, or when needing to move to any multi-database system. The downside, of course, is that it takes up twice the space of a bigint, and it's more complex than auto-increment.

First, NEVER STORE UUIDs AS VARCHAR. OK, now that's out of the way, here's some code:

This gem uses Mysql 8.0+'s UUID_TO_BIN and BIN_TO_UUID to store binary and retrieve UUID characters. https://github.com/nedap/mysql-binuuid-rails

gem 'mysql-binuuid-rails'

In the model, use the Attributes API from Rails 5+. Be sure to remember to set all foreign keys to decode the UUID, i.e. attribute :user_id, MySQLBinUUID::Type.new

class Account < ApplicationRecord
  attribute :id, MySQLBinUUID::Type.new
  before_create { self.id = ApplicationRecord.generate_uuid }
end

This migration specifies not to create the default primary key, and then manually specifies the primary key. The trigger is nice because if you run any queries that create records without ActiveRecord, it'll still generate and populate the primary keys correctly.

class CreateAccounts < ActiveRecord::Migration[6.0]
  def change
    create_table :accounts, id: false do |t|
      t.binary :id, limit: 16, primary_key: true
      t.string :email

      t.timestamps
    end


    reversible do |dir|
      dir.up do
        execute <<~SQL
          CREATE TRIGGER before_insert_set_account_uuid
            BEFORE INSERT ON accounts
            FOR EACH ROW
            BEGIN
              IF new.id IS NULL THEN
                SET new.id = UUID_TO_BIN(UUID(), 1);
              END IF;
            END
        SQL
        execute "UPDATE accounts SET id = UUID_TO_BIN(UUID());"
      end
      dir.down do
        execute <<~SQL
          DROP TRIGGER before_insert_set_account_uuid;
        SQL
      end
    end
  end
end

This is using MySQL's method to create a UUID. Note that this means two round-trips to the database, one to fetch the new UUID and one to save the record. It may be better to leverage UUIDTools etc. to generate it locally.

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  def self.generate_uuid
    ActiveRecord::Base.connection.execute("select UUID();").first[0]
  end
end

Add UUID Primary Key in Rails and MySQL

2 Answers2