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; }