Swap the store for Postgres — Build and host a full-featured, secure MCP server on Render

The whole point of putting NoteStore behind an interface was so this step is mechanical. You’ll:

Run Postgres locally in Docker.
Add the pg driver and a migration helper.
Write createPgStore(pool) that satisfies the same NoteStore interface.
Flip one line in src/app.ts.

After that you won’t touch persistence again until step 7, when you point DATABASE_URL at Render’s managed Postgres.

1. Local Postgres in Docker

Drop a Compose file at the repo root. Pinning to Postgres 18 matches what Render’s managed Postgres ships, so behavior stays consistent from laptop to prod.

services:
  postgres:
    image: postgres:18
    restart: unless-stopped
    environment:
      POSTGRES_USER: notes
      POSTGRES_PASSWORD: notes
      POSTGRES_DB: notes
    ports:
      - "5432:5432"
    volumes:
      - notes_pg:/var/lib/postgresql/data

volumes:
  notes_pg:

Boot local Postgres

$docker compose up -d
[+] Running 2/2
  Network notes-mcp_default      Created
  Container notes-mcp-postgres-1  Started
$docker compose ps
NAME                     STATUS              PORTS
notes-mcp-postgres-1     Up 3 seconds        0.0.0.0:5432->5432/tcp

2. Add `pg` and a config helper

npm install pg
npm install -D @types/pg

pnpm add pg
pnpm add -D @types/pg

yarn add pg
yarn add -D @types/pg

The template uses raw process.env reads. As soon as you have more than one required var (you’ll be at three by the end of step 5), it pays to centralize so missing vars fail loudly at boot instead of as a cannot read property of undefined deep in a handler.

function required(name: string): string {
  const value = process.env[name];
  if (!value) throw new Error(`Missing required env var: ${name}`);
  return value;
}

export const config = {
  port: Number(process.env.PORT ?? 10000),
  databaseUrl: required("DATABASE_URL"),
};

3. Write the migration

A single SQL file you run on every boot. Idempotent, so it’s safe to re-run.

