Add search to Rails with SQLite FTS5

Rails 8 leans hard into SQLite as a first-class production database. One of the nicest payoffs is that you can build a fast, typo-tolerant, relevance-ranked search engine without adding Elasticsearch, Meilisearch, or even the pg_search gem. SQLite ships with FTS5, and Rails 8.1 finally exposes a clean migration DSL for it.

While building Kamado.app, a social platform for Kamado enthusiasts, we wanted to add search quickly without introducing complex dependencies. No search cluster, no additional service to babysit, just the existing SQLite file. This post walks through the implementation we ended up with: a single search endpoint that indexes heterogeneous records, ranks them by relevance, supports prefix matching, and stays in sync automatically with minimal ceremony.

The architecture in one diagram

 Recipe ──┐
          │  include Searchable
 Cook ────┤  (after_save / after_destroy hooks)
          │
          ▼
 ┌──────────────────┐     after_save     ┌─────────────────────┐
 │  search_entries  │ ─────────────────► │ search_entries_fts  │
 │ (relational row) │                    │  (FTS5 virtual)     │
 └──────────────────┘                    └─────────────────────┘
          ▲                                        │
          │          SearchEntry.search(q)         │
          └────────────────────────────────────────┘
                    INNER JOIN ON rowid

Three layers, each with a single responsibility:

1. Searchable concern: a lifecycle hook on any model that wants to be searchable. Decides whether to index.
2. SearchEntry: a polymorphic, denormalized row that holds the extracted text (title + body). Decides what to index.
3. search_entries_fts: an FTS5 virtual table mirroring SearchEntry. Handles the actual full-text indexing and ranking.

Step 1. The migration

Rails 8.1 introduced create_virtual_table, so you no longer need raw SQL to declare an FTS5 table:

class CreateSearchEntries < ActiveRecord::Migration[8.0]
  def up
    create_table :search_entries do |t|
      t.string :searchable_type, null: false
      t.integer :searchable_id, null: false
      t.string :title, null: false
      t.text :body

      t.timestamps
    end

    add_index :search_entries,
              [:searchable_type, :searchable_id],
              unique: true

    create_virtual_table :search_entries_fts, :fts5, ["title", "body"]
  end

  def down
    drop_virtual_table :search_entries_fts, :fts5, ["title", "body"]
    drop_table :search_entries
  end
end

A few things to notice:

• The relational search_entries table is where we store the canonical text.
• search_entries_fts is the FTS5 virtual table. It only contains the tokenized index, not a full row, and its rowid column is what ties it back to search_entries.id.
• The searchable_type + searchable_id unique index prevents duplicate rows for the same record.

This also shows up in schema.rb. Rails 8.1 now faithfully dumps virtual tables:

create_virtual_table "search_entries_fts", "fts5", ["title", "body"]

Step 2. The SearchEntry model

SearchEntry is polymorphic. One row per searchable record, regardless of whether it's a Recipe or a Cook:

class SearchEntry < ApplicationRecord
  include FtsSyncable, Indexable

  validates :searchable_type, presence: true
  validates :searchable_id,   presence: true
  validates :title,           presence: true

  scope :for_type, ->(type) { where(searchable_type: type) }

  def self.search(query)
    return none if query.blank?

    fts_query = query.split.map { |term| "\"#{term}\"*" }.join(" ")

    joins("INNER JOIN search_entries_fts ON search_entries_fts.rowid = search_entries.id")
      .where("search_entries_fts MATCH ?", fts_query)
      .order(Arel.sql("search_entries_fts.rank"))
  end
end

The search class method is the interesting part. Let's unpack it.

Query transformation

A user types pulled pork in the search box. We want to match records that contain both terms, but we also want prefix matching so that pull already returns pulled. FTS5's MATCH syntax gives us both:

"pulled"* "pork"*

Each term is wrapped in double quotes (FTS5 phrase syntax, which avoids special-character surprises like - or :) and suffixed with * to enable prefix matching. The space between terms is an implicit AND.

The join

FTS5 virtual tables can be joined on their rowid. Because we insert into the FTS table using SearchEntry#id as the rowid, this join gives us back the full relational row, including the searchable_type and searchable_id columns we need later to resolve back to the real model.

