Full-Text Search

$text runs a full-text search on each database’s own engine. Declare a fulltext index on the entity, and a query passes only the search text:

You write

import { f, Entity, Field } from 'uql-orm';
import { pool } from './uql.config.js';

@Entity({
  indexes: (listing, index) => [
    index([{ column: listing.title, weight: 3 }, listing.description], {
      type: 'fulltext',
      config: 'spanish',
    }),
  ],
})
export class Listing {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().nullable()) title?: string | null;
  @Field(f.string().nullable()) description?: string | null;
  @Field(f.string().nullable()) status?: string | null;
  @Field(f.number().nullable()) createdAt?: number | null;
}

const found = await pool.findMany(Listing, {
  $where: { status: 'active', $text: { $value: 'wireless keyboard' } },
  $sort: { $text: 'desc', createdAt: 'desc' },
  $limit: 20,
});

$text sits at the top level of $where, beside ordinary conditions, and searches the columns of the entity’s fulltext index in the index’s language. $sort: { $text: 'desc' } ranks by the same search, so this returns the most relevant listings first, and the newest among equals. A title match outranks the same word in the description, because the title has weight 3.

To search other columns, pass $fields, keyed like $select so a typo does not compile. $config picks the language for this search. On SQL, an entity with no fulltext index, or more than one, must pass $fields.

await pool.findMany(Item, {
  $where: {
    $text: {
      $fields: { name: true },
      $value: 'running shoes',
      $config: 'english',
    },
  },
});

Ranking by relevance

The sort uses each engine’s own score: TS_RANK on PostgreSQL and CockroachDB, MATCH ... AGAINST on MySQL and MariaDB, BM25 on SQLite, textScore on MongoDB. It takes either direction, and later sort keys break ties. It needs a $text at the root of the same $where, and throws without one, since a search nested in $or or $not has no single score.

To read the score, give it a name with $project, as a vector search does for its distance, and it is on each row as a number:

const items = await pool.findMany(Item, {
  $where: { $text: { $value: 'wireless keyboard' } },
  $sort: { $text: { $project: 'score' } },
});
// items[0].score is the highest relevance

The name must not clash with any field, column or relation of the entity. Pass $order: 'asc' to put the least relevant first.

A populated relation searches and ranks its own rows the same way, $populate: { reviews: { $where: { $text: { $value: 'quiet' } }, $sort: { $text: { $project: 'score' } } } }. MongoDB throws there, since its $text runs only on the queried collection.

Column weights

A column without a weight has weight 1, and a match counts its column’s weight, as MongoDB’s textScore does. Weights are whole numbers from 1 to 99999. They only change the ranking, never which rows match.

PostgreSQL and CockroachDB add each heavier column’s own TS_RANK to the score. MySQL and MariaDB add each heavier column’s own MATCH, which needs a FULLTEXT index over that column alone, so migrations create one per column heavier than the lightest. MongoDB stores the weights in its text index, and drift:check compares them.

MySQL and MariaDB rebuild the table

InnoDB fills a fulltext index added to a table that already has one, and has rows, only once the table is optimized. Until then MariaDB scores it 0 and MySQL can fail the search. So a migration adding such an index follows it with OPTIMIZE TABLE, which rebuilds the table.

Language

The index’s config and a search’s $config set the language for stemming and stop words: 'english', 'spanish', or 'simple' for none, which is the default on every engine. PostgreSQL and CockroachDB use it as the text-search configuration for both the index and the query. MongoDB uses the same names for default_language and $language, mapping 'simple' to 'none', and drift:check reports an index built in another language. MySQL, MariaDB and SQLite ignore it and parse by their index.

What each engine runs

Migrations create the index from the same declaration on every engine that has one.

Engine	Index	Query
PostgreSQL / CockroachDB	GIN over TO_TSVECTOR(config, cols)	@@ WEBSEARCH_TO_TSQUERY($1); PLAINTO_TSQUERY on CockroachDB, which lacks the first
MySQL / MariaDB	FULLTEXT (cols), over exactly the columns searched, in order	MATCH(cols) AGAINST(?)
MongoDB	a text index, which declares its own fields, so $fields is ignored	{ $text: { $search } }
SQLite	none: you create an FTS5 virtual table yourself	table MATCH ?, each word quoted
SQL Server	refused: it needs a full-text catalog UQL does not create	refused

On PostgreSQL, $text reads input the way a search box does (quoted phrases, or, -negation) and never raises a syntax error. It also works without an index by scanning the table, so declare one for anything large. On MySQL and MariaDB, a search whose columns do not exactly match a FULLTEXT index fails with “Can’t find FULLTEXT index matching the column list”.

Combining with semantic search

Full-text search finds matching terms; vector search finds similar meaning. Hybrid search runs both and fuses the rankings: MongoDB has $rankFusion, and on PostgreSQL you write a reciprocal-rank-fusion query over a tsvector match and a pgvector distance.

Next steps

• Indexes: declaring fulltext indexes and their per-column options.
• Semantic Search: vector similarity when keywords are not enough.