full_search

SQLite FTS5 full-text search for Rails/ActiveRecord. A lightweight, self-contained alternative to pg_search for apps already running on SQLite.

Requirements

Ruby 3.2+
Rails 8.0+
SQLite 3.34+ (if using typo tolerance)

When to use

full_search is designed for apps with 100,000 records or fewer per table that want full-text search without running a separate service. If you're on SQLite and need keyword search, phrase matching, typo-tolerant substring queries, or result highlighting, this gem gives you full-text search suitable for small to medium SQLite-backed apps with zero infrastructure — no Elasticsearch, no Meilisearch, no Sidekiq queue.

Installation

Add to your Gemfile:

gem "full_search"

Run:

bundle install
bin/rails generate full_search:install

This creates the initializer and automatically runs full_search:prepare to create FTS tables. Use --skip-prepare if your database isn't ready yet:

bin/rails generate full_search:install --skip-prepare

Usage

class Customer < ApplicationRecord
  full_search do
    field :first_name, weight: 5
    field :last_name,  weight: 5
    filter :account_id, required: true
  end
end

Customer.search("sam", filters: { account_id: 1 }).page(params[:page])

Field aliasing with `as:`

Use as: to give a field a different column name in the FTS table. This is useful when the model's accessor name differs from the desired search index key — for example, storing a computed value (last 8 chars of VIN) in a shorter or semantically-named column:

class Vehicle < ApplicationRecord
  full_search do
    field :vin_last8, weight: 2, source: -> { vin&.last(8)&.upcase }, as: :vin
    field :make, weight: 4
    field :model, weight: 4
    filter :account_id, required: true
    highlight
  end
end

The FTS table stores the value under vin (not vin_last8). Search results highlight keyed as vin, and the full_search_text_for(:vin) lookup works as expected.

Features

Declarative full_search DSL with search / full_search query methods
SQLite FTS5 backed
Required-filter enforcement for multi-tenant apps
Exact-match queries for encrypted identifiers
Soft-delete awareness
Automatic table creation and schema-drift detection
Phrase, exclusion, and OR query operators
FTS5 highlight() support for result snippets
Opt-in trigram typo/substring fallback (requires SQLite >= 3.34)

Conditional indexing

Use index_if to restrict which records are included in the FTS index. Records that don't match the SQL condition are excluded from search results entirely.

class Customer < ApplicationRecord
  full_search do
    field :first_name, weight: 5
    field :last_name,  weight: 5
    filter :account_id, required: true

    index_if sql: "active = 1 AND deleted_at IS NULL"
  end
end

This is enforced at the database level — the FTS virtual table only contains rows matching the condition, and triggers skip inserts/updates for non-matching records.

Exact match

For exact-match lookups on encrypted identifiers or fields that need precise matching outside the FTS tokenizer, use exact_match:

class Customer < ApplicationRecord
  full_search do
    field :first_name, weight: 5
    filter :account_id, required: true

    exact_match :customer_number
  end
end

Customer.search("CUST-001", filters: { account_id: 1 })
# same query but only returns exact matches on customer_number

For case- or punctuation-normalized SQL exact matches, pass a normalize lambda that transforms the query the same way the SQL expression transforms the column:

exact_match :license_plate,
  sql: "UPPER(REPLACE(REPLACE(license_plate, ' ', ''), '-', ''))",
  normalize: ->(q) { q.to_s.upcase.gsub(/[ -]/, "") }

Per-model operations

Once a model declares full_search, you can call these class methods:

Method	What it does
`Customer.rebuild!`	Force-drop and recreate the model's FTS table, backfill from the source table, and reinstall triggers.
`Customer.reindex!`	Re-evaluates computed `source:` fields for every record and updates the FTS table in-place. Table structure is untouched.
`Customer.optimize!`	Runs FTS5 `optimize` on the model's index to merge b-tree segments.

Index management

