Building a RAG API with Express.js and pgvector

A retrieval-augmented generation service doesn’t need a dedicated vector DB to get started; a plain Postgres instance with the pgvector extension handles embeddings and cosine search well enough for most production workloads. This tutorial builds an express.js rag api pgvector backend that ingests text, embeds it via an OpenAI-compatible endpoint, stores the vectors, and answers questions by combining similarity search with an LLM call.

Prerequisites

• Node.js 18+ and npm
• PostgreSQL 15+ with the vector extension available (CREATE EXTENSION vector;)
• An OpenAI-compatible API key. For embeddings and chat we’ll point the OpenAI SDK at n4n.ai’s OpenAI-compatible endpoint, which exposes 240+ models behind one URL and fails over automatically when a provider is degraded.
• Basic familiarity with Express and SQL

Set these environment variables in a .env file:

DATABASE_URL=postgres://user:pass@localhost:5432/ragdb
LLM_API_KEY=sk-...
LLM_BASE_URL=https://api.n4n.ai/v1
EMBED_MODEL=text-embedding-3-small
CHAT_MODEL=gpt-4o-mini

Project Setup

Initialize the project and install dependencies:

npm init -y
npm install express pg openai dotenv

Create index.mjs as the entry point. We’ll use ES modules and the official openai SDK, which works against any compliant base URL.

import express from 'express';
import { Pool } from 'pg';
import OpenAI from 'openai';
import dotenv from 'dotenv';
dotenv.config();

const app = express();
app.use(express.json());

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const openai = new OpenAI({
  apiKey: process.env.LLM_API_KEY,
  baseURL: process.env.LLM_BASE_URL,
});

Database Schema

Connect to Postgres and create the table. The embedding dimension must match your model—text-embedding-3-small outputs 1536 floats.

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE IF NOT EXISTS documents (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  embedding VECTOR(1536)
);
-- ivfflat index is a reasonable starting point for < 1M rows
CREATE INDEX IF NOT EXISTS documents_embedding_idx
  ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Run it with psql "$DATABASE_URL" -f schema.sql. Expected output:

CREATE EXTENSION
CREATE TABLE
CREATE INDEX

Embedding Helper

Wrap the embeddings call so the rest of the code stays model-agnostic:

async function embed(text) {
  const res = await openai.embeddings.create({
    model: process.env.EMBED_MODEL,
    input: text,
  });
  return res.data[0].embedding;
}

Ingest Endpoint

The express.js rag api pgvector design splits writes and reads into two routes. Start with ingestion:

app.post('/ingest', async (req, res) => {
  const { text } = req.body;
  if (!text || typeof text !== 'string') {
    return res.status(400).json({ error: 'text string required' });
  }
  const embedding = await embed(text);
  await pool.query(
    'INSERT INTO documents (content, embedding) VALUES ($1, $2)',
    [text, embedding]
  );
  res.json({ ok: true });
});

Test it:

curl -s localhost:3000/ingest \
  -H 'Content-Type: application/json' \
  -d '{"text":"Postgres pgvector stores embeddings as first-class column types."}'

Expected response:

{"ok":true}

Ingest a second doc for a meaningful search:

curl -s localhost:3000/ingest -H 'Content-Type: application/json' \
  -d '{"text":"Express.js is a minimal Node.js HTTP server framework."}'

Similarity Search

pgvector uses the <=> operator for cosine distance. Lower distance means higher similarity; 1 - distance is the cosine similarity score.

async function retrieve(queryEmbedding, k = 5) {
  const { rows } = await pool.query(
    `SELECT content, 1 - (embedding <=> $1) AS similarity
     FROM documents
     ORDER BY embedding <=> $1
     LIMIT $2`,
    [queryEmbedding, k]
  );
  return rows;
}

Query Endpoint

The RAG flow embeds the question, pulls the top-k contexts, and forwards them to the chat model with a strict system prompt.

app.post('/query', async (req, res) => {
  const { question } = req.body;
  if (!question) return res.status(400).json({ error: 'question required' });

  const qEmbed = await embed(question);
  const contexts = await retrieve(qEmbed, 5);
  const contextText = contexts.map(r => `- ${r.content}`).join('\n');

  const completion = await openai.chat.completions.create({
    model: process.env.CHAT_MODEL,
    messages: [
      {
        role: 'system',
        content: 'Answer the user using ONLY the following context. If unsure, say so.\n\n' + contextText
      },
      { role: 'user', content: question }
    ],
  });

  res.json({
    answer: completion.choices[0].message.content,
    sources: contexts.map(c => ({ text: c.content, score: c.similarity })),
  });
});

Start the server (node index.mjs) and hit the endpoint:

curl -s localhost:3000/query -H 'Content-Type: application/json' \
  -d '{"question":"What does pgvector do?"}'

Expected shape:

{
  "answer": "pgvector stores embeddings as first-class column types in Postgres.",
  "sources": [
    {"text":"Postgres pgvector stores embeddings as first-class column types.","score":0.91},
    {"text":"Express.js is a minimal Node.js HTTP server framework.","score":0.74}
  ]
}

Chunking Real Documents

Single-string ingestion is fine for demo data, but real corpora need chunking. Split on paragraph or token boundaries (≈500 tokens) before calling /ingest per chunk. Keep chunk size below your model’s context window minus headroom for the prompt.

function chunk(text, maxChars = 1500) {
  const paras = text.split(/\n\s*\n/);
  const out = [];
  let buf = '';
  for (const p of paras) {
    if ((buf + p).length > maxChars) {
      if (buf) out.push(buf.trim());
      buf = p;
    } else buf += '\n\n' + p;
  }
  if (buf) out.push(buf.trim());
  return out;
}

Loop the chunks:

for (const c of chunk(longDoc)) {
  await fetch('http://localhost:3000/ingest', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ text: c }),
  });
}

Production Notes

• Connection pooling: The pg Pool handles concurrency, but tune max to your DB’s connection limit.
• Index tuning: ivfflat needs lists proportional to row count; for millions of rows consider hnsw (pgvector 0.5+).
• Metadata filtering: Add a jsonb metadata column and filter in the WHERE clause before the ORDER BY to scope retrieval per tenant.
• Cache control: When calling the LLM gateway, forward provider cache-control hints if your gateway honors them—this cuts cost on repeated context prefixes.
• Embedding consistency: Never change EMBED_MODEL without re-embedding every row; mismatched dimensions break the cosine operator.

The express.js rag api pgvector pattern above is the same core we run for mid-traffic internal tools: Postgres as the system of record, a thin Express layer, and a compliant LLM endpoint handling both embeddings and generation.