DEV Community

Cover image for UUID’s in Rails + SQLite shouldn’t be this hard (so I built a gem)
bart-oz
bart-oz

Posted on

UUID’s in Rails + SQLite shouldn’t be this hard (so I built a gem)

#ruby #rails #sqlite #programming

tl;dr — If you just want UUIDs or ULIDs working in your Rails + SQLite app:

# Gemfile
gem "sqlite_crypto"
Enter fullscreen mode Exit fullscreen mode 
# migration
create_table :users, id: :uuid do |t|
  t.string :email
  t.timestamps
end
Enter fullscreen mode Exit fullscreen mode

That's it. Foreign keys auto-detect, schema.rb stays clean, everything just works. Feel free to try it, it’s MIT licensed.

→ GitHub | RubyGems

The Problem I Hit with using UUID’s with SQLite

I was building a Rails 8 app with SQLite—embracing the "No PaaS Required" philosophy that DHH articulated in the Rails 8 release. SQLite as a production database finally felt real: WAL mode (Write-Ahead Logging) by default, improved busy handlers, the Solid Stack proving it at scale.

Then I needed UUID primary keys.

In PostgreSQL, this is a one-liner: enable_extension 'pgcrypto' and you're done. In SQLite? I fell into a rabbit hole.

What went wrong

First of all my schema.rb broke immediately. Rails dumped something like this:

create_table "users", id: false, force: :cascade do |t|
  t.string "id", limit: 36, null: false
  # ...
end
Enter fullscreen mode Exit fullscreen mode

Not id: :uuid. A verbose, non-reloadable mess.

Foreign keys became a nightmare. When I added a posts table with t.references :user, Rails created an INTEGER column. My UUID primary key and integer foreign key couldn't join. Every single reference needed manual type: :string, limit: 36 configuration.

User.first returned random records.* UUID v4 is randomly ordered, so "first" meant alphabetically first, not chronologically first. I learned about implicit_order_column the hard way.

What I had to implement manually

Before I built the gem, here's what my project required to make UUIDs work:

1. Verbose migration syntax with id: false:

create_table :users, id: false do |t|
  t.string :id, limit: 36, null: false, primary_key: true
  t.string :email
  t.timestamps
end
Enter fullscreen mode Exit fullscreen mode

Instead of the clean id: :uuid I wanted.

2. Manual type specification on every foreign key:

create_table :api_keys, id: false do |t|
  t.string :id, limit: 36, null: false, primary_key: true
  t.references :user, null: false, foreign_key: true, type: :string, limit: 36
  # ...
end
Enter fullscreen mode Exit fullscreen mode

Forget type: :string, limit: 36 once? Broken joins. That might lead to silent failures and hours of debugging.

3. Custom UUID generation in ApplicationRecord:

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  before_create :generate_uuid_id

  private

  def generate_uuid_id
    self.id ||= SecureRandom.uuid
  end
end
Enter fullscreen mode Exit fullscreen mode

4. Special handling for Active Storage:

Active Storage tables don't inherit from ApplicationRecord, so they needed their own initializer:

# config/initializers/active_storage_uuid.rb
Rails.application.config.to_prepare do
  ActiveStorage::Blob.class_eval do
    before_create { self.id ||= SecureRandom.uuid }
  end
  # ... repeat for Attachment, VariantRecord
end
Enter fullscreen mode Exit fullscreen mode

5. The schema format tradeoff:

Many tutorials suggested switching to structure.sql:

# config/application.rb
config.active_record.schema_format = :sql
Enter fullscreen mode Exit fullscreen mode

This "solved" the schema.rb dump problem but introduced others: SQL format, which is database-specific, harder to diff in PRs, and doesn't play as nicely with some deployment pipelines. I wanted to keep :ruby format.

All of this boilerplate for something that PostgreSQL handles with a single enable_extension 'pgcrypto'.

What I Tried

I searched RubyGems for existing solutions. Here's what I found:

  • One popular gem hadn't been updated since 2015 — ten years of Rails versions unsupported
  • Several options required manual id: false configuration and didn't handle foreign keys
  • One promising gem was still in alpha and required external SQLite extension management

The common pattern: solutions existed, but none provided the complete package. I wanted something that felt as natural as PostgreSQL's UUID support—install the gem, use id: :uuid, and forget about it.

But why UUIDs/ULIDs Matter (A Quick Primer)

If you're new to non-integer IDs, here's why they're worth considering:

INTEGER:  1, 2, 3, ... (sequential, guessable)
UUID:     550e8400-e29b-41d4-a716-446655440000 (random, 36 chars)
ULID:     01ARZ3NDEKTSV4RRFFQ69G5FAV (time-sortable, 26 chars)
Enter fullscreen mode Exit fullscreen mode

Security: Sequential IDs leak information. If your user ID is 47, attackers know there are ~47 users and can enumerate /users/1 through /users/47. UUIDs are effectively unguessable.

Distributed systems: Integer IDs require a central authority to prevent collisions. UUIDs can be generated anywhere—your server, a client device, an offline app—without coordination.

