Skip to content

Text Search ​

Full-text search lets you search across one or more string fields with ranked results and optional field weighting. Define your search indexes in the .as schema, then query with a simple API — the adapter handles the engine-specific implementation.

Defining a Fulltext Index ​

Fulltext search indexes are defined with @db.index.fulltext in your .as schema. See Indexes & Constraints — Full-Text Search Index for the annotation syntax, composite indexes, and field weighting.

Here's a quick example:

atscript
@db.table 'articles'
export interface Article {
    @meta.id
    id: number

    @db.index.fulltext "content_idx", 3
    title: string

    @db.index.fulltext "content_idx"
    body: string
}

Fields sharing the same index name form a composite full-text index. The optional weight argument controls relevance ranking — here, matches in title score 3× higher.

Integer fields: exact-number match ​

A fulltext index may also list an integer field (number.int and its sizes, a number with @expect.int, or a @db.default.increment key). Integers are never part of the engine's text index — they are matched by exact number, OR'd with the text match, when the whole search term is a whole number:

atscript
@db.table 'tickets'
export interface Ticket {
    @meta.id
    @db.index.fulltext "ticket_idx"   // the primary key is index-backed
    id: number.int

    @db.index.fulltext "ticket_idx"
    title: string

    @db.index.fulltext "ticket_idx"
    @db.index.unique                  // the first field of a plain/unique index
    refNo: number.int
}

search("2946") returns rows whose title contains the word 2946, plus the row whose id or refNo equals 2946. It does not return 29461277 — a native index matches whole numbers only; for substring matching on numbers use @db.column.searchable on the integer field (no native index).

TermNumeric branch?Notes
2946 (surrounding spaces are trimmed)yessurrounding blanks are trimmed
-12yesnegative numbers are exact matches too
02946, +5, -0, 1.5, 1e3, 12 34nonot a plain integer literal (text match only)
9007199254740993nobeyond the safe-integer range
invoice 2946noonly the whole term counts

Rules:

  • The integer member must be index-backed, so the equality does not scan: the primary key (the first @meta.id), _id, or the first field of a @db.index.plain / @db.index.unique. A schema that breaks this is a compile error in the editor and a throw when the table metadata is built.
  • Integer members belong to one index each: a search ORs only the integer members of the index it searches (on every adapter, MongoDB included). A search naming no index uses the first @db.index.fulltext that has at least one text member (the first index when none has), so an integer-only index declared first is not the default; name it with $index to search it alone.
  • @db.writeOnly cannot be combined with @db.index.fulltext or @db.column.searchable (compile error; a search hit would reveal the sealed value).
  • Floats (number, number.double), decimals, timestamps and @db.column.precision fields are refused with a diagnostic — filter those by range instead.
  • A weight on an integer member is ignored (the editor warns).
  • An index of integer members only needs no engine artifact (no DDL) but still exists: it is listed by getSearchIndexes(), makes the table natively searchable, and a term that is not a whole number matches nothing. Because the table is then natively searchable, @db.column.searchable fields are no longer consulted (native search wins).
  • Ordering of a mixed text + number result is adapter-defined (MongoDB ranks text hits first); pass $sort for a stable order.
  • Every entry point — search(), searchWithCount(), /query, /pages, $count, grouped $search aggregates, query targets and resolveQuery — applies the same predicate, so counts and rows agree.

Language and term matching ​

Since 0.1.168. Two interface-level annotations tune a fulltext index. Without an index name they apply to every @db.index.fulltext index of the interface; with one, to that index (a named one wins over the table-wide one):

atscript
@db.table 'articles'
@db.index.fulltext.language "russian"
@db.index.fulltext.match "any", "title_idx"
export interface Article {
    @meta.id
    id: number

    @db.index.fulltext "title_idx"
    title: string

    @db.index.fulltext "body_idx"
    body: string
}

@db.index.fulltext.language "<language>", "<index>"? sets the text language — which words share a stem and which are stop words. The values are none (no stemming, no stop words), danish, dutch, english, finnish, french, german, hungarian, italian, norwegian, portuguese, romanian, russian, spanish, swedish and turkish; the editor completes them. Without it each database keeps its default.

AdapterWhat the language doesDefault
PostgreSQLThe text search configuration of the index and of every query (none → simple), or the one textSearchConfigs maps the language to.english
MongoDBThe text index's default_language.english
SQLiteenglish builds the FTS5 table with the Porter stemmer. FTS5 has no other stemmer built in: any other language indexes words unstemmed (unicode61) and schema sync logs a warning, unless ftsTokenizers gives it a tokenizer.unicode61, no stemming
MySQLIgnored, with a warning at schema sync: FULLTEXT has no per-index language.the server's stop words

