SQLite FTS5.

Create

CREATE VIRTUAL TABLE posts_fts USING fts5(
    title, body,
    content='posts', content_rowid='id',
    tokenize='porter unicode61'
);

External content: keep main data in posts, only index in fts.

Triggers (keep in sync)

CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
    INSERT INTO posts_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, body) VALUES('delete', old.id, old.title, old.body);
    INSERT INTO posts_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, body) VALUES('delete', old.id, old.title, old.body);
END;

SELECT * FROM posts_fts WHERE posts_fts MATCH 'redis';
SELECT * FROM posts_fts WHERE posts_fts MATCH '"exact phrase"';
SELECT * FROM posts_fts WHERE posts_fts MATCH 'redis OR memcached';
SELECT * FROM posts_fts WHERE posts_fts MATCH 'redis AND tutorial';
SELECT * FROM posts_fts WHERE posts_fts MATCH 'NEAR(redis tutorial, 5)';
SELECT * FROM posts_fts WHERE posts_fts MATCH 'title:redis';

Join with main

SELECT p.* FROM posts p
JOIN posts_fts fts ON p.id = fts.rowid
WHERE fts.posts_fts MATCH 'redis';

Ranking (bm25)

SELECT *, bm25(posts_fts) AS rank
FROM posts_fts
WHERE posts_fts MATCH 'redis'
ORDER BY rank;     -- lower = better

Snippets / highlights

SELECT snippet(posts_fts, 1, '<b>', '</b>', '...', 10), bm25(posts_fts)
FROM posts_fts WHERE posts_fts MATCH 'redis';

SELECT highlight(posts_fts, 0, '<mark>', '</mark>')
FROM posts_fts WHERE posts_fts MATCH 'redis';

Rebuild

INSERT INTO posts_fts(posts_fts) VALUES('rebuild');

Tokenizers

  • unicode61 (default): handles Unicode.
  • porter: stemming.
  • ascii: ASCII only.
  • Custom tokenizer extension.

Prefix queries

SELECT * FROM posts_fts WHERE posts_fts MATCH 'redi*';

Performance

FTS5 is fast. Indexes word-by-word. Searches in milliseconds even for millions of docs.

Common mistakes

  • Forgetting to sync via triggers.
  • Mixing FTS5 syntax with regular WHERE.
  • Search query injection (escape special chars).

Read this next

If you want my FTS5 setup, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .