Enhancing your Rails app with SQLite: Prefixed ULID keys

I have written previously about using custom primary keys for your Rails app when using SQLite. In this post, I want to show how you can use the same sqlite-ulid extension to create prefixed ULIDs. Shout-out to Andy Stewart who suggested this.

In that previous post, we saw that the new feature to add support for custom primary keys to SQLite for Rails applications allowed us to wire up Alex Garcia’s sqlite-ulid extension (via the Ruby gem) to have ULIDs as primary keys.

Andy Stewart pointed out that the sqlite-ulid extension also supports prefixed ULIDs and thus we could use this function to mimic the behavior of Chris Oliver’s prefixed_ids gem.

Here is Andy’s approach:

I started with a Rails 7.1 application.

I installed your activerecord-enhancedsqlite3-adapter gem.

I installed this repo’s sqlite-ulid gem and updated my database config, exactly as described here.

I wrote migrations as outlined above:
create_table :things, id: false do |t|
  t.primary_key :id, :string, default: -> { "ULID_WITH_PREFIX('thing')" }
  t.belongs_to :widget, null: false, foreign_key: true, type: :string
end
create_table :things, id: false do |t|
  t.primary_key :id, :string, default: -> { "ULID_WITH_PREFIX('thing')" }
  t.belongs_to :widget, null: false, foreign_key: true, type: :string
end
All in all, a straightforward and painless process :)

My only previous experience with prefixed IDs was using @excid’s prefixed_ids gem on a Rails app with postgresql. That worked well, though it needs to inject code into your models, and your application code needs to be at least slightly aware of it, e.g. special finders.

Comparing the two approaches, I much prefer having the database, rather than the model, handle ID generation. My application code doesn’t have to know anything about the models’ IDs. It’s much simpler. (And ULIDs seem at least as good as hashids, if not better, for my purposes.)

— source

I agree with Andy’s assessment. I also prefer having the database handle the ID generation. It’s simpler and more performant. And this is a wonderful example of the kind of flexibility that comes when you embrace SQLite, its extension ecosystem, and the new features that Rails is adding to support SQLite (or that are currently only added in the activerecord-enhancedsqlite3-adapter gem).

Thanks Andy for such a great write-up of an excellent approach to using prefixed ULIDs with Rails and SQLite!

Enhancing your Rails app with SQLite: Prefixed ULID keys

All posts in this series #

All posts in this series