Docs / Wire a managed Postgres database into a Node app
Guidedeploymill://guides/database/node

Wiring a managed Postgres database into a Node app (deploymill)

You are an agent updating a deploymill-managed Node app that has just had a managed Postgres database provisioned. DATABASE_URL is already set in the app's environment (pooled connection string, SSL required) and will be present in the container at boot.

Follow these steps in order. Each step is a concrete file edit or command, not advice — apply them literally.

1. Add the Postgres client

Add pg to dependencies in package.json:

"pg": "^8.13.0"

If the project uses TypeScript, also add @types/pg to devDependencies:

"@types/pg": "^8.11.0"

Do not pin to older majors; pg@8 is the current line and supports pooled-connection endpoints natively.

2. Create a single pool module

Create src/db.js (or src/db.ts for TypeScript projects):

import pg from "pg";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is not set. Add `database: { provider: \"neon\" }` to .deploymill/project.json and run reconcile_project.");
}

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  // The pooled endpoint requires TLS. The URI already specifies
  // sslmode=require, but pg's Node driver needs this hint to honor it.
  ssl: { rejectUnauthorized: false },
  max: 10,
});

export async function query(text, params) {
  return pool.query(text, params);
}

Important: create the pool once per process, at module scope. Do not create a new pool per request — that exhausts the database's connection limit and defeats the pooler. Import pool or query from this module everywhere.

3. Add a health route that proves the DB is reachable

In the app's main entry file (the existing template has src/index.js using Hono), add:

import { query } from "./db.js";

app.get("/db", async (c) => {
  try {
    const r = await query("SELECT 1 AS ok");
    return c.json({ ok: true, result: r.rows[0] });
  } catch (e) {
    return c.json({ ok: false, error: String(e) }, 500);
  }
});

Hit /db after deploy to confirm the connection works.

4. Migrations: use node-pg-migrate

Add to devDependencies:

"node-pg-migrate": "^7.6.0"

Add scripts to package.json:

"scripts": {
  "migrate": "node scripts/migrate.js",
  "migrate:create": "node-pg-migrate create"
}

Create your first migration:

npx node-pg-migrate create init

That writes migrations/<timestamp>_init.js. Write it as an ES module. The deploymill node starter sets "type": "module" in package.json, so every .js file — migrations included — is loaded as ESM. CommonJS exports.up = … throws ReferenceError: exports is not defined and the container fails to boot. Use export const:

export const up = (pgm) => {
  pgm.createTable("users", {
    id: "id",
    email: { type: "text", notNull: true, unique: true },
    created_at: { type: "timestamptz", notNull: true, default: pgm.func("now()") },
  });
};

export const down = (pgm) => {
  pgm.dropTable("users");
};

Run the migrator with noLock against the pooled endpoint

DATABASE_URL is Neon's pooled connection (pgbouncer in transaction-pooling mode). That endpoint does not support the session-level advisory lock that node-pg-migrate up takes by default — leave locking on and migrations hang or error. Don't call the CLI directly; add a small programmatic runner that disables the lock and sets TLS explicitly. Create scripts/migrate.js:

import path from "node:path";
import * as npm from "node-pg-migrate";

const runner = npm.default ?? npm.runner ?? npm;

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is not set — provision the database first.");
}

await runner({
  databaseUrl: {
    connectionString: process.env.DATABASE_URL,
    ssl: { rejectUnauthorized: false },
  },
  dir: path.resolve(process.cwd(), "migrations"),
  direction: "up",
  count: Infinity,
  migrationsTable: "pgmigrations",
  noLock: true, // pooled endpoint = pgbouncer txn mode; no session advisory locks
  log: (msg) => console.log(msg),
});

console.log("migrations complete");
process.exit(0);

The container runs migrations serially on boot, so dropping the lock is safe.

5. Run migrations on deploy

Migrations must run before the app starts handling traffic on each deploy. The starter image uses pnpm. Copy scripts/ into the runtime stage and update the Dockerfile's CMD so it runs the migrator before starting the server:

COPY --from=build /app/scripts ./scripts
# ... (existing COPY lines for node_modules, package.json, src, migrations)
CMD ["sh", "-c", "pnpm run migrate && pnpm start"]

node-pg-migrate is idempotent — it skips migrations already in the pgmigrations table — so this is safe to run on every container start.

If migrations fail, the container exits and the platform marks the deploy as failed. That's intentional: an app with a half-applied schema should not serve traffic.

6. Commit and redeploy

Use push_files to commit package.json, src/db.js, src/index.js, scripts/migrate.js, the new migrations/ directory, and the Dockerfile. Then call deploy on the application — pass probePath: "/db" so the edge probe verifies the database route, not just /.

What NOT to do

  • Don't read DATABASE_URL inside a request handler — read it once at module load.
  • Don't use an ORM heavyweight (Prisma, TypeORM) unless the user asks for one. They add build steps, generated clients, and tighter coupling that hurt the "scaffolded by deploymill" simplicity.
  • Don't run node-pg-migrate down automatically. Rollbacks are destructive; leave them as a manual operator decision.
  • Don't write migrations that depend on prod data shape. By default create_preview forks a Neon branch (copy-on-write from the parent's data) and points the preview at it, so a preview's migration won't touch prod rows — but if previews.shareDatabase: true is set in the parent's .deploymill/project.json, or NEON_API_KEY isn't configured, the preview shares the parent's DATABASE_URL and a buggy migration hits prod directly. See deploymill://guides/previews for the matrix.
  • Don't disable TLS. The managed database requires it.

If something breaks

  • ECONNREFUSED / ENOTFOUND → DATABASE_URL is missing or malformed. Check list_env_vars on the app.
  • password authentication failed → the database role was rotated. Clear DATABASE_URL via delete_env_vars and re-run reconcile_project to mint a fresh one.
  • too many connections → multiple pools were created. There must be exactly one new pg.Pool() call in the entire process.