Semantic Search

UQL supports vector similarity search natively, on PostgreSQL (pgvector), CockroachDB, MariaDB, SQLite (sqlite-vec), libSQL and Turso (built in, no extension), MSSQL (SQL Server 2025, exact), and MongoDB Atlas ($vectorSearch). The same $vector query works on all of them. This page is the operator reference; for an end-to-end walkthrough (ingestion, querying, RAG thresholds), see AI & RAG.

Entity Setup

Define a vector field with type: 'vector' and dimensions. Optionally, add a vector index for efficient approximate nearest-neighbor (ANN) search.

You write

import { Entity, Id, Field, Index } from 'uql-orm';

@Entity()
@Index(['embedding'], {
  type: 'hnsw',
  distance: 'cosine',
  m: 16,
  efConstruction: 64,
})
export class Article {
  @Id({ type: Number }) id?: number;
  @Field({ type: String }) title?: string;
  @Field({ type: String }) category?: string;

  @Field({ type: 'vector', dimensions: 1536 })
  embedding?: number[];
}

For Postgres, UQL emits CREATE EXTENSION IF NOT EXISTS vector when your schema includes vector columns, and index migrations pick up the HNSW and IVFFlat parameters (m, efConstruction, lists) from the @Index decorator, so index changes ship with your normal migrations.

CockroachDB’s VECTOR type and vector index are native, needing no extension, and use the same <=>/<->/<#> operators as Postgres. See Vector Indexes below for its index syntax.

MariaDB

Vector search is built in from 11.7, with VECTOR(n) columns holding a packed float32 blob rather than text. UQL converts in both directions for you (VEC_FromText on write and inside the distance call, VEC_ToText on read), so a vector field still reads back as '[1,0,0]' like everywhere else. Two MariaDB rules to know: dimensions is required on the field, and a column carrying a vector index is emitted NOT NULL, because MariaDB rejects the index otherwise.

SQLite, libSQL and Turso

libSQL and Turso have vector functions built in, so their entities need nothing extra. Plain SQLite has none, and gets them from sqlite-vec: pass its path to the pool, which loads it on the connection:

Loading sqlite-vec

import { getLoadablePath } from 'sqlite-vec';
import { Sqlite3QuerierPool } from 'uql-orm/sqlite';

const pool = new Sqlite3QuerierPool('app.db', {
  extensions: [getLoadablePath()],
});

Vectors are stored as a JSON array of floats on all three, which every distance function below accepts directly.

SQL Server

Vector search needs SQL Server 2025, where VECTOR(n) and VECTOR_DISTANCE exist, and dimensions is required on the field. The query vector is cast to VECTOR(n), since the function refuses the NVARCHAR it binds as. Search is exact: the DiskANN index is still a preview feature, so every distance is computed.

MySQL and Cloudflare D1 have no vector search

MySQL has a VECTOR type but no distance function outside HeatWave and no vector index (verified on 9.7); D1 loads no extensions and has no vector functions. A $vector sort throws on both, naming what is missing; use Vectorize on Cloudflare. A type: 'vector' field still stores fine on MySQL, mapped to JSON.

---

Query by Similarity

Use $sort on a vector field with $vector and an optional $distance metric:

You write

import { pool } from './uql.config.js';

const results = await pool.findMany(Article, {
  $select: { id: true, title: true },
  $sort: { embedding: { $vector: queryEmbedding, $distance: 'cosine' } },
  $limit: 10,
});

PostgreSQL / CockroachDB

SELECT "id", "title" FROM "Article"
ORDER BY "embedding" <=> $1::vector
LIMIT 10

MariaDB

SELECT `id`, `title` FROM `Article`
ORDER BY VEC_DISTANCE_COSINE(`embedding`, VEC_FromText(?))
LIMIT 10

SQLite, sqlite-vec

SELECT `id`, `title` FROM `Article`
ORDER BY vec_distance_cosine(`embedding`, ?)
LIMIT 10

libSQL / Turso

SELECT `id`, `title` FROM `Article`
ORDER BY vector_distance_cos(`embedding`, ?)
LIMIT 10

