Full-Text Search

$text searches natural-language text using each database’s own full-text engine. It goes at the top level of $where, alongside regular field conditions.

You write

import { pool } from './uql.config.js';
import { Item } from './shared/models/index.js';

const items = await pool.findMany(Item, {
  $where: {
    $text: { $fields: ['name', 'description'], $value: 'wireless keyboard' },
    isActive: true,
  },
});

$fields lists the columns to search; $value is the user’s query text.

Per-Dialect SQL

PostgreSQL / CockroachDB

SELECT * FROM "Item"
WHERE to_tsvector("name" || ' ' || "description") @@ websearch_to_tsquery($1)
  AND "isActive" = $2

MySQL / MariaDB

SELECT * FROM `Item` WHERE MATCH(`name`, `description`) AGAINST(?) AND `isActive` = ?

SQLite

SELECT * FROM `Item` WHERE `Item` MATCH {`name` `description`} : ? AND `isActive` = ?

MongoDB maps $text to its own $text operator ({ $text: { $search: value } }).

PostgreSQL parses your text the way a search box does

websearch_to_tsquery accepts free-form input - quoted phrases ("wireless keyboard"), or, and -negation - and never raises a syntax error, unlike to_tsquery, which rejects anything it cannot parse including a plain two-word search.

Text-Search Configuration (PostgreSQL)

$config selects the text-search configuration - the dictionary that drives stemming and stop-words - and is applied to both the document and the query. It defaults to the server’s default_text_search_config.

You write

const items = await pool.findMany(Item, {
  $where: { $text: { $fields: ['name'], $value: 'running shoes', $config: 'english' } },
});

Generated SQL (PostgreSQL)

SELECT * FROM "Item" WHERE to_tsvector($1::regconfig, "name") @@ websearch_to_tsquery($1::regconfig, $2)

The value is bound as a parameter (never interpolated) and its placeholder is reused by both calls. Other dialects ignore $config.

Index Requirements

Full-text search needs dialect-specific setup - $text generates the query, not the index:

Dialect	Requirement
PostgreSQL / CockroachDB	Works without an index, but to_tsvector(col) computed per row cannot use one. For large tables, add a stored tsvector column (or expression index) with a GIN index.
MySQL / MariaDB	Requires a FULLTEXT index covering exactly the columns listed in $fields, in that order - otherwise the query errors with “Can’t find FULLTEXT index matching the column list”. Declare it with @Index(['title', 'body'], { type: 'fulltext' }) and UQL generates CREATE FULLTEXT INDEX for you.
SQLite	Requires an FTS5 virtual table; MATCH only applies to one.
MongoDB	Requires a text index, which declares the fields it covers - so $fields is accepted for API consistency and ignored, as $distance is for $vectorSearch.

Combining with semantic search

Full-text search finds exact term matches; vector search finds semantically similar text. Hybrid search runs both and fuses the rankings - MongoDB does this natively with $rankFusion, and on PostgreSQL it is a reciprocal-rank-fusion query over a tsvector match plus a pgvector distance sort.

---

Next Steps

• Semantic Search: Vector similarity when keywords are not enough.
• Indexes: The full-text indexes these queries rely on.
• Comparison Operators: Combining $text with ordinary conditions.
• Querier API: The full query API.