---
title: "SQLite FTS5 full-text search in C#"
url: https://recursiveai.org/onelite/cookbook/sqlite-full-text-search/
description: "Create a SQLite-compatible FTS5 index in C#, bind MATCH queries, rank results with BM25 and highlight matches using OneLite’s managed engine."
version: "1.0.0"
---

# 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.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/sqlite-full-text-search/sqlite-full-text-search.zip)

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

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onelite/cookbook/sqlite-full-text-search/sqlite-full-text-search.zip), 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.

```csharp
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.

```csharp
string search = "\"red bicycle\"";
```

```text
Workshop: Repair a [red bicycle]
```

## Related recipes

- [Create a SQLite database](https://recursiveai.org/onelite/cookbook/create-sqlite-database-csharp/)
- [Use SQLite transactions](https://recursiveai.org/onelite/cookbook/sqlite-transactions-csharp/)

## 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](https://sqlite.org/fts5.html) describes its query language and table options. OneLite documents its implemented behavior in its own [FTS5 reference](https://recursiveai.org/onelite/docs/fts5/).

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](https://sqlite.org/fts5.html#fts5_strings), 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`.

## What is FTS5 BM25 search?

`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](https://sqlite.org/fts5.html#the_bm25_function). 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.