FTS indexes are SQLite virtual tables (customers_fts, vehicles_fts, etc.) that mirror your model tables. They stay in sync via database triggers on INSERT / UPDATE / DELETE.

Rebuild vs reindex

These two operations are often confused:

Rebuild (full_search:rebuild / Customer.rebuild!) — drops and recreates the FTS virtual table. Needed when the DSL changes (fields added/removed, tokenizer changed, etc.). The table is re-created from scratch, backfilled, triggers re-installed, and the index is optimized.
Reindex (Customer.reindex! / FullSearch::Index.reindex_source_fields!) — updates existing FTS rows with fresh values from computed source: fields only. The table structure is untouched. Database triggers cover regular column changes automatically; only Ruby-evaluated source: blocks need an explicit reindex.

Setup lifecycle

full_search evaluates the DSL block and installs callbacks when the model class loads, but it does not create the FTS table until Rails after_initialize (or until you call FullSearch.setup! or FullSearch::Index.rebuild! manually). This guarantees that source: blocks run against fully-loaded model definitions, avoiding load-order issues where associations or methods defined later in the class body are not yet available.

Note on rebuild locking: The lock_rebuilds option prevents concurrent rebuilds within the same process/connection. For multi-process or multi-host deployments, run full_search:rebuild from a single deployment step.

First-deploy setup

After db:prepare creates your application tables, FTS virtual tables like customers_fts do not yet exist. Run full_search:prepare to create them:

bin/rails full_search:prepare

For Docker-based deployments, add this to your entrypoint script after db:prepare:with_data:

./bin/rails full_search:prepare

The full_search:prepare task is idempotent — it only creates missing tables and installs triggers. It will not drop or rebuild existing indexes, making it safe to run on every deploy.

Auto-rebuild on app load

When auto_rebuild_schema is enabled (default in the generated initializer), the railtie hooks into Rails after_initialize and:

Creates missing FTS tables for every model using full_search
Compares each table's stored config hash against the current DSL
Rebuilds the index automatically when the DSL changes (new fields, different tokenizer, etc.)

# config/initializers/full_search.rb
FullSearch.configure do |config|
  config.auto_rebuild_schema = true
end

In production

auto_rebuild_schema — Must be false in production. The generated initializer defaults to Rails.env.local?, so it's already off. If enabled, every process (web, worker, console) attempts schema rebuilds on boot, which is unnecessary and can cause issues during zero-downtime deploys where old and new processes overlap.

auto_rebuild_on_stale_query — Must be false in production. A query-time rebuild is dangerous — it can cause timeouts or race conditions under load. The generated initializer defaults to Rails.env.local?, keeping it off in production.

lock_rebuilds — This option uses a Ruby Mutex and only prevents concurrent rebuilds within the same process. It does not coordinate across processes or hosts. Multi-process or multi-host deployments must run full_search:rebuild from a single deployment step.

Docker entrypoint — For containerized environments, add full_search:prepare after db:prepare:with_data:

./bin/rails full_search:prepare

full_search:prepare is idempotent — it only creates missing FTS tables and installs triggers, making it safe to run on every deploy.

Auto-rebuild runs on every Rails process boot (web, worker, console). For zero-downtime deploys where old processes still serve traffic, or if you prefer explicit control, set auto_rebuild_schema to false and run the rebuild task manually:

# Rebuild only indexes whose DSL has changed (fast, safe for production)
bin/rails full_search:rebuild

# Target specific models by table name
bin/rails 'full_search:rebuild[customers,vehicles]'

The rebuild task checks each model's stored config hash against the current DSL and only drops/recreates indexes when they differ. For a full forced reset (drops and recreates all FTS tables regardless):

bin/rails full_search:reset

Schema dump

By default, full_search includes its FTS virtual tables in db/schema.rb so that db:schema:load produces a complete, loadable database. To exclude them:

FullSearch.configure do |config|
  config.dump_schema_virtual_tables = false
