UUID primary keys for ActiveRecord

I suspect that many people have been working successfully with uuid, guid, or any other db-specific unique identifier data type for years now. I know this has been the case in the SQL Server adapter. So finding these implementations for hints would be a great start. Here are some links to my work in the SQL Server adapter.

Wiki Article

http://git.io/Ttb6mg

Implementation & Test Details

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver/database_statements.rb#L186-192

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/test/schema/sqlserver_specific_schema.rb#L80-89

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/test/cases/specific_schema_test_sqlserver.rb#L161

So there are a few things going on here. A summary:

  1. SQL Server has a notion of unique identifiers where one is random and the other sequential. One is faster, the other more secure, etc. Which is better for a column primary key is debatable I think. Especially since sequential is not even sequential per table, but per system.

  2. It is really easy to setup a schema where the database champions assigning a new unique identifier. However, getting that key back on create/insert is a bit tricky since most low level connection modes do not treat unique identifier columns as real primary keys. Hence if you let the db generate the value, an insert would have to be followed by another crafted select. The SQL Server supers up to ActiveRecord’s #last_inserted_id and falls back to SCOPE_IDENTITY() which is fine for normal primary keys, but I do not know of a way to do the same (even assuming I reflected on the table having unique identifier or not) to get the random string that is a uuid column. A show stopper in itself.

  3. The challenges of #2 are the reason I tell people in the wiki article that it is much easier if you assign the unique identifier in a simple call back.

For these reasons, I am not seeing a way that ActiveRecord can champion a datatype like this at a low level. It seems to me to have always supported unique identifiers in simple patterns and that may be good enough. Thoughts?

  • Ken

ActiveRecord should be able to take the same approach that Sequel
uses, returning all of the inserted column values when inserting using
OUTPUT:

  DB.create_table(:ts) do
    String :id, :primary_key=>true, :default=>Sequel.function(:newid)
    String :name
  end
  class T < Sequel::Model; end
  T.create(:name=>'foo')
  # SQL: INSERT INTO [TS] ([NAME]) OUTPUT [INSERTED].* VALUES (N'foo')
  # => #<T @values={:id=>"41BE3F26-8DE4-46FA-9A92-582CA33835B2", :name=>"foo"}>

Jeremy

Jeremy,

That is just beautiful! In your example, that is just a string column with a default, not a real [uniqueidentifier] data type. But I suspect that is moot since this should work with any datatype.

So the adapter could support something like that. If ActiveRecord did move forward with it, we might need something like @uuid ivar/accessor to the Column object... perhaps not tho. Maybe the SQL Server adapter could just use the OUTPUT method pointed to the primary key column no matter what and it should all work now and in the future. I may look into that one day.

- Ken

The only reason to ask the database for a newly-inserted record’s ID, for sequential numeric IDs, is because the database maintains the sequence and only the database can get the next number in the sequence.

But when using UUIDs, there is no reason to ask the database for a newly-inserted record’s ID. Instead, the application can generate these UUIDs itself. This is, for example, what Hibernate and Mongoid do.

But when using UUIDs, there is no reason to ask the database for a newly-inserted record's ID.

There is if ActiveRecord uses it for a primary key.

Instead, the application can generate these UUIDs itself. This is, for example, what Hibernate and Mongoid do.

This is what my wiki article describes and how technically ActiveRecord has supported this notion for many years. But if there is a more formal notion of supporting unique identifiers as primary keys, then we have two options to engineer a solution. Either pre assigned callbacks that allow the app to generate them, not desired as it would require some extra config to hint that this primary key is a unique identifier. The other option is to make it transparent and push the concern down to the adapters to return that value.

- Ken

But ActiveRecord will have generated it, and given it to the DB to
insert - the DB won't have generated it; so there's no need to ask for
it back when AR already has it.

But when using UUIDs, there is no reason to ask the database for a newly-inserted record’s ID.

There is if ActiveRecord uses it for a primary key.

But ActiveRecord will have generated it, and given it to the DB to
insert - the DB won’t have generated it; so there’s no need to ask for
it back when AR already has it.

Yea, if you go that route in how ActiveRecord implements it. Like I said, that is how I tell people to do it in the wiki article or the SQL Server adapter.

  • Ken

Aaron, as you mention it’s pretty easy easy to do this already, but it’s off the beaten path. On 3.2.6 I’ve included the postgres_ext gem to handle UUID types in Postgres correctly. Migrations/models are also easy: https://gist.github.com/3344006

So far everything works A-OK. It just seems as though it would be nicer with some syntax in the migration. Perhaps create_table(:foos, :id => :uuid)?

Chris

Aaron, as you mention it's pretty easy easy to do this already, but it's
off the beaten path. On 3.2.6 I've included the postgres_ext gem to handle
UUID types in Postgres correctly. Migrations/models are also
easy: https://gist.github.com/3344006

I'd rather we do creation like this:

  CREATE TABLE x (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    y integer
  );

Then a model with uuid primary keys should act the same as one using
auto incrementing keys. IIRC, there is code in AR that depends on the
database generating the record's ID. If the DB generated the uuid,
everything should Just Work(tm) (I think).

So far everything works A-OK. It just seems as though it would be nicer
with some syntax in the migration. Perhaps `create_table(:foos, :id =>
:uuid)`?

Can we just modify the migration code to use the above form of
CREATE TABLE?

Somebody wrote a patch for Postgres UUID support in Rails. It was accepted and is available in edge.

https://github.com/rails/rails/pull/6713