MSSQL

SELECT "id", "title" FROM "Article"
ORDER BY VECTOR_DISTANCE('cosine', "embedding", CAST(@p1 AS VECTOR(1536)))
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

MongoDB Atlas

UQL translates into a $vectorSearch aggregation pipeline:

[
  {
    "$vectorSearch": {
      "index": "embedding_index",
      "path": "embedding",
      "queryVector": [/* queryEmbedding */],
      "numCandidates": 100,
      "limit": 10
    }
  }
]

MongoDB vector search needs an Atlas cluster with a vector search index configured on the target field. The distance metric is defined in that index, so $distance is accepted for API consistency and ignored.

Combined with Filtering

Vector search composes naturally with $where and regular $sort fields:

You write

const results = await pool.findMany(Article, {
  $where: { category: 'science' },
  $sort: {
    embedding: { $vector: queryVec, $distance: 'cosine' },
    title: 'asc',
  },
  $limit: 10,
});

PostgreSQL / CockroachDB

SELECT * FROM "Article"
WHERE "category" = $1
ORDER BY "embedding" <=> $2::vector, "title" ASC
LIMIT 10

MariaDB

SELECT * FROM `Article`
WHERE `category` = ?
ORDER BY VEC_DISTANCE_COSINE(`embedding`, VEC_FromText(?)), `title` ASC
LIMIT 10

SQLite, sqlite-vec

SELECT * FROM `Article`
WHERE `category` = ?
ORDER BY vec_distance_cosine(`embedding`, ?), `title` ASC
LIMIT 10

libSQL / Turso

SELECT * FROM `Article`
WHERE `category` = ?
ORDER BY vector_distance_cos(`embedding`, ?), `title` ASC
LIMIT 10

MSSQL

SELECT * FROM "Article"
WHERE "category" = @p1
ORDER BY VECTOR_DISTANCE('cosine', "embedding", CAST(@p2 AS VECTOR(1536))), "title" ASC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

MongoDB Atlas

$where is merged into the $vectorSearch.filter for optimal pre-filtering, and secondary sorts become a separate $sort stage:

[
  {
    "$vectorSearch": {
      "index": "embedding_index",
      "path": "embedding",
      "queryVector": [/* queryVec */],
      "numCandidates": 100,
      "limit": 10,
      "filter": { "category": "science" }
    }
  },
  { "$sort": { "title": 1 } }
]

UQL merges $where into $vectorSearch.filter rather than adding a separate $match stage, which is what Atlas recommends: the filter runs through the search index instead of scanning the collection after the vector search.

---

Distance Predicate

$sort ranks by distance; $near in $where filters by it, so “the closest ten” and “everything closer than 0.35” stay separate asks. Use it whenever a far-but-least-far row is worse than no row at all: RAG context, deduplication, match thresholds.

You write

const results = await pool.findMany(Article, {
  $where: { embedding: { $near: { $vector: queryVec, $lt: 0.35 } } },
  $limit: 10,
});

PostgreSQL / CockroachDB

SELECT * FROM "Article"
WHERE "embedding" <=> $1::vector < $2
LIMIT 10

MariaDB

SELECT * FROM `Article`
WHERE VEC_DISTANCE_COSINE(`embedding`, VEC_FromText(?)) < ?
LIMIT 10

SQLite, sqlite-vec

SELECT * FROM `Article`
WHERE vec_distance_cosine(`embedding`, ?) < ?
LIMIT 10

libSQL / Turso

SELECT * FROM `Article`
WHERE vector_distance_cos(`embedding`, ?) < ?
LIMIT 10

MSSQL

SQL Server pages only an ordered statement, so an unsorted one gets a constant ORDER BY:

SELECT * FROM "Article"
WHERE VECTOR_DISTANCE('cosine', "embedding", CAST(@p1 AS VECTOR(1536))) < @p2
ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Bounds

