Recursive AI

SQLite FTS5 full-text search in C#

OneLite is a free, open-source SQLite-compatible embedded SQL database for .NET from Recursive AI that reads and writes SQLite files. MIT or Apache-2.0, no native dependencies.

Full-text search indexes words so you can find and rank matching documents rather than scan for substrings.

Terminal
dotnet add package RecursiveAI.OneLite

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
using RecursiveAI.OneLite;
 
using var db = Database.Open(":memory:");
using var connection = db.Connect();
using (var create = connection.Prepare(
    "CREATE VIRTUAL TABLE notes " +
    "USING fts5(body)"))
    create.Step();
using (var insert = connection.Prepare(
    "INSERT INTO notes VALUES " +
    "('A red bicycle')"))
    insert.Step();
using var query = connection.Prepare(
    "SELECT body FROM notes " +
    "WHERE notes MATCH ?");
query.Bind(1, "bicycle");
while (query.Step())
    Console.WriteLine(query.GetString(0));
Text
A red bicycle

Unzip the example, then dotnet run.

The full program

The complete corpus is the three title/body pairs below. The program creates the index, binds the search expression, orders matches by BM25 and marks the matching words.

C#
using RecursiveAI.OneLite;
 
using var db = Database.Open(":memory:");
using var connection = db.Connect();
using (var create = connection.Prepare(
    "CREATE VIRTUAL TABLE notes USING fts5(title, body, tokenize='unicode61')")) create.Step();
foreach (var (title, body) in new[] {
    ("Workshop", "Repair a red bicycle"),
    ("Trail", "Ride a bicycle on the trail"),
    ("Kitchen", "Bake bread at home") })
{
    using var insert = connection.Prepare("INSERT INTO notes(title, body) VALUES (?, ?)");
    insert.Bind(1, title);
    insert.Bind(2, body);
    insert.Step();
}
string search = "bicycle";
using var query = connection.Prepare("""
    SELECT title, highlight(notes, 1, '[', ']')
    FROM notes WHERE notes MATCH ?
    ORDER BY bm25(notes), rowid
    """);
query.Bind(1, search);
while (query.Step())
    Console.WriteLine($"{query.GetString(0)}: {query.GetString(1)}");

dotnet run prints:

Text
Workshop: Repair a red [bicycle]
Trail: Ride a [bicycle] on the trail

A malformed FTS expression fails when evaluated; binding a parameter does not bypass the FTS query grammar.

Variations

Search for a phrase. Replace the search declaration in the full program.

C#
string search = "\"red bicycle\"";
Text
Workshop: Repair a [red bicycle]

The short answer

Create a virtual table with USING fts5, insert text into its columns, and select rows with MATCH. OneLite includes FTS5 in its managed SQL engine, so the example needs only the core NuGet package. Bind the search expression as a parameter instead of joining user text into SQL. Add ORDER BY bm25(notes), rowid for ranked results with a stable tie-breaker.

The download creates its own in-memory database and inserts the entire corpus shown in Program.cs. No database file or native extension needs to be downloaded first. To retain an index between runs, open a file-backed database and create and populate the table as part of your application’s initialization rather than recreating it on every search.

What is FTS5 in SQLite?

FTS5 is a virtual-table interface for full-text indexing. A tokenizer splits document text into searchable tokens. MATCH evaluates a text-search expression against that index; it is not an ordinary substring comparison. The SQLite FTS5 documentation describes its query language and table options. OneLite documents its implemented behavior in its own FTS5 reference.

The full program explicitly chooses unicode61. OneLite also provides ascii, porter and trigram tokenizers. Choose the tokenizer when creating the table: it determines the indexed vocabulary and how query text is split. The documented Unicode tables are Unicode 6.1, so do not assume the tokenizer follows the Unicode version of the host operating system.

The example stores both title and body in the FTS table. This keeps the recipe self-contained. External-content and contentless tables are supported options, but they change how text is stored and how an index must be maintained. Start with a content-storing table unless your application needs those different storage rules.

How to use FTS5?

Use parameters for both inserted text and search text. The full program binds each title and body, then binds the search expression. Parameter binding keeps the expression separate from SQL syntax. It does not turn FTS operators into literal text: a bound value still goes through the FTS query parser.

For a phrase, use a double-quoted FTS expression such as the variation’s "red bicycle". OneLite supports phrases, prefix *, parentheses, column filters, AND, OR, NOT and NEAR(...). Decide whether your search box accepts that language or only literal phrases. For a literal phrase, follow the FTS5 string-quoting rules, including doubling any embedded double quote. Invalid expressions can fail even though the SQL statement itself is correctly parameterized.

Keep the default full detail if you need positional queries. OneLite’s documentation states that multi-token phrases and initial-token queries are unsupported with detail=column or detail=none; multi-phrase NEAR is also unsupported there. Column filters additionally require more than detail=none.

bm25(notes) supplies a relevance score for a matching row. SQLite’s FTS5 convention puts better matches at lower numerical scores, so ascending order is intentional; see the BM25 definition. The program uses the row ID to order equal scores and prints the matched body with highlight rather than exposing a floating-point score as a user-facing confidence value.

highlight(notes, 1, '[', ']') marks matches in the second column, the body. The brackets are presentation markers, not HTML escaping. If you render stored text into a web page, escape the text for that output context; do not treat highlighted document text as trusted markup. OneLite also supports snippet for a shorter excerpt. Its reference documents detailed behavior and known differences for reduced-detail and unindexed-column combinations, so check those cases before changing the table layout.

All onelite recipes