sqlite_search

Full-text, vector, and hybrid search for ActiveRecord, without leaving SQLite. Declare which columns are searchable and get a query scope. No search cluster to run, no second copy of your data to keep in sync.

It covers three kinds of search behind one DSL. Full-text uses SQLite's FTS5 module with BM25 relevance ranking. Vector (semantic) search uses sqlite-vec through the neighbor gem, with your app supplying embeddings via a callback. Hybrid search fuses the two with Reciprocal Rank Fusion and an optional reranking step. There are no database triggers and no search server to run. Works on Rails 8.0+.

sqlite_search runs the knowledge base search in Universal Chatbot, where it handles keyword, semantic, and hybrid retrieval over customer documents on SQLite in production.

Declare a search index in a migration, add one line to the model, and query it:

# db/migrate/xxxx_create_posts_search.rb
class CreatePostsSearch < ActiveRecord::Migration[8.0]
  def change
    create_fts5_index :posts, :search, against: { title: 2.0, body: 1.0 }, backfill: true
  end
end

# app/models/post.rb
class Post < ApplicationRecord
  fts5_scope :search, against: { title: 2.0, body: 1.0 }
end

# anywhere
Post.search("morning coffee")                    # a normal, chainable relation
Post.search("coffee").order_by_rank.first.search_rank
Post.where(author_id: 7).search("coffee")        # searches only that author's posts

The scope is an ordinary ActiveRecord::Relation, so it composes with the rest of your query. User input is sanitized into a safe MATCH expression for you, so you can pass params[:q] straight through.

Add the gem:

gem "sqlite_search"

Then bundle install. In a Rails app the model DSL (fts5_scope, vec_scope, hybrid_scope) and the migration helpers are available in every model and migration automatically. Outside Rails, include SqliteSearch::Model in your models and ActiveRecord::Migration.include(SqliteSearch::Migration). Vector and hybrid search need two more gems; see Vector search.

create_fts5_index creates an FTS5 virtual table for a model. It is mixed into ActiveRecord::Migration, so it is available in any migration:

create_fts5_index :posts, :search, against: { title: 2.0, body: 1.0 }, backfill: true

against: takes a single column (:body), a list ([:title, :body]), or a hash of column to BM25 weight ({ title: 2.0, body: 1.0 }, weighting title matches higher). tokenizer: defaults to "porter unicode61", which folds case and accents and stems words, so a search for "running" also matches "run". backfill: true seeds the index from rows that already exist; leave it off for a brand-new table.

The second argument (:search) names the index. It becomes the FTS table name (posts_search_fts) and the scope name on the model, so keep the two in step. A rails g sqlite_search:fts5 generator writes the migration for you:

rails g sqlite_search:fts5 Post title body --weights 2,1
rails g sqlite_search:fts5 Post body --index search_body   # a second index on the same model

The virtual table is created with create_virtual_table, so it appears in schema.rb and restores cleanly from db:schema:load. Nothing is hidden in the database.

class Post < ApplicationRecord
  fts5_scope :search, against: { title: 2.0, body: 1.0 }
end

fts5_scope defines the Post.search scope and wires up after_save and after_destroy callbacks that keep the index in step whenever an indexed column changes. They run inside the transaction, so the index write is atomic with the row: if it fails, both roll back. A model can declare more than one index (fts5_scope :search_body, against: :body) and each gets its own scope.

When the text to index is not a plain column (assembled from JSON, cleaned up, or stemmed in Ruby), name a method with source:. It returns a hash of FTS column to text, and against: still names those columns and their weights. watch: lists the attributes whose change triggers a resync:

class Article < ApplicationRecord
  fts5_scope :keyword, against: { text: 1.0, tags: 2.0 },
    source: :search_document, watch: [:body, :metadata]

  def search_document
    { text: body, tags: metadata["tags"].join(" ") }
  end
end

