Difficulty: Intermediate
PostgreSQL is one of the most powerful and reliable open-source relational databases. It supports advanced features like JSONB columns, full-text search, window functions, and Common Table Expressions (CTEs). For Node.js applications, the `pg` package (node-postgres) provides a low-level, high-performance client for interacting with PostgreSQL directly using SQL queries.
Connection pooling is essential for production applications. Instead of opening a new database connection for every request (which is slow and resource-intensive), a connection pool maintains a set of reusable connections. The `pg.Pool` class manages this automatically - when you call pool.query(), it checks out a connection from the pool, executes the query, and returns the connection for reuse. A typical pool has 10-20 connections, which can handle hundreds of concurrent requests.
Parameterized queries are the single most important security practice when working with SQL databases. Instead of concatenating user input into SQL strings (which opens the door to SQL injection), you use placeholders ($1, $2, etc.) and pass values separately. The pg library handles escaping and type conversion, making SQL injection impossible when parameterized queries are used consistently.
Transactions are crucial when multiple database operations must succeed or fail as a unit. For example, transferring money between accounts requires debiting one account and crediting another - if one fails, both must be rolled back. With the pg library, you acquire a client from the pool, run BEGIN, execute your queries, and then either COMMIT (if all succeed) or ROLLBACK (if any fail). The try/catch/finally pattern ensures proper cleanup.
While ORMs like Prisma and Sequelize are popular for their convenience, using the pg library directly gives you full control over your SQL. This is valuable for complex queries with joins, subqueries, aggregations, and database-specific features that ORMs may not support or may generate inefficiently. Many production applications use an ORM for simple CRUD and raw SQL for complex reporting queries.
const { Pool } = require('pg');
// Create a connection pool
const pool = new Pool({
host: 'localhost',
port: 5432,
database: 'myapp',
user: 'postgres',
password: 'secret',
max: 20, // Maximum pool size
idleTimeoutMillis: 30000, // Close idle connections after 30s
connectionTimeoutMillis: 2000 // Fail if no connection in 2s
});
// Simple query using the pool
async function getUsers() {
const result = await pool.query('SELECT id, name, email FROM users LIMIT 5');
console.log('Rows:', result.rows);
console.log('Row count:', result.rowCount);
return result.rows;
}
// Parameterized query (prevents SQL injection)
async function getUserByEmail(email) {
const result = await pool.query(
'SELECT id, name, email FROM users WHERE email = $1',
[email]
);
return result.rows[0] || null;
}
// Insert with RETURNING
async function createUser(name, email) {
const result = await pool.query(
'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email',
[name, email]
);
console.log('Created user:', result.rows[0]);
return result.rows[0];
}
console.log('Pool created with max', pool.options.max, 'connections');
console.log('Query functions defined');The Pool manages connections automatically. Parameterized queries use $1, $2 placeholders with values in an array. The RETURNING clause in INSERT returns the newly created row, eliminating the need for a separate SELECT query.
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
async function transferFunds(fromId, toId, amount) {
// Get a dedicated client from the pool
const client = await pool.connect();
try {
await client.query('BEGIN');
// Debit from sender
const debitResult = await client.query(
'UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 RETURNING balance',
[amount, fromId]
);
if (debitResult.rowCount === 0) {
throw new Error('Insufficient funds or account not found');
}
// Credit to receiver
const creditResult = await client.query(
'UPDATE accounts SET balance = balance + $1 WHERE id = $2 RETURNING balance',
[amount, toId]
);
if (creditResult.rowCount === 0) {
throw new Error('Recipient account not found');
}
// Record the transaction
await client.query(
'INSERT INTO transfers (from_id, to_id, amount) VALUES ($1, $2, $3)',
[fromId, toId, amount]
);
await client.query('COMMIT');
console.log('Transfer successful:', amount, 'from', fromId, 'to', toId);
return { success: true };
} catch (error) {
await client.query('ROLLBACK');
console.log('Transfer rolled back:', error.message);
return { success: false, error: error.message };
} finally {
client.release(); // Return client to pool
}
}
console.log('transferFunds function defined');
console.log('Uses BEGIN/COMMIT/ROLLBACK pattern');Transactions use a dedicated client (not the pool directly) to ensure all queries run on the same connection. BEGIN starts the transaction, COMMIT saves all changes, ROLLBACK undoes everything if any step fails. The finally block always releases the client back to the pool.
const express = require('express');
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const app = express();
app.use(express.json());
// GET all users with optional search
app.get('/api/users', async (req, res) => {
try {
const { search, limit = 20, offset = 0 } = req.query;
let query = 'SELECT id, name, email, created_at FROM users';
const params = [];
if (search) {
query += ' WHERE name ILIKE $1 OR email ILIKE $1';
params.push(`%${search}%`);
}
query += ` ORDER BY created_at DESC LIMIT ${params.length + 1} OFFSET ${params.length + 2}`;
params.push(limit, offset);
const result = await pool.query(query, params);
res.json({ users: result.rows, count: result.rowCount });
} catch (error) {
console.error('Query error:', error.message);
res.status(500).json({ error: 'Database query failed' });
}
});
// POST create user
app.post('/api/users', async (req, res) => {
try {
const { name, email } = req.body;
const result = await pool.query(
'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
[name, email]
);
res.status(201).json(result.rows[0]);
} catch (error) {
if (error.code === '23505') { // Unique violation
return res.status(409).json({ error: 'Email already exists' });
}
res.status(500).json({ error: 'Failed to create user' });
}
});
console.log('Express routes with pg configured');This shows realistic Express routes using pg. The GET route demonstrates dynamic query building with parameterized values for search, pagination. The POST route handles the PostgreSQL unique constraint violation error (code 23505) specifically.
PostgreSQL, pg, connection pool, parameterized queries, transactions, SQL injection prevention