---
title: "SQLite vector nearest-neighbor search in C#"
url: https://recursiveai.org/onelite/cookbook/sqlite-vector-nearest-neighbor-search/
description: "Create a vec0 table in C#, bind a query vector and return nearest rows with distances using OneLite. Includes a runnable .NET example."
version: "1.0.0"
---

# SQLite vector nearest-neighbor search in C#

**OneLite** is a free, open-source SQLite-compatible database for .NET from Recursive AI. 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-vector-nearest-neighbor-search/sqlite-vector-nearest-neighbor-search.zip)

Vector search finds the stored numeric vectors closest to a query vector. `vec0` is OneLite’s built-in vector table; `float[2]` declares two-number vectors.

```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 items USING " +
    "vec0(embedding float[2])"))
    create.Step();
using (var insert = connection.Prepare(
    "INSERT INTO items(rowid, embedding) " +
    "VALUES (1, '[0,0]'), (2, '[3,4]'), " +
    "(3, '[6,8]')"))
    insert.Step();
using var query = connection.Prepare(
    "SELECT rowid, distance FROM items " +
    "WHERE embedding MATCH ? AND k = 2 " +
    "ORDER BY distance");
query.Bind(1, "[0,0]");
while (query.Step())
    Console.WriteLine($"{query.GetInt64(0)}: " +
        $"{query.GetDouble(1):0.0}");
```

```text
1: 0.0
2: 5.0
```

Unzip [the example](https://recursiveai.org/onelite/cookbook/sqlite-vector-nearest-neighbor-search/sqlite-vector-nearest-neighbor-search.zip), then `dotnet run`.

The three input vectors are `[0,0]`, `[3,4]` and `[6,8]`; the query is `[0,0]`. The result shows row IDs and L2 distances. Nothing is downloaded or embedded by a model. A vector with the wrong dimension fails with `OneLiteException`; every inserted vector must match the declared dimension.

## Variations

To calculate a distance without a virtual table, add this after the query. L1 adds the absolute coordinate differences; here it returns 7 instead of the L2 distance 5.

```csharp
using var distance = connection.Prepare(
    "SELECT vec_distance_l1('[0,0]', '[3,4]')");
if (distance.Step())
    Console.WriteLine(distance.GetDouble(0));
```

```text
7
```

## Related recipes

- [FTS5 full-text search](https://recursiveai.org/onelite/cookbook/sqlite-full-text-search/)
- [Create a SQLite database](https://recursiveai.org/onelite/cookbook/create-sqlite-database-csharp/)

## The short answer

Create a `vec0` virtual table, insert vectors of the declared dimension, then bind a query vector to `MATCH` and request `k` neighbors. The `distance` column reports the selected metric. Smaller distances are closer; `ORDER BY distance` returns the closest row first.

The example uses two-coordinate vectors so the result is easy to inspect: the origin is distance 0 from itself, `[3,4]` is distance 5, and `[6,8]` is farther away. It uses a private in-memory database; use a file path instead when the vectors need to survive the process. The [vector documentation](https://recursiveai.org/onelite/docs/vec/) describes the SQL surface and storage.

## How do I use vec0 for vector search?

`CREATE VIRTUAL TABLE ... USING vec0(...)` opts a table into vector search; ordinary tables remain ordinary SQL tables. A vector column such as `embedding float[2]` declares both the element type and the required number of coordinates. The example inserts JSON arrays as SQL values and binds the query array as a parameter.

`WHERE embedding MATCH ? AND k = 2` asks for two nearest rows. You can use a `LIMIT` instead of the `k` constraint. Without `MATCH`, the virtual table is a plain scan, not a nearest-neighbor query. Requesting neighbors and selecting `distance` are separate from generating vectors: OneLite does not generate embeddings. To store a model’s `float[]` output, declare its dimension (for example, `float[1536]` for 1,536 numbers), serialize the array as JSON and bind that JSON string when inserting the vector. Supply vectors from your own application, and use the same coordinate system for stored and query vectors.

## How do I choose a distance metric for SQLite vector search?

L2 is the default. For float vectors, OneLite also supports cosine and L1. Declare a different metric in the vector column definition with `distance_metric=cosine` or `distance_metric=l1`. Choose the metric that matches how your application’s vectors were produced, rather than choosing one from the example’s small numbers.

The L1 variation above computes a scalar distance directly. That is useful when you already have two vectors and do not need to search a stored collection. A virtual table is useful when you need row identities, stored metadata and a nearest-row result. OneLite also supports `int8` vectors and packed `bit` vectors, with Hamming distance for bits; a bit-vector dimension must be a multiple of eight.

## Can I filter a SQLite vector search?

A `vec0` definition can include metadata columns and partition keys. Metadata constraints narrow the candidates inside the nearest-neighbor query, so the result is the nearest qualifying rows rather than a filtered subset of an already truncated result. Auxiliary columns, declared with a `+` prefix, are stored separately and can be read but not used as metadata filters.

The search is exact, not an approximate graph search. Equal-distance ties break by rowid. That distinction matters when comparing a returned set with a brute-force distance calculation: this API does not expose a recall-tuning option.

## Can I open a vec0 database with another SQLite tool?

The surrounding database is a standard SQLite file, but the vector table’s shadow-table layout belongs to OneLite. It is not interchangeable with other vector extensions’ storage. An ordinary SQLite tool without this module can read the other tables and reports `no such module: vec0` when it tries to use the vector table.

For a persisted index, plan to open and update its vector tables through OneLite. Keeping the same virtual-table name in two engines does not make their vector storage formats compatible.