end

When disabled, you must run bin/rails full_search:prepare after every db:schema:load to recreate the indexes.

Rake tasks

Task	Description
`full_search:prepare`	Idempotent setup — creates missing FTS tables and triggers without dropping existing ones. Safe to run on every deploy. Add to your Docker entrypoint after `db:prepare:with_data` for first-deploy automation.
`full_search:rebuild`	Drops and recreates the FTS virtual table only when the DSL config hash has changed (safe for production). Pass model names to target specific tables.
`full_search:reset`	Force a full rebuild — drops and recreates all FTS tables regardless of config hash. Use when data may be out of sync.
`full_search:optimize`	Run FTS5 `optimize` to merge b-tree segments. Useful after bulk updates.
`full_search:backfill`	Force-rebuild FTS indexes for specified models (or all). Useful for recovery after bulk operations.
`full_search:status`	Show each model's index status (`ok` / `stale`) and count of empty sourced fields.
`full_search:health_check`	Verify all indexes are present and current; exits `1` if any table is missing or stale. Useful for deploy gates or Docker healthchecks.

Background jobs

Scheduled optimization

FTS5 b-tree segments accumulate over time as rows are inserted, updated, and deleted. Running optimize periodically merges these segments, keeping queries fast. For most apps, once a day during a low-traffic window is sufficient. Apps with heavy write volume may benefit from every few hours.

The gem ships FullSearch::OptimizeJob ready to use. Schedule it with Solid Queue's recurring.yml:

# config/recurring.yml
full_search_optimize:
  class: FullSearch::OptimizeJob
  schedule: daily at 4am
  description: "Merge FTS5 b-tree segments for full_search indexes"

If you're not on Solid Queue, most job frameworks support recurring schedules via gems like sidekiq-cron or whenever.

Config hash drift detection

Every FTS table stores a SHA256 digest of its DSL configuration in the full_search_index_versions table. When the DSL changes (e.g., adding a field), the stored hash no longer matches, and rebuild! is triggered automatically (if auto_rebuild_schema is true) to bring the index in line with the new definition. If auto_rebuild_schema is false, queries raise ConfigChangedError when the hash doesn't match (or log a warning and fall back if configured with :log_and_fallback).

Bulk imports

When importing thousands of records, disable synchronous FTS maintenance and rebuild the index afterward:

FullSearch.bulk_import(Customer) do
  Customer.insert_all(records)
end
# automatically enqueues FullSearch::BackfillJob for Customer

While inside the block, database triggers are dropped so raw SQL operations (including insert_all!, update_all, and delete_all) bypass FTS table updates entirely. On exit, triggers are re-created and a FullSearch::BackfillJob is enqueued to rebuild the index from scratch. Reindexing of computed source: blocks within the block is also skipped.

You can also call Customer.bulk_import { ... } directly on the model.

For manual recovery outside a bulk-import block:

bin/rails 'full_search:backfill[customers]'

Query-time auto-rebuild in local environments

By default, the generated initializer also enables auto_rebuild_on_stale_query in local environments:

FullSearch.configure do |config|
  config.auto_rebuild_on_stale_query = Rails.env.local?
end

When this option is true, the first search that detects a stale index automatically calls FullSearch::Index.rebuild_if_needed! and then proceeds with the query, so you don't need to restart the server or run a Rake task during iterative development. In production, this defaults to false; use full_search:rebuild or boot-time auto_rebuild_schema instead.

Missing table error

When the FTS table does not exist and a search is attempted, full_search raises MissingTableError with a clear message:

FullSearch::MissingTableError: FTS table `customers_fts` does not exist.
Run `bin/rails full_search:prepare` to create it.

This mirrors how Rails handles missing database tables — you see the error, run the task, and move on. Add full_search:prepare to your deploy pipeline or Docker entrypoint after db:prepare:with_data to prevent the error in production.

Query operators

