How to Use PostgreSQL With Node.js (pg Library Tutorial 2026)
⚡ Quick Answer
Learn how to connect PostgreSQL to Node.js using the pg library — connection pooling, CRUD queries, transactions, parameterized queries, and error handling.
Editorial note: This page is educational. Time-sensitive claims, comparisons, and reviews should include the supporting sources and update information in the article.
Read our editorial policy →Get more content like this on Telegram!
Daily AI tips, notes & resources free
ORMs are great until they're not. The moment you need a complex window function, a lateral join, or a custom aggregate, you find yourself fighting against the abstraction rather than working with it. That's when you want pg, the PostgreSQL client for Node.js.
The pg library is what ORMs like Prisma use under the hood. It's fast, well-maintained, and gives you direct control over your queries. This tutorial covers everything from initial setup through transactions and error handling, the things you actually need for production code.
Setup and Installation
npm install pg
npm install --save-dev @types/pg # if using TypeScriptYou'll need a PostgreSQL instance. If you're on macOS:
brew install postgresql@16
brew services start postgresql@16
createdb myapp_devOr spin up a Docker container:
docker run -d \
--name postgres \
-e POSTGRES_PASSWORD=mysecretpassword \
-e POSTGRES_DB=myapp_dev \
-p 5432:5432 \
postgres:16For cloud-based PostgreSQL with a free tier, the MongoDB Atlas equivalent for Postgres is Supabase, the article covers free cloud database options including PostgreSQL-compatible services.
Connection Pool Setup
For applications, you almost always want a connection pool rather than individual connections. Pools reuse existing connections instead of opening and closing one per query, which is expensive:
// src/db/pool.js
const { Pool } = require('pg');
const pool = new Pool({
host: process.env.PGHOST || 'localhost',
port: parseInt(process.env.PGPORT) || 5432,
database: process.env.PGDATABASE || 'myapp_dev',
user: process.env.PGUSER || 'postgres',
password: process.env.PGPASSWORD,
max: 20, // Maximum pool size
idleTimeoutMillis: 30000, // Close idle connections after 30s
connectionTimeoutMillis: 2000, // Fail if can't connect within 2s
maxUses: 7500, // Recycle connections after 7500 queries
});
// Test connection on startup
pool.on('connect', (client) => {
console.log('New database connection established');
});
pool.on('error', (err, client) => {
console.error('Unexpected error on idle client', err);
process.exit(-1);
});
// Graceful shutdown
process.on('SIGINT', async () => {
await pool.end();
console.log('Pool has ended');
process.exit(0);
});
module.exports = pool;Alternatively, use a connection string (more common with cloud providers):
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: process.env.NODE_ENV === 'production'
? { rejectUnauthorized: false } // For cloud providers like Heroku
: false
});Creating the Table Schema
-- migrations/001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
role VARCHAR(50) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Auto-update updated_at on row changes
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- Index for common queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);Run it with:
psql -d myapp_dev -f migrations/001_create_users.sqlFor more on SQL performance, the SQL query optimization guide covers indexes and EXPLAIN output in depth.
CRUD Queries with Parameterized Inputs
Parameterized queries are non-negotiable. They prevent SQL injection and allow PostgreSQL to cache query plans:
// src/db/users.js
const pool = require('./pool');
// CREATE
const createUser = async ({ email, name, role = 'user' }) => {
const query = `
INSERT INTO users (email, name, role)
VALUES ($1, $2, $3)
RETURNING *
`;
const { rows } = await pool.query(query, [email, name, role]);
return rows[0];
};
// READ — get by ID
const getUserById = async (id) => {
const { rows } = await pool.query(
'SELECT * FROM users WHERE id = $1',
[id]
);
return rows[0] || null;
};
// READ — list with pagination and filtering
const getUsers = async ({ role, limit = 20, offset = 0 } = {}) => {
const conditions = [];
const params = [];
if (role) {
params.push(role);
conditions.push(`role = $${params.length}`);
}
const whereClause = conditions.length > 0
? `WHERE ${conditions.join(' AND ')}`
: '';
// Total count (separate query for accuracy with filters)
const countQuery = `SELECT COUNT(*) FROM users ${whereClause}`;
const dataQuery = `
SELECT id, email, name, role, created_at
FROM users
${whereClause}
ORDER BY created_at DESC
LIMIT $${params.length + 1} OFFSET $${params.length + 2}
`;
const [countResult, dataResult] = await Promise.all([
pool.query(countQuery, params),
pool.query(dataQuery, [...params, limit, offset])
]);
return {
users: dataResult.rows,
total: parseInt(countResult.rows[0].count)
};
};
// UPDATE
const updateUser = async (id, updates) => {
const allowed = ['name', 'email', 'role'];
const fields = Object.keys(updates).filter(k => allowed.includes(k));
if (fields.length === 0) return null;
const setClauses = fields.map((field, i) => `${field} = $${i + 2}`);
const values = fields.map(f => updates[f]);
const query = `
UPDATE users
SET ${setClauses.join(', ')}
WHERE id = $1
RETURNING *
`;
const { rows } = await pool.query(query, [id, ...values]);
return rows[0] || null;
};
// DELETE
const deleteUser = async (id) => {
const { rowCount } = await pool.query(
'DELETE FROM users WHERE id = $1',
[id]
);
return rowCount > 0;
};
// EXISTS check
const emailExists = async (email) => {
const { rows } = await pool.query(
'SELECT 1 FROM users WHERE email = $1 LIMIT 1',
[email]
);
return rows.length > 0;
};
module.exports = { createUser, getUserById, getUsers, updateUser, deleteUser, emailExists };The RETURNING * clause is PostgreSQL-specific and extremely useful, you get the full inserted/updated row back without needing a second SELECT query.
Transactions
Transactions are essential when you need multiple queries to succeed or fail as a unit. The classic example: transferring money between accounts, or creating a user and their associated profile in the same operation.
// src/db/transactions.js
const pool = require('./pool');
// Helper for running a transaction
const withTransaction = async (callback) => {
const client = await pool.connect();
try {
await client.query('BEGIN');
const result = await callback(client);
await client.query('COMMIT');
return result;
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release(); // Always return the client to the pool
}
};
// Example: Create user + profile atomically
const createUserWithProfile = async ({ email, name, bio, avatarUrl }) => {
return withTransaction(async (client) => {
// Step 1: Create user
const { rows: [user] } = await client.query(
'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
[email, name]
);
// Step 2: Create profile (linked to user)
const { rows: [profile] } = await client.query(
'INSERT INTO profiles (user_id, bio, avatar_url) VALUES ($1, $2, $3) RETURNING *',
[user.id, bio, avatarUrl]
);
return { user, profile };
});
};
// Example: Transfer points between users
const transferPoints = async (fromUserId, toUserId, amount) => {
return withTransaction(async (client) => {
// Lock rows to prevent concurrent modifications (SELECT FOR UPDATE)
const { rows: [sender] } = await client.query(
'SELECT points FROM users WHERE id = $1 FOR UPDATE',
[fromUserId]
);
if (!sender || sender.points < amount) {
throw new Error('Insufficient points');
}
await client.query(
'UPDATE users SET points = points - $2 WHERE id = $1',
[fromUserId, amount]
);
await client.query(
'UPDATE users SET points = points + $2 WHERE id = $1',
[toUserId, amount]
);
return { success: true, transferred: amount };
});
};
module.exports = { withTransaction, createUserWithProfile, transferPoints };The finally block with client.release() is critical, without it, you'll exhaust your connection pool if a transaction fails.
Advanced Queries
// Full-text search (PostgreSQL built-in)
const searchUsers = async (searchTerm) => {
const { rows } = await pool.query(`
SELECT id, email, name,
ts_rank(to_tsvector('english', name || ' ' || email), plainto_tsquery($1)) AS rank
FROM users
WHERE to_tsvector('english', name || ' ' || email) @@ plainto_tsquery($1)
ORDER BY rank DESC
LIMIT 20
`, [searchTerm]);
return rows;
};
// Bulk insert with unnest (much faster than looping)
const bulkCreateUsers = async (users) => {
const emails = users.map(u => u.email);
const names = users.map(u => u.name);
const { rows } = await pool.query(`
INSERT INTO users (email, name)
SELECT * FROM unnest($1::text[], $2::text[])
ON CONFLICT (email) DO NOTHING
RETURNING *
`, [emails, names]);
return rows;
};
// Upsert pattern
const upsertUser = async ({ email, name }) => {
const { rows } = await pool.query(`
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW()
RETURNING *
`, [email, name]);
return rows[0];
};
// Window functions (ranking, running totals)
const getUsersWithRank = async () => {
const { rows } = await pool.query(`
SELECT
id,
name,
points,
RANK() OVER (ORDER BY points DESC) AS rank,
SUM(points) OVER () AS total_points
FROM users
ORDER BY points DESC
LIMIT 10
`);
return rows;
};For comparing PostgreSQL against other databases, the PostgreSQL vs MySQL vs SQLite comparison covers the architectural differences.
Error Handling
The pg library throws errors with standard PostgreSQL error codes:
// src/db/errorHandler.js
const handleDbError = (err) => {
switch (err.code) {
case '23505': // unique_violation
const column = err.constraint?.replace(/_key$/, '') || 'field';
throw Object.assign(new Error(`${column} already exists`), { statusCode: 409 });
case '23503': // foreign_key_violation
throw Object.assign(new Error('Referenced record does not exist'), { statusCode: 400 });
case '23502': // not_null_violation
throw Object.assign(new Error(`${err.column} is required`), { statusCode: 422 });
case '22P02': // invalid_text_representation (e.g., invalid integer)
throw Object.assign(new Error('Invalid data format'), { statusCode: 400 });
case 'ECONNREFUSED':
throw Object.assign(new Error('Database connection failed'), { statusCode: 503 });
default:
console.error('Unhandled database error:', err);
throw Object.assign(new Error('Database error'), { statusCode: 500 });
}
};
// Wrap db functions to use error handler
const createUser = async (data) => {
try {
return await _createUser(data);
} catch (err) {
handleDbError(err);
}
};Library Comparison Table
| Library | Type | Raw SQL | Schema | Type Safety | Bundle Size | Best For |
|---|---|---|---|---|---|---|
| pg | Driver | Full | No | Manual | Small | Raw queries, performance |
| pg-promise | Driver wrapper | Full | No | Manual | Small | Raw SQL + helpers |
| Prisma | ORM | Escape hatch | SDL | Excellent (TS) | Large | Type-safe CRUD |
| Sequelize | ORM | Escape hatch | JS classes | Moderate | Medium | Standard CRUD |
| Knex.js | Query builder | Yes | Migrations | None | Medium | Dynamic queries |
| Drizzle | ORM | Yes | TypeScript | Excellent (TS) | Small | Modern TS projects |
The Prisma ORM PostgreSQL tutorial shows the other end of the spectrum, where you trade SQL control for developer ergonomics and type safety. For most production Node.js apps, a mix makes sense: Prisma for standard CRUD, raw pg for complex analytics queries.
You can reference PostgreSQL's official documentation for the full SQL reference, and node-postgres documentation for the full pg API.
Conclusion
The pg library keeps you close to the database, which means you write better SQL and you understand what's actually happening with your queries. Connection pooling handles the infrastructure concern, parameterized queries handle security, and transactions handle consistency.
For simple apps with standard CRUD, an ORM will serve you faster. But when your queries get complex, window functions, CTEs, custom aggregates, bulk operations, pg gives you the control you need without fighting the abstraction layer.
If you're comparing the overall Node.js API architecture, the Node.js Express MongoDB REST API tutorial shows the same patterns with a document database. For adding authentication to your PostgreSQL-backed API, the API authentication methods comparison covers JWT and session-based approaches.
FAQ
Why use pg directly instead of an ORM like Prisma or Sequelize?
ORMs add abstraction that's great for CRUD-heavy apps, but they generate SQL you don't fully control. Raw pg gives you exact queries, which matters for performance-critical paths, complex joins, and operations that ORMs handle awkwardly. Many teams use both: pg for complex queries where they need control, and an ORM for standard CRUD operations. Starting with pg also teaches you SQL properly.
How many connections should I put in my connection pool?
The common rule is (2 × CPU cores) + 1 for the server. For a typical 4-core server, that's about 9. But it depends heavily on your query duration, long-running queries tie up connections longer. Start with 10, monitor idle and waiting connections in production, and adjust. PostgreSQL itself can handle around 100 connections by default before you need PgBouncer.
How do I prevent SQL injection when using the pg library?
Always use parameterized queries, pass your values as the second argument to client.query(). Never concatenate user input into SQL strings. The pg library handles escaping and type casting automatically for parameterized queries. If you need dynamic column names or table names (which you rarely should), use a whitelist approach, check the value against an allowed list before interpolating it.
Discussion
0Sign in with Google to comment, reply and manage your own comments.
Frequently Asked Questions
Why use pg directly instead of an ORM like Prisma or Sequelize?
How many connections should I put in my connection pool?
How do I prevent SQL injection when using the pg library?
Problem Solver and Cloud Expert
Cloud and infrastructure contributor supporting AiTechWorlds’ deployment, reliability, and platform operations.
Not sure yet? Ask AI about this article
Get an instant, unbiased AI summary of “How to Use PostgreSQL With Node.js (pg Library Tutorial 2026)”.
Related Articles
How to Use MongoDB Atlas: Free Cloud Database Setup (2026)
Set up a free MongoDB Atlas cloud cluster, connect with Mongoose, and build a full CRUD app. Plus a real comparison of Atlas free vs Supabase, Neon, and PlanetScale free tiers.
10 Free Public APIs for Practice (No Auth Required)
Discover 10 completely free public APIs requiring no authentication — perfect for practicing API calls, building portfolio projects, and learning REST fundamentals.
SQL Cheat Sheet for Interviews and Real Projects (JOINs, Windows and Traps)
A practical SQL cheat sheet — the JOIN types explained properly, window functions, query order of execution, and the LEFT JOIN mistake that silently breaks results.
How to Use Docker Compose for Local Dev (Node.js + PostgreSQL)
Set up a full local dev environment with Docker Compose, Node.js, PostgreSQL, and pgAdmin. Includes .env config, named volumes, healthchecks, and common error fixes.