$lt, $lte, $gt, $gte and $between, the ordering comparisons. At least one is required: a $near carrying only a vector keeps every row, so it throws instead. There is no $eq or $ne, because a distance is a floating-point number and exact equality against one is a bug every time.

// a band, for deduplication: close enough to be related, far enough not to be the same document
$where: { embedding: { $near: { $vector: queryVec, $between: [0.05, 0.4] } } }

Two bounds spell the distance twice in the SQL, since a WHERE has no output alias to point back at.

Filter and rank together

Each clause states its own search, so you can filter by similarity and order by anything else:

// the relevant documents, newest first
const results = await pool.findMany(Article, {
  $where: {
    category: 'docs',
    embedding: { $near: { $vector: queryVec, $lte: 0.4 } },
  },
  $sort: { createdAt: 'desc' },
});

To do both on the same vector, name it once in a const. $sort keeps $project, so the score still comes back:

import type { WithDistance } from 'uql-orm';

const queryVec = await embed(question);

const results = (await pool.findMany(Article, {
  $where: { embedding: { $near: { $vector: queryVec, $lt: 0.35 } } },
  $sort: { embedding: { $vector: queryVec, $project: 'score' } },
  $limit: 30,
})) as WithDistance<Article, 'score'>[];

$near never borrows the $sort’s vector. That is what lets the same predicate mean the same thing where there is no $sort at all: in an entity filter merged into someone else’s $where, or in exists, which takes a filter and nothing else:

// near-duplicate check before inserting
await pool.exists(Article, {
  $where: { embedding: { $near: { $vector: queryVec, $lt: 0.05 } } },
});

Not available on MongoDB Atlas

Atlas has no distance operator: it scores by the similarity set in the Atlas index definition, which UQL neither emits nor reads. Converting a distance bound to that scale would mean guessing which metric produced the score, and guessing wrong drops the wrong rows silently, so $near throws there. Project the score with $project and filter on it in your app instead. MySQL and D1 throw the same way they do for a vector $sort.

---

Distance Metrics

Metric	Postgres Operator	CockroachDB Operator	MariaDB Function	SQLite Function (sqlite-vec)	libSQL Function	Turso Function	MSSQL Function	MongoDB Atlas	Use Case
cosine	<=>	<=>	VEC_DISTANCE_COSINE	vec_distance_cosine	vector_distance_cos	vector_distance_cos	VECTOR_DISTANCE('cosine')	✅ (index-defined)	Text embeddings (OpenAI, Cohere)
l2	<->	<->	VEC_DISTANCE_EUCLIDEAN	vec_distance_L2	vector_distance_l2	vector_distance_l2	VECTOR_DISTANCE('euclidean')	✅ (index-defined)	Image search, spatial data
inner	<#>	<#>	❌	❌	❌	vector_distance_dot	VECTOR_DISTANCE('dot')	✅ (index-defined)	Maximum inner product
l1	<+>	❌	❌	vec_distance_L1	❌	❌	❌	❌	Manhattan distance

Any metric marked ❌ throws at query build time on that dialect, naming the metric, rather than reaching the database as a call to a function it does not have.

l1 is not yet implemented on CockroachDB, and inner needs Turso’s Rust engine (vector_distance_dot); no libSQL build has it.

If omitted, $distance defaults to 'cosine'. You can also set a default per-field:

@Field({ type: 'vector', dimensions: 1536, distance: 'l2' })
embedding?: number[];

Queries on this field use l2 unless overridden with $distance at query time, in $sort or in $near.

---

Distance Projection

Project the computed distance as a named field in the result with $project:

You write

import type { WithDistance } from 'uql-orm';

const results = (await pool.findMany(Article, {
  $select: { id: true, title: true },
  $sort: {
    embedding: { $vector: queryVec, $distance: 'cosine', $project: 'distance' },
  },
  $limit: 10,
})) as WithDistance<Article, 'distance'>[];

results.forEach((r) => console.log(r.title, r.distance));

PostgreSQL / CockroachDB

SELECT "id", "title", "embedding" <=> $1::vector AS "distance" FROM "Article"
ORDER BY "distance"
LIMIT 10

