Wiring a managed Postgres database into a Node app

You are an agent updating a DeployMill-managed Node app that has just had a managed Postgres database provisioned.

Precondition: the database is already provisioned, so DATABASE_URL is set in the app's environment and present in the container at boot. Done looks like: a /db health route returns { ok: true } after deploy, and migrations run automatically on each container start.

Its exact shape depends on which backend you provisioned. The pool module below works for all three by honoring the connection string:

• postgres: internal Postgres, the house default (no external account), a direct postgresql://user:pass@host/db connection.
• Neon and Supabase: pooled connections that require TLS (their URI carries sslmode=require).

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

SQLite (currently disabled as a selectable backend)

This playbook is for Postgres (postgres / neon / supabase), the selectable managed-database backends today. A file-on-volume sqlite engine is implemented behind the database seam but is currently disabled as a selectable backend, so you can't provision it. The notes below describe how it behaves for when it is re-enabled: DATABASE_URL would be a file: URL (e.g. file:/data/sqlite/<org>/<app>.db), not a Postgres connection string, so skip the pg steps below and use a SQLite driver (better-sqlite3, or Node's built-in node:sqlite) that opens that file path. Open the DB once at module scope (same single-handle rule as the pool below). If you pick better-sqlite3, it's a native (C/C++) addon. The slim base image has no compiler and pnpm blocks addon build scripts by default, so an unguarded install crash-loops at runtime with a missing-bindings error. Follow deploymill://guides/conventions/node → "Native (C/C++) addon dependencies" first (or use the dependency-free built-in node:sqlite to sidestep it entirely). Before you commit to SQLite, read these two non-negotiables:

• Runtime envelope: single-writer, in-process, file-on-volume. SQLite is one file opened by one process. There is no server and no network. It's a great fit for low-concurrency apps (a small internal tool, a worker, a low-traffic site), where the simplicity is a real win. It is the wrong choice for a multi-instance web app: you cannot run two app containers against one SQLite file safely (concurrent writers corrupt it), so SQLite caps you at a single replica. The .db file must live on a persistent volume (the server's SQLITE_DATA_DIR). On ephemeral container storage it's wiped on every redeploy. Heavy write concurrency will also serialize on SQLite's single write lock.
• SQLite → Postgres is a one-way door: a rewrite, not a data move. If you outgrow SQLite, switching to Postgres later is not a "change the provider and migrate the data" operation. The SQL dialect and type semantics differ (SQLite's dynamic typing, no native boolean/timestamptz/numeric the same way, different AUTOINCREMENT/sequence behavior, looser constraint enforcement). Tools like pgloader can move the bytes, but the schema semantics and your app's queries/migrations are on you to port. Choose SQLite only when you're confident the app stays within its envelope, or accept that growing out of it is real migration work. Previews still get an isolated copy of the parent .db (so destructive preview migrations stay off prod), exactly like the Postgres backends.

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: { engine: \"postgres\", provider: \"deploymill\" }` to .deploymill/project.json and run reconcile_project.");
}

// TLS only when the connection string asks for it. The house default
// (internal Postgres) is a direct, non-TLS in-cluster connection, so forcing
// `ssl` there fails with "server does not support SSL connections". Neon and
// Supabase carry `sslmode=require` and need the hint pg's driver honors.
const requiresTls = /sslmode=require/.test(process.env.DATABASE_URL);

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: requiresTls ? { rejectUnauthorized: false } : undefined,
  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

Add a small programmatic runner instead of calling the CLI directly, so you can set noLock and conditional TLS. 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.");
}

const requiresTls = /sslmode=require/.test(process.env.DATABASE_URL);

await runner({
  databaseUrl: {
    connectionString: process.env.DATABASE_URL,
    ssl: requiresTls ? { rejectUnauthorized: false } : undefined,
  },
  dir: path.resolve(process.cwd(), "migrations"),
  direction: "up",
  count: Infinity,
  migrationsTable: "pgmigrations",
  noLock: true, // see note below
  log: (msg) => console.log(msg),
});

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

noLock: true skips the session-level advisory lock node-pg-migrate takes by default. A pooled endpoint (Neon/Supabase in pgbouncer transaction-pooling mode) does not support that lock, so leaving it on makes migrations hang or error there. The container runs migrations serially on boot, so dropping the lock is safe on every backend, including the direct internal-Postgres connection, which would support the lock but doesn't need it here.

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, passing 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 gives the preview its own isolated database, a Neon branch (copy-on-write) or an internal-Postgres dump/restore copy depending on the provider, so a preview's migration won't touch prod rows. The preview shares the parent's DATABASE_URL only if previews.shareDatabase: true is set in the parent's .deploymill/project.json (or, for Neon specifically, if NEON_API_KEY isn't configured), in which case a buggy migration hits prod directly. See deploymill://guides/previews for the matrix.
• Don't force TLS on a connection that doesn't use it. Pooled backends (Neon/Supabase) require TLS. The internal-Postgres house default is a direct connection without it. Drive ssl from the connection string (as above) rather than hardcoding it on.

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.