Connect a Node.js API to PostgreSQL using a bounded pool, parameterized queries, transactions, timeouts, and clean shutdown without leaking clients.
What you will build
This tutorial produces a working, verifiable result rather than a command list. Read each command before running it, keep configuration in version control without secrets, and record the versions used for repeatable deployment.
1. Prepare the project
createdb node_demo
psql node_demo -c 'CREATE TABLE users (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text UNIQUE NOT NULL, created_at timestamptz NOT NULL DEFAULT now());'
npm install pg
export DATABASE_URL='postgresql://app:change-me@127.0.0.1:5432/node_demo'
Run preparation commands as an unprivileged application user unless a command explicitly requires sudo. A clean working tree and lockfile make rollback much easier.
2. Implement the solution
import pg from 'pg'; const {Pool}=pg;
const pool=new Pool({connectionString:process.env.DATABASE_URL,max:10,idleTimeoutMillis:30_000,connectionTimeoutMillis:3_000});
export async function createUser(email){
const client=await pool.connect();
try { await client.query('BEGIN'); const result=await client.query('INSERT INTO users(email) VALUES($1) RETURNING id,email,created_at',[email]); await client.query('COMMIT'); return result.rows[0]; }
catch(error){await client.query('ROLLBACK'); throw error;} finally {client.release();}
}
process.once('SIGTERM',async()=>{await pool.end(); process.exit(0);});
Save the example in the filename indicated by its comment or surrounding instructions. Treat it as a minimal baseline: production applications should separate transport, business logic, persistence, and configuration into testable modules.
3. Verify end to end
node -e "import('./db.js').then(async m=>console.log(await m.createUser('reader@example.com')))"
psql "$DATABASE_URL" -c 'SELECT id,email,created_at FROM users;'
Verification should cover both process state and a real request or data operation. A process that is merely running is not necessarily ready to serve traffic.
4. Troubleshooting and production notes
Always release a checked-out client in finally. Parameter placeholders protect values, not dynamic table or column names. Size the pool across every application replica so the total remains below the database connection limit.
Production checklist
- The supported Node.js LTS version and dependency lockfile are recorded.
- Configuration is validated at startup and secrets are stored outside source control.
- Input limits, authentication, authorization, timeouts, and error boundaries are explicit.
- Logs identify a request without exposing credentials or personal data.
- Health checks, graceful termination, resource limits, backup, and rollback have been tested.
Reference: official topic documentation. For production version selection, use a supported LTS line from the Node.js release schedule.
