BuntokBuntok

Vector Search#

Semantic search that matches by meaning, not keywords. Powered by PostgreSQL pgvector and AI-generated embeddings.

Warning
Vector search requires PostgreSQL with the pgvector extension. MySQL is not supported.

Overview#

Full-text search matches by keywords - the words in the query must appear in the data. Vector search matches by meaning - a search for "best wineries" can surface an article titled "Top Vineyards to Visit" even though the words don't overlap.

FeatureFull-TextVector
Matches byKeywordsMeaning
SpeedFastSlower (embedding + similarity)
AccuracyExact termsSemantic similarity
Best forIDs, codes, exact namesDescriptions, articles, recommendations

How It Works#

typescript
// 1. Store: convert text to vector embedding, save alongside data
const embedding = await Embeddings::for([article.title]).generate();
await prisma.article.update({
  where: { id: article.id },
  data: { embedding: embedding[0].embedding }  // [0.012, -0.044, ...]
});

// 2. Query: convert search query to embedding, find closest vectors
const queryEmbedding = await Embeddings::for(["best wineries"]).generate();
const results = await prisma.$queryRaw`
  SELECT *, 1 - (embedding <=> ${queryVector}::vector) AS similarity
  FROM articles
  WHERE embedding IS NOT NULL
  ORDER BY embedding <=> ${queryVector}::vector
  LIMIT 10
`;

Setup#

1. Enable pgvector Extension#

typescript
-- Run once as superuser
CREATE EXTENSION IF NOT EXISTS vector;

2. Add Vector Column#

Add a vector column to your table. The dimensions must match your embedding model's output (e.g., 1536 for OpenAI text-embedding-3-small).

3. Create HNSW Index#

typescript
-- HNSW index for fast similarity search
CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops);
Info
HNSW (Hierarchical Navigable Small World) is the recommended index for read-heavy semantic search. It offers strong recall without a training phase.

Prisma#

Query#

typescript
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function vectorSearch(queryEmbedding: number[], limit = 10, minSimilarity = 0.3) {
  const vector = `[${queryEmbedding.join(",")}]`;

  return prisma.$queryRaw`
    SELECT *,
      1 - (embedding <=> ${vector}::vector) AS similarity
    FROM articles
    WHERE embedding IS NOT NULL
      AND 1 - (embedding <=> ${vector}::vector) >= ${minSimilarity}
    ORDER BY embedding <=> ${vector}::vector
    LIMIT ${limit}
  `;
}

// Usage
const results = await vectorSearch(queryEmbedding, 10, 0.3);

Helper Function#

typescript
// Reusable vector search helper for Prisma
async function vectorSearch<T>(
  prisma: PrismaClient,
  table: string,
  embedding: number[],
  opts: { minSimilarity?: number; limit?: number; where?: string } = {}
): Promise<(T & { similarity: number })[]> {
  const vector = `[${embedding.join(",")}]`;
  const minSimilarity = opts.minSimilarity ?? 0.3;
  const limit = opts.limit ?? 10;
  const whereClause = opts.where ? `AND ${opts.where}` : "";

  return prisma.$queryRawUnsafe(`
    SELECT *,
      1 - (embedding <=> '${vector}'::vector) AS similarity
    FROM ${table}
    WHERE embedding IS NOT NULL
      AND 1 - (embedding <=> '${vector}'::vector) >= ${minSimilarity}
    ${whereClause}
    ORDER BY embedding <=> '${vector}'::vector
    LIMIT ${limit}
  `);
}

// Usage
const results = await vectorSearch(prisma, "articles", queryEmbedding, {
  minSimilarity: 0.3,
  limit: 10,
  where: "published = true"
});

Drizzle#

Query#

