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.

DATABASE_URL is a direct postgresql://user:pass@host/db connection to deploymill's managed Postgres — the only selectable managed-database backend. The pool module below drives TLS from the connection string rather than hardcoding it, so it keeps working unchanged if the connection string ever gains an sslmode.

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

// TLS only when the connection string asks for it. DeployMill's managed
// Postgres is a direct, non-TLS in-cluster connection, so forcing `ssl` there
// fails with "server does not support SSL connections". A connection string
// that does ask for TLS carries `sslmode=require`, and this honors it.
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. The container runs migrations serially in its boot command before it serves traffic, so the lock protects against nothing here — it is pure failure surface. The managed database's direct connection would support the lock; it just doesn't need it. (A session lock also can't be held through a transaction-pooling proxy, so dropping it keeps the runner portable.)

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.

Free Explore orgs can't edit the build recipe. A managed database is available on the free tier, but a push_files that includes a Dockerfile or .dockerignore is refused with build_recipe_locked (the whole push, before anything commits). If you hit that, run the migration from app code at startup instead — call the migrator from your entrypoint module before the server starts listening — and push only app source.

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 dump/restore copy of the parent's), 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 if the managed-database backend isn't configured on the server — in which case a buggy migration hits prod directly. create_preview reports which path it took via database.action and the top-level hitsProdData flag. See deploymill://guides/previews for the matrix.
• Don't force TLS on a connection that doesn't use it. The managed database is a direct in-cluster connection without TLS. 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 the binding with delete_secret({ scope: "app", applicationId, name: "DATABASE_URL" }), then re-run reconcile_project to mint a fresh one. Use delete_secret, not delete_env_vars: the managed DATABASE_URL is stored as an app-scoped secret (masked in list_env_vars, injected at deploy time), so delete_env_vars reports it under missing and silently changes nothing. delete_secret also strips any legacy plaintext copy left in the env blob.
• too many connections → multiple pools were created. There must be exactly one new pg.Pool() call in the entire process.