Full-text search

.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.

#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

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.

#See also

• Generated columns — the FTS pattern is "generated tsvector + GIN expression index"
• Indexes — .expressionIndex({ kind: 'gist' }) for the GIN/GiST plumbing under FTS
• Vector columns — for embedding-based semantic search (different problem from keyword FTS)