MariaDB

SELECT `id`, `title`, VEC_DISTANCE_COSINE(`embedding`, VEC_FromText(?)) AS `distance` FROM `Article`
ORDER BY `distance`
LIMIT 10

SQLite, sqlite-vec

SELECT `id`, `title`, vec_distance_cosine(`embedding`, ?) AS `distance` FROM `Article`
ORDER BY `distance`
LIMIT 10

libSQL / Turso

SELECT `id`, `title`, vector_distance_cos(`embedding`, ?) AS `distance` FROM `Article`
ORDER BY `distance`
LIMIT 10

MSSQL

SELECT "id", "title", VECTOR_DISTANCE('cosine', "embedding", CAST(@p1 AS VECTOR(1536))) AS "distance" FROM "Article"
ORDER BY "distance"
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

MongoDB Atlas

On MongoDB the score becomes a real field with $addFields, which the query’s own projection then keeps like any other:

[
  {
    "$vectorSearch": {
      "index": "embedding_index",
      "path": "embedding",
      "queryVector": ["..."],
      "numCandidates": 100,
      "limit": 10
    }
  },
  { "$addFields": { "distance": { "$meta": "vectorSearchScore" } } },
  { "$project": { "_id": 1, "title": 1, "distance": 1 } }
]

Adding it rather than projecting it is what lets a query name no columns at all and still get whole documents back, each with its score.

Find methods return the plain entity, so annotate the result with the exported WithDistance<Article, 'distance'> helper to type the projected distance field.

The projection costs nothing extra: on SQL dialects ORDER BY references the projected alias instead of recomputing the distance expression, and on MongoDB Atlas returns the score through $meta.

---

Vector Types

UQL supports three vector storage types; use the one that best fits your model and performance needs:

Type	SQL (Postgres)	SQL (CockroachDB)	Storage	Max Dimensions	Use Case
'vector'	VECTOR(n)	VECTOR(n)	32-bit float	2,000	Standard embeddings (OpenAI, etc.)
'halfvec'	HALFVEC(n)	VECTOR(n)	16-bit float	4,000	50% storage savings, near-identical accuracy
'sparsevec'	SPARSEVEC(n)	VECTOR(n)	Sparse	1,000,000	SPLADE, BM25-style sparse retrieval

@Field({ type: 'vector', dimensions: 1536 })    // OpenAI ada-002
embedding?: number[];

@Field({ type: 'halfvec', dimensions: 1536 })   // Same model, half storage
embedding?: number[];

@Field({ type: 'sparsevec', dimensions: 30000 }) // SPLADE sparse
sparseEmbedding?: number[];

halfvec and sparsevec come from pgvector, so they exist on Postgres alone. CockroachDB, MariaDB and MSSQL map them to their own VECTOR type and the SQLite family to TEXT holding a JSON array, casts included, so a halfvec field binds as vector on CockroachDB where the type does not exist.

Whichever you declare, you hand UQL a dense number[]. For sparsevec it converts to pgvector’s sparse literal ({1:1,3:2}/3) on the way out, since that type rejects the dense form. An index on a narrower type gets the matching operator class (halfvec_cosine_ops). IVFFlat refuses sparsevec and l1, pgvector having no operator class for either, so use hnsw there.

---

Vector Indexes

Define vector indexes with @Index() for efficient approximate nearest-neighbor (ANN) search:

Index Type	Postgres	CockroachDB	MariaDB	SQLite family	MongoDB Atlas	Notes
hnsw	✅ USING hnsw with operator classes	✅ Its native vector index	❌	❌	❌	Best accuracy, higher memory
ivfflat	✅ USING ivfflat with lists param	❌	❌	❌	❌	Faster build, large datasets
vector	❌	✅ Native CREATE VECTOR INDEX	✅ CREATE VECTOR INDEX	❌	❌	CockroachDB’s and MariaDB’s own native vector index
vectorSearch	❌	❌	❌	❌	✅ Atlas vector search index	MongoDB’s managed ANN index

