Rails 5 Mysql UUID

Question

Found out that rails 5 has a native uuid integration, wanted to try it out but I'm getting this error:

== 20170330041631 EnableUuidExtension: migrating ==============================
-- enable_extension("uuid-ossp")
  -> 0.0000s
== 20170330041631 EnableUuidExtension: migrated (0.0001s) =====================

== 20170331035925 CreateUsers: migrating ======================================
-- create_table(:users, {:id=>:uuid})
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest' at line 1: CREATE TABLE `users` (`id` uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB
/home/zetacu/projects/rails-5-test/db/migrate/20170331035925_create_users.rb:3:in `change'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `load'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `<main>'
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest' at line 1: CREATE TABLE `users` (`id` uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB
/home/zetacu/projects/rails-5-test/db/migrate/20170331035925_create_users.rb:3:in `change'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `load'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `<main>'
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest' at line 1
/home/zetacu/projects/rails-5-test/db/migrate/20170331035925_create_users.rb:3:in `change'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `load'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `<main>'
Tasks: TOP => db:migrate

This are the migrations acoording to the post:

class EnableUuidExtension < ActiveRecord::Migration[5.0]
  def change
    enable_extension 'uuid-ossp'
  end
end


class CreateUsers < ActiveRecord::Migration[5.0]
  def change
    create_table :users, id: :uuid do |t|
      t.string :name
      t.string :username
      t.string :password_digest

      t.timestamps
    end
  end
end

on application.rb:

config.generators do |g|
  g.orm :active_record, primary_key_type: :uuid
end

What I'm missing?, Does Rails-5 has mysql support or has to be manual like in Rails-4?

create_table :users, id: false do |t|
  t.string :uuid, limit: 36, primary: true, null: false
  ...

gem versions:

rails (~> 5.0.2)
mysql2 (>= 0.3.18, < 0.5)

Puree

FWIW, uuid-ossp is a postgresql extension. Therefore your above code only works if using postgresql backend. For mysql you would need a string column like you pointed out in your own answer. – Hinds

Yeah that's why I was asking if Rails 5-MySQL there was a build in way to do it, but I couldn't find one solution. – Puree

Answer 1

My answer is an update of @santosh's answer. I am incorporating all the best practices described here:

• https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/
• http://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/

I am using the simple_uuid gem because it can generate "v1" UUIDs. Ruby's built-in SecureRandom.uuid generates v4. We need v1, because that is what incorporates timestamp as part of the UUID. Read the links above to get a deeper understanding. MySQL's UUID() function generates v1 UUIDs.

app/models/concerns/binary_uuid_pk.rb

module BinaryUuidPk
  extend ActiveSupport::Concern

  included do
    before_validation :set_id, on: :create
    validates :id, presence: true
  end

  def set_id
    uuid_object = SimpleUUID::UUID.new
    uuid_string = ApplicationRecord.rearrange_time_of_uuid( uuid_object.to_guid )
    uuid_binary = ApplicationRecord.id_binary( uuid_string )
    self.id = uuid_binary
  end

  def uuid
    self[:uuid] || (id.present? ? ApplicationRecord.format_uuid_with_hyphens( id.unpack('H*').first ).upcase : nil)
  end


  module ClassMethods
    def format_uuid_with_hyphens( uuid_string_without_hyphens )
      uuid_string_without_hyphens.rjust(32, '0').gsub(/^(.{8})(.{4})(.{4})(.{4})(.{12})$/, '\1-\2-\3-\4-\5')
    end

    def rearrange_time_of_uuid( uuid_string )
      uuid_string_without_hyphens = "#{uuid_string[14, 4]}#{uuid_string[9, 4]}#{uuid_string[0, 8]}#{uuid_string[19, 4]}#{uuid_string[24..-1]}"
      ApplicationRecord.format_uuid_with_hyphens( uuid_string_without_hyphens )
    end

    def id_binary( uuid_string )
      # Alternate way: Array(uuid_string.downcase.gsub(/[^a-f0-9]/, '')).pack('H*')
      SimpleUUID::UUID.new( uuid_string ).to_s
    end

    def id_str( uuid_binary_string )
      SimpleUUID::UUID.new( uuid_binary_string ).to_guid
    end

    # Support both binary and text as IDs
    def find( *ids )
      ids = [ids] unless ids.is_a?( Array )
      ids = ids.flatten

      array_binary_ids = ids.each_with_object( [] ) do |id, array|
        case id
          when Integer
            raise TypeError, 'Expecting only 36 character UUID strings as primary keys'
          else
            array <<  SimpleUUID::UUID.new( id ).to_s

        end
      end

      super( array_binary_ids )
    end
  end
end

app/models/application_record.rb

