Full-text search is an experimental feature and requires the fts feature to be enabled at compile time.
Creating an FTS index
Create an FTS index on one or more text columns using the USING fts syntax:
CREATE INDEX idx_articles ON articles USING fts (title, body);
FTS changes within a transaction are not visible to queries until COMMIT. ROLLBACK correctly discards both table changes and FTS index changes together.
Tokenizers
Configure how text is tokenized using the WITH clause:
CREATE INDEX idx_products ON products USING fts (name) WITH (tokenizer = 'ngram');
| Tokenizer | Description | Use case |
|---|
default | Lowercase, punctuation split, 40-character limit | General English text |
raw | No tokenization — exact match only | IDs, UUIDs, tags, categories |
simple | Basic whitespace and punctuation split, no lowercasing | Simple text without case normalization |
whitespace | Split on whitespace only | Space-separated tokens |
ngram | 2–3 character n-grams | Autocomplete, substring matching |
-- default: "Hello World" → ["hello", "world"]
-- Searches for "hello" or "HELLO" both match
-- raw: "user-123" → ["user-123"]
-- Only the exact string "user-123" matches; "user" does not
-- ngram: "iPhone" → ["iP", "iPh", "Ph", "Pho", "ho", "hon", "on", "one", "ne"]
-- Search for "Pho" matches documents containing "iPhone"
Field weights
Configure the relative importance of indexed columns for BM25 relevance scoring:
-- Title matches are 2x more important than body matches
CREATE INDEX idx_articles ON articles USING fts (title, body)
WITH (weights = 'title=2.0,body=1.0');
-- Combine tokenizer and weights
CREATE INDEX idx_docs ON docs USING fts (name, description)
WITH (tokenizer = 'simple', weights = 'name=3.0,description=1.0');
- The default weight for all fields is
1.0.
- Weights affect the BM25 score returned by
fts_score().
- Higher weights increase the score contribution from that field.
- Weights must be positive numbers.
Query functions
Turso provides three FTS functions for querying indexed text.
fts_match(col1, col2, ..., 'query')
Returns a boolean indicating whether the row matches the query. Use in WHERE clauses.
SELECT id, title FROM articles WHERE fts_match(title, body, 'database');
fts_score(col1, col2, ..., 'query')
Returns the BM25 relevance score for the row. Higher scores indicate stronger relevance.
SELECT fts_score(title, body, 'database') AS score, id, title
FROM articles
WHERE fts_match(title, body, 'database')
ORDER BY score DESC
LIMIT 10;
fts_highlight(col1, col2, ..., before_tag, after_tag, 'query')
Returns text with matching terms wrapped in the specified tags. Multiple columns are concatenated with spaces. Returns NULL if the query, before_tag, or after_tag is NULL. Returns the original text unchanged if no matches are found.
SELECT fts_highlight(body, '<mark>', '</mark>', 'database') AS highlighted
FROM articles
WHERE fts_match(title, body, 'database');
-- Returns: "Learn about <mark>database</mark> optimization"
Query syntax
The query string passed to fts_match and fts_score uses Tantivy’s query parser syntax.
| Syntax | Example | Description |
|---|
| Single term | database | Match documents containing “database” |
| Multiple terms | database sql | Match documents with “database” OR “sql” |
| AND | database AND sql | Match documents with both terms |
| NOT | database NOT nosql | Match “database” but exclude “nosql” |
| Phrase | "full text search" | Match the exact phrase |
| Prefix | data* | Match terms starting with “data” |
| Column filter | title:database | Match “database” only in the title field |
| Boosting | title:database^2 body:database | Boost matches in title field |
Complex queries
FTS functions work alongside additional WHERE conditions:
SELECT id, title, fts_score(title, body, 'Rust') AS score
FROM articles
WHERE fts_match(title, body, 'Rust')
AND category = 'tech'
AND published = 1
ORDER BY score DESC;
fts_match() or fts_score() are detected in a query, the FTS index is used even with additional SELECT columns, extra WHERE conditions, or non-score ORDER BY expressions.
Index maintenance
Use OPTIMIZE INDEX to merge Tantivy segments into a single optimized segment. This improves query performance and reduces storage overhead, particularly after bulk inserts.
-- Optimize a specific FTS index
OPTIMIZE INDEX idx_articles;
-- Optimize all FTS indexes
OPTIMIZE INDEX;
Turso FTS uses NoMergePolicy — segments are not merged automatically. Call OPTIMIZE INDEX manually when needed.
Limitations
| Limitation | Description |
|---|
No MATCH operator | Use fts_match() instead of WHERE table MATCH 'query' |
| No read-your-writes in transaction | FTS changes are visible only after COMMIT |
| No automatic segment merging | Use OPTIMIZE INDEX for manual segment merging |
| No snippet function | snippet() is not available; use fts_highlight() instead |
Example: document search
-- Create a documents table
CREATE TABLE documents (
id INTEGER PRIMARY KEY,
title TEXT,
content TEXT,
category TEXT
);
-- Create FTS index with weighted fields
CREATE INDEX fts_docs ON documents USING fts (title, content)
WITH (weights = 'title=2.0,content=1.0');
-- Insert documents
INSERT INTO documents VALUES
(1, 'Introduction to SQL', 'Learn SQL basics and queries', 'tutorial'),
(2, 'Advanced SQL Techniques', 'Complex joins and optimization', 'tutorial'),
(3, 'Database Design', 'Schema design best practices', 'architecture');
-- Search with relevance ranking and highlighted snippets
SELECT
id,
title,
fts_score(title, content, 'SQL') AS score,
fts_highlight(content, '<b>', '</b>', 'SQL') AS snippet
FROM documents
WHERE fts_match(title, content, 'SQL')
ORDER BY score DESC;