Changing the language rebuilds the index on the next schema sync (PostgreSQL, MongoDB, SQLite), and so does changing the adapter's textSearchConfigs / ftsTokenizers entry for it. An index without the annotation keeps the index it has.

@db.index.fulltext.match "all" | "any", "<index>"? decides whether a search needs every term or any term:

Adapter"all""any"Without the annotation
PostgreSQLwebsearch_to_tsquery with the text as typed (terms AND-ed)websearch_to_tsquery with the terms OR-ed (a leading - dropped)plainto_tsquery (all)
SQLiteFTS5 terms AND-edFTS5 terms OR-edall
MySQLIN BOOLEAN MODE, every word required (+word) except short and stop wordsnatural-language modeany
MongoDBeach word (split on punctuation) becomes a phrase, so every word is required (matched as written, without stemming)$text as typedany

Results stay in relevance order. Under match "all" PostgreSQL reads the web-search syntax: a "quoted phrase", or between words, -word to exclude; under "any" a leading - is dropped, since one excluded term OR-ed with the others would match nearly every row. On MySQL, the text is split into words the way InnoDB splits it — at every character that is not a letter, a number, a combining mark or _, so the, quick is the words the and quick. Under "all", words shorter than 3 characters and InnoDB's default stop words stay optional — the server does not index them, so requiring one would match nothing; a server with a larger innodb_ft_min_token_size or its own stop-word list can still miss such words.

Several languages ​

A fulltext index has one language. There is no annotation for several languages in one index; what each database can do instead:

  • PostgreSQL — one text search configuration per index. A configuration maps each kind of word to its own dictionaries: the built-in russian, for example, already stems words of plain ASCII letters with english_stem. For other combinations create a custom configuration and map the index's language to it with textSearchConfigs.
  • MongoDB — one text index per collection, with one default_language. MongoDB itself reads a document field named language as that document's language (and rejects a write whose language value it does not support); a search is stemmed with the index's language. Naming another field for this (language_override) is not supported by the adapter.
  • SQLite — one FTS5 tokenizer per index: the one ftsTokenizers gives the index's language. Whether it handles more than one language depends on the tokenizer you load.
  • MySQL — FULLTEXT does not stem in any language, so text in any language is matched by its word forms as written.

On PostgreSQL and SQLite, to search texts in different languages with each one's stemming, give each language its own field and fulltext index (@db.index.fulltext.language "<language>", "<index>"), and pick the index with $index.

Programmatic API ​

Checking Capabilities ​

typescript
const articles = db.getTable(Article);

// Does this table have fulltext indexes?
articles.isSearchable(); // true

// List all search indexes
articles.getSearchIndexes();
// [{ name: 'content_idx', description: '...', type: 'text' }]

isSearchable() counts text indexes only. getSearchIndexes() also lists vector indexes (type: 'vector') so index pickers can offer them, but a vector index answers vectorSearch() and nothing else: a table whose only search declaration is @db.search.vector reports isSearchable() === false and rejects search() with DbError("INVALID_QUERY", [{ path: "$search" }]). Vector capability has its own probe — isVectorSearchable().

typescript
const results = await articles.search("typescript tutorial", {
  filter: {},
  controls: { $limit: 20 },
});

The search() method returns records ranked by relevance.

Ranking ​