## ApplicationRecord (new parent of all models in Rails 5)
class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  include BinaryUuidPk
end

Now, all models will support optimized UUID primary keys.

Sample Migration

class CreateUserProfiles < ActiveRecord::Migration[5.0]
  def change
    create_table :user_profiles, id: false do |t|
      t.binary :id, limit: 16, primary_key: true, null: false
      t.virtual :uuid, type: :string, limit: 36, as: "insert( insert( insert( insert( hex(id),9,0,'-' ), 14,0,'-' ), 19,0,'-' ), 24,0,'-' )"
      t.index :uuid, unique: true

      t.string :name, null: false
      t.string :gender, null: false
      t.date :date_of_birth
      t.timestamps null: false
    end

    execute <<-SQL
      CREATE TRIGGER before_insert_user_profiles
        BEFORE INSERT ON user_profiles
        FOR EACH ROW
        BEGIN
          IF new.id IS NULL THEN
            SET new.id = UUID_TO_BIN(uuid(), 1);
          END IF;
        END
    SQL
  end
end

Add UUID_TO_BIN() function to the MySQL DB:

DELIMITER //
CREATE FUNCTION UUID_TO_BIN(string_uuid BINARY(36), swap_flag INT)
        RETURNS BINARY(16)
        LANGUAGE SQL  DETERMINISTIC  CONTAINS SQL  SQL SECURITY INVOKER
      RETURN
        UNHEX(CONCAT(
            SUBSTR(string_uuid, 15, 4),
            SUBSTR(string_uuid, 10, 4),
            SUBSTR(string_uuid,  1, 8),
            SUBSTR(string_uuid, 20, 4),
            SUBSTR(string_uuid, 25) ));
//
DELIMITER ;

The above function is built-in to MySQL 8.0 and above. At the time of writing, 8.0 is not yet GA. So, I am adding the function for now. But I have kept the function signature the same as what is there in MySQL 8.0 . So, when we move to 8.0, all our migrations and triggers would still work.

Villiform

This is an oldie but wondering if you ever found an elegant solution for handling association tables that use bin uuids, e.g. a UserLocation table where each row has a user_id and a location_id column, each a bin uuid. I'm getting "invalid byte sequence in UTF-8" errors. – Auriga

Answer 2

Din't found any documentation about mysql/rails-5 uuid integration, I ended up doing it like this:

## Migration
class CreateUsers < ActiveRecord::Migration[5.0]
  def change
    create_table :users, id: false do |t|
      t.string :id, limit: 36, primary_key: true, null: false 
      t.string :name
      t.string :username
      t.string :password_digest
      t.timestamps
    end
  end
end


#user model
class User < ApplicationRecord
  before_create :set_uuid

  def set_uuid
    self.id = SecureRandom.uuid
  end
end

It works and all but was hopping to use a "magic rails" solution to handle the uuid and relations automatically.

Puree

Answer 3

My answer is UPDATE of @zetacu answer. It work perfectly for MySQL in rails 5.0.2

  ## Model
   class Tip < ActiveRecord::Base
     before_validation :set_uuid, on: :create
     validates :id, presence: true

     def set_uuid
       self.id = SecureRandom.uuid
     end

   end

    ## Migration
    class CreateTip < ActiveRecord::Migration[5.0]
      def change
        create_table :tips, id: false, force: true do |t|
          t.string :id, :limit => 36, :primary_key => true
          t.string :title, :null => false, :default => ""
          t.text :description

          t.timestamps
        end
      end
    end

Radbun

Answer 4

I suggest using https://github.com/nedap/mysql-binuuid-rails

class AddUuidToUserProfiles < ActiveRecord::Migration[6.0]
  def change
    add_column :user_profiles, :uuid, :binary, limit: 16, null: false
    reversible do |dir|
      dir.up do
        execute <<~SQL
          CREATE TRIGGER before_insert_user_profiles
            BEFORE INSERT ON user_profiles
            FOR EACH ROW
            BEGIN
              IF new.uuid IS NULL THEN
                SET new.uuid = UUID_TO_BIN(UUID(), 1);
              END IF;
            END
        SQL
        execute "UPDATE user_profiles SET uuid = UUID_TO_BIN(UUID());"
      end
      dir.down do
        execute <<~SQL
          DROP TRIGGER before_insert_user_profiles;
        SQL
      end
    end
  end
end

The trigger is strictly optional; if you want to use ActiveRecord callbacks to generate the UUID on creation that's viable too.

  attribute :uuid, MySQLBinUUID::Type.new

At the top of your model. Something like

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

In the model or in ApplicationRecord will do for UUID generation if you don't go the trigger route.

This is assuming MySQL 8.0+, and note for my use case I am not using the UUID as a primary key.

Epner