Skip to content

Using PostgreSQL UUIDs as primary keys with Doorkeeper

Wesley Beary edited this page May 1, 2023 · 10 revisions

PostgreSQL has a native datatype for UUIDs and Rails 4.0+ has built-in support for using them as the primary key. You can use UUIDs as the primary key on Doorkeeper tables in a few simple steps.

Enable the extension

Before using UUIDs in PostgreSQL, you must first enable the extension. If you're already using UUIDs, you've already done this step. To enable the extension, generate a migration:

rails g migration enable_pgcrypto_extension # enable_uuid_extension

That should generate a migration that looks like this:

class EnableUuidExtension < ActiveRecord::Migration
  def change
    enable_extension 'pgcrypto'
  end
end

After you run the migration, you'll see the following addition at the top of your db/schema.rb file:

   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
+  enable_extension "pgcrypto"

Edit the Doorkeeper migration

Once the extension is enabled, follow the Doorkeeper set up guide and generate the migration. Before running the migration, edit it in the following ways:

  1. Add id: :uuid to the end of all create_table calls. So create_table :oauth_applications becomes create_table :oauth_applications, id: :uuid.
  2. Update the foreign keys to be of type UUID. So t.integer :application_id, null: false becomes t.uuid :application_id, null: false.

Here's the diff:

--- create_doorkeeper_tables.rb
+++ create_doorkeeper_tables.rb
@@ -1,6 +1,6 @@
 class CreateDoorkeeperTables < ActiveRecord::Migration
   def change
-    create_table :oauth_applications do |t|
+    create_table :oauth_applications, id: :uuid do |t|
       t.string  :name,         null: false
       t.string  :uid,          null: false
       t.string  :secret,       null: false
@@ -11,9 +11,9 @@
 
     add_index :oauth_applications, :uid, unique: true
 
-    create_table :oauth_access_grants do |t|
-      t.references  :resource_owner, null: false
-      t.references  :application,    null: false
+    create_table :oauth_access_grants, id: :uuid do |t|
+      t.references  :resource_owner, null: false, type: :uuid
+      t.references  :application,    null: false, type: :uuid
       t.string   :token,             null: false
       t.integer  :expires_in,        null: false
       t.text     :redirect_uri,      null: false
@@ -24,9 +24,9 @@
 
     add_index :oauth_access_grants, :token, unique: true
 
-    create_table :oauth_access_tokens do |t|
-      t.references  :resource_owner
-      t.references  :application
+    create_table :oauth_access_tokens, id: :uuid do |t|
+      t.references  :resource_owner, type: :uuid
+      t.references  :application,    type: :uuid
       t.string   :token,             null: false
       t.string   :refresh_token
       t.integer  :expires_in

Finished

Run the migration — and that's it! Now your Doorkeeper models will be using nice UUIDs as their primary key. Note that in JSON responses, this will return a string instead of an integer.

Clone this wiki locally