Node.js with PostgreSQL

Install pg (node-postgres)

npm install pg dotenv

Add your connection string to .env:

DATABASE_URL=postgres://user:password@localhost:5432/mydb

Create a Connection Pool

import 'dotenv/config';
import pg from 'pg';

const { Pool } = pg;

export const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 20,                  // max connections in pool
    idleTimeoutMillis: 30000, // close idle connections after 30s
    connectionTimeoutMillis: 2000  // fail fast if no connection
});

// Test the connection on startup
pool.on('error', (err) => {
    console.error('Unexpected pool error:', err);
});

Always use a pool, never a single Client, in web servers. A Client opens a new connection per request — slow and resource-hungry.

Run Your First Query

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

const result = await pool.query('SELECT NOW() as now');
console.log(result.rows[0].now);
// 2026-10-05T12:00:00.000Z

Create a table and insert data:

await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at TIMESTAMPTZ DEFAULT NOW()
    )
`);

const insert = await pool.query(
    'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name',
    ['Sarah', 'sarah@example.com']
);

console.log(insert.rows[0]); // { id: 1, name: 'Sarah' }

CRUD Operations

// READ all
const all = await pool.query('SELECT * FROM users ORDER BY created_at DESC');

// READ one
const one = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

// UPDATE
await pool.query(
    'UPDATE users SET name = $1 WHERE id = $2',
    ['Sarah Updated', userId]
);

// DELETE
await pool.query('DELETE FROM users WHERE id = $1', [userId]);

Transactions

const client = await pool.connect();
try {
    await client.query('BEGIN');

    await client.query(
        'INSERT INTO accounts (name, balance) VALUES ($1, $2)',
        ['Sarah', 100]
    );
    await client.query(
        'UPDATE accounts SET balance = balance - 50 WHERE name = $1',
        ['Sarah']
    );

    await client.query('COMMIT');
} catch (err) {
    await client.query('ROLLBACK');
    throw err;
} finally {
    client.release();  // ALWAYS return to pool
}

Use transactions when multiple queries must all succeed or all fail (transfers, orders, etc.).

Alternative — Prisma ORM

If you prefer type safety and migrations over raw SQL:

npm install prisma @prisma/client
npx prisma init

Define your schema in prisma/schema.prisma:

model User {
    id        Int      @id @default(autoincrement())
    name      String
    email     String   @unique
    createdAt DateTime @default(now())
}

Run migrations and query:

npx prisma migrate dev --name init

// Then in code:
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

const users = await prisma.user.findMany();
const user = await prisma.user.create({
    data: { name: 'Sarah', email: 'sarah@example.com' }
});