Customer.search('"Sam Smith"', filters: { account_id: 1 })      # phrase
Customer.search("honda -civic", filters: { account_id: 1 })     # exclusion
Customer.search("honda OR toyota", filters: { account_id: 1 })  # OR

Highlighting

class Customer < ApplicationRecord
  full_search do
    field :first_name, weight: 5
    highlight open_tag: "<mark>", close_tag: "</mark>"
  end
end

Customer.search("sam", filters: { account_id: 1 }, highlight: true)
# each result has #full_search_snippet

Typo tolerance

class Customer < ApplicationRecord
  full_search do
    field :first_name, weight: 5
    typo_tolerance
  end
end

typo_tolerance uses an FTS5 trigram shadow table as a fallback when the primary index returns no results. It is substring/trigram fallback, not edit-distance correction, and requires SQLite >= 3.34.

You can disable the expensive LIKE 'term%' fallback for very short queries:

full_search do
  typo_tolerance
  min_like_prefix_length 3
end

Multi-search

Search across multiple models in a single call with FullSearch.multi_search:

results = FullSearch.multi_search(
  query: "Honda",
  groups: [
    {key: :customers, label: "Customers", model: Customer, filters: {account_id: 1}},
    {key: :vehicles,  label: "Vehicles",  model: Vehicle,  filters: {account_id: 1}}
  ]
)

results[:customers] #=> [#<Customer...>, ...]
results[:vehicles]  #=> [#<Vehicle...>, ...]

Pass includes: to eager-load associations for each group:

FullSearch.multi_search(
  query: "Honda",
  groups: [
    {key: :vehicles, label: "Vehicles", model: Vehicle,
     filters: {account_id: 1}, includes: [:customer]}
  ]
)

Testing

full_search ships with FullSearch::TestHelpers, a framework-agnostic module you can include in Minitest or RSpec. It provides helpers to rebuild, reindex, and reset FTS tables from your tests.

Setup

Call setup_for_tests! once in your test boot file. It applies safe defaults:

Disables rebuild locking (lock_rebuilds = false)
Enables query-time auto-rebuild for stale indexes (auto_rebuild_on_stale_query = true)
Keeps stale-query behaviour as raise so real config drift fails fast
Forces Active Job to run inline so background reindex jobs execute synchronously

Minitest (`test/test_helper.rb`)

require "minitest/autorun"
require "full_search"
require "full_search/test_helpers"

class ActiveSupport::TestCase
  include FullSearch::TestHelpers
end

FullSearch::TestHelpers.setup_for_tests!

RSpec (`spec/rails_helper.rb`)

require "full_search"
require "full_search/test_helpers"

RSpec.configure do |config|
  config.include FullSearch::TestHelpers, type: :model
end

FullSearch::TestHelpers.setup_for_tests!

Why you must rebuild after creating data

FTS tables are updated by database triggers on normal inserts/updates, but computed source: fields are evaluated by Ruby during a rebuild/reindex. The safest pattern is to create your records, then call rebuild_full_search_index(model) before searching.

Available helpers

# Rebuild a single model's FTS table from scratch (drops and recreates it).
# Accepts a model class, symbol, or string class name.
rebuild_full_search_index(Customer)
rebuild_full_search_index(:customer)
rebuild_full_search_index("Customer")

# Re-evaluate computed source: fields only. Leaves table structure untouched.
reindex_full_search(Customer)

# Rebuild every registered search model, or only the ones passed in.
reset_full_search!
reset_full_search!(Customer, Vehicle)

# Idempotently create any missing FTS tables/triggers for registered models.
ensure_full_search_tables

# Run a block with inline Active Job, restoring the adapter afterwards.
with_full_search_async_jobs_inline do
  # ReindexJob / BackfillJob execute synchronously here
end

# Run a block inside a rebuild, dropping the table at the end.
with_full_search_rebuild(Customer) do
  # search and assert here
end