MySQL is absent from the table on purpose: it has no vector index, so any of these types throws when migrations are generated rather than emitting DDL the server rejects. MSSQL is absent too: its DiskANN index is still a preview feature, so a search there computes every distance, and migrations refuse any of these types for the same reason as on MySQL.

Postgres HNSW

@Index(['embedding'], { type: 'hnsw', distance: 'cosine', m: 16, efConstruction: 64 })

Postgres IVFFlat

@Index(['embedding'], { type: 'ivfflat', distance: 'l2', lists: 100 })

CockroachDB

@Index(['embedding'], { type: 'vector', distance: 'cosine' })

MariaDB

@Index(['embedding'], { type: 'vector', distance: 'cosine', m: 8 })

MongoDB Atlas

@Index(['embedding'], { type: 'vectorSearch', name: 'my_search_index' })

CockroachDB shares MariaDB’s type: 'vector' marker but emits a standalone CREATE VECTOR INDEX "idx" ON "table" ("embedding" vector_cosine_ops), with no access-method keyword of the kind pgvector’s USING ivfflat / USING hnsw carries. type: 'hnsw' builds the same index, so a Postgres entity migrates there; ivfflat is refused. It covers cosine, l2 and inner but not l1, and UQL does not yet map its tuning knobs, which are named differently: m/efConstruction are dropped there.

SQLite, libSQL, Turso and D1 have no USING <method> clause on CREATE INDEX, so any type you declare emits a plain index: a Postgres entity migrates there unchanged, without ANN. Vector queries on these engines scan every row and compute the distance, which holds up to tens of thousands of vectors.

MongoDB uses @Index with type: 'vectorSearch' to tell UQL which Atlas index the $vectorSearch stage should reference. Creating that index is done in Atlas; UQL does not manage Atlas search indexes.

---

Tuning Recall

An ANN index is approximate: it explores part of the graph and returns what it found, so a query can miss a row that is genuinely closer. $candidates widens that exploration for one query, trading speed for recall.

You write

const results = await pool.transaction((querier) =>
  querier.findMany(Article, {
    $sort: { embedding: { $vector: queryVec } },
    $limit: 10,
    $candidates: 200,
  }),
);

The number is the index’s own unit, not a portable one, so it is not comparable across index types:

Engine	Index	Becomes	Engine default
PostgreSQL / CockroachDB	hnsw	SET LOCAL hnsw.ef_search	40
PostgreSQL / CockroachDB	ivfflat	SET LOCAL ivfflat.probes	1
MariaDB	vector	SET STATEMENT mhnsw_ef_search=N FOR ...	20
MongoDB Atlas	vectorSearch	numCandidates in the stage	10x $limit

$candidates is ignored where there is nothing to widen: SQLite, libSQL, Turso and MSSQL compute every distance, and a field carrying no ANN index is scanned exactly either way. It is also statement-level, like $lock - a populated relation’s rows are assembled after the ranking, so a relation’s own query has nothing to tune.

Postgres needs an open transaction

SET LOCAL applies to the enclosing transaction and to nothing at all without one. Rather than let a query look tuned while running at the default recall, UQL refuses it; wrap the query in pool.transaction(...). MariaDB scopes the variable to the single statement and MongoDB sets it on the stage, so neither needs one.

Thresholds need it most

A distance predicate on an HNSW index is where low recall becomes visible: the index hands back its candidate list and the predicate then removes from it, so you can get fewer rows than qualify. When a query combines $near with a vector $sort, UQL adds hnsw.iterative_scan = strict_order alongside ef_search so the scan keeps going until the limit is filled: strict_order, never relaxed_order, which would return rows out of distance order and contradict the ORDER BY. Nothing to opt into: pair a $near with a vector $sort and $candidates, as the RAG shape above does, and you get it.

---

Next Steps

• AI & RAG: End-to-end walkthrough: ingestion, querying, RAG thresholds.
• Indexes: Declaring hnsw / ivfflat vector indexes and their metric.
• Full-Text Search: Keyword search, and how to combine it with vectors.
• Querier API: The full query API.