Add Server Side Search to a Go Website Without Elastic

Most sites that ask for "search" are asking for something modest: a box in the header, a few hundred or a few thousand pages, and results that put the right page first. The reflex answer is a search server such as Elasticsearch, with a cluster to run, a mapping to design, a client library and a synchronisation job. That is the right tool for millions of documents and complex relevance. For a blog, a documentation site or a catalogue, it is a lot of machinery to avoid a feature your database already has.

SQLite ships a full-text search engine, FTS5, and the pure Go driver used in the earlier projects includes it. This project uses it to build search with stemming, ranking, highlighted snippets and search-as-you-type, and spends most of its attention on the part that goes wrong in practice: what happens to the text a visitor types. It continues the Markdown blog (whose posts are the documents), uses the storage setup from the URL shortener, and belongs to the web cluster that ends in a production HTTP server. Everything ran on Go 1.25.6 with modernc.org/sqlite v1.50.0.

What FTS5 gives you

An FTS5 table is a virtual table that indexes the text of its columns into an inverted index: for each word, the list of rows that contain it. Queries use a MATCH operator against the table, and a few built-in functions return rankings and highlighted excerpts. Four properties matter here.

Tokenization decides what a word is. The tokenizer in this project, porter unicode61 remove_diacritics 2, splits on anything that is not a letter or digit, applies Unicode case folding, strips accents, and then applies the Porter stemmer, which reduces English words to roots (leaking, leaked and leaks all become leak). That is why the query goroutine leak finds the article that says goroutines and leaked. The stemmer is English only; for other languages use unicode61 alone, accept exact-word matches, or index a stemmed copy of the text.

Ranking is bm25. The bm25() function scores each match by how often the terms appear in the document, how rare they are in the whole collection and how long the document is. It takes a weight per column, and the call bm25(docs, 8.0, 1.0) makes a hit in the title count eight times as much as one in the body, so an article titled "Slices explained" wins the query "slices" over an article that merely mentions slices once. One quirk: SQLite's bm25 returns negative numbers where more negative is a better match, so you sort ascending, and the values mean nothing outside comparison.

Snippets are generated for you. snippet() returns a short excerpt around the matches with markers you choose, which is what turns a result list from bare titles into something a person can scan.

Prefix queries make it feel instant. A term followed by * matches any word starting with it. If the last word of the query becomes a prefix, results update as the visitor types.

The index

package main

import (
	"context"
	"database/sql"
	"html"
	"strings"
	"unicode"

	_ "modernc.org/sqlite"
)

type Doc struct {
	Slug, Title, Body string
}

type Hit struct {
	Slug    string
	Title   string
	Snippet string // already HTML-escaped, with <mark> around the matches
	Rank    float64
}

type Index struct{ db *sql.DB }

func OpenIndex(dsn string) (*Index, error) {
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1)
	_, err = db.Exec(`CREATE VIRTUAL TABLE IF NOT EXISTS docs USING fts5(
		title, body, slug UNINDEXED, tokenize = 'porter unicode61 remove_diacritics 2')`)
	if err != nil {
		db.Close()
		return nil, err
	}
	return &Index{db: db}, nil
}

func (ix *Index) Close() error { return ix.db.Close() }