watch: defaults to the against: columns. reindex rebuilds a source: index record by record in Ruby, inside one transaction. backfill: true in the migration copies columns straight from the table, so it does not apply here; run reindex after the migration instead.

Post.search("coffee")                              # AND of the sanitized terms
Post.search("coffee").where(published: true)       # composes with any AR scope
Post.search("cof", prefix: true)                   # prefix match on the last term
Post.search(raw: "coffee OR tea")                  # bypass the sanitizer, pass raw FTS5 syntax

A blank or nil query (including a call with no positional argument, such as Post.search(prefix: true)) returns .none rather than matching everything or raising. Any string you pass as the positional argument goes through SqliteSearch::Query, which keeps quoted phrases and alphanumeric terms and strips FTS5 operator syntax, so untrusted input is safe. Reserve raw: for trusted, internally built expressions.

By default the scope filters but does not order, so it composes with your own order. Ask for relevance ordering explicitly:

posts = Post.search("coffee").order_by_rank
posts.first.search_rank   # higher is more relevant

order_by_rank orders by SQLite's bm25(), inverted so higher means better, and honors the per-column weights from against:. It replaces any order chained before it; chain an order after it to add a tiebreaker. order_by_rank(threshold: 8.0) keeps only rows whose rank is at least the threshold. Each ranked record carries a <name>_rank reader (search_rank for a scope named :search).

Bulk writes that skip callbacks (insert_all, update_all, raw SQL, another connection) leave the index stale. Rebuild it from the base table:

Post.reindex(:search)   # one index
Post.reindex            # every fts5_scope on the model

or rake sqlite_search:reindex[Post,search] from the command line.

Semantic search ranks rows by embedding similarity instead of keywords. It uses sqlite-vec through the neighbor gem. sqlite_search stores and queries the vectors; your app produces them. Add both gems, since neither is a dependency of sqlite_search itself:

gem "neighbor"
gem "sqlite-vec"

Then have neighbor load the sqlite-vec extension on every connection:

rails g neighbor:sqlite

That writes config/initializers/neighbor.rb, which calls Neighbor::SQLite.initialize!. Do this before running a create_vec_index migration: without it, migrations and db:schema:load (which do not load your models) fail with no such module: vec0.

sqlite_search calls this block whenever it needs to turn text into a vector, both when indexing a row and when running a query, so the same model does both sides:

SqliteSearch.embedder do |text, model:, scope:|
  MyEmbeddingClient.embed(text)   # returns an Array<Float> of length `dimensions`
end

The block receives the text (the against: columns joined), the model class, and the scope name, so you can route to different embedding models per scope if you want.

# migration
create_vec_index :posts, :semantic_search, dimensions: 768

# model
vec_scope :semantic_search, against: [:title, :body], dimensions: 768

The vector table is posts_semantic_search_vec, a vec0 virtual table that also round-trips through schema.rb. A rails g sqlite_search:vec Post --index semantic --dimensions 768 generator writes the migration. create_vec_index does not backfill (there is no text to embed at migration time), so run Post.reembed(:semantic_search) once afterward to embed existing rows.

Both create_vec_index and vec_scope take a distance:: :cosine (the default), :euclidean (L2), or :taxicab (L1). Set the same one on both.

By default a save enqueues a background SqliteSearch::EmbedJob to do the embedding, so an expensive embedding call stays out of the request. Pass sync: :inline to embed inside the callback instead, which you want when the embedder is cheap or when your app does not use ActiveJob:

vec_scope :semantic_search, against: [:title, :body], dimensions: 768, sync: :inline

Route the job to a specific queue with SqliteSearch.config.job_queue = :embeddings.

Pass sync: :manual when your app already runs its own embedding pipeline (to track progress on the row, say). Saves then never embed, destroys still remove the vector, and your code embeds a record when it is ready:

vec_scope :semantic_search, against: :body, dimensions: 768, sync: :manual

post.reembed(:semantic_search)   # embed this record now

