Full-Text Search
Add search across text fields using SQLite FTS5. Configure in your table definition, query with the @@ operator.
Config
typescript
{
name: 'articles',
autoSetUid: true,
fields: [
...baseFields,
{ name: 'title', type: 'text', sqlType: 'text', notNull: true },
{ name: 'content', type: 'editor', sqlType: 'text' },
{ name: 'tags', type: 'text', sqlType: 'text' },
{ name: 'author_id', type: 'relation', sqlType: 'text',
foreignKey: { table: 'users', column: 'id' } },
],
triggers: [createdTrigger, updatedTrigger],
// FTS5 index — searches title, content, and tags
fullTextSearch: {
fields: ['title', 'content', 'tags'],
tokenize: 'trigram', // good for partial matches and non-English text
},
extensions: [{
name: 'rules',
listRule: 'true',
viewRule: 'true',
createRule: 'auth.uid != null',
updateRule: 'auth.uid != null',
deleteRule: 'auth.uid != null',
} as TableRulesExtensionData],
}Search queries
The @@ operator triggers an FTS5 MATCH query. Results are automatically ordered by relevance (rank).
bash
# Search across all indexed fields
curl 'http://localhost:8787/api/v1/table/articles/list' \
-H 'Content-Type: application/json' \
-d '{"where": "articles @@ \"search term\""}'
# Search a specific column
curl 'http://localhost:8787/api/v1/table/articles/list' \
-H 'Content-Type: application/json' \
-d '{"where": "title @@ \"search term\""}'
# Combine search with other filters
curl 'http://localhost:8787/api/v1/table/articles/list' \
-H 'Content-Type: application/json' \
-d '{"where": "articles @@ \"typescript\" & author_id == \"USER_ID\""}'Tokenizer options
| Tokenizer | Best for | Example |
|---|---|---|
unicode61 | General text (default) | Full word matches across languages |
porter | English text | Stems words: "running" matches "run" |
trigram | Partial matches, autocomplete | "typ" matches "typescript" |
ascii | ASCII-only text | Fast, simple matching |