Enhancing your Rails app with SQLite: ULID primary keys

When using SQLite as our Ruby on Rails database, you might wonder how to use something like UUIDs or ULIDs as primary keys.

I got this question on Twitter, and it merits its own post to answer:

Any resources about using UUIDs (or ULIDs) as primary keys in Rails? I’ve gotten used to that with PostgreSQL but it seems a little wonky with SQLite3.

— @claytonlz

Let’s start with how we configure Rails to use a custom primary key type. Paweł Urbanek has an excellent blog post laying out the steps for using Postgres’ native UUID data type as the primary key for tables. You should give the entire article a read, but for our purposes the two key details are:

  1. how to set the primary key type in a migration, and
  2. how to configure Rails to use UUIDs as primary keys for generators

In a migration, we simply pass id: :uuid as an option to the create_table method:

create_table :comments, id: :uuid  do |t|
  t.string :content
  t.uuid :user_id
  t.timestamps
end

In order to have all generated migrations set this option automatically, we can configure Rails:

# config/initializers/generators.rb
Rails.application.config.generators do |g|
  g.orm :active_record, primary_key_type: :uuid
end

These are the foundational changes you will need to make to your Rails application to enable custom primary keys. How could we do this in SQLite though?

To be honest, custom primary keys weren’t possible when using SQLite with Rails up to today. In order to support this feature, I opened a pull request to add support for RETURNING non-id columns for SQLite. And, luckily for all of us SQLite lovers, it has been merged! This means that if you use the main Rails branch for your application, you can get access to custom primary keys today. So, let’s dig into how.

Personally, I prefer ULIDs to UUIDs, since they are shorter, easier to select and copy, and sortable. Plus, in SQLite-land Alex Garcia’s sqlite-ulid extension is available as a Ruby gem.

As detailed in a past post on loading extensions, we can extend Rails’ database adapter to support loading extensions. By loading the sqlite-ulid extension, we add the ulid() and ulid_bytes() functions to our SQLite database. We can then use one of these functions for our custom primary keys.

Since the SQLite adapter doesn’t support a ulid data-type, we need to define our custom primary keys somewhat differently. Instead of passing the id: :ulid option to the create_table command, we should instead pass id: false. This will disable Rails’ default primary key mechanism for your database engine. We can then define the t.primary_key macro to set the details of our custom primary key. In our case, using ULIDs, we could define the custom primary key like so:

create_table :posts, force: true, id: false do |t|
  t.primary_key :id, :string, default: -> { "ULID()" }
end

Running this migration will create a table with a ULID primary key:

CREATE TABLE "posts" (
  "id" varchar DEFAULT (ULID()) NOT NULL PRIMARY KEY
)

Now, calling Post.create! will return a model instance like this #<Post id: "01hayj8d41d5e4hx0fdfbvja76">.

Unfortunately, we can’t setup the Rails generators to auto-create migrations like this, since we don’t have a custom ulid data-type. But, a bit of manual effort isn’t a bad thing.

Once we have custom primary keys, we need to ensure that our foreign keys are appropriately matched. In order to ensure our foreign keys are correctly bound to our custom primary keys, we need to tweak our migrations minorly:

create_table :comments, force: true, id: false do |t|
  t.primary_key :id, :string, default: -> { "ULID()" }
  t.belongs_to :post, null: false, foreign_key: true, type: :string
end

Whatever the data-type we use for our primary keys, we need to set that type for our foreign keys. This is the only thing we need to ensure. Using the belongs_to or references method will automatically setup the rest of our foreign key with our custom primary key. A create_table like the one above will produce the following SQL:

CREATE TABLE "comments" (
  "id" varchar DEFAULT (ULID()) NOT NULL PRIMARY KEY,
  "post_id" varchar NOT NULL, CONSTRAINT "fk_rails_2fd19c0db7"
  FOREIGN KEY ("post_id") REFERENCES "posts" ("id")
)

That’s it. That’s everything you need to know to setup custom primary and foreign keys for your Rails application using SQLite as your database engine.

In the future, I will be investigating how to register a custom ulid data-type so that we can simplify this setup even further. So, keep your eyes out for that upcoming post.

In the meantime, I hope you enjoyed this exploration into one of the features unlocked by supporting RETURNING statements with the SQLite adapter.

All posts in this series