Rails 5 Mysql UUID
It turned out that rails 5 has uuid integration built in , wanted to try it but I get 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 is the mail migration:
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 am I missing? Does Rails-5 have mysql support or should it be manual like 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)
source to share
Didn't find any documentation on 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
He works and that's it, but he hopped to use the magic rails solution to automatically handle uuids and relationships.
source to share
My answer is UPDATE to @ zetacu's answer. It works fine for MySQL on 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
source to share
My answer is this answer to @ santosh's answer. I use all the best practices outlined here:
- https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/
- http://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/
I use gem simple_uuid
because it can generate "v1" U1ID files. Ruby built-in SecureRandom.uuid
generates v4. We need v1 because that's what includes the timestamp as part of the UUID. Read the links above to get a deeper understanding. MySQL UUID()
generates v1 UUID.
app / models / problems / 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
application / models / application_record.rb
## ApplicationRecord (new parent of all models in Rails 5)
class ApplicationRecord < ActiveRecord::Base
self.abstract_class = true
include BinaryUuidPk
end
All models will now support optimized UUID primary keys.
Migration example
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 function UUID_TO_BIN()
to MySQL database :
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 into MySQL 8.0 and above. At the time of this writing, 8.0 is not yet GA. So now I add the function. But I kept the function signature the same as in MySQL 8.0. So when we get to 8.0 all our migrations and triggers will work.
source to share
UID support was introduced in MySQL 8.0.
https://mysqlserverteam.com/mysql-8-0-uuid-support/
Which version did you use?
source to share
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 UUIDs on creation, which is also viable.
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 the ApplicationRecord will do to generate the UUID if you don't follow the trigger route.
This assumes MySQL 8.0+, and note that for my use case, I am not using the UUID as the primary key.
source to share