Haystack Tutorial (TypeScript): connecting to PostgreSQL for intermediate developers

This tutorial shows you how to wire Haystack’s TypeScript runtime to PostgreSQL so your agent can persist, query, and reuse structured data instead of keeping everything in memory. You need this when your pipeline has to survive restarts, support multi-user workloads, or read from an existing Postgres-backed system of record.

What You'll Need

• •Node.js 18+ and npm
• •A PostgreSQL instance you can connect to locally or remotely
• •A database name, username, password, host, and port
• •A TypeScript project with tsconfig.json
• •Haystack TypeScript packages installed:
  • •@haystack/core
  • •@haystack/postgres
• •pg for the PostgreSQL driver
• •A .env file for connection settings

Install the dependencies:

npm install @haystack/core @haystack/postgres pg dotenv
npm install -D typescript ts-node @types/node

Step-by-Step

1. •Create a clean TypeScript project and load your environment variables first. Keep database credentials out of source control and make sure your runtime reads them before any connection is created.

// src/config.ts
import "dotenv/config";

export const pgConfig = {
  host: process.env.PGHOST ?? "localhost",
  port: Number(process.env.PGPORT ?? 5432),
  user: process.env.PGUSER ?? "postgres",
  password: process.env.PGPASSWORD ?? "postgres",
  database: process.env.PGDATABASE ?? "haystack_demo",
};

2. •Create a PostgreSQL client that Haystack can use. This example uses the standard pg driver, which is the safest path when you want predictable behavior in production.

// src/db.ts
import { Client } from "pg";
import { pgConfig } from "./config";

export async function createClient() {
  const client = new Client(pgConfig);
  await client.connect();
  return client;
}

3. •Set up a small table for storing records your Haystack workflow will use. For intermediate work, start with a simple table shape that maps cleanly to agent inputs and outputs.

// src/setup.ts
import { createClient } from "./db";

async function main() {
  const client = await createClient();

  await client.query(`
    CREATE TABLE IF NOT EXISTS customer_notes (
      id SERIAL PRIMARY KEY,
      customer_id TEXT NOT NULL,
      note TEXT NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);

  await client.end();
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});

4. •Use Haystack components to read from PostgreSQL and turn rows into pipeline-friendly objects. The exact component names depend on the package version, but the pattern is consistent: fetch rows with SQL, then pass them into your Haystack flow as structured data.

// src/read-notes.ts
import { createClient } from "./db";

type NoteRow = {
  id: number;
  customer_id: string;
  note: string;
  created_at: string;
};

async function main() {
  const client = await createClient();

  const result = await client.query<NoteRow>(
    `SELECT id, customer_id, note, created_at
     FROM customer_notes
     WHERE customer_id = $1
     ORDER BY created_at DESC`,
    ["cust_123"]
  );

  const notes = result.rows.map((row) => ({
    id: row.id,
    customerId: row.customer_id,
    note: row.note,
    createdAt: row.created_at,
  }));

  console.log(notes);

  await client.end();
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});

5. •Write back to PostgreSQL from your application flow after Haystack has produced output. Parameterized inserts matter here; do not interpolate user input into SQL strings.

// src/write-note.ts
import { createClient } from "./db";

async function main() {
  const client = await createClient();

  const customerId = "cust_123";
  const note = "Customer requested policy document resend.";

  await client.query(
    `INSERT INTO customer_notes (customer_id, note)
     VALUES ($1, $2)`,
    [customerId, note]
  );

  await client.end();
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});

6. •Wrap the database access in a reusable module so your Haystack pipeline stays focused on orchestration instead of connection details. That keeps the agent code testable and makes it easy to swap schemas later.

// src/repository.ts
import { createClient } from "./db";

export async function addCustomerNote(customerId: string, note: string) {
  const client = await createClient();

  try {
    await client.query(
      `INSERT INTO customer_notes (customer_id, note)
       VALUES ($1, $2)`,
      [customerId, note]
    );
  } finally {
    await client.end();
  }
}

Testing It

Run the setup script first so the table exists before you insert anything. Then run the write script and confirm a row appears in PostgreSQL with the expected customer_id and note. After that, run the read script and verify it returns the same row in descending timestamp order.

If you are wiring this into a real Haystack pipeline, test one path that only reads from Postgres and one path that both reads and writes. That catches permission issues early, especially if your production role can read but not create tables.

A quick manual check works fine:

npx ts-node src/setup.ts
npx ts-node src/write-note.ts
npx ts-node src/read-notes.ts

Next Steps

• •Add a connection pool with pg.Pool for higher concurrency.
• •Map query results into Haystack documents or typed tool outputs.
• •Add migrations with a tool like Prisma Migrate or node-pg-migrate so schema changes stay controlled.

Keep learning

• •The complete AI Agents Roadmap — my full 8-step breakdown
• •Free: The AI Agent Starter Kit — PDF checklist + starter code
• •Work with me — I build AI for banks and insurance companies

By Cyprian Aarons, AI Consultant at Topiax.