Full-text search

.fullTextIndex() + .matching() for tsvector / FULLTEXT INDEX search across columns. Cross-dialect per-engine where available.

.fullTextIndex(name, columns, options?) declares an FTS index on one or more text columns. .matching('indexName', 'query') on the query builder runs the search.

Quick start

import { id, text, table } from '@voltro/database'

export const posts = table('posts', {
  id:    id(),
  title: text(),
  body:  text(),
}).fullTextIndex('postSearch', ['title', 'body'], {
  config:  'english',
  weights: { title: 'A', body: 'B' },
})

// Query
const hits = await ctx.store.query(
  queryFor(database.posts).matching('postSearch', 'voltro effect').descriptor,
)

What the framework emits

Postgres — tsvector + GIN

Postgres' canonical pattern:

ALTER TABLE "posts"
  ADD COLUMN IF NOT EXISTS "postSearch_tsv" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce("title", '')), 'A') ||
    setweight(to_tsvector('english', coalesce("body",  '')), 'B')
  ) STORED;

CREATE INDEX IF NOT EXISTS "postSearch" ON "posts" USING GIN ("postSearch_tsv");

The framework adds the synthetic <index>_tsv STORED tsvector column + a GIN index. .matching(...) queries compile to:

"postSearch_tsv" @@ plainto_tsquery('voltro effect')

MySQL / MariaDB — FULLTEXT INDEX

CREATE FULLTEXT INDEX `postSearch` ON `posts` (`title`, `body`);

.matching(...) queries compile to:

MATCH("title", "body") AGAINST('voltro effect' IN NATURAL LANGUAGE MODE)

The IN NATURAL LANGUAGE MODE is mysql's default-tier ranking — relevance-weighted, no Boolean operators. For Boolean mode (+voltro +effect), use the raw SQL escape hatch.

SQLite — native FTS5

The migrator creates a native FTS5 virtual table named <table>_<index>_fts plus three triggers that keep it in lockstep with the base table on every INSERT / UPDATE / DELETE:

CREATE VIRTUAL TABLE IF NOT EXISTS "posts_postSearch_fts"
  USING fts5("title", "body", row_id UNINDEXED, tokenize='porter');
-- + AFTER INSERT / UPDATE / DELETE triggers syncing row_id = posts.id

row_id carries the base row's string id (FTS5's integer rowid can't hold a TypeID). .matching(...) queries compile to:

"id" IN (SELECT "row_id" FROM "posts_postSearch_fts" WHERE "posts_postSearch_fts" MATCH 'voltro effect')

tokenize='porter' gives stemming comparable to postgres' 'english' config + mysql's natural-language mode.

MSSQL — ranked LIKE fallback

MSSQL native full-text needs a server-managed CREATE FULLTEXT CATALOG (operations work, not always available). Rather than require that, the framework emits a portable ranked-LIKE search at query time — no catalog, no CONTAINS, works on every MSSQL install. The migrator emits no FTS DDL (every base column is already present) and notes the fallback at boot:

[voltro:migrate] full-text index 'postSearch' on 'posts':
  mssql uses a ranked LIKE search at query time (no native FTS catalog required).

.matching(...) compiles to a per-column case-insensitive LIKE disjunction:

(LOWER("title") LIKE LOWER('%voltro effect%') ESCAPE '\'
  OR LOWER("body") LIKE LOWER('%voltro effect%') ESCAPE '\')

This is substring matching, not tokenised FTS — it won't stem or rank on term frequency the way postgres/mysql/sqlite do. It's the honest portable path; for true server FTS on MSSQL set up a catalog + use raw SQL with CONTAINS.

Turso — the same LIKE fallback

Turso's MVCC mode has no virtual tables, so there is no FTS5 index to create. The migrator creates none, and the turso store runs .matching(...) as the same case-insensitive LIKE disjunction as mssql, with .rankBy() counting the columns that hit. Queries, the table and the .fullTextIndex() declaration stay the same on every dialect; only the ranking is coarser.

In-memory store — word match

The in-memory store (store: 'memory', and every @voltro/testing context) answers .matching(...) itself, so a test means what the query means on a SQL store: a row matches when every word of the query starts a word in one of the index's columns, case-insensitively. It does not stem. .rankBy() scores a weighted hit count (weights A 4, B 2, C 1, D 0.5; unweighted columns 1).

In migrations — created after the plan, never in it

The objects above are not schema operations. voltro dev, voltro db apply and voltro db deploy create them after the schema plan, on every dialect, and voltro db plan never lists them: after a deploy the next plan is empty, and a later deploy neither drops nor re-creates an index that exists. Removing a .fullTextIndex() declaration leaves its objects in place — drop them in a file migration if you want them gone.

Word beginnings — `{ prefix: true }`

queryFor(posts).matching('postSearch', 'tomato bed', { prefix: true })

Each word of the query then matches as a word beginning, and every word is required: tomato bed finds "Tomatoes in raised beds". Postgres compiles it to to_tsquery(cfg, 'tomato:* & bed:*'), MySQL/MariaDB to AGAINST('+tomato* +bed*' IN BOOLEAN MODE), SQLite to the FTS5 query "tomato"* "bed"*; MSSQL's LIKE fallback and the in-memory store already match that way. Only the letters and digits of the query reach the database — operators a user types (|, !, -, OR, NOT, quotes) are dropped, so no dialect's query syntax can be injected. A query with no letters or digits falls back to the plain match.

Configuring per-language

.fullTextIndex('postSearch', ['title', 'body'], {
  config:  'german',                        // postgres tsvector config
  weights: { title: 'A', body: 'B', tags: 'C' },
})

config defaults to 'english'. Postgres ships configs for ~20 languages; install additional ones via CREATE TEXT SEARCH CONFIGURATION if needed. The framework emits the config name verbatim into the to_tsvector(...) call.

weights is ignored on non-postgres dialects (no per-column weight concept in FULLTEXT INDEX).

Multi-column with per-column weights

.fullTextIndex('postSearch', ['title', 'body', 'tags'], {
  weights: { title: 'A', body: 'B', tags: 'C' },
})

Postgres combines them in the generated tsvector — a query that matches title ranks higher than one that only matches tags.

Relevance ranking — `.rankBy()`

Chain .rankBy() after .matching(...) to surface a relevance score column AND order results best-match-first — on every dialect:

queryFor(database.posts)
  .matching('postSearch', input.query)
  .rankBy()                                  // adds a `rank` column + ORDER BY rank DESC
  .limit(20)

Each returned row carries a numeric rank (higher = better). The score expression is per-dialect, but the API is identical:

Dialect	Score expression
postgres	`ts_rank(<tsvCol>, plainto_tsquery(...))`
mysql / mariadb	`MATCH(cols) AGAINST(...)` score
sqlite	`-bm25(<fts>)` (negated so higher = better)
mssql	count of columns whose `LOWER LIKE` the query
memory	weighted count of word hits

Options:

.rankBy({ alias: 'score' }) — name the column score instead of rank.
.rankBy({ order: false }) — surface the score column WITHOUT forcing ORDER BY (combine with your own .orderBy(...)).

// Score column, but sort by recency with relevance as a secondary key:
queryFor(database.posts)
  .matching('postSearch', input.query)
  .rankBy({ order: false })
  .orderBy('createdAt', 'desc')
  .limit(20)

On postgres the ts_rank reads the same stored tsvector column the GIN index covers, so ranking stays index-accelerated.

Querying

.matching('indexName', 'query') is composable with other chain methods:

queryFor(database.posts)
  .where(eq('orgId', oid))                  // pre-filter
  .matching('postSearch', input.query)       // FTS
  .orderBy('createdAt', 'desc')              // tie-breaker
  .limit(20)

Multiple .matching() calls override — FTS is a single-string concept, no AND-merge across queries.

Limitations

MSSQL is a ranked LIKE fallback, not tokenised FTS. It matches substrings (LOWER(col) LIKE '%query%') and ranks by per-column hit count — no stemming, no term-frequency weighting. For true server FTS on MSSQL, set up a CREATE FULLTEXT CATALOG and use raw SQL with CONTAINS.
Boolean / phrase queries on postgres — .matching(...) uses plainto_tsquery which strips operators. For Boolean (+voltro -effect) drop to store.raw with to_tsquery(...):
```
import { sql } from '@voltro/database/sql'
await ctx.store.raw!<{ id: string }>(sql`
  SELECT id FROM posts
  WHERE "postSearch_tsv" @@ to_tsquery(${input.query})
`, { dependsOn: ['posts'] })
```
The query string binds as a parameter; to_tsquery parses its operators (+, -, &, |, <->).
Reactivity — FTS subscriptions ARE reactive on postgres (the tsvector column is a regular column the dispatcher tracks). On mysql/mariadb the MATCH AGAINST predicate isn't bucketed into the matcher's index — subscriptions re-evaluate on every write to the source columns.

When NOT to use

Trigram / fuzzy match — postgres' pg_trgm extension is a different access pattern. Install + use raw SQL.
Vector search — see Vector columns for embeddings-based semantic search.
Substring search on a small table — LIKE '%term%' is fine up to ~100k rows. Don't carry the FTS overhead for tiny tables.

Full-text search

#Quick start

#What the framework emits

#Postgres — tsvector + GIN

#MySQL / MariaDB — FULLTEXT INDEX

#SQLite — native FTS5

#MSSQL — ranked LIKE fallback

#Turso — the same LIKE fallback

#In-memory store — word match

#In migrations — created after the plan, never in it

#Word beginnings — { prefix: true }

#Configuring per-language

#Multi-column with per-column weights

#Relevance ranking — .rankBy()

#Querying

#Limitations

#When NOT to use

#See also