// Replace rebuilds the index from scratch inside one transaction, so searches never see a half-built index.
func (ix *Index) Replace(ctx context.Context, docs []Doc) error {
	tx, err := ix.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err := tx.ExecContext(ctx, `DELETE FROM docs`); err != nil {
		return err
	}
	for _, d := range docs {
		if _, err := tx.ExecContext(ctx, `INSERT INTO docs (title, body, slug) VALUES (?, ?, ?)`, d.Title, d.Body, d.Slug); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// matchExpr turns what a person typed into a safe FTS5 expression: every word quoted, the last one a prefix.
// Raw user input must never reach MATCH, because AND, OR, NEAR, quotes and parentheses are operators there.
func matchExpr(q string) string {
	words := strings.FieldsFunc(q, func(r rune) bool { return !unicode.IsLetter(r) && !unicode.IsDigit(r) })
	if len(words) == 0 {
		return ""
	}
	parts := make([]string, len(words))
	for i, w := range words {
		parts[i] = `"` + w + `"`
	}
	parts[len(parts)-1] += "*" // search as you type: "gorou" finds goroutines
	return strings.Join(parts, " ")
}

const (
	markOpen  = "\x02"
	markClose = "\x03"
)

// Search returns one page of hits and the total number of matches.
func (ix *Index) Search(ctx context.Context, q string, limit, offset int) ([]Hit, int, error) {
	expr := matchExpr(q)
	if expr == "" {
		return nil, 0, nil
	}
	var total int
	if err := ix.db.QueryRowContext(ctx, `SELECT count(*) FROM docs WHERE docs MATCH ?`, expr).Scan(&total); err != nil {
		return nil, 0, err
	}
	rows, err := ix.db.QueryContext(ctx,
		`SELECT slug, title, snippet(docs, 1, ?, ?, '…', 14), bm25(docs, 8.0, 1.0)
		 FROM docs WHERE docs MATCH ? ORDER BY bm25(docs, 8.0, 1.0) LIMIT ? OFFSET ?`,
		markOpen, markClose, expr, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	var hits []Hit
	for rows.Next() {
		var h Hit
		var snip string
		if err := rows.Scan(&h.Slug, &h.Title, &snip, &h.Rank); err != nil {
			return nil, 0, err
		}
		// Escape first, then turn the private markers into tags: the page content cannot inject markup.
		h.Snippet = strings.NewReplacer(markOpen, "<mark>", markClose, "</mark>").Replace(html.EscapeString(snip))
		hits = append(hits, h)
	}
	return hits, total, rows.Err()
}

The schema line carries the design. title and body are indexed, slug is stored but marked UNINDEXED, so it comes back with the row without cluttering the index. Replace rebuilds everything in one transaction: for a site of a few thousand pages, re-indexing takes milliseconds to a second, and rebuilding is simpler and safer than tracking individual changes. Readers searching during the rebuild see the old index until the commit, because SQLite isolates the transaction. If rebuilding becomes too slow, keep the content in a normal table and let FTS5 index it as an external content table with triggers that update the index on each insert, update and delete.

Never hand raw text to MATCH

The function matchExpr is the most important code in the file. The right-hand side of MATCH is not a bag of words, it is a small query language: AND, OR and NOT are operators, double quotes delimit phrases, parentheses group, NEAR(...) finds proximity, col:term restricts a column, - and + have meanings. A visitor typing goroutine AND or an unbalanced quote produces a syntax error, which in a naive handler becomes a 500 page. The program shows the failure directly in its last lines.

Beyond the error, there is an exposure. A raw query lets people use the operators deliberately, such as column filters to probe fields you did not mean to expose. The cure is to treat the input as plain words and build the expression yourself: split on anything that is not a letter or digit, wrap each word in double quotes so it is a literal term, and join with spaces, which FTS5 reads as AND. The last word gets a * so that typing is matched as a prefix. The result of AND OR ( " NEAR is the harmless expression "AND" "OR" "NEAR"*, which finds nothing.

This is the same principle as parameterised SQL. The ? placeholder already protects the SQL statement from injection, but MATCH parses its argument, so a second layer of escaping is needed for the query language inside it.

Searching

package main

import (
	"context"
	"fmt"
)

func main() {
	ix, err := OpenIndex("file::memory:")
	if err != nil {
		panic(err)
	}
	defer ix.Close()
	docs := []Doc{
		{"goroutine-leaks", "Finding goroutine leaks", "A goroutine blocked on a channel nobody reads is leaked. Use pprof to count goroutines and find the stack where they wait."},
		{"context-cancel", "Cancelling work with context", "Pass a context to every call that can block. When the parent is cancelled, goroutines return promptly instead of leaking."},
		{"slices", "Slices explained", "A slice is a view onto an array. Appending can reallocate; the <script>alert(1)</script> in this sentence must stay text."},
		{"worker-pools", "Worker pools", "A pool of workers reads jobs from a channel. Size it from the work, not from the number of goroutines you can afford."},
		{"errors", "Error handling", "Wrap errors with %w, compare with errors.Is, and avoid logging and returning the same error."},
		{"café", "Café notes", "Notes on naming: a café, a resume, a naïve approach."},
	}
	if err := ix.Replace(context.Background(), docs); err != nil {
		panic(err)
	}

	run := func(q string) {
		hits, total, err := ix.Search(context.Background(), q, 10, 0)
		fmt.Printf("query %-26q expr=%-28s total=%d err=%v\n", q, matchExpr(q), total, err)
		for _, h := range hits {
			fmt.Printf("    %-16s rank=%6.2f  %s\n", h.Slug, h.Rank, h.Snippet)
		}
	}
	run("goroutine leak")
	run("gorou")
	run("channels")
	run("context cancel")
	run("cafe")
	run("script alert")
	run(`AND OR ( " NEAR`)
	run("wrap errors AND")
	run("   ")

	// What happens when the raw text is handed to MATCH.
	for _, raw := range []string{`AND OR ( " NEAR`, `goroutine AND`, `title:channel`} {
		var n int
		err := ix.db.QueryRow(`SELECT count(*) FROM docs WHERE docs MATCH ?`, raw).Scan(&n)
		fmt.Printf("raw MATCH %-20q -> count=%d err=%v\n", raw, n, err)
	}
}

query "goroutine leak"           expr="goroutine" "leak"*          total=2 err=<nil>
    goroutine-leaks  rank= -1.12  A <mark>goroutine</mark> blocked on a channel nobody reads is <mark>leaked</mark>. Use pprof to count…
    context-cancel   rank= -0.56  …When the parent is cancelled, <mark>goroutines</mark> return promptly instead of <mark>leaking</mark>.
query "gorou"                    expr="gorou"*                     total=3 err=<nil>
    goroutine-leaks  rank= -0.00  A <mark>goroutine</mark> blocked on a channel nobody reads is leaked. Use pprof to count…
    context-cancel   rank= -0.00  …When the parent is cancelled, <mark>goroutines</mark> return promptly instead of leaking.
    worker-pools     rank= -0.00  …Size it from the work, not from the number of <mark>goroutines</mark> you can afford.
query "channels"                 expr="channels"*                  total=2 err=<nil>
    goroutine-leaks  rank= -0.55  A goroutine blocked on a <mark>channel</mark> nobody reads is leaked. Use pprof to count…
    worker-pools     rank= -0.55  A pool of workers reads jobs from a <mark>channel</mark>. Size it from the work…
query "context cancel"           expr="context" "cancel"*          total=1 err=<nil>
    context-cancel   rank= -4.99  Pass a <mark>context</mark> to every call that can block. When the parent is <mark>cancelled</mark>…
query "cafe"                     expr="cafe"*                      total=1 err=<nil>
    café             rank= -2.62  Notes on naming: a <mark>café</mark>, a resume, a naïve approach.
query "script alert"             expr="script" "alert"*            total=1 err=<nil>
    slices           rank= -2.96  …Appending can reallocate; the &lt;<mark>script</mark>&gt;<mark>alert</mark>(1)&lt;/<mark>script</mark>&gt; in this sentence must stay text.
query "AND OR ( \" NEAR"         expr="AND" "OR" "NEAR"*           total=0 err=<nil>
query "wrap errors AND"          expr="wrap" "errors" "AND"*       total=1 err=<nil>
    errors           rank= -4.84  <mark>Wrap</mark> <mark>errors</mark> with %w, compare with <mark>errors</mark>.Is, <mark>and</mark> avoid logging <mark>and</mark> returning the…
query "   "                      expr=                             total=0 err=<nil>
raw MATCH "AND OR ( \" NEAR"   -> count=0 err=SQL logic error: fts5: syntax error near "AND" (1)
raw MATCH "goroutine AND"      -> count=0 err=SQL logic error: fts5: syntax error near "" (1)
raw MATCH "title:channel"      -> count=0 err=<nil>

Look at the output. The query goroutine leak produces the expression "goroutine" "leak"* and finds two articles, ranked with the one that has both words in its first sentence on top, and the snippets show stemming at work (leaked, goroutines and leaking are highlighted). gorou matches three articles by prefix. The query context cancel ranks the article whose title contains context far ahead, with a score of -4.99 against around -0.5 to -1.1 for body-only matches, which is the title weighting. cafe finds café.

The injection row is the one to read twice. The page content contains <script>alert(1)</script>, and the query script alert returns it with the angle brackets shown as < and > and the matched words wrapped in <mark>. That works because Search first HTML-escapes the snippet and only then swaps the two private marker characters (control codes 2 and 3) for the tags. The other order, inserting <mark> first and escaping after, would escape the tags too. The marker characters are control characters on purpose: ordinary text cannot contain them, so the replacement cannot be tricked into producing markup.

The last three lines are the raw MATCH experiments: handing the string AND OR ( " NEAR straight to the engine, or a trailing operator, or a title: column filter, produces either a syntax error or a successful query that a visitor was not supposed to be able to write. The sanitised path never errors.

Keeping the index in step with the content

The rebuild-from-scratch approach works when content changes in batches: a deploy, a nightly import, a publish button. Run Replace after each, in the same process that loaded the posts, and the search always matches what the site shows. When content changes continuously (user posts, a catalogue edited all day), keep the source of truth in an ordinary table and let FTS5 follow it. An external content table (content='posts', content_rowid='id') stores the text once, and three triggers on insert, update and delete write to the index, which the FTS5 documentation shows in full. The trap is forgetting the delete trigger, which leaves removed pages findable; a test that deletes a row and searches for it catches that.

Decide also what is searchable at all. Drafts, unpublished and scheduled posts must not be in the index, and neither should anything behind a login, since a search result is a way of learning that a page exists. Filter at indexing time, not when displaying results, so the index never contains what the public must not see.

Search that people can use

The engine is half of the feature. The rest is the interface around it. Show the number of results and the query that was run, so a visitor can tell a bad search from a bad page. Keep the query in the URL (/search?q=goroutine) so results can be shared and the back button works. Highlight terms in titles as well as in snippets. Offer a useful empty state: popular articles, the topic list, a spelling hint if you can compute one cheaply. Log the queries that return nothing, because that list is the best content plan you will get for free: it is what your readers wanted and did not find.

Search-as-you-type needs care on both ends. On the client, wait 150 to 250 ms after the last keystroke before sending, and cancel the in-flight request when a new one starts. On the server, the prefix expression makes short queries expensive: a single letter followed by * matches a large share of the index. Require at least two or three characters before running a prefix search, and cap limit at a small number.

Relevance tests

Search quality drifts as content grows, and nobody notices until someone complains. A handful of golden queries in a test turns relevance into something you can protect. Pick the queries people really type (from your logs), write the page that should come first for each, and assert on it.

package main

import (
	"context"
	"math/rand"
	"strings"
	"testing"
)

func testIndex(t *testing.T) *Index {
	t.Helper()
	ix, err := OpenIndex("file::memory:")
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { ix.Close() })
	err = ix.Replace(context.Background(), []Doc{
		{"slices", "Slices explained", "Everything about arrays and views."},
		{"maps", "Maps", "A map can hold slices as values, and slices as keys are not allowed."},
		{"goroutines", "Goroutines", "Lightweight threads managed by the runtime."},
	})
	if err != nil {
		t.Fatal(err)
	}
	return ix
}

func TestTitleMatchOutranksBodyMatch(t *testing.T) {
	hits, _, err := testIndex(t).Search(context.Background(), "slices", 10, 0)
	if err != nil || len(hits) != 2 || hits[0].Slug != "slices" {
		t.Fatalf("hits = %+v, err = %v; the article titled Slices must come first", hits, err)
	}
}

func TestPaging(t *testing.T) {
	ix := testIndex(t)
	first, total, _ := ix.Search(context.Background(), "slices", 1, 0)
	second, _, _ := ix.Search(context.Background(), "slices", 1, 1)
	if total != 2 || len(first) != 1 || len(second) != 1 || first[0].Slug == second[0].Slug {
		t.Fatalf("total=%d first=%v second=%v", total, first, second)
	}
}

func TestNoUserStringCanBreakTheQuery(t *testing.T) {
	ix := testIndex(t)
	r := rand.New(rand.NewSource(1))
	alphabet := []rune(`abc AND OR NOT NEAR ()"*:-+^ é<>&\'` + "\x00\x02\x03")
	for i := 0; i < 2000; i++ {
		var b strings.Builder
		for j := r.Intn(12); j >= 0; j-- {
			b.WriteRune(alphabet[r.Intn(len(alphabet))])
		}
		if _, _, err := ix.Search(context.Background(), b.String(), 5, 0); err != nil {
			t.Fatalf("query %q failed: %v", b.String(), err)
		}
	}
}

func TestSnippetsCannotInjectMarkup(t *testing.T) {
	ix, _ := OpenIndex("file::memory:")
	defer ix.Close()
	ix.Replace(context.Background(), []Doc{{"x", "X", "before <img src=x onerror=alert(1)> after needle"}})
	hits, _, _ := ix.Search(context.Background(), "needle", 5, 0)
	if len(hits) != 1 || strings.Contains(hits[0].Snippet, "<img") || !strings.Contains(hits[0].Snippet, "<mark>needle</mark>") {
		t.Fatalf("snippet = %q", hits[0].Snippet)
	}
}

=== RUN   TestTitleMatchOutranksBodyMatch
--- PASS: TestTitleMatchOutranksBodyMatch (0.02s)
=== RUN   TestPaging
--- PASS: TestPaging (0.01s)
=== RUN   TestNoUserStringCanBreakTheQuery
--- PASS: TestNoUserStringCanBreakTheQuery (3.62s)
=== RUN   TestSnippetsCannotInjectMarkup
--- PASS: TestSnippetsCannotInjectMarkup (0.01s)
PASS
ok  	example.test/lab	5.025s

The third test is a cheap fuzz: two thousand random strings built from search-syntax characters, quotes, operators, control characters and non-ASCII letters, none of which may produce an error. Go's native fuzzing (go test -fuzz) is the natural next step if you want it to run for minutes instead of milliseconds. The last test pins the escape-then-mark order described above.

A note on cost and scale

How far does this go? SQLite's FTS5 indexes hundreds of thousands of documents on a laptop-class machine, and queries return in milliseconds to tens of milliseconds because the inverted index does the work. The index size is typically of the order of the text itself, so a site with 100 MB of content has an index in the same range, which fits comfortably in memory. The limits you meet first are not speed: a single writer (the rebuild takes the write lock for its duration, though readers continue), one machine, and the English-only stemming. For the typical documentation site or blog, none of them binds, and the whole search feature is one file you can copy, back up and inspect with the sqlite3 shell.

That last point is underrated. Debugging relevance is far easier when you can open the database and run the exact MATCH expression the application sent, change a weight and look at the scores. A hosted search service gives you a dashboard; this gives you a query prompt.

From here

Feed it real content. Index the plain text of each Markdown post, not the Markdown: strip the syntax or render to HTML and extract text, or the index fills with ## and URLs. Index the title, a summary, the tags and the body, and weight them in the bm25 call.

Handle the empty and the long. A zero-result page should say what to try, offer popular topics and keep the box filled. Cap the query at a few words and a couple of hundred characters; a thousand-word query is an attack or an accident, and either way it should be cheap to reject.

Rate limit it. Search is the cheapest way for a bot to make your server work. Apply rate limiting, and for search-as-you-type debounce on the client so a visitor typing six letters sends one request, not six.

Know where it stops. FTS5 does not do typo tolerance, synonyms, per-language analysis or faceting, and it is one process on one file. When you need those, Meilisearch and Typesense are small search servers with typo tolerance out of the box, PostgreSQL's full-text search (tsvector and GIN indexes) gives you the same model inside a database you may already run, and Bleve is a pure Go library. Moving later is not painful if search sits behind a method like Search(ctx, q, limit, offset), as it does here. The FTS5 query syntax, tokenizers and auxiliary functions are documented in the SQLite FTS5 reference.