Vector Search (pgvector)

Query pgvector columns with findMany: orderBy ranks nearest neighbors and where filters by distance. Use it for semantic search, recommendations and RAG retrieval. You need the pgvector extension and a vector column.

KNN, order by distance#

Order by distance and add limit to take the closest k:

const similar = await db.items.findMany({
  orderBy: { embedding: { distance: { to: queryVector, metric: 'cosine' } } },
  limit: 5,
});
// queryVector is a number[]; the 5 nearest rows, nearest-first

This compiles to ORDER BY "embedding" <=> $1::vector ASC. The default direction is 'asc', nearest first; pass direction: 'desc' for farthest first:

const farthest = await db.items.findMany({
  orderBy: { embedding: { distance: { to: queryVector, metric: 'cosine', direction: 'desc' } } },
  limit: 5,
});

Distance filter, keep rows within a threshold#

Put distance in where with a comparator to keep rows inside or outside a threshold:

const close = await db.items.findMany({
  where: { embedding: { distance: { to: queryVector, metric: 'l2', lt: 0.3 } } },
});
// Generates: WHERE "embedding" <-> $1::vector < $2

Filters take lt, lte, gt or gte; a filter with none throws ValidationError (E003). Combine a filter with ordering to get the closest matches that clear a bar:

const results = await db.items.findMany({
  where: { embedding: { distance: { to: queryVector, metric: 'cosine', lt: 0.4 } } },
  orderBy: { embedding: { distance: { to: queryVector, metric: 'cosine' } } },
  limit: 10,
});

Metrics#

`metric`	Operator	Distance
`'l2'`	`<->`	Euclidean (L2) distance
`'cosine'`	`<=>`	Cosine distance
`'ip'`	`<#>`	Negative inner product

An unknown metric, or distance on a column that is not a vector, throws ValidationError (E003).

A selected vector column reads back as a string such as '[1,2,3]', because pg has no parser for the type. Register one (for example with pgvector's client helpers) if you need arrays.

The exported types#

Every vector type is exported from the package root, for building queries in helpers:

import type {
  VectorMetric,
  VectorFilter,
  VectorDistanceFilter,
  VectorOrderBy,
  VectorOrderByDistance,
} from 'turbine-orm';

Type	Shape	Where it goes
`VectorMetric`	`'l2' \| 'cosine' \| 'ip'`	The `metric` field of both specs below
`VectorDistanceFilter`	`{ to: number[]; metric: VectorMetric; lt?, lte?, gt?, gte?: number }`	The `distance` value inside a `where`
`VectorFilter`	`{ distance: VectorDistanceFilter }`	`where: { embedding: VectorFilter }`
`VectorOrderByDistance`	`{ to: number[]; metric: VectorMetric; direction?: 'asc' \| 'desc' }`	The `distance` value inside an `orderBy`
`VectorOrderBy`	`{ distance: VectorOrderByDistance }`	`orderBy: { embedding: VectorOrderBy }`

A retrieval helper that builds both halves from one embedding:

import type { VectorFilter, VectorMetric, VectorOrderBy } from 'turbine-orm';
 
function knn(to: number[], metric: VectorMetric = 'cosine', maxDistance?: number) {
  const order: VectorOrderBy = { distance: { to, metric } };
  const filter: VectorFilter | undefined =
    maxDistance === undefined ? undefined : { distance: { to, metric, lt: maxDistance } };
  return { order, filter };
}
 
const { order, filter } = knn(queryVector, 'cosine', 0.4);
 
const results = await db.items.findMany({
  where: filter ? { embedding: filter } : undefined,
  orderBy: { embedding: order },
  limit: 10,
});

Limits#

PostgreSQL only. On SQLite, MySQL, SQL Server and PowDB, a distance filter or ordering throws UnsupportedFeatureError (E017).
The query vector is bound as $n::vector and each threshold as $n; the metric comes from a fixed list, so no input reaches the SQL text.
An empty to, a non-finite element (NaN, Infinity) or a non-finite threshold throws ValidationError (E003) before the query runs.

The same query, three ways#

The 10 nearest rows by cosine distance, under a threshold. In Turbine it is the combined where and orderBy query above.

Prisma: a pgvector column is Unsupported("vector"), so distance queries go through $queryRaw.

const embedding = `[${queryVector.join(',')}]`;
 
const results = await prisma.$queryRaw<
  { id: number; title: string; distance: number }[]
>`
  SELECT id, title, embedding <=> ${embedding}::vector AS distance
  FROM items
  WHERE embedding <=> ${embedding}::vector < 0.4
  ORDER BY distance
  LIMIT 10
`;

Drizzle: a vector column type plus the cosineDistance expression helper.

import { asc, cosineDistance, lt } from 'drizzle-orm';
 
const distance = cosineDistance(items.embedding, queryVector);
 
const results = await db
  .select({ id: items.id, title: items.title, distance })
  .from(items)
  .where(lt(distance, 0.4))
  .orderBy(asc(distance))
  .limit(10);