To embed text that is not a plain column, name a method with source: that returns the text, and list the attributes it depends on in watch: (not needed with sync: :manual, which never re-embeds on save):

vec_scope :semantic_search, source: :embedding_text, watch: [:body, :metadata], dimensions: 768

Post.semantic_search("a warm drink to start the day", k: 20, threshold: 0.3)

k: caps how many nearest neighbors to fetch (default 20). Each returned record exposes a <name>_distance reader (the raw distance, smaller is closer), and a cosine scope also exposes <name>_similarity (1 - distance, in [-1, 1], higher is closer). threshold: filters by relevance: on a cosine scope it is a minimum similarity, on a euclidean or taxicab scope it is a maximum distance. A blank or nil query returns .none.

Results come back nearest first, replacing any order chained before the search.

Re-embed after a bulk write the same way you reindex FTS5:

Post.reembed(:semantic_search)   # one named vec index
Post.reembed                     # every vec_scope on the model

or rake sqlite_search:reembed[Post,semantic_search] from the command line. Re-embedding overwrites vectors row by row, so the index stays searchable while it runs and an embedder failure partway leaves the remaining rows' vectors in place. It then drops vectors whose row no longer exists.

Hybrid search runs the keyword search and the vector search together, as two "arms", and fuses their rankings, which catches both exact-term matches and semantic ones. It reuses an fts5_scope and a vec_scope you have already declared:

class Post < ApplicationRecord
  fts5_scope :search_body, against: :body
  vec_scope :semantic_search, against: :body, dimensions: 768, sync: :inline
  hybrid_scope :search, fts5: :search_body, vec: :semantic_search
end

