Vector DB Basics With pgvector

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
  id        BIGSERIAL PRIMARY KEY,
  body      TEXT NOT NULL,
  embedding vector(1536) NOT NULL   -- 1536 for text-embedding-3-small
);

-- HNSW index for fast approximate nearest-neighbor search
CREATE INDEX ON docs
  USING hnsw (embedding vector_cosine_ops);

import OpenAI from "openai";
import { Pool } from "pg";

const openai = new OpenAI();
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function embed(text: string): Promise<number[]> {
  const r = await openai.embeddings.create({
    model: "text-embedding-3-small",
    input: text,
  });
  return r.data[0].embedding;
}

export async function insert(body: string) {
  const vec = await embed(body);
  await pool.query(
    "INSERT INTO docs (body, embedding) VALUES ($1, $2)",
    [body, `[${vec.join(",")}]`]
  );
}

export async function search(query: string, k = 5) {
  const vec = await embed(query);
  const { rows } = await pool.query(
    "SELECT id, body, 1 - (embedding <=> $1) AS score FROM docs ORDER BY embedding <=> $1 LIMIT $2",
    [`[${vec.join(",")}]`, k]
  );
  return rows;
}