> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Full-Text Search

> Search and rank text with $text on PostgreSQL, CockroachDB, MySQL, MariaDB, SQLite and MongoDB, with one index declaration and per-column weights.

Source: https://uql-orm.dev/querying/full-text

`$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:

```ts title="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`.

```ts
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](https://uql-orm.dev/querying/semantic-search.md) does for its distance, and it is on each row as a number:

```ts
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](https://uql-orm.dev/migrations.md) 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](https://sqlite.org/fts5.html) virtual table yourself | `table MATCH ?`, each word quoted |
| SQL Server | refused: it needs a [full-text catalog](https://uql-orm.dev/mssql.md) 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](https://uql-orm.dev/querying/semantic-search.md) 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](https://uql-orm.dev/entities/indexes.md): declaring `fulltext` indexes and their per-column options.
- [Semantic Search](https://uql-orm.dev/querying/semantic-search.md): vector similarity when keywords are not enough.