CREATE TABLE IF NOT EXISTS notes (
  id          UUID         PRIMARY KEY,
  title       TEXT         NOT NULL,
  body        TEXT         NOT NULL,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS notes_created_at_idx
  ON notes (created_at DESC);

The descending index on created_at is what makes recent(limit) cheap - it’s the access pattern the tool exercises every time.

tsc doesn’t copy non-.ts files. Update package.json so the migration ends up next to the compiled JS:

{
  "scripts": {
    "build": "tsc && cp -R src/migrations dist/migrations"
  }
}

4. Implement `createPgStore`

The new implementation lives alongside the in-memory one. Same interface, different guts.

import { readFile } from "node:fs/promises";
import { randomUUID } from "node:crypto";
import { Pool } from "pg";

export type Note = {
  id: string;
  title: string;
  body: string;
  createdAt: string;
};

export interface NoteStore {
  create(input: { title: string; body: string }): Promise<Note>;
  recent(limit: number): Promise<Note[]>;
  getById(id: string): Promise<Note | null>;
}

export function createMemoryStore(): NoteStore {
  const notes: Note[] = [];
  return {
    async create({ title, body }) {
      const note: Note = { id: randomUUID(), title, body, createdAt: new Date().toISOString() };
      notes.unshift(note);
      return note;
    },
    async recent(limit) { return notes.slice(0, limit); },
    async getById(id) { return notes.find((n) => n.id === id) ?? null; },
  };
}

export type PgStore = { store: NoteStore; pool: Pool };

export async function createPgStore(databaseUrl: string): Promise<PgStore> {
  const pool = new Pool({
    connectionString: databaseUrl,
    max: 10,
    idleTimeoutMillis: 30_000,
    ssl: databaseUrl.includes("localhost") ? false : { rejectUnauthorized: false },
  });

  const migration = await readFile(new URL("./migrations/001_notes.sql", import.meta.url), "utf8");
  await pool.query(migration);

  const store: NoteStore = {
    async create({ title, body }) {
      const id = randomUUID();
      const { rows } = await pool.query<{
        id: string; title: string; body: string; created_at: Date;
      }>(
        `INSERT INTO notes (id, title, body)
         VALUES ($1, $2, $3)
         RETURNING id, title, body, created_at`,
        [id, title, body],
      );
      const r = rows[0];
      return { id: r.id, title: r.title, body: r.body, createdAt: r.created_at.toISOString() };
    },

    async recent(limit) {
      const { rows } = await pool.query<{
        id: string; title: string; body: string; created_at: Date;
      }>(
        `SELECT id, title, body, created_at
         FROM notes
         ORDER BY created_at DESC
         LIMIT $1`,
        [limit],
      );
      return rows.map((r) => ({ id: r.id, title: r.title, body: r.body, createdAt: r.created_at.toISOString() }));
    },

    async getById(id) {
      const { rows } = await pool.query<{
        id: string; title: string; body: string; created_at: Date;
      }>(
        `SELECT id, title, body, created_at FROM notes WHERE id = $1`,
        [id],
      );
      if (!rows.length) return null;
      const r = rows[0];
      return { id: r.id, title: r.title, body: r.body, createdAt: r.created_at.toISOString() };
    },
  };

  return { store, pool };
}

Two production-shaped details:

Detail	Why
`max: 10` on the pool	Cap concurrent connections so a request spike doesn’t blow through Postgres’ `max_connections`. Tune this for real in the Postgres connection-limits step.
`ssl: { rejectUnauthorized: false }` on non-localhost URLs	Render’s internal connections use TLS with their own CA. The `pg` driver wants `rejectUnauthorized: false` to accept it. Local Docker doesn’t use TLS at all, so skip it there.

createPgStore returns both the store and the pool - the pool gets a second consumer in step 6 (the DB-backed health check).

5. Flip the store in `src/app.ts`

The change is two lines: import createPgStore instead of createMemoryStore, and make the store initialization async at module scope using top-level await (works because package.json already has "type": "module").

//...other imports unchanged...
import { Request, Response, NextFunction } from "express";
import { config } from "./config.js";
import { z } from "zod/v4";
import { createPgStore, type NoteStore } from "./store.js";

const MCP_API_TOKEN = process.env.MCP_API_TOKEN;
const { store, pool } = await createPgStore(config.databaseUrl);

Keep pool because step 6 needs it for /health. Everything from the export function createServer() line down is unchanged - the factory still closes over store exactly the way it did with the in-memory implementation.

6. Run it

The server now needs DATABASE_URL in the environment. Point it at the local container, then call notes.create a few times from the MCP Inspector and confirm the rows landed in Postgres.

Boot with DATABASE_URL, then verify rows in psql

$export DATABASE_URL='postgres://notes:notes@localhost:5432/notes'
$npm run build && npm start
MCP server listening on port 10000
$#...in another tab, call notes.create from the MCP Inspector a few times...
$docker compose exec postgres psql -U notes -d notes \
  -c 'SELECT id, title, created_at FROM notes ORDER BY created_at DESC LIMIT 3;'
                  id                  |    title    |          created_at
--------------------------------------+-------------+-------------------------------
 c4b3f4...                            | third note  | 2026-05-22 09:14:55.117+00
 8a01ce...                            | second note | 2026-05-22 09:14:42.882+00
 d2e8ab...                            | first note  | 2026-05-22 09:14:31.408+00
(3 rows)

The notes survive restarts now - Ctrl-C the server, run npm start again, reconnect the inspector, and your notes are still there.

Show hint

error: relation "notes" does not exist means the migration didn’t run. Most often the URL path is wrong - postgres://notes:notes@localhost:5432/notes (note the trailing /notes database name). The pool.query(migration) line on boot is what creates the table; without a successful connection, no migration.

7. Nothing about MCP changed

That’s the whole point of having the interface. Re-read the change list:

You added a Postgres-backed implementation of NoteStore.
You swapped one constructor call.
You added one config helper and one new build artifact (the migration SQL).

createServer(), the tool handlers, the resource handlers, the template’s /mcp route, the bearer-token middleware - none of them moved. The MCP wire protocol is identical to step 3; clients can’t tell the storage swapped.

flowchart LR
  client["MCP client"]
  http["app.post('/mcp')"]
  mk["createServer()"]
  tools["notes.create"]
  res["notes://recent"]
  iface["NoteStore (interface)"]
  pg[("Postgres")]
  mem[("In-memory (replaced)")]:::ghost

  client --> http --> mk --> tools & res --> iface
  iface --> pg
  iface -.- mem

  classDef ghost fill:transparent,stroke-dasharray:4 2;

Why did you put SSL config on the `pg` pool as `{ rejectUnauthorized: false }` only when the URL isn't `localhost`?

It's required for performance reasons in productionLocal Postgres doesn't speak TLS by default; Render's managed Postgres requires TLS but uses its own CA the `pg` driver doesn't trust out of the boxRender rejects connections that verify certificatesIt's a workaround for a `pg` driver bug

What you learned

Local Postgres in Docker matches Render's Postgres 18 - same SQL on laptop and prod
The `NoteStore` interface let Postgres slot in without touching the template's `createServer()` factory or `/mcp` handler
Module-scope `await createPgStore(...)` runs the migration once at import time - fine for a single-table service
Render's TLS requires `rejectUnauthorized: false` on `pg` - the connection is still encrypted
Holding on to the `pg.Pool` here sets you up for the DB-backed `/health` in step 6

1. Local Postgres in Docker

2. Add pg and a config helper

3. Write the migration

4. Implement createPgStore

5. Flip the store in src/app.ts

6. Run it

7. Nothing about MCP changed

What you learned

2. Add `pg` and a config helper

4. Implement `createPgStore`

5. Flip the store in `src/app.ts`