ULID advantage: Unlike random UUIDs, ULIDs encode creation time. User.first returns the oldest record, not a random one. You get security benefits while preserving intuitive ordering.

The tradeoff: UUIDs use 36 bytes vs 8 bytes for integers. Queries are ~2-5% slower from my performance testing. For most applications, this is negligible. For write-heavy analytics tables processing millions of rows per hour, you might want to stick with standard incremented ID’s.

Performance Reality Check

I ran benchmarks comparing Integer, UUID, and ULID primary keys. Here's what I found with 10,000 records:

Operation Integer UUID ULID
Insert 10k records baseline +3-5% +5-8%
Find by ID (1k lookups) baseline +2-4% +3-5%
Where queries baseline ~same ~same
Storage per 1M records ~8 MB ~34 MB ~25 MB

My Solution: sqlite_crypto

I built sqlite_crypto to make UUID/ULID primary keys feel native in Rails + SQLite.

Installation 

# Gemfile
gem "sqlite_crypto"
Enter fullscreen mode Exit fullscreen mode 
bundle install
Enter fullscreen mode Exit fullscreen mode

No generators. No configuration files. No initializers.

UUID Primary Keys usage 

class CreateUsers < ActiveRecord::Migration[8.0]
  def change
    create_table :users, id: :uuid do |t|
      t.string :email
      t.string :name
      t.timestamps
    end
  end
end
Enter fullscreen mode Exit fullscreen mode

ULID Primary Keys (Time-Sortable) usage 

class CreatePosts < ActiveRecord::Migration[8.0]
  def change
    create_table :posts, id: :ulid do |t|
      t.string :title
      t.text :content
      t.timestamps
    end
  end
end
Enter fullscreen mode Exit fullscreen mode

Automatic Foreign Key Detection

This is the feature I'm most proud of. The gem inspects the referenced table's primary key and creates matching foreign keys automatically:

# Users has UUID primary key
create_table :users, id: :uuid do |t|
  t.string :name
end

# Posts automatically gets varchar(36) user_id — no manual type: needed!
create_table :posts do |t|
  t.references :user  # Just works™
  t.string :title
end
Enter fullscreen mode Exit fullscreen mode

Works with ULID too:

create_table :categories, id: :ulid do |t|
  t.string :name
end

create_table :articles do |t|
  t.references :category  # Creates varchar(26) foreign key
  t.string :title
end
Enter fullscreen mode Exit fullscreen mode

For non-standard table names, use :to_table:

t.references :author, to_table: :users  # Looks up users table's type
Enter fullscreen mode Exit fullscreen mode

Clean Schema Output

Your db/schema.rb stays readable:

create_table "users", id: :uuid, force: :cascade do |t|
  t.string "email"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end
Enter fullscreen mode Exit fullscreen mode

No more id: false with verbose column definitions.

Model Extensions for Auto-Generation

Need to generate UUIDs/ULIDs for non-primary-key columns? Sure you can!

class User < ApplicationRecord
  generates_uuid :api_token
  generates_ulid :tracking_id, unique: true
end
Enter fullscreen mode Exit fullscreen mode 
user = User.create!(email: "dev@example.com")
user.api_token    #=> "550e8400-e29b-41d4-a716-446655440000"
user.tracking_id  #=> "01ARZ3NDEKTSV4RRFFQ69G5FAV"
Enter fullscreen mode Exit fullscreen mode

If you’re curious I prepared a spec especially for checking each of the ID types performance. Just run benchmarks on your own hardware:

bundle exec rspec --tag performance
Enter fullscreen mode Exit fullscreen mode

What I Learned Building This

1. Rails' type system is more extensible than I expected

Registering custom types is straightforward:

ActiveRecord::Type.register(:uuid, SqliteCrypto::Type::Uuid, adapter: :sqlite3)
Enter fullscreen mode Exit fullscreen mode

The hard part was getting the schema dumper to output clean id: :uuid instead of verbose column definitions. That required prepending modules at exactly the right point in Rails' initialization sequence.

2. Test against real Rails versions, not just your development version

My CI matrix tests against Ruby 3.1-3.4 and Rails 7.1-8.1. I found edge cases that only appeared in specific combinations—Rails 8.0's schema dumper behaved differently than 7.2's in subtle ways.

Try It 

# Gemfile
gem "sqlite_crypto"
Enter fullscreen mode Exit fullscreen mode

If you hit issues, open a GitHub issue. If it helps your project, consider starring the repo—it helps others discover the gem.

Links:

One More Thing

If you've been thinking about contributing to the Ruby ecosystem but haven't started — I encourage you to do it. The process of building sqlite_crypto taught me more about Rails internals than years of application development. The community needs tools, and you might be the person to build the next one.

If you see gaps that you hit in your Rails + SQLite workflow, feel free to share it with me. I'm genuinely curious what other pain points exist in this new SQLite-first world.

Building something with sqlite_crypto? I'd love to hear about it. Drop a comment or find me on GitHub.

Top comments (0)