fts5: and vec: name the two arms. They must already be declared, or hybrid_scope raises SqliteSearch::Error at load time (as it does if the hybrid name collides with an arm's name). k: sets the RRF constant (default 60), which is a different k: than the neighbor count on vec_scope.

posts = Post.search("coffee", limit: 20)
posts.first.search_score   # fused (or reranker) score, higher is better

Each arm produces a ranked candidate list, Reciprocal Rank Fusion combines them, an optional reranker reorders the result, and the top limit records come back as a relation in fused order (replacing any order chained before the search), each carrying a <name>_score reader. limit: defaults to 20. A blank query returns .none. Pass rerank: false to skip the reranker and return the plain fused order.

Each arm can drop weak matches before fusion, so a poor match from one arm is not lifted into the results by the other. fts5_threshold: is a minimum keyword rank, and vec_threshold: is the vector scope's threshold: (minimum similarity on a cosine scope):

Post.search("coffee", fts5_threshold: 8.0, vec_threshold: 0.3)

Register a reranker once and every hybrid scope uses it, unless a call opts out:

SqliteSearch.reranker do |query, documents, model:, scope:|
  # One relevance score per text, from your cross-encoder or rerank API.
  scores = MyRerankClient.score(query, documents.map(&:body))
  documents.zip(scores).sort_by { |_doc, score| -score }
end

documents is an Array of loaded records of the searched model, the same objects Post.find returns, in fused (RRF) order. It holds every fused candidate, before the limit: cut: each arm contributes up to [limit * 3, 100].min, so at most 200 records. You choose which attributes to send to your reranker (body above, or the text you embed).

Return [record, score] pairs in the order you want, as above, and each record's <name>_score becomes your reranker's score. You can also return just the records, reordered, and <name>_score keeps the fused RRF score. Either way the order you return is the result order. Records are matched back by primary key: a candidate you leave out is dropped from the results, a record that was not a candidate is ignored, and an empty array gives .none. The top limit of your order comes back.

model: is the class the search was called on, and scope: is the hybrid scope name (the same pair is passed to the embedder block). Reranking is best-effort: if the block raises, the failure is logged and the search falls back to the fused order, so a broken reranker never takes down a search.

Conditions you chain before the search push into the query as an exact pre-filter, not a post-filter:

Post.where(tenant_id: 5).published.search("coffee")   # pre-filters both arms
Post.where(tenant_id: 5).semantic_search("coffee")           # pre-filters the KNN scan

The keyword arm uses the ordinary WHERE/JOIN SQL that ActiveRecord already builds for the chained scope. The vector arm joins back to the source table before the scan runs. That pre-filter is exact rather than approximate, because vec0's KNN is a brute-force scan to begin with, so folding in your conditions just narrows what it scans. Concretely, Post.where(tenant_id: 5).semantic_search("coffee", k: 10) returns the 10 nearest neighbors within tenant 5, not the global top 10 trimmed to tenant 5 afterward.

Chaining a condition after the search is the escape hatch. It post-filters the result set like any relation:

Post.search("coffee").where("created_at > ?", 1.week.ago)

Only conditions that have a SQL form against the source table (or tables reached through joins) can be pushed into the pre-filter. A condition that exists only in Ruby has nothing to push, so it lands as a post-filter, which narrows an already-fused set; raise k: or limit: if you need more rows to survive it.

Ruby 3.3 or newer. Vector and hybrid search run on neighbor 1.x, which requires Ruby 3.3.

ActiveRecord 8.0 or newer. The migration helpers and their schema.rb round-trip rely on create_virtual_table, which arrived in Rails 8.0. The gem does not run on 7.1 or 7.2, and the gemspec enforces that.

SQLite with FTS5. The gem builds directly on SQLite's FTS5 module, so the SQLite library your app links against must have FTS5 compiled in. The sqlite3 gem's bundled build has it, as do most modern system builds.

Integer primary keys only. FTS5 and vec0 both key rows by an integer id, so a model with a string or UUID primary key cannot be indexed. The sync callback raises a clear SqliteSearch::Error in that case rather than letting a cryptic SQLite3::MismatchException surface from deep in the driver.

Sync runs in ActiveRecord callbacks, not triggers. Any write that skips callbacks (insert_all, update_all, raw SQL, a second connection) leaves the index stale until you run reindex or reembed. That is the price of keeping everything in schema.rb with nothing hidden in the database.

A failed vector sync leaves the row committed; FTS5 does not. The FTS5 index syncs inside the transaction, so a failed FTS5 write (for example the integer-PK guard) rolls the row back with it: index and row stay atomic. The vector index syncs after commit, because it enqueues a job by default and embedding is an out-of-band call you do not want holding a transaction open. So if a vector sync raises, the row is already saved and only the vector write was skipped. Treat a raised exception from the vector path as "saved, vectors may be stale," and recover with reembed.

A missing index table raises a plain SQLite error. Query a scope before its migration has run and you get no such table: <table>_<scope>_fts. Run the migration or the generator. A friendlier message is on the list; it is awkward today because ActiveRecord 8.1 hides virtual tables from table_exists?.

Vector search needs neighbor and sqlite-vec. Add both to your Gemfile before declaring a vec_scope. sqlite-vec ships prebuilt native extensions and has no build for musl platforms such as Alpine.

Cosine, euclidean, and taxicab distance. vec_scope and create_vec_index take distance: :cosine (the default), :euclidean (L2), or :taxicab (L1), and the two must agree. Inner-product distance is not offered: neighbor can compute it, but vec0 does not accept it as a table distance_metric (only cosine, l2, and l1), so it would need a mismatched metric on the table.

Embedding is async by default. A row saved right now may not appear in .semantic_search results until its EmbedJob runs. Use sync: :inline for immediate indexing (or if you do not use ActiveJob).

.semantic_search and .search are eager. Unlike an ordinary scope, they run the embedding call and the queries the moment you call them rather than building a lazy relation. .search does the most work, re-running both arms, the fusion, and any reranker (possibly a network call) on every invocation, so do not call either one inside a loop.

The candidate pool is capped. A hybrid scope pulls [limit * 3, 100].min candidates from each arm before fusing, so a very large limit: still fuses from at most 100 per arm.

Released under the MIT License.