The ordering

FTS5 exposes a built-in rank column backed by the BM25 algorithm (the same algorithm Elasticsearch defaults to). More relevant matches get a smaller rank value, so ordering ascending naturally sorts by relevance. We wrap it in Arel.sql because it isn't a column on search_entries itself and Rails would otherwise reject it.

Step 3. Keeping FTS in sync: FtsSyncable

Every time a SearchEntry is saved, we need to reflect the change in the FTS5 virtual table. This is the whole concern:

module SearchEntry::FtsSyncable
  extend ActiveSupport::Concern

  included do
    after_save    :sync_to_fts
    after_destroy :remove_from_fts
  end

  private
    def sync_to_fts
      remove_from_fts
      add_to_fts
    end

    def add_to_fts
      self.class.connection.execute(
        self.class.sanitize_sql(
          ["INSERT INTO search_entries_fts(rowid, title, body) VALUES (?, ?, ?)",
           id, title, body]
        )
      )
    end

    def remove_from_fts
      self.class.connection.execute(
        self.class.sanitize_sql(
          ["DELETE FROM search_entries_fts WHERE rowid = ?", id]
        )
      )
    end
end

Notes worth highlighting:

• Delete-then-insert is simpler than trying to build an idempotent upsert against an FTS5 virtual table, and for a single row the cost is negligible.
• sanitize_sql parameterizes the query properly. Never interpolate user data into a raw connection.execute call.
• The virtual table is managed manually because FTS5 is not a normal Active Record table. You can't just save a record into it.

An alternative you'll sometimes see in the wild is SQLite "contentless" or "external content" FTS5 tables with triggers that mirror the parent table automatically. That works, but it hides the sync in schema-level triggers and is harder to reason about when debugging. Doing it in Ruby keeps it explicit and testable.

Step 4. Extracting text: Indexable

FtsSyncable handles how to push text into FTS5. Indexable handles what text to push in the first place. It's a per-model switch-case on the domain:

module SearchEntry::Indexable
  extend ActiveSupport::Concern

  class_methods do
    def upsert_record(record)
      title, body = searchable_text_for(record)
      return if title.blank?

      entry = find_or_initialize_by(
        searchable_type: record.class.name,
        searchable_id:   record.id
      )
      entry.update!(title: title, body: body)
    end

    def remove_record(record)
      where(searchable_type: record.class.name, searchable_id: record.id)
        .destroy_all
    end

    private
      def searchable_text_for(record)
        case record
        when Recipe
          body_parts = [record.description]
          body_parts << record.ingredients.pluck(:name).join(" ") if record.respond_to?(:ingredients)
          body_parts << record.cook&.name
          [record.title, body_parts.compact.join(" ")]
        when Cook
          [record.name, record.tagline]
        else
          [nil, nil]
        end
      end
  end
end

A few design choices:

• Denormalization is the whole point. We flatten the recipe's description, its ingredient names, and its author's name into a single body string. FTS5 doesn't care about foreign keys, it just tokenizes text.
• find_or_initialize_by gives us idempotent upsert semantics for the relational row. The FTS5 sync is handled downstream by FtsSyncable.
• Return early on blank title. We never want a SearchEntry without a title. The presence: true validation would reject it anyway, but bailing early avoids a useless database round-trip.

Step 5. Hooking it up to models: Searchable

The concern that lives on searchable models (as opposed to the SearchEntry plumbing) is tiny:

module Searchable
  extend ActiveSupport::Concern

  included do
    after_save    :upsert_search_entry, if:      :should_be_indexed?
    after_save    :remove_search_entry, unless:  :should_be_indexed?
    after_destroy :remove_search_entry
  end

  private
    def upsert_search_entry
      SearchEntry.upsert_record(self)
    end

    def remove_search_entry
      SearchEntry.remove_record(self)
    end

    def should_be_indexed?
      true
    end
end

The magic is in should_be_indexed?. It's a hook point that lets each model control visibility. For Cook, the default true is fine: all cooks are publicly searchable. For Recipe, we only want published recipes to appear in search:

