SqlHydra.Query.Pgvector 0.1.0-alpha.8

SqlHydra.Query.Pgvector

Vector similarity search for SqlHydra.Query, powered by pgvector.

The goal is to let you write select and ORDER BY queries that compare embeddings by cosine, L2 (Euclidean), or inner-product distance — all in strongly-typed F#, with the native pgvector operators (<=>, <->, <#>) generated for you. It also aims to teach the SqlHydra code generator about vector columns so they come through as Pgvector.Vector in your generated types.

Status: early alpha, and substantially AI-written. Behavior and APIs may shift between versions, so your mileage may vary. Issues and PRs are very welcome.

Before you start

You'll need:

• A PostgreSQL database with the pgvector extension enabled (CREATE EXTENSION vector;) and a table with a vector column.
• SqlHydra.Query set up for that database. If you're new to SqlHydra, start with its docs — this package just adds vector search on top of the queries you already write.

Installation

dotnet add package SqlHydra.Query.Pgvector

Searching by similarity

Open the package alongside SqlHydra.Query, then use the distance functions in a query. queryVector below is your search embedding — the Pgvector.Vector you want to find the nearest rows to.

open SqlHydra.Query
open SqlHydra.Query.Pgvector.PgvectorExtensions
open type SqlHydra.Query.Pgvector.PgvectorExtensions.PgvectorFn

// Distance between two vector columns (e.g. how far each document is from a
// cluster centroid). Both arguments must be column references:
let centroidDistance =
    select {
        for d in documents do
            select (cosine_distance (d.embedding, d.centroid))
    }

// Find the 10 closest documents to your query vector (nearest-neighbour search).
// The query vector is bound as a parameter, so it's safe to pass user input:
let nearest =
    select {
        for d in documents do
            orderByCosineDistance d.embedding queryVector
            take 10
    }

That should be it — no setup or registration call needed.

Available distance functions

Use these inside select to get a distance back as a column:

These emit the infix operator between two columns (e.g. embedding <=> other_embedding). Both arguments must be column references. Passing a literal Pgvector.Vector (or array) as the second argument is not supported in a select projection — SqlHydra fails fast at compile time rather than inlining the value. To rank rows against a query vector, use the orderBy*Distance operations below, which bind the vector as a parameter.

Use these to order results from closest to farthest:

In the orderBy*Distance path the query vector is always sent as a query parameter, so it's safe to pass user input.

Nullable and left-joined vector columns

A vector column that can be NULL, either a nullable column or any column of a left-view joined with leftJoin', works as it is with the orderBy*Distance operations: a row with a NULL vector has no distance, and PostgreSQL sorts it last.

select {
    for d in documents do
    leftJoin' c in LeftJoined.centroids
    on' (Some d.clusterId = c.id)
    orderByCosineDistance c.embedding queryVector
}

The distance functions return float, so a distance read from a row where either vector is NULL fails when the row is read. Filter those rows out first, e.g. where (c.embedding <> None).

Generating types for vector columns

So that SqlHydra generates a Pgvector property for each pgvector column, add this package to the [extensions] section of your SqlHydra generator TOML:

[extensions]
type_mappings = ["SqlHydra.Query.Pgvector"]

Build the project, then re-run dotnet sqlhydra. The generator loads the extension from your project's bin/, and this package puts its assembly there even when your project is a library, which does not otherwise copy package assemblies into its output. There is no need for CopyLocalLockFileAssemblies or a copy step of your own.

Your pgvector columns come through as Pgvector types:

That holds for tables, views and materialized views, for a column typed by a domain over one of these, and when the extension lives in a schema that is not on the search path (such as CREATE EXTENSION vector SCHEMA extensions). To read and write these values, register the pgvector types with Npgsql (dataSourceBuilder.UseVector()).

Building this project

Tasks are driven by mise:

mise run build    # build the solution
mise run test     # run all tests (the integration tests need Docker)
mise run ci       # the full gate: format check + build + lint + test
mise run format   # format with Fantomas

The integration tests spin up a real PostgreSQL + pgvector container via Testcontainers, so they need a running Docker daemon. To run them against a PostgreSQL server you already have instead, point PGVECTOR_TEST_SERVER at it with a connection string. The server needs the pgvector extension available and a role that can create databases: the tests create a scratch database there and drop it afterwards.

PGVECTOR_TEST_SERVER="Host=localhost;Port=5433;Username=postgres;Database=postgres" mise run test

To run only the in-process unit tests, with no database at all:

dotnet test --solution SqlHydra.Query.Pgvector.slnx \
    --filter-not-trait "Category=Integration"

License

MIT