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.

Without $fields, $text searches the columns of the entity’s fulltext index, declared with @Index(['name', 'description'], { type: 'fulltext' }). On SQL, an entity with no such index, or more than one, has to name $fields. PostgreSQL has no fulltext index type and its migrations refuse one, so name $fields there.

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 like "wireless keyboard", or, -negation) and never raises a syntax error, unlike to_tsquery, which rejects even a plain two-word search.

Text-Search Configuration (PostgreSQL)

$config selects the text-search configuration (the dictionary that drives stemming and stop-words) for 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' },
  },
});

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.
MSSQL	Not supported. CONTAINS needs a full-text catalogue, which UQL does not create, so $text is refused.

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.