class Recipe < ApplicationRecord
  include Publishable, Searchable

  after_publish   :upsert_search_entry
  after_unpublish :remove_search_entry

  def should_be_indexed?
    published?
  end
end

Our Publishable concern follows the pattern Basecamp uses in Fizzy's Board::Publishable: publishing and unpublishing are their own lifecycle events with dedicated after_publish and after_unpublish callbacks, not just attribute writes that flow through after_save. That means Searchable's after_save hook alone doesn't see the state transition, so we wire the publish lifecycle into the indexing methods explicitly on the model. Editing a draft still does nothing (the if: :should_be_indexed? guard on after_save keeps it out of the index), publishing fires upsert_search_entry, and unpublishing fires remove_search_entry.

Step 6. Using it from a controller

The read path is the boring part, which is exactly what you want. Pull the matching IDs from SearchEntry, then use them to scope a regular Active Record query:

class Account::RecipesController < Account::ApplicationController
  def index
    scope = Recipe.published.with_attached_cover_image

    if params[:query].present?
      recipe_ids = SearchEntry.search(params[:query])
                              .for_type("Recipe")
                              .select(:searchable_id)
      scope = scope.where(id: recipe_ids)
    end

    @pagy, @recipes = pagy(scope, limit: 12)
    @query = params[:query]
  end
end

A couple of things to appreciate here:

• We don't pluck the IDs. We select(:searchable_id) and pass the relation directly into where(id: ...). Rails composes it into a single SQL query with a subquery, no intermediate Ruby array.
• Normal scopes still apply. published, eager-loading of attached images, none of that knows or cares that FTS is involved. Search just narrows the ID set.

Step 7. Testing full-text search

Because everything flows through normal Active Record callbacks, the tests are surprisingly ergonomic:

class SearchEntryTest < ActiveSupport::TestCase
  setup do
    @recipe = recipes(:pulled_pork)
  end

  test "search finds matching records by title" do
    SearchEntry.upsert_record(@recipe)

    results = SearchEntry.search("Pulled")
    assert_includes results.map(&:searchable_id), @recipe.id
  end

  test "search supports prefix matching" do
    SearchEntry.upsert_record(@recipe)

    results = SearchEntry.search("Pull")
    assert_includes results.map(&:searchable_id), @recipe.id
  end

  test "search returns none for blank query" do
    assert_equal SearchEntry.none.to_a, SearchEntry.search("").to_a
  end
end

And the lifecycle semantics ("unpublishing a recipe removes it from search") are testable without any FTS-specific scaffolding:

test "unpublishing a recipe removes the search entry" do
  recipe = recipes(:pulled_pork)
  SearchEntry.upsert_record(recipe)

  recipe.unpublish!

  assert_not SearchEntry.exists?(
    searchable_type: "Recipe",
    searchable_id:   recipe.id
  )
end

Because the FTS table is a normal part of the test database, bin/rails test runs all of this against a real FTS5 engine. No mocking.

What you get for free

A few things fall out of this design that are worth calling out:

• BM25 relevance ranking courtesy of FTS5's built-in rank column.
• Unicode tokenization via the default unicode61 tokenizer, so Dutch accented characters like "crème brûlée" tokenize sensibly.
• Prefix matching from a tiny query rewrite ("term"*).
• Multi-type search from the polymorphic searchable_type column.
• Visibility control from a single should_be_indexed? override.
• Zero extra infrastructure. Same SQLite file, same backups, same deploy. No cluster to babysit.

When to reach for something heavier

This approach scales beautifully up to millions of rows on commodity hardware, but there are limits worth knowing:

• No cross-field boosting. You can't easily say "title is 3x more important than body" without dropping into custom FTS5 rank functions.
• No synonym dictionaries, stemming, or fuzzy matching beyond prefix wildcards. pork won't match porcs.
• SQLite-specific. Move to Postgres later and you're rewriting this. Though pg_search is a close analogue, and the architecture above (concern, polymorphic entry table, conditional indexing) maps over cleanly.

For most SaaS product search boxes, none of that matters. What matters is that users can type three characters and find the recipe they just saved. And for that, 120 lines of Ruby and a virtual table is plenty.