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)

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.