The last post built a question answering setup with LangChain and Qdrant. It worked, but two things nagged at me. I couldn’t tell you what most of the code was doing, and I’d added a second database to a project that already had Postgres in it.
So this time: no framework, and no new database. Just Postgres with the
pgvector extension, the pg driver, and about a hundred lines of
TypeScript in a single file. It turns out RAG is small enough to write by
hand, and writing it by hand is the fastest way to understand it.
What RAG actually is
Retrieval-augmented generation is three steps in a trench coat:
- Find the few paragraphs of your own text that relate to the question.
- Paste them into the prompt.
- Ask the model to answer using them.
Step 2 and 3 are string concatenation and one API call. Everything interesting is in step 1, and that’s what the database is for.
What an embedding is
An embedding is a list of numbers that stands in for the meaning of a piece of text. You send text to an embedding model, you get back a fixed-length array of floats — 1536 of them for the model I use below.
The useful property is that text with similar meaning gets similar numbers. “When do I replace the chain?” and “the wear gauge dropped past 0.75” have almost no words in common, but their embeddings land close together. So if you embed every paragraph of your notes once, you can find the relevant paragraphs for any question by comparing numbers rather than matching keywords.
“Close together” needs a definition, and that definition is what pgvector adds to Postgres.
The three pieces
Postgres with pgvector. pgvector is an extension that adds a vector
column type and a handful of distance operators. That’s genuinely all it
is. Your embeddings live in a normal table, next to the text they came
from, and you query them with normal SQL. If your app already has Postgres,
this is one CREATE EXTENSION away.
OpenRouter. One key for both models I need: an embedding model to turn
text into vectors, and a chat model to write the final answer. It speaks
the OpenAI format, so the openai package talks to it unchanged. Any other
provider works the same way — swap the baseURL.
The pg driver. node-postgres. No ORM, no query builder. I write the
SQL, which is the point of the exercise.
What you need
- Node.js 22.18 or newer. Newer Node runs
.tsfiles directly by stripping the types out, so there’s no build step and notsconfig.jsonin this tutorial. Check withnode --version. - Docker, to run Postgres locally.
- An OpenRouter API key from openrouter.ai,
starting with
sk-or-v1-. Embedding models cost money, but barely — indexing the notes in this post costs a fraction of a cent. - Two npm packages. Step 2.
Step 1: Start Postgres with pgvector
The pgvector project publishes a Postgres image with the extension already compiled in, so you don’t have to build anything:
docker run --name rag-postgres -p 5432:5432 \
-e POSTGRES_PASSWORD=postgres \
-v "$(pwd)/pgdata:/var/lib/postgresql/data" \
pgvector/pgvector:pg17Leave that running in its own terminal. The -v keeps the data in a
pgdata folder so it survives a restart. To poke around later:
docker exec -it rag-postgres psql -U postgresThe extension files are in the image, but they’re not switched on for your
database yet. The script does that with CREATE EXTENSION IF NOT EXISTS vector on startup.
Step 2: Set up the project
mkdir notes-rag && cd notes-rag
npm init -y
npm pkg set type=module
npm install pg openai
npm install -D @types/pg @types/nodeTwo runtime packages. openai ships its own types; pg doesn’t, hence
@types/pg. The dev dependencies are only there so your editor knows what
client.query returns — Node ignores types entirely when it runs the file.
That’s worth being clear about, because it surprised me the first time:
Node doesn’t type-check anything. It deletes the types and runs the
JavaScript that’s left. You get red squiggles in your editor and clean runs
in the terminal, which is a fine trade for a script this size. Run npx tsc --noEmit if you want the check to be real.
The pieces
Here’s each part on its own before the whole file.
Connect
import { Client } from "pg";
const database = new Client({
connectionString: "postgres://postgres:postgres@localhost:5432/postgres",
});
await database.connect();One connection is plenty for a script. A web app would use Pool instead.
Make the table
await database.query("CREATE EXTENSION IF NOT EXISTS vector");
await database.query(`
CREATE TABLE IF NOT EXISTS chunks (
id bigserial PRIMARY KEY,
source text NOT NULL,
content text NOT NULL,
embedding vector(1536) NOT NULL
)
`);An ordinary table with one unusual column. vector(1536) means “an array
of exactly 1536 floats”, and 1536 is not a number I picked — it’s how many
numbers text-embedding-3-small returns. The column size and the
embedding model are welded together. Change the model and you have to
change the column, and re-embed everything in it, because vectors from
different models are not comparable.
content sitting next to embedding is the bit I like about doing this in
Postgres. The vector is how you find the row; the text is what you
actually send to the model, and it’s right there in the same row.
Turn text into numbers
import OpenAI from "openai";
const openrouter = new OpenAI({
baseURL: "https://openrouter.ai/api/v1",
apiKey: "sk-or-v1-your-key-here",
});
async function embed(texts: string[]): Promise<number[][]> {
const response = await openrouter.embeddings.create({
model: "openai/text-embedding-3-small",
input: texts,
});
return response.data.map((entry) => entry.embedding);
}input takes an array, so a whole document’s worth of chunks goes in one
request instead of twenty. The order of response.data matches the order
you sent, which is what lets me line the vectors back up with their text
later.
const [vector] = await embed(["When do I replace the chain?"]);
console.log(vector.length); // 1536
console.log(vector.slice(0, 3)); // [ -0.0182, 0.0311, 0.0047 ]You’ll never read these numbers. It’s still worth printing them once.
Cut the text into chunks
Don’t embed a whole document as one vector. Two reasons: the meaning of a long text averages out into something that matches nothing in particular, and whatever you retrieve gets pasted into the prompt, so a hit on a 50-page document means sending 50 pages.
Frameworks give you a splitter. It’s twelve lines:
const CHUNK_SIZE = 300;
const CHUNK_OVERLAP = 50;
function splitIntoChunks(text: string): string[] {
const paragraphs = text
.split(/\n\s*\n/)
.map((paragraph) => paragraph.trim())
.filter((paragraph) => paragraph.length > 0);
const chunks: string[] = [];
let currentChunk = "";
for (const paragraph of paragraphs) {
const wouldOverflow = currentChunk.length + paragraph.length > CHUNK_SIZE;
if (wouldOverflow && currentChunk.length > 0) {
chunks.push(currentChunk);
currentChunk = currentChunk.slice(-CHUNK_OVERLAP);
}
currentChunk =
currentChunk.length > 0 ? `${currentChunk}\n\n${paragraph}` : paragraph;
}
if (currentChunk.length > 0) chunks.push(currentChunk);
return chunks;
}Paragraphs are the natural seam in notes, so it splits on blank lines and
packs paragraphs together until adding the next one would go over the size.
The slice(-CHUNK_OVERLAP) carries the tail of the previous chunk into the
next one, so a thought that straddles a boundary survives whole in one of
the two.
It has one honest limitation: a single paragraph longer than CHUNK_SIZE
stays in one oversized chunk. For notes that’s fine. For real documents
you’d add a fallback that cuts long paragraphs on sentence boundaries.
Store a chunk
await database.query(
"INSERT INTO chunks (source, content, embedding) VALUES ($1, $2, $3::vector)",
[note.source, chunkText, JSON.stringify(embedding)],
);JSON.stringify on a number array gives you [-0.018,0.031,...], and that
string is exactly pgvector’s text format for a vector. Lucky, but not a
coincidence — it’s why the format looks like that.
How similarity search works
Picture each embedding as an arrow from the origin in 1536-dimensional space. You can’t, but the maths doesn’t mind. Two pieces of text with similar meaning produce arrows pointing in a similar direction, and “similar direction” is something you can measure: the angle between them.
Cosine distance is that angle expressed as a number between 0 and 2:
- 0 — pointing the same way. Same meaning.
- 1 — at right angles. Unrelated.
- 2 — pointing opposite ways. Rare with real text.
Searching, then, is: embed the question, measure the distance from that vector to every row, sort ascending, keep the top few. There’s no index and no cleverness in that — it really is a scan over the table — but Postgres sorts a few thousand distances without noticing. More on indexes at the end.
Here’s the query:
const result = await database.query(
`SELECT source, content, embedding <=> $1::vector AS distance
FROM chunks
ORDER BY embedding <=> $1::vector
LIMIT $2`,
[JSON.stringify(questionEmbedding), 3],
);What embedding <=> $1::vector means
That line is the whole retrieval engine, so it’s worth taking apart piece by piece.
embedding is the column. Nothing special.
<=> is an operator pgvector adds to Postgres. It takes two vectors and
returns the cosine distance between them as a float. It’s an operator in
exactly the same sense + is — SELECT '[1,0]'::vector <=> '[0,1]'::vector
returns 1 in psql. pgvector ships a few of them:
| Operator | Distance | Use it when |
|---|---|---|
<=> | Cosine | Text embeddings. The default choice. |
<-> | Euclidean (L2) | You care about magnitude as well as direction |
<#> | Negative inner product | You know your vectors are normalised |
<+> | Taxicab (L1) | Rarely, for sparse-ish data |
For text embeddings from the usual models, <=> is the one you want.
$1 is a parameter placeholder. node-postgres sends the values in the
second argument to query separately from the SQL, so nothing is glued
into the query string and there’s no injection to worry about. $1 is the
question’s vector, $2 is the limit. The same $1 appears twice, once in
SELECT and once in ORDER BY, and that’s fine — one parameter can be
used as many times as you like.
::vector is a Postgres cast, the short form of CAST(... AS vector). The
driver hands $1 over as the string [-0.018,0.031,...] with no type
attached, and leaves Postgres to work out what it is. In this particular
query it could work it out on its own, because <=> only ever takes
vectors. The cast is there because “could work it out” stops being true
quickly. Put a parameter somewhere that doesn’t pin its type — comparing
two parameters to each other, say — or use a client that sends parameters
explicitly typed as text, and you get one of these:
error: could not determine data type of parameter $1
error: operator does not exist: vector <=> textBoth are Postgres saying the same thing: I know <=> for two vectors, and
that wasn’t two vectors. Writing the cast every time means the question
never comes up, and it documents what’s in the string for the next person
reading the query.
Put back together: for every row, measure the cosine distance between its
embedding and this vector I’m handing you, sort by that distance, and give
me the closest three. ORDER BY is ascending by default, and smaller
distance means more similar, so ascending is what you want — sorting the
wrong way here returns the three least relevant chunks in your database,
which is a fun bug to debug.
The extra AS distance in the SELECT list isn’t needed for the search.
It’s there so you can look at the numbers, and you should:
distance source content
0.28 flat.md Bins: paper on the first Monday of the month...
0.49 flat.md The boiler resets with the black button...
0.52 bike.md # Bike. The chain is a KMC X11...That’s the answer to “which bin goes out on Friday?”. The top hit is the one that matters, and the gap between 0.28 and 0.49 tells you it’s a confident match. When every row comes back around 0.5, your notes don’t contain the answer, and the model is about to guess unless you’ve told it not to.
Ask the model
const context = relevantChunks
.map((matchedChunk) => `[${matchedChunk.source}]\n${matchedChunk.content}`)
.join("\n\n");
const stream = await openrouter.chat.completions.create({
model: "openai/gpt-4o-mini",
stream: true,
messages: [
{
role: "system",
content:
"Answer the question using only the notes below. If they do not " +
`contain the answer, say you don't know.\n\n${context}`,
},
{ role: "user", content: question },
],
});
for await (const streamChunk of stream) {
process.stdout.write(streamChunk.choices[0]?.delta?.content ?? "");
}This is a normal chat request. The retrieved text goes into the system
message with a [filename] label above each chunk, the question goes in as
the user message, and that’s the entire “augmented” part of
retrieval-augmented generation. The model has no idea a database was
involved.
The instruction to say “I don’t know” earns its place. Without it the model will happily fill a gap in your notes with something plausible.
The whole thing: one file
Everything above, in one file, including the notes themselves so there’s
nothing else to create. Save it as rag.ts, paste your key in at the top.
// rag.ts
import { Client } from "pg";
import OpenAI from "openai";
// Paste your key here. Fine for a script on your own machine.
const API_KEY = "sk-or-v1-paste-your-key-here";
const DATABASE_URL = "postgres://postgres:postgres@localhost:5432/postgres";
const EMBEDDING_MODEL = "openai/text-embedding-3-small";
const EMBEDDING_DIMENSIONS = 1536;
const CHAT_MODEL = "openai/gpt-4o-mini";
const CHUNK_SIZE = 300;
const CHUNK_OVERLAP = 50;
const CHUNKS_TO_RETRIEVE = 3;
// The knowledge base. A real one would read a folder off disk.
const notes = [
{
source: "bike.md",
text: `# Bike
The chain is a KMC X11. Replace it once the wear gauge drops past 0.75,
which so far has been about every 3,000 km. A worn chain chews through the
cassette, so swapping it early is the cheap option.
Tyre pressure is 5.5 bar front, 6 bar rear. The track pump in the cellar
reads about 0.3 bar high, so aim slightly over.
The rear brake rubs after the wheel comes out. Loosen the two caliper
bolts, squeeze the lever, tighten the bolts while it is still held.`,
},
{
source: "flat.md",
text: `# Flat
The boiler resets with the black button behind the little hatch. Pressure
should sit between 1 and 1.5 bar. If it drops below 1, top it up with the
grey filling loop underneath and stop as soon as it reaches 1.2.
Bins: paper on the first Monday of the month, plastic every other Tuesday,
general waste every Friday. The code for the bin store is 4417.
The router lives in the hallway cupboard. If the internet drops, unplug it
for thirty seconds. The wifi password is on a sticker underneath.`,
},
];
// A row coming back from the search query.
type MatchedChunk = {
source: string;
content: string;
distance: number;
};
const openrouter = new OpenAI({
baseURL: "https://openrouter.ai/api/v1",
apiKey: API_KEY,
});
const database = new Client({ connectionString: DATABASE_URL });
async function createTable(): Promise<void> {
await database.query("CREATE EXTENSION IF NOT EXISTS vector");
await database.query(`
CREATE TABLE IF NOT EXISTS chunks (
id bigserial PRIMARY KEY,
source text NOT NULL,
content text NOT NULL,
embedding vector(${EMBEDDING_DIMENSIONS}) NOT NULL
)
`);
}
function splitIntoChunks(text: string): string[] {
const paragraphs = text
.split(/\n\s*\n/)
.map((paragraph) => paragraph.trim())
.filter((paragraph) => paragraph.length > 0);
const chunks: string[] = [];
let currentChunk = "";
for (const paragraph of paragraphs) {
const wouldOverflow = currentChunk.length + paragraph.length > CHUNK_SIZE;
if (wouldOverflow && currentChunk.length > 0) {
chunks.push(currentChunk);
currentChunk = currentChunk.slice(-CHUNK_OVERLAP); // keep a little overlap
}
currentChunk =
currentChunk.length > 0 ? `${currentChunk}\n\n${paragraph}` : paragraph;
}
if (currentChunk.length > 0) chunks.push(currentChunk);
return chunks;
}
async function embed(texts: string[]): Promise<number[][]> {
const response = await openrouter.embeddings.create({
model: EMBEDDING_MODEL,
input: texts,
});
return response.data.map((entry) => entry.embedding);
}
async function indexNotes(): Promise<void> {
// Simplest way to stay idempotent: throw the old rows away first.
await database.query("TRUNCATE chunks");
let storedChunks = 0;
for (const note of notes) {
const chunks = splitIntoChunks(note.text);
const embeddings = await embed(chunks);
for (const [position, chunkText] of chunks.entries()) {
await database.query(
"INSERT INTO chunks (source, content, embedding) VALUES ($1, $2, $3::vector)",
[note.source, chunkText, JSON.stringify(embeddings[position])],
);
}
storedChunks += chunks.length;
}
console.log(`Indexed ${notes.length} notes into ${storedChunks} chunks.`);
}
async function findRelevantChunks(
questionEmbedding: number[],
): Promise<MatchedChunk[]> {
const result = await database.query<MatchedChunk>(
`SELECT source, content, embedding <=> $1::vector AS distance
FROM chunks
ORDER BY embedding <=> $1::vector
LIMIT $2`,
[JSON.stringify(questionEmbedding), CHUNKS_TO_RETRIEVE],
);
return result.rows;
}
async function answerQuestion(question: string): Promise<void> {
const [questionEmbedding] = await embed([question]);
const relevantChunks = await findRelevantChunks(questionEmbedding);
if (relevantChunks.length === 0) {
console.error('No chunks in the database. Run "node rag.ts index" first.');
return;
}
const context = relevantChunks
.map((matchedChunk) => `[${matchedChunk.source}]\n${matchedChunk.content}`)
.join("\n\n");
const stream = await openrouter.chat.completions.create({
model: CHAT_MODEL,
stream: true,
messages: [
{
role: "system",
content:
"Answer the question using only the notes below. If they do not " +
`contain the answer, say you don't know.\n\n${context}`,
},
{ role: "user", content: question },
],
});
for await (const streamChunk of stream) {
process.stdout.write(streamChunk.choices[0]?.delta?.content ?? "");
}
const sources = [...new Set(relevantChunks.map((chunk) => chunk.source))];
console.log(`\n\nSources: ${sources.join(", ")}`);
}
async function main(): Promise<void> {
if (API_KEY === "sk-or-v1-paste-your-key-here") {
console.error("Open rag.ts and paste your OpenRouter key into API_KEY.");
process.exit(1);
}
const command = process.argv[2];
if (!command) {
console.error('Usage: node rag.ts index | node rag.ts "your question"');
process.exit(1);
}
await database.connect();
try {
await createTable();
if (command === "index") {
await indexNotes();
} else {
await answerQuestion(command);
}
} finally {
await database.end();
}
}
main().catch((error) => {
console.error(`Failed: ${error.message}`);
process.exit(1);
});Running it
Index once, then ask as often as you like:
$ node rag.ts index
Indexed 2 notes into 6 chunks.
$ node rag.ts "when should I change the chain?"
Replace the chain once the wear gauge drops past 0.75, which has worked out
to roughly every 3,000 km. Doing it early saves the cassette.
Sources: bike.md
$ node rag.ts "which bin goes out on Friday?"
General waste goes out every Friday. The bin store code is 4417.
Sources: flat.md
$ node rag.ts "what pressure should the boiler be at?"
Between 1 and 1.5 bar. If it falls below 1, top it up with the grey filling
loop underneath and stop at 1.2.
Sources: flat.md
$ node rag.ts "what is the alarm code at the office?"
I don't know — the notes don't mention an office alarm code.
Sources: flat.md, bike.mdThe last one is the interesting one. There is no office in the notes, so the search still returned its three closest chunks — something is always closest — and the bin store code sitting in one of them is exactly the kind of number a model would hand you if you hadn’t told it to admit ignorance. Retrieval never fails loudly. That’s why the instruction in the system message matters, and why printing the distances is worth doing.
You can look at what got stored:
docker exec -it rag-postgres psql -U postgres \
-c "SELECT id, source, left(content, 40) FROM chunks"What’s going on
main is the router. One argument: either the literal word index or a
question in quotes. Both paths call createTable first, so a fresh
database sets itself up on the first run, and the finally closes the
connection so the process actually exits.
indexNotes is a straight line. For each note: split into chunks, embed
all the chunks in one request, insert each chunk with its vector. The
TRUNCATE at the top means you can re-run it as often as you like without
piling up duplicates. Fine for a script; a real system would key rows by
document and only re-embed what changed.
answerQuestion is the RAG part proper, and it’s six lines of work: embed
the question with the same model used for indexing, run the distance
query, join the results into one block of text, send that block as the
system message, stream the reply.
That “same model” is not a style note. A question embedded with a different model produces numbers in a different space, and the distances against your stored vectors come back as meaningless noise. Not an error — just bad answers. It’s the single easiest thing to get wrong here.
The Sources line at the end is just the filenames from the matched rows
with duplicates removed. Small thing, but it turns “the computer said so”
into something you can go and check.
The complete flow
Start to finish, here’s what happens when you type a question — with what the data actually looks like at each step.
User question
"which bin goes out on Friday?"A plain string from process.argv.
→ Embedding
One call to openrouter.embeddings.create turns that string into 1536
floats:
[0.0141, -0.0226, 0.0079, ... ]The same model that embedded the notes, or none of the following works.
→ pgvector similarity search
The vector goes into the query as $1, cast to vector. Postgres computes
embedding <=> $1::vector for every row, sorts ascending, returns the top
three:
distance source content
0.28 flat.md Bins: paper on the first Monday...
0.49 flat.md The boiler resets with...
0.52 bike.md # Bike. The chain is a KMC X11...No AI in this step. It’s arithmetic and a sort.
→ Relevant documents
The rows become one block of text, each labelled with the file it came from:
[flat.md]
Bins: paper on the first Monday of the month, plastic every other
Tuesday, general waste every Friday. The code for the bin store is 4417.
[flat.md]
The boiler resets with the black button behind the little hatch...→ LLM
That block goes into the system message under “answer using only the notes below”, the question goes in as the user message, and the request goes off to the chat model. The model isn’t searching anything. It’s reading a prompt you built, the way it always does.
→ Final answer
General waste goes out every Friday. The bin store code is 4417.Streamed back token by token, with the source filenames printed underneath.
Two things fall out of laying it out like this. The model is the last step
and the least interesting one — swap CHAT_MODEL for Claude or Gemini and
the pipeline is unchanged. And the quality of the answer is decided long
before the model sees anything: by how you chunked the text, and by whether
the right chunk made it into the top three.
Making it faster
The query above compares the question against every row in the table. On a few thousand chunks that’s milliseconds and you should leave it alone — it’s exact, and an approximate index would only make it worse.
When the table gets big enough to feel slow, add an HNSW index:
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);Two things to know about that line. The operator class has to match the
operator you search with: vector_cosine_ops goes with <=>, and if you
were using <-> you’d want vector_l2_ops instead. Mismatch them and
Postgres quietly ignores the index and scans anyway. And an HNSW search is
approximate — it can miss a genuine nearest neighbour now and then, in
exchange for being dramatically faster. That trade is almost always worth
it, but it’s a trade, and it’s why I wouldn’t add it on day one.
One size limit worth knowing before you pick a model: pgvector can index
vector columns up to 2000 dimensions. 1536 fits. If you want a
3072-dimension model, store it as halfvec and index that instead.
Picking an embedding model
| Model | Dimensions | Cost per 1M tokens |
|---|---|---|
openai/text-embedding-3-small | 1536 | $0.02 |
qwen/qwen3-embedding-8b | 4096 | $0.01 |
baai/bge-m3 | 1024 | $0.01 |
Prices are from when I wrote this; the live list is at
openrouter.ai/collections/embedding-models.
The dimensions column is the one that matters here, because it has to match
vector(...) in your table. Switching models means a new column size and a
full re-index — TRUNCATE, ALTER TABLE, run index again.
About that hardcoded key
Same as always. A key pasted into a script on your own machine is fine. A key pasted into a script that ends up in a git repo is not, because bots scan public GitHub for exactly this. Before you commit, change one line:
const API_KEY = process.env.OPENROUTER_API_KEY!;Put it in a .env file, add that to .gitignore, and run with:
node --env-file=.env rag.ts indexNode has had --env-file since 20.6, so there’s still nothing to install.
The ! is TypeScript’s way of saying “I know this could be undefined, let
me have it” — reasonable here, since the check at the top of main catches
a missing key anyway.
Where to go from here
- Read notes off disk instead of the array at the top.
fs.readdirplusfs.readFile, and the rest of the file doesn’t change. - Add a
UNIQUEconstraint on(source, content)and useON CONFLICT DO NOTHING, so re-indexing skips chunks you’ve already paid to embed. - Print the distances next to each match while you’re experimenting. It tells you whether a bad answer came from bad retrieval or a bad prompt, and those have very different fixes.
- Filter with plain SQL:
WHERE source = $3searches one document,WHERE created_at > now() - interval '30 days'searches recent ones. This is the thing a dedicated vector database makes you work for and Postgres gives away. - Combine it with full-text search. Postgres has
tsvectorbuilt in, and running both then merging the rankings catches things pure vector search misses, like exact product codes. - The pgvector README is short and genuinely worth reading end to end.
That’s it. One table, one file, one key.