Node.js 39 🟢 Connecting to PostgreSQL using the Native pg Driver
The pg package, also known as node-postgres, is the canonical PostgreSQL client for Node.js. It is not a full ORM. It does not model your tables as JavaScript classes, does not manage migrations, and does not hide SQL behind a query builder. It sends SQL to PostgreSQL and returns the rows as plain JavaScript objects. This low-level approach is deliberate: node-postgres is un-opinionated, and it leaves the higher-level abstractions to the libraries built on top of it . What it provides instead is a complete, reliable interface to PostgreSQL’s wire protocol, with connection pooling, parameterized queries, prepared statements, cursors, streaming, and SSL support .
The library exposes two primary connection abstractions: Client and Pool. A Client is a single connection to the database. You create it, connect it, run queries, and end it. A Pool manages a set of Client connections and reuses them across queries. For any application that serves more than one request, the Pool is the correct choice. Creating a new connection for every query is expensive — it involves a network handshake, authentication, and the creation of a backend process on the PostgreSQL server. A pool amortizes that cost by keeping connections open and handing them out as needed .
The connection configuration follows the same environment variables as libpq and psql. If PGHOST, PGUSER, PGPASSWORD, PGPORT, and PGDATABASE are set, new Pool() with no arguments connects to the right database . This means the same application code can run in development, staging, and production by changing only the environment variables. For cases where the configuration is managed differently, the pool accepts a programmatic object with user, password, host, port, and database, or a connectionString URI .
This chapter covers three areas. First, why node-postgres exists and how its connection model works — the difference between Client and Pool, and when each is appropriate. Second, how to connect and query — the configuration options, parameterized queries, and the result object. Third, how to use the pool for transactions and prepared statements — checking out a client, BEGIN/COMMIT/ROLLBACK, and the pattern for safe concurrent access. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the pool lifecycle.
Key point: Client is a single connection; Pool manages a set of connections. Use Pool for applications that serve multiple requests. Always use parameterized queries ($1, $2) to prevent SQL injection. For transactions, check out a client from the pool with pool.connect(), run BEGIN/COMMIT/ROLLBACK on that client, and release it in a finally block.
Why node-postgres exists
The PostgreSQL-wire-protocol problem. PostgreSQL communicates over a binary protocol that is not HTTP and not JSON. A Node.js application cannot talk to PostgreSQL by sending strings over a socket and parsing the responses. The protocol has handshake phases, authentication flows, message framing, and type encoding. pg implements this protocol in pure JavaScript, with optional C/C++ bindings for performance . It translates JavaScript values into PostgreSQL’s binary format and translates the results back. Without this layer, every Node.js application would need to reimplement the protocol.
The connection-cost problem. Opening a PostgreSQL connection is expensive. The client must complete a TCP handshake, negotiate SSL if configured, send the startup message, authenticate (often with SCRAM-SHA-256), and wait for the server to spawn a backend process. For a single query, this overhead can dominate the query time. A Pool solves this by keeping connections open and reusing them. The first query creates a connection; subsequent queries reuse it. The pool size limits how many connections are open at once, preventing the application from exhausting the server’s max_connections .
The transaction-isolation problem. A PostgreSQL transaction is scoped to a single connection. BEGIN, the statements, and COMMIT must all run on the same connection, or they are not part of the same transaction. The pool.query() method does not guarantee this: it checks out a connection, runs the query, and releases it. For a transaction, the application must check out a client explicitly with pool.connect(), run the transaction statements on that client, and release it when done . This is the most common mistake with node-postgres: using pool.query() for BEGIN and then a different pool.query() for the statements, which runs them on different connections and outside the transaction.
The injection problem. String concatenation for SQL is vulnerable to injection. A value like ' OR '1'='1 changes the meaning of the query. Parameterized queries solve this by sending the SQL template and the parameters separately. PostgreSQL parses the template with placeholders and substitutes the parameters as data, never as SQL. The pg library supports this with $1, $2, and so on, and the parameters are passed as an array . This is not optional for user input; it is the only correct way to build queries with variable data.
The configuration-reuse problem. Hardcoding connection details in the source code makes the application environment-specific. The pg library reads the same environment variables as libpq, so the same code runs against a local database, a Docker Compose database, or a managed cloud database without changes . The configuration is external to the code, and the code is portable.
The trade-off. pg is low-level. It returns rows as plain objects, with no type mapping beyond what it does by default. It does not validate that a query returns the columns you expect. It does not manage schema changes. It does not provide a query builder. The application is responsible for the SQL and for the shape of the results. The trade-off is between the control and predictability of raw SQL and the convenience of an ORM. For applications that need complex queries, precise control over transactions, and minimal abstraction overhead, pg is the right choice. For applications that need rapid CRUD scaffolding, an ORM built on pg may be more appropriate.
a. Client and Pool — the two connection models
A Client is a single connection. It is created, connected, used, and ended. It is appropriate for scripts, one-off tasks, and tests where the connection is not reused.
import { Client } from 'pg';
const client = new Client({
host: 'localhost',
port: 5432,
database: 'mydb',
user: 'postgres',
password: 'secret',
});
await client.connect();
const res = await client.query('SELECT NOW() as now');
console.log(res.rows[0].now);
await client.end();The connect() method establishes the connection. The query() method sends a query and returns a result. The end() method closes the connection. If the connection fails, connect() rejects, and the error must be caught .
A Pool manages a set of connections. It is appropriate for any application that serves multiple requests. The pool creates connections on demand, up to a maximum, and reuses them across queries.
import { Pool } from 'pg';
const pool = new Pool({
host: 'localhost',
port: 5432,
database: 'mydb',
user: 'postgres',
password: 'secret',
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
const res = await pool.query('SELECT NOW() as now');
console.log(res.rows[0].now);The pool.query() method checks out a connection, runs the query, and returns the connection to the pool. The max option sets the maximum number of connections. The idleTimeoutMillis option closes connections that have been idle for the specified time. The connectionTimeoutMillis option controls how long pool.connect() waits for a connection before rejecting .
The pool emits an error event for errors on idle clients. This event must be handled, or the application crashes. A common pattern is to log the error and continue, because the pool will create a new connection for the next query.
pool.on('error', (err) => {
console.error('Unexpected error on idle client', err);
});The pool must be ended when the application shuts down. The pool.end() method closes all connections and returns a promise that resolves when they are closed. In a server, this is typically called in the SIGTERM or SIGINT handler.
process.on('SIGTERM', async () => {
await pool.end();
process.exit(0);
});b. Querying with parameters
The query() method takes a SQL string and an optional array of parameters. The SQL uses $1, $2, and so on as placeholders, and the parameters are substituted as data, not as SQL .
const res = await pool.query(
'SELECT * FROM users WHERE email = $1 AND active = $2',
['alice@example.com', true]
);
console.log(res.rows);The result object has several properties. rows is an array of the returned rows, each as a plain object. rowCount is the number of rows affected or returned. command is the SQL command that was executed. fields is an array of field metadata .
const res = await pool.query(
'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name',
['Alice', 'alice@example.com']
);
console.log(res.rows[0].id); // the generated id
console.log(res.rowCount); // 1The RETURNING clause is the standard way to get data back from an insert or update. PostgreSQL returns the specified columns for each affected row, and pg exposes them in res.rows.
Parameterized queries also provide a performance benefit. PostgreSQL caches the query plan for a parameterized statement. When the same SQL template is executed with different parameters, the plan is reused, which can significantly improve performance for queries that run frequently .
c. Transactions and client checkout
A transaction must run on a single connection. The pool.query() method does not guarantee this, because it may check out a different connection for each call. For a transaction, the application must check out a client explicitly, run the transaction statements on that client, and release it when done .
const client = await pool.connect();
try {
await client.query('BEGIN');
const userRes = await client.query(
'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id',
['Bob', 'bob@example.com']
);
const userId = userRes.rows[0].id;
await client.query(
'INSERT INTO orders (user_id, total) VALUES ($1, $2)',
[userId, 99.99]
);
await client.query('COMMIT');
} catch (e) {
await client.query('ROLLBACK');
throw e;
} finally {
client.release();
}The pool.connect() method returns a promise that resolves to a Client. The client is checked out of the pool and is not returned until release() is called. If an error occurs, the ROLLBACK undoes the transaction, and the error is rethrown. The finally block ensures the client is released even if the transaction fails .
The client.release() method returns the connection to the pool. The connection is not closed; it is available for the next checkout. If the client is not released, the connection leaks, and the pool eventually runs out of connections.
The same pattern applies to prepared statements. A prepared statement is cached for the client that prepares it. To reuse the plan, the same client must execute the statement. Checking out a client for a batch of related queries is the pattern that enables this .
Complete Example Session
// ============================================
// PART 1: A MINIMAL CLIENT CONNECTION
// ============================================
import { Client } from 'pg';
const client = new Client({
host: 'localhost',
port: 5432,
database: 'mydb',
user: 'postgres',
password: 'secret',
});
await client.connect();
const res = await client.query('SELECT $1::text as message', ['Hello']);
console.log(res.rows[0].message); // Hello
await client.end();
// ============================================
// PART 2: ENVIRONMENT VARIABLE CONFIGURATION
// ============================================
// If PGHOST, PGUSER, PGPASSWORD, PGDATABASE, PGPORT
// are set, the pool uses them automatically.
import { Pool } from 'pg';
const pool = new Pool(); // uses environment variables
const res = await pool.query('SELECT NOW() as now');
console.log(res.rows[0].now);
await pool.end();
// ============================================
// PART 3: PROGRAMMATIC CONFIGURATION
// ============================================
const pool = new Pool({
host: 'database.server.com',
port: 3211,
database: 'mydb',
user: 'dbuser',
password: 'secretpassword',
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
// ============================================
// PART 4: CONNECTION STRING URI
// ============================================
const pool = new Pool({
connectionString: 'postgresql://dbuser:secretpassword@database.server.com:3211/mydb',
});
// Common in environments where the database URL
// is supplied as an environment variable.
// ============================================
// PART 5: PARAMETERIZED QUERY
// ============================================
const res = await pool.query(
'SELECT * FROM users WHERE email = $1',
['alice@example.com']
);
// The $1 placeholder is replaced with the value.
// The value is treated as data, not SQL.
// ============================================
// PART 6: INSERT WITH RETURNING
// ============================================
const res = await pool.query(
'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name',
['Alice', 'alice@example.com']
);
console.log(res.rows[0].id); // the generated id
console.log(res.rowCount); // 1
// ============================================
// PART 7: TRANSACTION WITH CLIENT CHECKOUT
// ============================================
const client = await pool.connect();
try {
await client.query('BEGIN');
const userRes = await client.query(
'INSERT INTO users (name) VALUES ($1) RETURNING id',
['Bob']
);
const userId = userRes.rows[0].id;
await client.query(
'INSERT INTO orders (user_id, total) VALUES ($1, $2)',
[userId, 99.99]
);
await client.query('COMMIT');
console.log('Transaction committed');
} catch (e) {
await client.query('ROLLBACK');
console.error('Transaction rolled back', e);
throw e;
} finally {
client.release();
}
// ============================================
// PART 8: THE db/index.js MODULE PATTERN
// ============================================
// A single module that wraps the pool and exports
// query and getClient functions.
import { Pool } from 'pg';
const pool = new Pool();
export const query = (text, params) => {
return pool.query(text, params);
};
export const getClient = () => {
return pool.connect();
};
export const close = () => {
return pool.end();
};
// The rest of the application imports from this
// module instead of importing pg directly.
// ============================================
// PART 9: ERROR HANDLING ON THE POOL
// ============================================
pool.on('error', (err) => {
console.error('Unexpected error on idle client', err);
});
// The pool emits error events for idle clients.
// Without this handler, the process crashes.
// ============================================
// PART 10: GRACEFUL SHUTDOWN
// ============================================
process.on('SIGTERM', async () => {
console.log('Shutting down...');
await pool.end();
process.exit(0);
});
// pool.end() closes all connections.
// It should be called when the application stops.The ten parts show a minimal client connection, environment variable configuration, programmatic configuration, connection string URI, parameterized queries, insert with returning, transactions with client checkout, the module pattern, pool error handling, and graceful shutdown.
Quick Reference
Client vs Pool
| Aspect | Client | Pool |
|---|---|---|
| Connections | One | Many |
| Reuse | No | Yes |
| Use case | Scripts, tests | Applications |
| Transaction | Direct | Check out client |
| Creation | new Client() | new Pool() |
| Connection | await client.connect() | pool.query() auto-connects |
| Cleanup | client.end() | pool.end() |
Configuration Options
| Option | Default | Purpose |
|---|---|---|
host | localhost | Database host |
port | 5432 | Database port |
database | process.env.USER | Database name |
user | process.env.USER | Database user |
password | null | Database password |
max | 10 | Max connections in pool |
idleTimeoutMillis | 10000 | Close idle connections after |
connectionTimeoutMillis | 0 | Timeout for acquiring connection |
connectionString | — | URI for connection |
ssl | false | SSL configuration object |
Environment Variables
| Variable | Default |
|---|---|
PGHOST | localhost |
PGPORT | 5432 |
PGUSER | process.env.USER |
PGPASSWORD | null |
PGDATABASE | process.env.USER |
Query Result Properties
| Property | Meaning |
|---|---|
rows | Array of returned rows |
rowCount | Number of rows affected/returned |
command | SQL command executed |
fields | Array of field metadata |
Best Practices
✅ Do This:
// Use a pool for applications
const pool = new Pool({ max: 20 }); // ✅
// Use parameterized queries
await pool.query('SELECT * FROM users WHERE email = $1', [email]); // ✅
// Use transactions with a checked-out client
const client = await pool.connect();
try { await client.query('BEGIN'); ... } finally { client.release(); } // ✅
// Handle pool errors
pool.on('error', (err) => console.error(err)); // ✅
// Close the pool on shutdown
process.on('SIGTERM', async () => { await pool.end(); }); // ✅
// Use environment variables for configuration
const pool = new Pool(); // reads PG* env vars // ✅❌ Don’t Do This:
// Don't create a client per request
app.get('/', async () => { const c = new Client(); await c.connect(); }); // ❌
// Don't use pool.query for transactions
await pool.query('BEGIN'); // different connection next query // ❌
// Don't concatenate values into SQL
`SELECT * FROM users WHERE email = '${email}'` // injection // ❌
// Don't forget to release a checked-out client
const client = await pool.connect(); // never released // ❌
// Don't forget pool.end()
process.exit(0); // connections leak // ❌
// Don't ignore pool error events
// no pool.on('error') → crash on idle client error // ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Transaction does not work | pool.query uses different connections | Check out client with pool.connect() |
| Connection leak | Client not released | Always release() in finally |
| SQL injection | String concatenation | Use $1 parameters |
| Pool exhausted | No max or too many concurrent checkouts | Set max, release clients |
| Process crashes on idle error | No pool.on('error') handler | Add the handler |
ECONNREFUSED | Wrong host/port or database not running | Check config and database status |
password authentication failed | Wrong credentials | Check PGPASSWORD or config |
| Pool does not close | pool.end() not called | Call in shutdown handler |
Real-World Examples
1. Minimal Pool
const pool = new Pool();
const res = await pool.query('SELECT NOW()');2. Parameterized Query
await pool.query('SELECT * FROM users WHERE id = $1', [id]);3. Insert with Returning
await pool.query('INSERT INTO users (name) VALUES ($1) RETURNING id', [name]);4. Transaction
const client = await pool.connect();
try { await client.query('BEGIN'); ... } finally { client.release(); }5. Connection String
new Pool({ connectionString: process.env.DATABASE_URL });6. Environment Variables
new Pool(); // uses PGHOST, PGUSER, etc.7. SSL Connection
new Pool({ ssl: { rejectUnauthorized: false } });8. Pool Error Handler
pool.on('error', (err) => console.error(err));9. Graceful Shutdown
process.on('SIGTERM', async () => { await pool.end(); });10. Module Wrapper
// db/index.js
export const query = (text, params) => pool.query(text, params);Visual
Client vs Pool
┌──────────────────────────────────────────────────────────────┐
│ CLIENT vs POOL │
│ │
│ CLIENT: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ App ──► Client ──► PostgreSQL │ │
│ │ │ │
│ │ One connection. Created, used, ended. │ │
│ │ No reuse. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ POOL: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ App ──┬──► Client 1 ──► PostgreSQL │ │
│ │ ├──► Client 2 ──► PostgreSQL │ │
│ │ ├──► Client 3 ──► PostgreSQL │ │
│ │ └──► Client N ──► PostgreSQL │ │
│ │ │ │
│ │ Connections are reused. │ │
│ │ Pool manages the lifecycle. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Pool Lifecycle
┌──────────────────────────────────────────────────────────────┐
│ POOL LIFECYCLE │
│ │
│ new Pool(config) │
│ │ │
│ ▼ │
│ pool.query(text, params) │
│ │ │
│ ├── Check for idle connection in pool │
│ │ │
│ ├── If found: reuse it │
│ │ │
│ ├── If not found and below max: create new │
│ │ │
│ └── If at max: wait for one to be released │
│ │ │
│ ▼ │
│ Run query on connection │
│ │ │
│ ▼ │
│ Return connection to pool │
│ │ │
│ ▼ │
│ (Connection stays open for next query) │
│ │
│ pool.end() closes all connections. │
│ │
└──────────────────────────────────────────────────────────────┘Transaction with Checkout
┌──────────────────────────────────────────────────────────────┐
│ TRANSACTION WITH CHECKOUT │
│ │
│ pool.connect() │
│ │ │
│ ▼ │
│ client = checked out connection │
│ │ │
│ ├── client.query('BEGIN') │
│ │ │
│ ├── client.query('INSERT ...') │
│ │ │
│ ├── client.query('INSERT ...') │
│ │ │
│ ├── client.query('COMMIT') or client.query('ROLLBACK') │
│ │ │
│ ▼ │
│ client.release() │
│ │ │
│ ▼ │
│ Connection returns to pool │
│ │
│ All statements run on the SAME connection. │
│ This is why pool.query() does not work for transactions. │
│ │
└──────────────────────────────────────────────────────────────┘Parameterized Query Flow
┌──────────────────────────────────────────────────────────────┐
│ PARAMETERIZED QUERY │
│ │
│ pool.query( │
│ 'SELECT * FROM users WHERE email = $1', │
│ ['alice@example.com'] │
│ ) │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SQL template: SELECT * FROM users WHERE email = $1 │ │
│ │ Parameters: ['alice@example.com'] │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ PostgreSQL parses the template with the placeholder. │
│ The parameter is substituted as DATA, not SQL. │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Input: ' OR '1'='1 │ │
│ │ Result: SELECT * FROM users WHERE email = $1 │ │
│ │ Parameters: ["' OR '1'='1"] │ │
│ │ │ │
│ │ The string is treated as a literal value. │ │
│ │ No SQL injection. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
pg | Node.js PostgreSQL client |
Client | Single connection |
Pool | Connection pool |
| Configuration | Environment variables, object, or connection string |
| Query | pool.query(text, params) |
| Parameters | $1, $2, … |
| Transaction | pool.connect(), BEGIN/COMMIT/ROLLBACK, client.release() |
| Result | rows, rowCount, command |
| Cleanup | pool.end() |
| Error handling | pool.on('error', ...) |
Key takeaways:
Poolis for applications;Clientis for scripts. A pool reuses connections across queries, which is essential for performance and for avoiding connection exhaustion. A client is a single connection that is created and ended for one-off tasks .- Connection configuration follows
libpqenvironment variables.PGHOST,PGUSER,PGPASSWORD,PGPORT, andPGDATABASEare read automatically. The same code runs in every environment by changing the variables . - Parameterized queries are mandatory for user input. The
$1,$2placeholders separate SQL from data, preventing injection. The parameters are passed as an array . - Transactions require a checked-out client.
pool.query()does not guarantee the same connection.pool.connect()checks out a client, andBEGIN/COMMIT/ROLLBACKrun on that client. The client must be released in afinallyblock . - The pool emits error events for idle clients. Without a handler, an idle client error crashes the process. Always attach
pool.on('error', ...). pool.end()closes all connections. It should be called on application shutdown, typically in aSIGTERMorSIGINThandler.- A single module wrapping the pool simplifies maintenance. A
db/index.jsthat exportsqueryandgetClientcentralizes logging, diagnostics, and configuration. The rest of the application imports from that module, not frompgdirectly .
Remember: The pg driver is the foundation of PostgreSQL access in Node.js. It is low-level and un-opinionated, which means it gives you the full power of SQL and the responsibility for using it correctly. A Pool manages connections and reuses them; a Client is a single connection that must be closed. Parameterized queries prevent injection and reuse query plans. Transactions require a checked-out client, because they are scoped to a single connection. The pool is the unit of configuration and lifecycle, and it must be ended on shutdown. For applications that need precise control over SQL and transactions, pg is the right tool. It does not hide the database; it exposes it.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!