PostgreSQL for Node.js Apps - Connect, Pool and Migrate

PostgreSQL is a popular database for Node.js apps: it is open source, reliable, has strong support for JSON alongside relational data, and works with every major Node.js library. Connecting to it is easy in development. Running it well in production takes a few extra decisions: how you store the connection details, how many connections you open, whether the connection is encrypted and how you change the schema without breaking the live app. This guide covers each one with the pg package (node-postgres), the most widely used PostgreSQL driver for Node.js.

The connection string

A PostgreSQL connection is usually described by one URL:

postgresql://app_user:[email protected]:5432/app_db

Keep it in an environment variable, usually DATABASE_URL, never in your code or Git repository. Most libraries, including Prisma and Drizzle, read that variable by convention. Our guide to environment variables in production explains build-time versus runtime variables, which matters for Next.js apps.

Use a Pool, not a new client per request

Opening a database connection takes time and uses memory on the database server. Creating a new connection for every request makes the app slower and can exhaust the server's connection limit under load. Use a pool: a small set of open connections that requests borrow and return.

// db.js
import pg from "pg";

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                       // connections in the pool
  idleTimeoutMillis: 30_000,     // close idle connections after 30 s
  connectionTimeoutMillis: 5_000 // fail fast if the database is unreachable
});

pool.on("error", (err) => {
  // An idle connection failed (e.g. the server restarted). Log it; the pool replaces it.
  console.error("PostgreSQL pool error", err);
});

Then query through the pool:

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

const { rows } = await pool.query("SELECT id, name FROM customers WHERE id = $1", [customerId]);

Always pass values as parameters ($1, $2) as above, never by building SQL strings yourself. Parameters prevent SQL injection.

How big should the pool be?

Smaller than you think. Every app instance has its own pool, and the database has a total connection limit shared by all of them. If you run three instances with max: 10, that's up to 30 connections, plus your migration tool and any admin sessions. Start with a small pool and increase it only if you see requests waiting for connections.

Transactions need one client

For a transaction, check out a single client so every statement uses the same connection, and always release it:

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amount, from]);
  await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, to]);
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();
}

Forgetting client.release() is a classic leak: the pool slowly runs out of connections and the app hangs.

Encryption (SSL/TLS)

If your app and database are on the same private network, the connection may not need TLS. Across the internet, it does. Ask your database provider whether TLS is available and how to verify the server's certificate, then enable it with the ssl option or sslmode in the connection string. Avoid turning off certificate checks (rejectUnauthorized: false) as a permanent fix: it encrypts the connection but no longer proves you're talking to the right server.

Migrations: changing the schema safely

Your database schema should change through migrations: small, versioned scripts that live in your repository and run in order. Popular choices in Node.js are Prisma Migrate, Drizzle Kit, Knex and node-pg-migrate. Whichever you use:

• Commit every migration with the code that needs it.
• Run migrations as a deploy step, before the new code starts, not by hand on the live server.
• Make changes backwards compatible. Add a nullable column first, deploy code that writes to it, backfill, then make it required in a later release. Renaming or dropping a column the running code still uses causes errors during the deploy.
• Take a backup before big migrations. See backing up MySQL and PostgreSQL.

If you prefer an ORM, our Prisma guide uses MySQL, but the workflow is the same for PostgreSQL: change the provider to postgresql and point DATABASE_URL at your PostgreSQL database.

Shut down cleanly

When your app is restarted, for example during a deploy, close the pool so in-flight queries finish and connections are released properly:

process.on("SIGTERM", async () => {
  await pool.end();
  process.exit(0);
});

Graceful shutdown in Node.js shows the complete pattern with an HTTP server.

Common errors and what they mean

Frequently asked questions

Should I choose PostgreSQL or MySQL for a Node.js app?

Both work well. PostgreSQL is often chosen for its standards support, JSON features and extensions; MySQL is widespread and familiar to many teams. If your framework, ORM or existing data favours one, use that.

Can I use the same database for development and production?

Don't. Use a separate database for development, so testing never touches live customer data, which also helps with POPIA.

Where should my PostgreSQL database be hosted?

Close to your app. Every query is a network round trip, so an app in South Africa talking to a database overseas is slower on every page.

On NewHost, managed databases include PostgreSQL and MySQL database plans with automatic backups, and every Node.js hosting app plan includes databases you create next to your app. The dashboard shows the host, port, user and connection URL to put in your environment variables.