SQLite FTS5 for Static Site Search

For search on websites, developers usually default to elastic search containers, Algolia subscriptions, or simple JS regex lookups. But what if you could compile your entire static site's search index into a mini **SQLite database file**, publish that file alongside your HTML, and query it directly using raw SQL inside the user's browser?

With SQLite's built-in **FTS5 (Full-Text Search)** virtual tables and web assembly (WASM) compilers, this setup is now highly practical. It allows advanced rank query scoring (like BM25), prefix token matching, and complex search syntax, all running client-side with 0% server database RAM or CPU load.

Why SQLite FTS5 Wins

Relational and Search Combined: Query your posts metadata (author, date, category) and search text inside the same database transaction.
Advanced Rank Scoring: FTS5 supports the BM25 relevance calculation natively. Results are sorted by relevance out-of-the-box.
Optimized File Sizes: A compressed SQLite index database is often smaller than standard JSON index text files because of SQLite's efficient binary indexing structures.
Standard SQL Queries: Write standard SQL like SELECT title FROM posts WHERE content MATCH 'database AND performance'.

Step 1: Build the SQLite Database Index

You can compile your site's search index during development. The following Python script scans a directory of HTML posts, creates an SQLite database file with an FTS5 virtual table, and indexes the content:

import os
import sqlite3
import re
from html.parser import HTMLParser

class TextExtractor(HTMLParser):
    def __init__(self):
        super().__init__()
        self.text = []
    def handle_data(self, data):
        self.text.append(data)
    def get_text(self):
        return " ".join(self.text)

def create_fts5_db(posts_dir="posts", db_path="search.db"):
    # Delete old index if exists
    if os.path.exists(db_path):
        os.remove(db_path)

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Create FTS5 virtual table for full-text search
    cursor.execute("""
        CREATE VIRTUAL TABLE posts_search USING fts5(
            title,
            description,
            content,
            url UNINDEXED
        );
    """)

    for filename in os.listdir(posts_dir):
        if filename.endswith(".html"):
            filepath = os.path.join(posts_dir, filename)
            with open(filepath, "r", encoding="utf-8") as f:
                content = f.read()

            title_match = re.search(r"<title>(.*?)</title>", content)
            title = title_match.group(1).replace(" — Next Harizon", "") if title_match else filename

            desc_match = re.search(r'meta name="description" content="(.*?)"', content)
            desc = desc_match.group(1) if desc_match else ""

            body_match = re.search(r'<div class="article-body">(.*?)</div>', content, re.DOTALL)
            body_html = body_match.group(1) if body_match else content
            
            parser = TextExtractor()
            parser.feed(body_html)
            text_content = re.sub(r"\s+", " ", parser.get_text()).strip()

            cursor.execute(
                "INSERT INTO posts_search (title, description, content, url) VALUES (?, ?, ?, ?)",
                (title, desc, text_content, f"posts/{filename}")
            )

    conn.commit()
    conn.close()
    print(f"Created SQLite FTS5 database at: {db_path}")

if __name__ == "__main__":
    create_fts5_db()

Step 2: Querying the Database in Browser (WASM)

By compiling SQLite to WebAssembly, you can open and query your database directly in the browser. You only load the SQLite WASM library and your database file when the user focuses on the search bar:

// Lazy-loading SQLite WASM and database
let sqlite = null;
let db = null;

async function initSearch() {
  if (db) return; // already loaded
  
  // Load SQLite WASM script
  const sqlModule = await import('https://esm.sh/@sqlite.org/sqlite-wasm');
  sqlite = await sqlModule.default();
  
  // Download database search.db as bytes
  const response = await fetch('/search.db');
  const arrayBuffer = await response.arrayBuffer();
  
  // Open database inside memory
  db = new sqlite.oo1.DB();
  const byteArray = new Uint8Array(arrayBuffer);
  sqlite.wasm.sqlite3_deserialize(db.pointer, byteArray, byteArray.byteLength, byteArray.byteLength, 0);
  console.log("SQLite search database loaded successfully");
}

async function search(query) {
  if (!db) await initSearch();
  
  // Clean query for safety and query FTS5 using BM25 scoring
  const cleanQuery = query.replace(/[^a-zA-Z0-9\s]/g, '');
  const sql = `
    SELECT title, description, url, bm25(posts_search) as score 
    FROM posts_search 
    WHERE posts_search MATCH ? 
    ORDER BY score 
    LIMIT 5;
  `;
  
  const results = [];
  db.exec({
    sql: sql,
    bind: [cleanQuery],
    rowMode: 'object',
    callback: (row) => {
      results.append(row);
    }
  });
  return results;
}

Conclusion

Using SQLite FTS5 virtual tables brings desktop-grade search capabilities to flat HTML site deployment. It consumes negligible memory, bypasses hosting database clusters entirely, and provides advanced ranking models with 100% offline support.

SQLite FTS5 for Static Site Search Engines

Why SQLite FTS5 Wins

Step 1: Build the SQLite Database Index

Step 2: Querying the Database in Browser (WASM)

Conclusion