# Scope anonymous searchable models to a block so they don't leak into
# the global registry and affect later tests or Rake tasks.
with_full_search_models_registered do
  model = Class.new(Customer) do
    full_search { field :first_name, weight: 5 }
  end
  model.table_name = "customers"
  rebuild_full_search_index(model)
  # ... search and assert
end

Minitest example

class CustomerSearchTest < ActiveSupport::TestCase
  def setup
    @account = Account.create!(name: "Acme")
    @customer = Customer.create!(account: @account, first_name: "Sam")
    rebuild_full_search_index(Customer)
  end

  def test_finds_by_first_name
    results = Customer.search("Sam", filters: {account_id: @account.id})
    assert_includes results.to_a, @customer
  end
end

RSpec example

RSpec.describe Customer, type: :model do
  let(:account) { create(:account) }
  let(:customer) { create(:customer, account: account, first_name: "Sam") }

  before { rebuild_full_search_index(Customer) }

  it "finds by first name" do
    results = Customer.search("Sam", filters: {account_id: account.id})
    expect(results).to include(customer)
  end
end

Advanced configuration

If you need to override the defaults set by setup_for_tests!, use FullSearch::TestHelpers.configure:

FullSearch::TestHelpers.configure do |config|
  config.lock_rebuilds = true
  config.auto_rebuild_on_stale_query = false
  config.stale_query_behavior = :log_and_fallback
end

Production readiness

full_search is designed for small to medium SQLite-backed Rails apps. Before running in production, review this checklist.

Initial deploy

Run your normal database setup so application tables exist:
```
bin/rails db:prepare:with_data
```
Create the FTS virtual tables and triggers:
```
bin/rails full_search:prepare
```
For containerized deploys, add step 2 to your entrypoint after db:prepare:with_data.

Configuration

The generated initializer defaults to production-safe values. Do not change these in production:

auto_rebuild_schema must be false. If enabled, every web/worker/console process tries to rebuild indexes on boot.
auto_rebuild_on_stale_query must be false. A query-time rebuild under load can cause timeouts and race conditions.

If you need to change the search DSL, ship the change and then run:

bin/rails full_search:rebuild

from a single deployment step. The gem checks each model's stored config hash and only rebuilds indexes whose DSL has changed.

Monitoring and maintenance

Health check — use the built-in Rake task for deploy gates or container healthchecks:
```
bin/rails full_search:health_check
```
It exits 0 when every registered model has a current FTS table, or 1 if any table is missing or stale.
Status overview — bin/rails full_search:status prints ok / stale and the count of empty sourced fields per model.

Scheduled optimize — queue FullSearch::OptimizeJob once a day during a low-traffic window to merge FTS5 b-tree segments:

# config/recurring.yml
full_search_optimize:
  class: FullSearch::OptimizeJob
  schedule: daily at 4am
  description: "Merge FTS5 b-tree segments for full_search indexes"

Recovery after bulk operations — if a bulk operation bypassed triggers, rebuild the affected index:
```
bin/rails 'full_search:backfill[customers]'
```

Common errors

Error	Meaning	Fix
`FullSearch::MissingTableError`	The FTS table does not exist yet.	Run `bin/rails full_search:prepare`.
`FullSearch::ConfigChangedError`	The DSL has changed and the index is stale.	Run `bin/rails full_search:rebuild`.

Locking note

lock_rebuilds uses a Ruby Mutex and only prevents concurrent rebuilds within the same process/connection. It does not coordinate across processes or hosts. Always run full_search:rebuild from a single deployment step.

Known limitations

Queries run with highlight: true return an Array of records, not an ActiveRecord::Relation. No further chaining (.where, .order, .limit) is possible after highlighting is applied.
Per-model only; multi_search returns grouped results, not a unified ranked list
No built-in multi-model aggregator

Security

If you discover a security vulnerability, please email the maintainer directly at the address listed in the gemspec. Do not file a public issue.