Without a $sort, results come in relevance order on every adapter (since 0.1.165 — before, PostgreSQL and SQLite returned matches in storage order): PostgreSQL ranks with ts_rank, SQLite with FTS5 bm25(), MySQL by MATCH … AGAINST, MongoDB by textScore. The weights of @db.index.fulltext apply on PostgreSQL, SQLite and MongoDB. When the whole term is a whole number, rows matched by an integer member come first. The primary key breaks ties (_id on MongoDB's classic text index), so offset pages are stable — except an Atlas $search, which keeps Atlas's own score order with no tie-breaker. An explicit $sort replaces relevance order.

On PostgreSQL the rank recomputes to_tsvector for every matched row before LIMIT applies (the GIN index serves the match, not the rank), so a broad term over a large table costs a pass over all its matches. Narrow with a filter, or turn ranking off with $rank: false when relevance does not matter.

Unranked search ​

Since 0.1.175. $rank: false matches like any search but computes no relevance score: rows come in $sort order, or in primary-key order without a $sort (_id on MongoDB; a view without a primary key keeps the database's order).

typescript
// e.g. collecting candidate ids that your app ranks itself
const rows = await articles.search("typescript", {
  filter: {},
  controls: { $rank: false, $select: ["id"], $limit: 500 },
});
  • No adapter computes a rank expression: no ts_rank (PostgreSQL), bm25() (SQLite), MATCH … AGAINST order (MySQL) or textScore (MongoDB). Rows matched by an integer member are not moved first either.
  • On a MongoDB Atlas $search the engine still scores inside its stage; the results come in _id order. That order is an extra sort over every hit before $skip / $limit, so on Atlas an unranked search can be slower than a ranked one.
  • searchWithCount() and /pages order the page the same way; the count does not change.
  • vectorSearch() and geoSearch() reject it with INVALID_QUERY: their results are the nearest rows, so similarity or distance is the order.
  • On a table without native search (the @db.column.searchable fallback), reads are not ranked anyway, and $rank: false changes nothing.

Search with Count ​

For paginated search results:

typescript
const result = await articles.searchWithCount("typescript tutorial", {
  filter: {},
  controls: { $skip: 20, $limit: 10 },
});

console.log(result.data); // matching articles
console.log(result.count); // total matches

Targeting a Specific Index ​

When a table has multiple fulltext indexes, pass the name declared in @db.index.fulltext:

typescript
const results = await articles.search(
  "tutorial",
  {
    filter: {},
    controls: {},
  },
  "content_idx",
);

If omitted, the default index answers: the first one with a text member. getSearchIndexes() (and /meta searchIndexes) list each index under its declared name; older names that still resolve are in the entry's aliases — the internal atscript__fulltext__<name> key on PostgreSQL and MySQL, DEFAULT for MongoDB's default index (since 0.1.165; before, PostgreSQL and MySQL resolved only the key and MongoDB's default index only as DEFAULT). Use the declared name. An unknown name throws Search index "<name>" not found.

Combining Search with Filters ​

Search results can be further filtered by regular query conditions:

typescript
const results = await articles.search("typescript", {
  filter: { status: "published", category: "tutorials" },
  controls: { $limit: 10 },
});

The text search narrows by relevance, then the filter conditions narrow further. Sorting, pagination, and field selection all work as usual.

HTTP Access ​

Text search is available via HTTP using the $search URL parameter on the /query or /pages endpoints:

GET /articles/query?$search=typescript%20tutorial
GET /articles/query?$search=typescript%20tutorial&$index=content_idx
GET /articles/query?$search=database&category=tech
GET /articles/pages?$search=typescript&$page=1&$size=20
GET /articles/query?$search=typescript&$rank=false

The $search parameter provides the search text. Add $index to target a specific fulltext index when multiple exist, and $rank=false for an unranked search. Regular filter parameters (like category=tech) combine with search results using AND logic.

See HTTP — Advanced for the full URL query syntax.

Adapter Implementations ​

Each adapter maps @db.index.fulltext to its native full-text search engine:

AdapterIndex typeSearch mechanismPer-field weights?Integer members
PostgreSQLGIN index on to_tsvector('<language>', ...)plainto_tsquery() (websearch_to_tsquery() under match)Yes — in the ts_rank order (setweight buckets A–D)OR "col" = CAST(? AS BIGINT)
MongoDBClassic text index (works on all deployments)$textYes — field weights$or of $text and { col: { $eq: n, $type: "number" } }
SQLiteFTS5 virtual tableMATCHYes — bm25() column weightsOR "col" = ? next to the FTS5 rowid lookup
MySQLFULLTEXT indexMATCH ... AGAINST (natural language; boolean under match "all")Noprimary-key set of a UNION of the MATCH and = n arms

INFO

All adapters expose the same search() and searchWithCount() API — engine differences are handled internally. See individual adapter pages for engine-specific details and configuration options.

MongoDB Atlas goes beyond word matching with autocomplete/typeahead, query-time fuzzy tolerance, and a per-index match strategy — declared with the @db.mongo.search.* annotations (separate from @db.index.fulltext, Atlas-only, queried via $search). See MongoDB → Atlas Search.

Term syntax per engine ​

The $search text is passed to the engine, so each engine's own operator syntax applies:

  • SQLite (FTS5) — the term is quoted before it reaches MATCH, so no input can raise an FTS5 syntax error (-2946, a AND, title:x, ( are plain text). Words are AND-ed (OR-ed under match "any"); a "quoted phrase" stays a phrase; a trailing * on a word is a prefix match (quok*). AND / OR / NOT / NEAR and column filters are not operators — they are searched as words.
  • MongoDB ($text) — operators apply natively: -word excludes a word, "a phrase" matches the phrase, and a plain list of words matches any of them (OR). A term made only of a negation (-word) matches nothing by itself; the integer equality branch is unaffected.
  • PostgreSQL — plainto_tsquery: words are AND-ed, punctuation is ignored. Under match, websearch_to_tsquery: "phrase", or, -word.
  • MySQL — natural-language mode: relevance-ranked, words OR-ed, short and stop words ignored. Under match "all", boolean mode with every word required except short and stop words; words are split at punctuation as InnoDB splits them, and the user's own + - < > ( ) ~ * " @ are text.

Next Steps ​

Released under the MIT License.