search_index.sql in SQL

A working search feature: an index over an existing table, kept in sync, queried and ranked.

-- Full-text search over posts, from empty to ranked results.

DROP TRIGGER IF EXISTS posts_search_insert;
DROP TRIGGER IF EXISTS posts_search_delete;
DROP TRIGGER IF EXISTS posts_search_update;
DROP TABLE IF EXISTS posts_search;

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

CREATE TRIGGER posts_search_insert
AFTER INSERT ON posts
BEGIN
    INSERT INTO posts_search (rowid, title, body)
    VALUES (NEW.id, NEW.title, NEW.body);
END;

CREATE TRIGGER posts_search_delete
AFTER DELETE ON posts
BEGIN
    INSERT INTO posts_search (posts_search, rowid, title, body)
    VALUES ('delete', OLD.id, OLD.title, OLD.body);
END;

CREATE TRIGGER posts_search_update
AFTER UPDATE ON posts
BEGIN
    INSERT INTO posts_search (posts_search, rowid, title, body)
    VALUES ('delete', OLD.id, OLD.title, OLD.body);
    INSERT INTO posts_search (rowid, title, body)
    VALUES (NEW.id, NEW.title, NEW.body);
END;

INSERT INTO posts (id, title, body, published_at)
VALUES (1, 'Indexes and plans',
        'An index the planner never chooses is only a write cost.',
        '2026-07-01'),
       (2, 'Window functions',
        'A window sees the rows around it without collapsing them.',
        '2026-07-14'),
       (3, 'Full-text search',
        'FTS5 indexes words, so a search does not scan every row.',
        '2026-07-22');

-- Populate from the content table in one go.
INSERT INTO posts_search (posts_search)
VALUES ('rebuild');

-- Ranked results with a preview, title matches weighted above body.
SELECT
    p.id,
    p.title,
    p.published_at,
    ROUND(BM25(posts_search, 8.0, 1.0), 3) AS score,
    SNIPPET(posts_search, 1, '[', ']', '...', 10) AS preview
FROM posts_search
JOIN posts AS p
    ON p.id = posts_search.rowid
WHERE posts_search MATCH 'index* OR search'
ORDER BY rank
LIMIT 10;

How it works

content= points the index at the table so the text is stored once.
Three triggers keep it current; rebuild recovers if one ever misses.
The query end weights the title, ranks by bm25, and returns a snippet.

Keywords and builtins used here

AFTER
AS
BEGIN
BY
CREATE
DELETE
DROP
END
EXISTS
FROM
IF
INSERT
INTO
JOIN
LIMIT
MATCH
NEW
OLD
ON
ORDER
SELECT
TABLE
TRIGGER
UPDATE
USING
VALUES
WHERE

The run, in numbers

Lines: 66
Characters to type: 1763
Tokens: 316
Three-star pace: 100 tpm

At the three-star pace of 100 tokens a minute, this run takes about 190 seconds.

Type this snippet

Step 1 of 2 in Encore, step 13 of 14 in JSON & search.

← Previous Next →