standard Node.js-to-PostgreSQL connection setup
Standard Node.js Connection to PostgreSQL: The Differences Between Client, Connection, Session, and Pool
Connecting a Node.js application to PostgreSQL may seem simple at first: we create a Client, call the connect() method, execute a Query, and close the connection at the end.
This approach is perfectly acceptable for a short script. However, when our application is a real API that receives multiple concurrent Requests, the situation becomes more complex. In such an application, we need to understand exactly what Client, Connection, Session, and Pool are, how they relate to one another, and where each one belongs in the architecture.
The recommended model for most Node.js APIs is: create one shared Pool per application Process, use
pool.query()for independent Queries, and obtain a Client directly from the Pool only when multiple operations must run on a specific Session.
The Problem with Creating a Connection for Every Request
Suppose we create a new Client for every Request in an Express application:
import express from "express";
import { Client } from "pg";
const app = express();
app.get("/users", async (_request, response, next) => {
const client = new Client({
connectionString: process.env.DATABASE_URL,
});
try {
await client.connect();
const result = await client.query(
"SELECT id, username, email FROM users",
);
response.json(result.rows);
} catch (error) {
next(error);
} finally {
await client.end();
}
});
From a functional perspective, this code makes sense: it establishes a connection, executes the Query, and closes the connection at the end. The problem begins when this process is repeated for every single Request.
For each Request, roughly the following steps occur:
- An Object of type
Clientis created. - A real Connection to PostgreSQL is established.
- For a network connection, a TCP handshake takes place.
- If TLS is enabled, a TLS handshake also takes place.
- PostgreSQL authenticates the user.
- A PostgreSQL Session is created.
- The Query is executed.
- The Connection is closed.
- The Session on the PostgreSQL side ends.
| A simplified view of this process: |
|---|
Request |
| ↓ |
Create a Client |
| ↓ |
Establish a Connection |
| ↓ |
Authentication and creation of a Session |
| ↓ |
Execute the Query |
| ↓ |
Close the Connection |
Creating a new Connection has a cost. This cost is not limited to the execution time of the SELECT; establishing communication, authentication, and creating state on the PostgreSQL side are also part of it.
Creating a new Connection has a cost. This cost is not limited to the execution time of the SELECT; establishing the connection, authentication, and creating state on the PostgreSQL side are also part of it.
If 100 Requests arrive at nearly the same time, the application attempts to repeat this process many times:
Request 1 → Connect → Query → Disconnect
Request 2 → Connect → Query → Disconnect
Request 3 → Connect → Query → Disconnect
...
Request 100 → Connect → Query → Disconnect
This model has three main problems:
- Response time increases.
- PostgreSQL continuously creates and destroys new Connections and Sessions.
- Controlling the number of concurrent Connections becomes difficult.
For a Migration, Seed, CLI tool, or one-time script, this model may be appropriate. However, for a Web API that continuously receives Requests, Connections should be reused. This is exactly the problem that a Connection Pool solves.
The Four Core Concepts of Connecting to PostgreSQL
Before examining the Pool, we need to distinguish these four concepts:
Client
Connection
Session
Pool
These concepts are related, but they are not synonymous.
| A simplified model of their relationship: |
|---|
Node.js code |
| ↓ |
Client |
| ↓ |
Connection |
| ↓ |
PostgreSQL Session |
And when a Pool is introduced into the architecture:
Pool
├── Client 1
│ └── Connection 1
│ └── Session 1
├── Client 2
│ └── Connection 2
│ └── Session 2
└── Client 3
└── Connection 3
└── Session 3
Now let us examine each one more closely.
What Is a Client?
In the pg package, also known as node-postgres, a Client is essentially a JavaScript Object inside the Node.js application.
import { Client } from "pg";
const client = new Client({
connectionString: process.env.DATABASE_URL,
});
At this point, only the Object has been created. There is not necessarily an active Connection to PostgreSQL yet.
When connect() is called, the Client attempts to establish the connection:
await client.connect();
We can then execute a Query:
const result = await client.query(
"SELECT NOW() AS current_time",
);
And finally, close the connection completely:
await client.end();
Therefore, a Client:
- Is an Object inside the Node.js application.
- Is part of the
pglibrary. - Is not PostgreSQL itself.
- Is not the network channel itself.
- Is the application's interface for creating and controlling a Connection.
- Converts and sends the Query using the protocol understood by PostgreSQL.
- Receives PostgreSQL's response and converts it into JavaScript structures.
Mental model:
Application Code
↓
pg.Client Object
↓
Connection
↓
PostgreSQL
A Client can be thought of as a controller. The application interacts with this Object, while the Client manages the details of communicating with PostgreSQL.
What Is a Connection?
A Connection is the actual communication channel between the Node.js application and PostgreSQL.
If the application and database communicate over a network, this Connection is usually a TCP/IP Connection:
Node.js
↕ TCP/IP Connection
PostgreSQL
If both Processes are running on the same Unix-like system, a Unix Domain Socket can also be used:
Node.js
↕ Unix Domain Socket
PostgreSQL
Therefore, a Connection may be one of the following:
TCP/IP Connection
Unix Domain Socket
The Connection is responsible for transferring data. When we execute this code:
await client.query(
"SELECT id, username FROM users WHERE id = $1",
[userId],
);
The Client prepares the SQL command and its parameters according to the PostgreSQL protocol and sends them through the Connection. PostgreSQL returns the result through the same Connection.
The important distinction is:
| Description | Concept |
|---|---|
The class provided by the pg package from which an object such as client is created | Client |
The actual network communication between the client and the PostgreSQL server | Connection |
There is no need to go deeply into TCP/IP, Unix Sockets, TLS, and Handshakes at this point. For now, it is enough to know that a Client communicates with PostgreSQL through a Connection.
What Is a PostgreSQL Session?
When a Connection is successfully established and the initial connection and authentication steps are completed, PostgreSQL creates a Session for that connection.
A Session is the state maintained by PostgreSQL for the lifetime of the Connection.
Client
↓
Connection
↓
PostgreSQL Session
A Session can retain information and state such as:
- The user used to establish the connection
- The selected database
- The current Transaction
- Settings applied with
SET - Temporary Tables
- Defined Cursors
- Channels being monitored with
LISTEN - Session-level Advisory Locks
- Prepared Statements
- Certain settings related to date formatting, Timezone, or Encoding
For example, if the following command is executed on a Session:
SET TIME ZONE 'UTC';
This setting belongs to that specific Session. Another Session does not necessarily have the same setting.
Or, if we create a Temporary Table:
CREATE TEMP TABLE imported_users (
id UUID,
email TEXT
);
This temporary Table exists only within that Session.
A Transaction also belongs to a Session:
BEGIN;
PostgreSQL must know which Transaction the subsequent Queries belong to, and this state is maintained in that same Session.
This point will become very important later:
Each active Connection usually corresponds to one specific Session in PostgreSQL, and Session state is not shared across different Connections.
When the Connection is closed, the Session also ends, and its associated state is destroyed.
What Is a Pool?
A Pool is a manager for a collection of Clients and Connections.
Pool
├── Client 1 → Connection 1 → Session 1
├── Client 2 → Connection 2 → Session 2
└── Client 3 → Connection 3 → Session 3
The Pool itself:
- Is not a Connection.
- Is not a Session.
- Is not a specific Query.
- Is not a single Client.
A Pool is a management Object inside the application that performs the following tasks:
- Creates new Clients.
- Keeps created Connections available.
- Selects an available Client for executing a Query.
- Creates a new Client when necessary, up to the configured limit.
- Keeps released Clients available for reuse.
- Queues requests that are waiting for a Client.
- Closes Idle Connections according to its configuration.
- Manages the overall state of active and available Clients.
Creating a Pool:
import { Pool } from "pg";
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
A Pool usually does not create all possible Connections immediately during Startup. Connections are created when needed and can be reused by later requests after a Query finishes.
Suppose the Pool has three Connections:
Pool
├── Client 1 → available
├── Client 2 → busy
└── Client 3 → available
When a new Query arrives, the Pool selects one of the available Clients:
| Execution path of the new Query: |
|---|
New Query |
| ↓ |
Pool |
| ↓ |
Client 1 |
| ↓ |
PostgreSQL |
After the Query finishes, the Client is returned to the Pool:
| After the Query finishes: |
|---|
Query completed |
| ↓ |
Client 1 became available |
| ↓ |
| Ready to be reused |
This eliminates the need to establish an entirely new Connection for every Query.
The Complete Relationship Between Pool, Client, Connection, and Session
A more precise model:
Node.js Process
│
└── Pool
│
├── Client 1
│ └── TCP/IP or Unix Socket Connection 1
│ └── PostgreSQL Session 1
│
├── Client 2
│ └── TCP/IP or Unix Socket Connection 2
│ └── PostgreSQL Session 2
│
└── Client 3
└── TCP/IP or Unix Socket Connection 3
└── PostgreSQL Session 3
Each layer has a different responsibility:
| Concept | Location | Responsibility |
|---|---|---|
Pool | Inside Node.js | Manages multiple Clients and Connections |
Client | Inside Node.js and the pg library | Provides the application interface for controlling a Connection and executing Queries |
Connection | Between Node.js and PostgreSQL | Transfers data through TCP/IP or a Unix Socket |
Session | On the PostgreSQL side | Maintains connection state such as Transactions, settings, and Temporary Tables |
A simple analogy:
| Concept | Description |
|---|---|
Pool | A manager for a collection of connections |
Client | A tool for managing and using a connection |
Connection | The actual communication established between the application and PostgreSQL |
Session | The state and interaction associated with that connection on the PostgreSQL side |
The analogy is not perfect, but it is useful for distinguishing the concepts.
One Pool per Request or per Process?
A Pool should not be created for every Request.
Incorrect model:
app.get("/users", async (_request, response) => {
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
const result = await pool.query(
"SELECT id, username FROM users",
);
await pool.end();
response.json(result.rows);
});
In this model, a new Pool is created and then closed for every Request. This effectively removes the benefits of Connection Pooling.
Correct model:
Node.js Process
│
├── Request A ─┐
├── Request B ─┼──> one shared Pool
├── Request C ─┤
└── Request D ─┘
In other words:
In each Node.js Process, one Pool is usually created, and all Requests handled by that Process use it.
For example:
// database.ts
import { Pool } from "pg";
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
Then, the same Pool is imported in different parts of the application:
import { pool } from "./database.js";
What Does Process Mean?
When the application is started like this:
node dist/server.js
We have one Node.js Process:
Process 1
└── Pool 1
However, if we run four instances of the application with PM2:
pm2 start dist/server.js -i 4
We will have four independent Processes:
Process 1 → Pool 1
Process 2 → Pool 2
Process 3 → Pool 3
Process 4 → Pool 4
These four Processes do not share memory. Therefore, the Pool is not shared between them, and each one creates its own Pool.
As a result, the phrase “one Pool per Process” does not mean the entire infrastructure always has only one Pool. If we have multiple Processes, Pods, Containers, or Instances, each one will have its own independent Pool.
The Relationship Between Singleton and Pool
It is sometimes said that a Singleton should be used for connecting to PostgreSQL. On its own, this statement can be misleading because Singleton and Pool do not solve the same problem.
What Does Singleton Control?
Singleton concerns the number of Objects created in the application's memory:
Only one Pool Object should be created in each Process
What Does Pool Control?
Pool concerns the management of multiple Clients and Connections:
| Pool structure: |
|---|
one Pool Object |
| ↓ |
multiple Clients and Connections |
Therefore, these two concepts are not competitors. A common model in a real API looks like this:
| Recommended structure: |
|---|
one shared Pool per Node.js Process |
| ↓ |
multiple Connections managed inside the Pool |
A more precise statement is therefore:
We keep the Pool as one shared instance within each Process, while the Pool itself manages multiple Connections.
What is usually not appropriate is using one permanent shared Client for all Requests:
| Communication structure: |
|---|
all Requests |
| ↓ |
one Client |
| ↓ |
one Connection |
In this model, there is only one Connection, Queries run on the same Session, and Session-dependent operations may interfere with other Requests. We will examine this issue in greater detail in the article about Transactions.
Why Is a Singleton Class Usually Unnecessary?
We might write a class like this:
import { Pool } from "pg";
export class PostgresSingleton {
private static instance: PostgresSingleton | undefined;
public readonly pool: Pool;
private constructor() {
this.pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
}
public static getInstance(): PostgresSingleton {
if (!PostgresSingleton.instance) {
PostgresSingleton.instance = new PostgresSingleton();
}
return PostgresSingleton.instance;
}
}
This code can work, but it is unnecessary in most Node.js projects.
In the usual Module structure, the database file is evaluated once within the Module Graph of that Process, and its Export is reused:
// postgres.ts
import { Pool } from "pg";
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
In another file:
import { pool } from "./postgres.js";
As long as all Imports resolve to the same Module and we do not deliberately create duplicate Modules or separate Loaders, the same Pool is used within the Process.
Creating a Singleton Class can:
- Make the code more complex.
- Make testing more difficult.
- Make replacing the Dependency harder.
- Complicate Dependency Injection.
- Add an extra layer without providing a real benefit.
For most projects, one central Module is sufficient:
// database/postgres.ts
export const pool = new Pool(config);
Of course, in architectures that use Dependency Injection, the Pool may be created in the Composition Root and injected into Adapters. The main principle remains unchanged: the number of Pools in each Process should be deliberate and controlled.
The Difference Between pool.query() and pool.connect()
There are two primary ways to execute a Query through a Pool.
Executing an Independent Query with pool.query()
For most independent Queries, it is better to use pool.query() directly:
const result = await pool.query(
`
SELECT
id,
username,
email
FROM users
WHERE id = $1
`,
[userId],
);
Behind the scenes, the Pool:
- Finds an available Client.
- Creates a new Client if none is available and capacity still remains.
- Executes the Query on the selected Client.
- Automatically returns the Client to the Pool after the Query finishes.
Model:
The pool.query() process: |
|---|
pool.query() |
| ↓ |
Automatically acquire a Client |
| ↓ |
Execute the Query |
| ↓ |
Automatically return the Client to the Pool |
In this case, we must not call release() ourselves because the Pool handles it automatically.
This method is appropriate for:
- An independent
SELECT - An independent
INSERT - An independent
UPDATE - An independent
DELETE - Any operation in which multiple Queries do not need to run on one specific Session
Acquiring a Client with pool.connect()
Sometimes multiple operations must run on one specific Client and Session. In this case, we acquire a Client directly from the Pool:
const client = await pool.connect();
try {
const userResult = await client.query(
"SELECT id, username FROM users WHERE id = $1",
[userId],
);
const taskResult = await client.query(
"SELECT id, title FROM tasks WHERE user_id = $1",
[userId],
);
return {
user: userResult.rows[0] ?? null,
tasks: taskResult.rows,
};
} finally {
client.release();
}
When we use pool.connect(), returning the Client is our responsibility. For this reason, release() must be placed in a finally block so that it is executed even if an error occurs.
The most important use case for this method is a Transaction:
const client = await pool.connect();
try {
await client.query("BEGIN");
// All Queries in the Transaction use this same client
await client.query("COMMIT");
} catch (error) {
await client.query("ROLLBACK");
throw error;
} finally {
client.release();
}
Because a Transaction belongs to a Session, all of its Queries must be executed on one specific Client. Transactions, BEGIN, COMMIT, and ROLLBACK will be covered in full detail in a separate article.
A Simple Rule
One independent Query: |
|---|
pool.query() |
Multiple operations tied to one Session: |
|---|
pool.connect() |
| ↓ |
Use the same Client |
| ↓ |
client.release() |
The Difference Between release() and end()
These two methods must not be confused with one another.
client.release()
When we acquire a Client from a Pool:
const client = await pool.connect();
We Release it at the end:
client.release();
release() usually means:
Releasing the Client: |
|---|
The Client is no longer busy |
| ↓ |
It returns to the Pool |
| ↓ |
The Connection remains open for later use |
In other words, the Connection and Session do not necessarily end. They return to the Pool so that the next Query can reuse the same Connection.
client.end()
When we have created an independent Client:
const client = new Client(config);
await client.connect();
Calling end() actually closes the connection:
await client.end();
Result:
Result of closing the Connection: |
|---|
The Connection is closed |
| ↓ |
The Session on the PostgreSQL side ends |
pool.end()
This method is used to close the entire Pool:
await pool.end();
It is appropriate when the application is shutting down, not at the end of every Request.
Summary:
client.release() |
|---|
Returns the Client to the Pool |
| ↓ |
The Connection remains open for reuse |
client.end() |
|---|
Closes the Connection associated with an independent Client |
| ↓ |
The Session ends |
pool.end() |
|---|
Drains the Pool |
| ↓ |
The Connections inside the Pool are closed |
Recommended Project Structure
For an Express and TypeScript project, the following structure can be used:
src/
├── config/
│ └── env.ts
│
├── infrastructure/
│ └── database/
│ └── postgres.ts
│
├── modules/
│ └── users/
│ ├── user.repository.ts
│ ├── user.service.ts
│ ├── user.controller.ts
│ └── user.routes.ts
│
├── app.ts
└── server.ts
Dependency flow:
HTTP Request
↓
Controller
↓
Service / Use Case
↓
Repository
↓
Database Adapter
↓
Pool
↓
PostgreSQL
Controller
The Controller is responsible for HTTP concerns:
- Receiving input
- Reading Parameters
- Sending Status Codes
- Building the Response
- Passing Errors to the Error Handler
Service or Use Case
The Service or Use Case is responsible for Application and Business logic:
- Making decisions about the operation flow
- Coordinating Repositories
- Enforcing Business Rules
- Defining Transaction boundaries for complex operations
Repository
A Repository is responsible for data access for a specific part of the application:
usersQueriestasksQueries- Converting database Rows into the models required by the application
Database Adapter
A Database Adapter is a central layer for communicating with the database Driver:
- Executing Queries
- Managing the Pool
- Checking connection health
- Graceful Shutdown
- Logging and Metrics
- A Transaction helper in more advanced versions
Pool
The Pool manages PostgreSQL Clients and actual Connections.
Implementing a Central Pool
First, install the packages:
npm install pg
npm install --save-dev @types/pg
In versions where Types are provided directly with the package, the need for
@types/pgmay differ. Check your project configuration and the version being used.
Environment variables:
DATABASE_URL=postgresql://app_user:strong_password@127.0.0.1:5432/app_db
PG_POOL_MAX=10
PG_CONNECTION_TIMEOUT_MS=5000
PG_IDLE_TIMEOUT_MS=30000
APP_NAME=my-api
The central PostgreSQL file:
// src/infrastructure/database/postgres.ts
import {
Pool,
type QueryResult,
type QueryResultRow,
} from "pg";
function parsePositiveInteger(
value: string | undefined,
fallback: number,
): number {
if (!value) {
return fallback;
}
const parsedValue = Number.parseInt(value, 10);
if (!Number.isFinite(parsedValue) || parsedValue <= 0) {
return fallback;
}
return parsedValue;
}
const databaseUrl = process.env.DATABASE_URL;
if (!databaseUrl) {
throw new Error("DATABASE_URL is not defined");
}
const pool = new Pool({
connectionString: databaseUrl,
// Maximum number of Clients in this Pool
max: parsePositiveInteger(process.env.PG_POOL_MAX, 10),
// Maximum time to wait for a Connection to be established
connectionTimeoutMillis: parsePositiveInteger(
process.env.PG_CONNECTION_TIMEOUT_MS,
5_000,
),
// How long an available Client remains in the Pool
idleTimeoutMillis: parsePositiveInteger(
process.env.PG_IDLE_TIMEOUT_MS,
30_000,
),
application_name: process.env.APP_NAME ?? "node-api",
});
pool.on("error", (error) => {
console.error("Unexpected PostgreSQL pool error", {
name: error.name,
message: error.message,
stack: error.stack,
});
});
export async function query<
T extends QueryResultRow = QueryResultRow,
>(
text: string,
values: unknown[] = [],
): Promise<QueryResult<T>> {
const startedAt = performance.now();
try {
return await pool.query<T>(text, values);
} finally {
const durationMs = performance.now() - startedAt;
if (durationMs >= 500) {
console.warn("Slow PostgreSQL query detected", {
durationMs: Math.round(durationMs),
});
}
}
}
export async function checkDatabaseConnection(): Promise<void> {
await pool.query("SELECT 1");
}
export async function closeDatabaseConnection(): Promise<void> {
await pool.end();
}
export function getPoolStatistics() {
return {
total: pool.totalCount,
idle: pool.idleCount,
waiting: pool.waitingCount,
};
}
export { pool };
A few points about this file:
- The Pool is created only once in the central Module.
- Repositories do not need to know the connection configuration directly.
- The
query()function is the central execution point for independent Queries. - The
errorlistener records unexpected errors from Pool Clients. SELECT 1is available for an initial PostgreSQL availability check.pool.end()is available for application Shutdown.- Pool statistics can be retrieved for Monitoring.
- Query parameters are intentionally not written to Logs to prevent sensitive information from leaking.
The precise configuration of
max,idleTimeoutMillis,connectionTimeoutMillis, and other Pool options depends on the application's workload and Deployment architecture. There is no need to go deeper into this topic at this point.
Using the Database Adapter in a Repository
A simple Repository for User:
// src/modules/users/user.repository.ts
import {
query,
} from "../../infrastructure/database/postgres.js";
interface UserRow {
id: string;
username: string;
email: string;
first_name: string;
last_name: string;
}
interface CreateUserInput {
id: string;
username: string;
email: string;
firstName: string;
lastName: string;
passwordHash: string;
}
export class UserRepository {
async findById(id: string): Promise<UserRow | null> {
const result = await query<UserRow>(
`
SELECT
id,
username,
email,
first_name,
last_name
FROM users
WHERE id = $1
`,
[id],
);
return result.rows[0] ?? null;
}
async findByUsername(
username: string,
): Promise<UserRow | null> {
const result = await query<UserRow>(
`
SELECT
id,
username,
email,
first_name,
last_name
FROM users
WHERE username = $1
`,
[username],
);
return result.rows[0] ?? null;
}
async create(input: CreateUserInput): Promise<UserRow> {
const result = await query<UserRow>(
`
INSERT INTO users (
id,
username,
email,
first_name,
last_name,
password
)
VALUES ($1, $2, $3, $4, $5, $6)
RETURNING
id,
username,
email,
first_name,
last_name
`,
[
input.id,
input.username,
input.email,
input.firstName,
input.lastName,
input.passwordHash,
],
);
const createdUser = result.rows[0];
if (!createdUser) {
throw new Error("PostgreSQL did not return the created user");
}
return createdUser;
}
}
In this structure, the Repository does not know how the Pool was created, what the Connection String is, or what happens during Shutdown. It only uses the internal API provided by the Database Adapter.
The benefits of this separation include:
- Connection configuration is centralized.
- Logging changes are made in only one file.
- Health Checks are controlled from a single point.
- Repositories remain simpler.
- Mocking the Database Adapter in tests becomes easier.
- Driver-specific changes are less widely distributed throughout the project.
In larger architectures, instead of directly importing the query function, an Interface can be defined and the Database Adapter can be provided to the Repository through Dependency Injection. However, as a starting point, this central structure is far better than importing and creating a Pool inside every Repository.
Parameterized Queries and Preventing SQL Injection
Never insert user input directly into SQL through String interpolation or Concatenation.
Unsafe Approach
const result = await pool.query(
`
SELECT id, username
FROM users
WHERE username = '${username}'
`,
);
Suppose the value of username is controlled by the user. The user can construct an input that changes the structure of the SQL statement.
Correct Approach
const result = await pool.query(
`
SELECT id, username
FROM users
WHERE username = $1
`,
[username],
);
In a parameterized Query, the SQL text and the Values are sent separately:
SQL:
SELECT ... WHERE username = $1
Values:
[username]
PostgreSQL processes the value as Data, not as part of the SQL structure.
For multiple Parameters:
const result = await pool.query(
`
SELECT
id,
username,
email
FROM users
WHERE email = $1
AND status = $2
`,
[email, "ACTIVE"],
);
Placeholder numbering starts at one:
$1 → email
$2 → ACTIVE
A Note About Identifiers
Parameters such as $1, $2, and so on are for Values, not Table or Column names.
This does not work:
await pool.query(
"SELECT * FROM $1",
[tableName],
);
If a Table or Column name must be dynamic, it is better to:
- Use a clearly defined Allowlist.
- Avoid constructing Identifiers from unrestricted user input.
- Use an appropriate tool for escaping Identifiers.
- Reconsider the Query design so that unnecessary Dynamic SQL can be eliminated.
Checking the Connection During Startup
If the API cannot perform its core function without PostgreSQL, it is better to verify the database connection before the HTTP Server starts listening.
// src/server.ts
import app from "./app.js";
import {
checkDatabaseConnection,
} from "./infrastructure/database/postgres.js";
const port = Number(process.env.PORT ?? 3000);
async function bootstrap(): Promise<void> {
await checkDatabaseConnection();
app.listen(port, () => {
console.log(`HTTP server is running on port ${port}`);
});
}
bootstrap().catch((error) => {
console.error("Application startup failed", error);
process.exit(1);
});
Flow:
Process starts
↓
Validate environment variables
↓
SELECT 1
↓
Connection successful?
├── Yes → Start the HTTP Server
└── No → Exit the Process with an error
The benefit of this approach is that the application does not appear to be Up while no Queries can actually be executed.
Of course, this is an architectural decision. Some services may be able to provide part of their functionality without the database or may prefer to Retry the connection later. However, for most APIs that depend on PostgreSQL, Failing Fast during Startup provides behavior that is easier to understand and monitor.
Graceful Shutdown
When the Process receives a termination signal, it should not exit abruptly. It should:
- Stop accepting new Requests.
- Allow in-progress Requests time to finish.
- Close the Pool.
- End the PostgreSQL Connections.
- Exit the Process.
Common signals:
| Signal | Usually sent by |
|---|---|
SIGINT | Pressing Ctrl + C in the terminal |
SIGTERM | Tools such as PM2, Docker, Kubernetes, or the operating system |
Implementation:
// src/server.ts
import app from "./app.js";
import {
checkDatabaseConnection,
closeDatabaseConnection,
} from "./infrastructure/database/postgres.js";
const port = Number(process.env.PORT ?? 3000);
async function bootstrap(): Promise<void> {
await checkDatabaseConnection();
const server = app.listen(port, () => {
console.log(`HTTP server is running on port ${port}`);
});
let isShuttingDown = false;
async function shutdown(signal: string): Promise<void> {
if (isShuttingDown) {
return;
}
isShuttingDown = true;
console.log(`${signal} received. Starting graceful shutdown.`);
server.close(async (httpError) => {
try {
await closeDatabaseConnection();
} catch (databaseError) {
console.error(
"Failed to close PostgreSQL pool",
databaseError,
);
}
if (httpError) {
console.error(
"HTTP server shutdown failed",
httpError,
);
process.exit(1);
}
console.log("Application stopped successfully");
process.exit(0);
});
}
process.once("SIGINT", () => {
void shutdown("SIGINT");
});
process.once("SIGTERM", () => {
void shutdown("SIGTERM");
});
}
bootstrap().catch((error) => {
console.error("Application startup failed", error);
process.exit(1);
});
In Production, an emergency Shutdown Timeout is usually also configured so that if a Request or Resource remains stuck indefinitely, the Process is forcefully terminated after a specified period. Its value should be coordinated with the Grace Period configured in the Deployment tool.
Why Do We Not Call pool.end() After Every Query?
Because pool.end() is intended to end the lifetime of the Pool. If it is called after every Request, the next Query can no longer use the Pool, and we would have to create a new Pool again.
End of a Query: |
|---|
The Client returns to the Pool |
End of the Process: |
|---|
pool.end() |
Common Mistakes
1. Creating a Pool Inside a Route or Controller
app.get("/users", async (_request, response) => {
const pool = new Pool(config);
const result = await pool.query("SELECT * FROM users");
response.json(result.rows);
});
Problem: a new Pool is created for every Request.
2. Creating a Pool in Every Repository
export class UserRepository {
private readonly pool = new Pool(config);
}
If we have ten Repositories, we may create ten independent Pools inside one Process. Each Pool can also create multiple Connections, causing the total number of Connections to increase rapidly.
Correct model:
| Database access structure: |
|---|
all Repositories |
| ↓ |
shared Database Adapter |
| ↓ |
one Pool in the same Process |
3. Using One Permanent Client for All Requests
const client = new Client(config);
await client.connect();
export { client };
This can be intentional for certain dedicated Sessions, such as LISTEN, but it is not appropriate for all Queries in a general-purpose API.
Problems:
- There is only one Connection.
- Queries run on one shared Session.
- Session-bound operations can affect other Requests.
- Transactions may interfere with one another in dangerous ways.
- A failure of that single Connection affects all database access.
4. Forgetting to Call release()
const client = await pool.connect();
await client.query("SELECT * FROM users");
// client.release() was forgotten
This Client does not return to the Pool and remains marked as busy. Repeating this mistake causes a Client Leak, and new Requests begin waiting for a Connection.
Correct approach:
const client = await pool.connect();
try {
await client.query("SELECT * FROM users");
} finally {
client.release();
}
5. Using pool.connect() Without a Reason
There is no need to manually acquire a Client for one independent Query:
const client = await pool.connect();
try {
return await client.query(
"SELECT * FROM users WHERE id = $1",
[userId],
);
} finally {
client.release();
}
A simpler version:
return pool.query(
"SELECT * FROM users WHERE id = $1",
[userId],
);
pool.connect() is valuable when we genuinely need a fixed Client and Session for multiple operations.
6. Executing a Transaction with Multiple pool.query() Calls
This model is dangerous:
await pool.query("BEGIN");
await pool.query("UPDATE accounts SET ...");
await pool.query("COMMIT");
The Pool does not guarantee that all these commands will be executed on the same Client. A Transaction must be executed with a Client acquired through pool.connect().
7. Closing the Pool After Every Request
await pool.query("SELECT ...");
await pool.end();
pool.end() is for shutting down the entire Application, not for ending a Query.
8. Building SQL with User Input
const sql = `
SELECT *
FROM users
WHERE email = '${email}'
`;
This approach exposes the application to SQL Injection. Use parameterized Queries instead.
9. Logging Sensitive Query Values
console.log({
text,
values,
});
The values array may contain:
- Password hashes
- Refresh Tokens
- Email addresses
- Phone numbers
- Personal information
- Secrets
For safer Logging, record the Query Name, execution time, Row count, and Error Code. Values should only be included in Logs under a clearly defined Redaction policy.
10. Assuming a Singleton Is Shared Across Processes
If we have four PM2 Processes, each one has independent memory:
Process 1 → its own Singleton → its own Pool
Process 2 → its own Singleton → its own Pool
Process 3 → its own Singleton → its own Pool
Process 4 → its own Singleton → its own Pool
A Singleton only has meaning within the same Process.
Guide to Choosing Client or Pool
| Scenario | Appropriate choice |
|---|---|
| Typical Web API | One shared Pool per Process |
| One independent Query | pool.query() |
| Multiple Queries inside a Transaction | pool.connect() and one fixed Client |
| Session-dependent operations | A fixed Client |
| Short script | A direct Client or a short-lived Pool |
| Migration | A Client or a short-lived Pool |
| Seed | A Client or a short-lived Pool |
| CLI | A Client or a short-lived Pool |
LISTEN / NOTIFY | A dedicated, long-lived Client |
| Long-running Cursor or Streaming operation | A dedicated Client acquired from the Pool or an independent Client |
| Application Shutdown | pool.end() |
The main rule:
Independent Query?
→ pool.query()
Need a fixed Session?
→ pool.connect()
Is it a short, one-time program?
→ Direct Client or short-lived Pool
Is the application shutting down?
→ pool.end()
Summary
The standard model for connecting a Node.js API to PostgreSQL usually looks like this:
Node.js Process
│
└── one shared Pool
├── Client 1
│ └── Connection 1
│ └── PostgreSQL Session 1
├── Client 2
│ └── Connection 2
│ └── PostgreSQL Session 2
└── Client N
└── Connection N
└── PostgreSQL Session N
Key points:
- Do not create a new Pool for every Request.
- Usually, maintain one shared Pool per Process.
- Pool and Singleton are not competing concepts.
- A Pool manages multiple Clients and Connections.
- A Client is an Object inside the Node.js application.
- A Connection is the actual communication channel.
- A Session is the state maintained on the PostgreSQL side.
- Use
pool.query()for an independent Query. - Use
pool.connect()for operations that require a fixed Session. - Always release a Client acquired from the Pool inside a
finallyblock. release()does not close the Connection; it returns the Client to the Pool.client.end()closes the Connection of an independent Client.pool.end()is used when shutting down the entire Pool.- Use parameterized Queries.
- Manage the Pool through a central Database Adapter.
- Verify the connection during Startup.
- Cleanly shut down the HTTP Server and Pool during Shutdown.
- Remember that every Process, Pod, or Container has its own independent Pool.