typescript
import { sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";

const db = drizzle(process.env.DATABASE_URL!);

async function vectorSearch(queryEmbedding: number[], limit = 10, minSimilarity = 0.3) {
  const vector = `[${queryEmbedding.join(",")}]`;

  return db.execute(sql`
    SELECT *,
      1 - (embedding <=> ${vector}::vector) AS similarity
    FROM articles
    WHERE embedding IS NOT NULL
      AND 1 - (embedding <=> ${vector}::vector) >= ${minSimilarity}
    ORDER BY embedding <=> ${vector}::vector
    LIMIT ${limit}
  `);
}

// Usage
const results = await vectorSearch(queryEmbedding, 10, 0.3);

Helper Function#

typescript
import { sql } from "drizzle-orm";
import type { NodePGDatabase } from "drizzle-orm/node-postgres";

async function vectorSearch<T>(
  db: NodePGDatabase<any>,
  table: string,
  embedding: number[],
  opts: { minSimilarity?: number; limit?: number; where?: string } = {}
) {
  const vector = `[${embedding.join(",")}]`;
  const minSimilarity = opts.minSimilarity ?? 0.3;
  const limit = opts.limit ?? 10;
  const whereClause = opts.where ? sql` AND ${sql.raw(opts.where)}` : sql``;

  return db.execute(sql`
    SELECT *,
      1 - (embedding <=> ${vector}::vector) AS similarity
    FROM ${sql.raw(table)}
    WHERE embedding IS NOT NULL
      AND 1 - (embedding <=> ${vector}::vector) >= ${minSimilarity}
    ${whereClause}
    ORDER BY embedding <=> ${vector}::vector
    LIMIT ${limit}
  `);
}

TypeORM#

Query#

typescript
import { DataSource } from "typeorm";

const dataSource = new DataSource({ /* ... */ });

async function vectorSearch(queryEmbedding: number[], limit = 10, minSimilarity = 0.3) {
  const vector = `[${queryEmbedding.join(",")}]`;

  return dataSource.query(`
    SELECT *,
      1 - (embedding <=> $1::vector) AS similarity
    FROM articles
    WHERE embedding IS NOT NULL
      AND 1 - (embedding <=> $1::vector) >= $2
    ORDER BY embedding <=> $1::vector
    LIMIT $3
  `, [vector, minSimilarity, limit]);
}

// Usage
const results = await vectorSearch(queryEmbedding, 10, 0.3);

Helper Function#

typescript
import type { DataSource } from "typeorm";

async function vectorSearch<T>(
  dataSource: DataSource,
  table: string,
  embedding: number[],
  opts: { minSimilarity?: number; limit?: number; where?: string } = {}
): Promise<(T & { similarity: number })[]> {
  const vector = `[${embedding.join(",")}]`;
  const minSimilarity = opts.minSimilarity ?? 0.3;
  const limit = opts.limit ?? 10;
  const whereClause = opts.where ? `AND ${opts.where}` : "";

  return dataSource.query(`
    SELECT *,
      1 - (embedding <=> $1::vector) AS similarity
    FROM ${table}
    WHERE embedding IS NOT NULL
      AND 1 - (embedding <=> $1::vector) >= $2
    ${whereClause}
    ORDER BY embedding <=> $1::vector
    LIMIT $3
  `, [vector, minSimilarity, limit]);
}

Hybrid Search#

Combine full-text search (keyword precision) with vector search (semantic meaning) for best results.

typescript
// Prisma example: hybrid search
async function hybridSearch(query: string, queryEmbedding: number[]) {
  const vector = `[${queryEmbedding.join(",")}]`;

  // Full-text search for exact keywords
  const keywordResults = await prisma.article.findMany({
    where: {
      OR: [
        { title: { contains: query, mode: "insensitive" } },
        { body: { contains: query, mode: "insensitive" } },
      ],
    },
    take: 20,
  });

  // Vector search for semantic matches
  const semanticResults = await prisma.$queryRaw`
    SELECT *, 1 - (embedding <=> ${vector}::vector) AS similarity
    FROM articles
    WHERE embedding IS NOT NULL
    ORDER BY embedding <=> ${vector}::vector
    LIMIT 20
  `;

  // Merge and deduplicate
  const seen = new Set();
  return [...semanticResults, ...keywordResults].filter((item) => {
    if (seen.has(item.id)) return false;
    seen.add(item.id);
    return true;
  });
}

Recommendations#

Find similar items by using an existing record's embedding as the anchor:

typescript
// Find articles similar to article #42
const article = await prisma.article.findUnique({ where: { id: 42 } });

const recommendations = await prisma.$queryRaw`
  SELECT *,
    1 - (embedding <=> ${article.embedding}::vector) AS similarity
  FROM articles
  WHERE id != 42
    AND embedding IS NOT NULL
  ORDER BY embedding <=> ${article.embedding}::vector
  LIMIT 5
`;

Cost Considerations#

OperationCost
pgvector extensionFree (open source)
Embedding generationAPI call to AI provider (~$0.0001 per text)
Vector storageSame as regular column storage
Similarity queryFree (computed by PostgreSQL)
Info
Generate embeddings asynchronously using a queue job. Do not block HTTP requests on embedding API calls.
PreviousAI ModuleNext API Docs