Connecting Node.js to PostgreSQL: node-postgres (pg)

You have a schema. You have SQL skills. Now let's connect an application to the database and run queries from TypeScript code.

The tool for this is node-postgres — the pg package — the foundational PostgreSQL driver for Node.js. Every major PostgreSQL abstraction (Knex, Drizzle, Prisma) uses pg under the hood. Learning it directly means you understand what those abstractions are doing, when to trust them, and when to bypass them.

This article deliberately comes before ORMs. Not because raw SQL is always the right choice — it isn't — but because you'll make better decisions about which abstraction to use once you understand what they're abstracting.

Quick Reference

Install:

npm install pg
npm install --save-dev @types/pg

Connection pool (the right way):

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Query (parameterized — always):

const { rows } = await pool.query(
  "SELECT * FROM products WHERE category = $1 AND price < $2",
  ["Electronics", 100],
);

Transaction:

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE ...', [...]);
  await client.query('INSERT ...', [...]);
  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

Gotchas:

⚠️ Never use string concatenation to build queries — always parameterized queries ($1, $2, ...)
⚠️ Never create new Client() per request — use a Pool
⚠️ Always call client.release() in a finally block — leaked clients exhaust the pool
⚠️ Never commit credentials to source code — use environment variables

Version Information

Tested with:

pg: ^8.11.0
Node.js: v18.x, v20.x, v22.x
PostgreSQL: 15.x, 16.x
TypeScript: 5.x

Last verified: May 2025

What You Need to Know First

Required reading:

SQL Basics: Reading Data with SELECT through Indexes: Making Queries Fast — you need to be comfortable writing SQL before running it from code

Tools you'll need:

Node.js v18 or later
A running PostgreSQL instance (local, or a free cloud instance at Neon or Supabase)
The ShopFlow schema from Schema Design already created and seeded

What We'll Cover in This Article

By the end of this guide, you'll understand:

How to install and configure pg
The difference between a Client and a Pool — and why you always use Pool
How to run parameterized queries safely
How SQL injection works — and why parameterized queries prevent it
How to type query results with TypeScript
How to implement transactions using a pool client
How to integrate pg with an Express application

What We'll Explain Along the Way

We'll introduce these concepts with full explanations:

What connection pooling is and why it matters
The $1, $2 parameter syntax in PostgreSQL
How pg maps PostgreSQL types to JavaScript types
Environment variables for secrets management

Part 1: Installing and Configuring

npm install pg
npm install --save-dev @types/pg

pg is the driver. @types/pg adds TypeScript type definitions.

Environment Variables — Never Hardcode Credentials

Your database credentials (host, port, username, password, database name) must never appear in source code. Use environment variables.

Create a .env file in your project root (and add it to .gitignore immediately):

# .env — DO NOT COMMIT THIS FILE
DATABASE_URL=postgresql://shopflow_user:your_password@localhost:5432/shopflow

The DATABASE_URL format is: postgresql://username:password@host:port/database

For production, set this environment variable in your hosting environment directly — never in a file that gets committed.

Load environment variables using dotenv:

npm install dotenv

// src/db.ts — load before anything else
import "dotenv/config";

Or pass it to Node.js directly:

node --env-file=.env dist/index.js  # Node.js 20.6+

Part 2: Client vs Pool — Use Pool

pg exports two main classes: Client and Pool. Understanding the difference is essential.

Client — One Connection

new Client() opens a single connection to PostgreSQL. You connect, run queries, then disconnect.

import { Client } from "pg";

const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
const result = await client.query("SELECT * FROM products");
await client.end();

Never use Client for web server request handling. Here's why:

Opening a database connection takes 50–300ms — an eternity for a web request
PostgreSQL has a limited number of connection slots (default 100)
If every incoming HTTP request opens its own connection, you'll exhaust PostgreSQL's limit under any real load
If the request throws an error before client.end(), the connection leaks

Client is useful for one-off scripts, migrations, and CLI tools. Never in a web server request handler.

Pool — The Right Choice for Web Servers

new Pool() manages a pool of connections that are reused across requests:

// src/db.ts
import { Pool } from "pg";

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,

  // Maximum number of connections in the pool
  // Set to ~(number of CPU cores * 2) for most apps
  // Don't exceed PostgreSQL's max_connections limit
  max: 20,

  // Close idle connections after 30 seconds
  idleTimeoutMillis: 30_000,

  // Throw an error if a connection isn't available within 2 seconds
  connectionTimeoutMillis: 2_000,
});

// Optional: verify the connection on startup
pool.on("error", (err) => {
  console.error("Unexpected error on idle client", err);
  process.exit(-1);
});

With a pool:

Connections are created upfront and reused — no 300ms handshake per request
The pool manages checkout/return automatically
If all connections are busy, requests wait up to connectionTimeoutMillis before failing
Idle connections are closed automatically

The pool is a singleton. Create it once when your application starts, export it, and import it wherever you need to query. Don't create a new Pool per request or per module.

Part 3: Running Queries

Basic Query

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

// pool.query() checks out a connection, runs the query, returns it to the pool
const result = await pool.query("SELECT id, name, price FROM products");

// result.rows is an array of objects — one per row
// Each object's keys are the column names
console.log(result.rows);
// [
//   { id: 1, name: 'Wireless Keyboard', price: '89.99' },
//   { id: 2, name: 'Mechanical Mouse', price: '64.99' },
//   ...
// ]

Notice that price comes back as a string ('89.99'), not a number. This is because PostgreSQL's NUMERIC type doesn't map cleanly to JavaScript's number (which is a float and would lose precision). We'll handle this in the TypeScript types section.

Destructuring the Result

You usually only need rows:

const { rows } = await pool.query("SELECT id, name, price FROM products");

Other properties on the result object:

const result = await pool.query("SELECT * FROM products");
result.rows; // Array of row objects
result.rowCount; // Number of rows returned (or affected for INSERT/UPDATE/DELETE)
result.fields; // Array of column metadata objects (name, dataTypeID, etc.)
result.command; // The SQL command ('SELECT', 'INSERT', etc.)

Part 4: Parameterized Queries — The Only Safe Way

This is the most important section in this article.

Never build a SQL query by concatenating strings with user input:

// ❌ NEVER DO THIS — SQL injection vulnerability
const category = req.query.category; // user input
const query = `SELECT * FROM products WHERE category = '${category}'`;
await pool.query(query);

Here's why. Suppose a malicious user sends this as the category parameter:

'; DROP TABLE products; --

Your query becomes:

SELECT * FROM products WHERE category = ''; DROP TABLE products; --'

Two statements: a harmless SELECT and a DROP TABLE that destroys your products table. This is a SQL injection attack — one of the most common and damaging web vulnerabilities.

Parameterized Queries

Parameterized queries separate the SQL statement from the values. The database receives the query and the values separately — values can never be interpreted as SQL:

// ✅ Safe: values passed separately as an array
const { rows } = await pool.query(
  "SELECT * FROM products WHERE category = $1 AND price < $2",
  ["Electronics", 100],
);

Parameters are referenced as $1, $2, $3... (1-indexed, not 0-indexed). The values array maps positionally: $1 → 'Electronics', $2 → 100.

Now if a user sends '; DROP TABLE products; --' as the category, it's treated as a literal string value — the database looks for a product with that exact (absurd) category name and returns zero results. No injection possible.

Parameterized Writes

The same pattern applies to INSERT, UPDATE, and DELETE:

// INSERT
const { rows } = await pool.query(
  `INSERT INTO products (name, description, price, stock_quantity, category)
   VALUES ($1, $2, $3, $4, $5)
   RETURNING id, name`,
  ["Standing Desk Mat", "Anti-fatigue mat", 79.99, 50, "Accessories"],
);
const newProduct = rows[0]; // { id: 9, name: 'Standing Desk Mat' }

// UPDATE
const { rowCount } = await pool.query(
  "UPDATE products SET price = $1 WHERE id = $2",
  [44.99, 3],
);
console.log(`Updated ${rowCount} row(s)`);

// DELETE
const { rowCount } = await pool.query(
  "DELETE FROM products WHERE id = $1",
  [9],
);
console.log(`Deleted ${rowCount} row(s)`);

Rule: every value that comes from outside your codebase goes through a parameter. This includes user input, URL parameters, request bodies, data from other services — everything. The only strings that go directly into the SQL template are column names and table names (which should never come from user input).

Part 5: TypeScript Types for Query Results

By default, pool.query() returns QueryResult<any> — rows is any[]. You can narrow this with a generic type parameter:

// Define the shape of a product row
interface ProductRow {
  id: number;
  name: string;
  description: string | null;
  price: string; // NUMERIC comes back as string — parse it if needed
  stock_quantity: number;
  category: string;
}

// Pass the type to pool.query
const { rows } = await pool.query<ProductRow>(
  "SELECT id, name, description, price, stock_quantity, category FROM products WHERE category = $1",
  ["Electronics"],
);

// rows is now ProductRow[] — TypeScript knows the shape
rows.forEach((product) => {
  console.log(product.name); // ✅ string
  console.log(product.price); // ✅ string (parse with parseFloat if you need a number)
  console.log(product.missing); // ❌ TypeScript error — unknown property
});

Handling NUMERIC as Number

PostgreSQL's NUMERIC type comes back as a string to preserve precision. If you need a JavaScript number:

interface ProductRow {
  id: number;
  name: string;
  price: string; // raw from pg
}

interface Product {
  id: number;
  name: string;
  price: number; // parsed
}

function parseProduct(row: ProductRow): Product {
  return {
    ...row,
    price: parseFloat(row.price),
  };
}

const { rows } = await pool.query<ProductRow>(
  "SELECT id, name, price FROM products",
);
const products: Product[] = rows.map(parseProduct);

For financial calculations, consider keeping prices as strings and using a library like decimal.js to avoid floating-point arithmetic issues.

Part 6: Transactions with Pool

For transactions, you need a dedicated client from the pool — a single connection that holds the transaction open across multiple queries:

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

async function placeOrder(
  userId: number,
  items: Array<{ productId: number; quantity: number; price: number }>,
): Promise<{ orderId: number }> {
  // Check out a dedicated connection from the pool
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    // Step 1: Create the order
    const orderResult = await client.query<{ id: number }>(
      `INSERT INTO orders (user_id, status)
       VALUES ($1, 'pending')
       RETURNING id`,
      [userId],
    );
    const orderId = orderResult.rows[0].id;

    // Step 2: Insert each item and update stock
    for (const item of items) {
      // Add item to order
      await client.query(
        `INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase)
         VALUES ($1, $2, $3, $4)`,
        [orderId, item.productId, item.quantity, item.price],
      );

      // Reduce stock — check for sufficient inventory
      const stockUpdate = await client.query(
        `UPDATE products
         SET stock_quantity = stock_quantity - $1
         WHERE id = $2 AND stock_quantity >= $1
         RETURNING stock_quantity`,
        [item.quantity, item.productId],
      );

      if (stockUpdate.rowCount === 0) {
        // No rows updated = insufficient stock — abort
        throw new Error(`Insufficient stock for product ${item.productId}`);
      }
    }

    await client.query("COMMIT");
    return { orderId };
  } catch (error) {
    // Always rollback on error
    await client.query("ROLLBACK");
    throw error;
  } finally {
    // Always release the client back to the pool
    // This runs whether the transaction succeeded or failed
    client.release();
  }
}

The finally block is critical. If you forget client.release(), the client stays checked out of the pool forever — eventually exhausting all available connections and hanging every subsequent database operation.

Part 7: Express Integration

Here's a complete Express route module using pg to serve ShopFlow product data:

// src/routes/products.ts
import { Router, Request, Response, NextFunction } from "express";
import { pool } from "../db.js";

const router = Router();

// GET /products — list products with optional filtering
router.get("/", async (req: Request, res: Response, next: NextFunction) => {
  try {
    const { category, max_price, in_stock } = req.query;

    // Build query dynamically — note: column names are hardcoded, values are parameterized
    const conditions: string[] = [];
    const values: unknown[] = [];
    let paramIndex = 1;

    if (category) {
      conditions.push(`category = $${paramIndex++}`);
      values.push(category);
    }

    if (max_price) {
      conditions.push(`price <= $${paramIndex++}`);
      values.push(Number(max_price));
    }

    if (in_stock === "true") {
      conditions.push(`stock_quantity > 0`);
      // No parameter needed — this is a hardcoded condition, not user input
    }

    const whereClause =
      conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";

    const { rows } = await pool.query(
      `SELECT id, name, description, price, stock_quantity, category
       FROM products
       ${whereClause}
       ORDER BY name ASC`,
      values,
    );

    res.json({ products: rows });
  } catch (error) {
    next(error); // Pass to Express error handler
  }
});

// GET /products/:id — single product
router.get("/:id", async (req: Request, res: Response, next: NextFunction) => {
  try {
    const { rows } = await pool.query("SELECT * FROM products WHERE id = $1", [
      req.params.id,
    ]);

    if (rows.length === 0) {
      return res.status(404).json({ error: "Product not found" });
    }

    res.json({ product: rows[0] });
  } catch (error) {
    next(error);
  }
});

// POST /products — create product
router.post("/", async (req: Request, res: Response, next: NextFunction) => {
  try {
    const { name, description, price, stock_quantity, category } = req.body;

    // Basic validation — in production, use a library like zod
    if (!name || !price || !category) {
      return res
        .status(400)
        .json({ error: "name, price, and category are required" });
    }

    const { rows } = await pool.query(
      `INSERT INTO products (name, description, price, stock_quantity, category)
       VALUES ($1, $2, $3, $4, $5)
       RETURNING *`,
      [name, description ?? null, price, stock_quantity ?? 0, category],
    );

    res.status(201).json({ product: rows[0] });
  } catch (error) {
    next(error);
  }
});

export default router;

// src/index.ts
import "dotenv/config";
import express from "express";
import productRoutes from "./routes/products.js";

const app = express();
app.use(express.json());
app.use("/products", productRoutes);

// Generic error handler
app.use(
  (
    err: Error,
    req: express.Request,
    res: express.Response,
    next: express.NextFunction,
  ) => {
    console.error(err.stack);
    res.status(500).json({ error: "Internal server error" });
  },
);

const PORT = process.env.PORT ?? 3000;
app.listen(PORT, () => {
  console.log(`ShopFlow API running on port ${PORT}`);
});

Common Misconceptions

❌ Misconception: Creating a new Client per request is fine for small apps

Reality: It slows down every request (connection handshake overhead), risks exhausting PostgreSQL's connection limit, and leaks connections on errors. There's no scale at which new Client() per request is the right approach for a web server.

Fix: Always use Pool. One pool, created at startup, shared across all requests.

❌ Misconception: Parameterized queries are just a security best practice, not always necessary

Reality: Parameterized queries are the only safe way to include variable data in SQL. String concatenation is dangerous even for "trusted" values — code changes over time, and what's trusted today might accept user input tomorrow. Make parameterization a reflex.

❌ Misconception: `pg` types match PostgreSQL types directly

Reality: pg maps types approximately — INTEGER becomes number, TEXT becomes string, but NUMERIC/DECIMAL becomes string (to preserve precision), BOOLEAN becomes boolean, and TIMESTAMP becomes a JavaScript Date. Always check the actual type of rows[0].column when unsure.

Troubleshooting Common Issues

Problem: `Error: connect ECONNREFUSED 127.0.0.1:5432`

Symptoms: Connection fails immediately on startup.

Common causes:

PostgreSQL isn't running
Wrong host or port in DATABASE_URL
PostgreSQL is running on a different port

Solution:

# Check if PostgreSQL is running
pg_isready -h localhost -p 5432

# Check what's listening on 5432
lsof -i :5432

Problem: `Error: password authentication failed for user`

Symptoms: Connection attempt rejected by PostgreSQL.

Cause: Wrong username or password in DATABASE_URL.

Solution: Verify credentials with psql directly:

psql "postgresql://shopflow_user:your_password@localhost:5432/shopflow"

Problem: Pool exhaustion — requests hanging indefinitely

Symptoms: After some load, all requests hang. Pool connections never return.

Most likely cause: A transaction client that was never released — client.release() was not called (missing finally block, or called only in the success path).

Diagnostic:

// Monitor pool stats
setInterval(() => {
  console.log({
    total: pool.totalCount, // total connections created
    idle: pool.idleCount, // connections waiting for a query
    waiting: pool.waitingCount, // requests waiting for a connection
  });
}, 5000);

If waiting grows while idle stays at 0 and total is at max, you have a leak.

Fix: Always use try/catch/finally for any code that calls pool.connect():

const client = await pool.connect();
try {
  // ... your queries ...
} finally {
  client.release(); // This MUST run — even on error
}

Check Your Understanding

Quick Quiz

Why do you use Pool instead of Client for web server request handlers?

Show Answer
Pool maintains a set of reusable connections, avoiding the overhead of establishing a new connection per request (50–300ms). It also prevents connection exhaustion — a web server under load would quickly exceed PostgreSQL's connection limit if every request created its own Client. Pool checks out an existing connection, runs the query, and returns it — all efficiently.
What's wrong with this code?
```
const category = req.query.category;
await pool.query(`SELECT * FROM products WHERE category = '${category}'`);
```
Show Answer
This is a SQL injection vulnerability. If category contains SQL code (like '; DROP TABLE products; --), it becomes part of the SQL statement and executes. Always use parameterized queries:
await pool.query("SELECT * FROM products WHERE category = $1", [category]);
What happens if you forget client.release() after a transaction?

Show Answer
The client stays checked out of the pool forever — it's never returned for reuse. Over time (or quickly under load), all pool connections are leaked and unavailable. New requests that need a connection wait indefinitely (up to connectionTimeoutMillis), then fail. The application appears to hang. Always call client.release() in a finally block.

Hands-On Challenge

Task: Write a getUserOrderSummary(userId: number) function using pool.query() that returns an object with:

user: { id, name, email }
orders: array of { id, status, created_at, total, item_count }

Use a JOIN query with aggregation. Add TypeScript types for the result.

Show Solution

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

interface UserRow {
  id: number;
  name: string;
  email: string;
}

interface OrderSummaryRow {
  order_id: number;
  status: string;
  created_at: Date;
  total: string; // NUMERIC comes back as string
  item_count: string; // COUNT comes back as string in pg
}

interface OrderSummary {
  orderId: number;
  status: string;
  createdAt: Date;
  total: number;
  itemCount: number;
}

interface UserOrderSummary {
  user: { id: number; name: string; email: string } | null;
  orders: OrderSummary[];
}

async function getUserOrderSummary(userId: number): Promise<UserOrderSummary> {
  // Fetch user
  const userResult = await pool.query<UserRow>(
    "SELECT id, name, email FROM users WHERE id = $1",
    [userId],
  );

  if (userResult.rows.length === 0) {
    return { user: null, orders: [] };
  }

  // Fetch order summaries
  const ordersResult = await pool.query<OrderSummaryRow>(
    `SELECT
       o.id                                          AS order_id,
       o.status,
       o.created_at,
       COALESCE(SUM(oi.price_at_purchase * oi.quantity), 0) AS total,
       COALESCE(COUNT(oi.id), 0)                    AS item_count
     FROM orders o
     LEFT JOIN order_items oi ON oi.order_id = o.id
     WHERE o.user_id = $1
     GROUP BY o.id, o.status, o.created_at
     ORDER BY o.created_at DESC`,
    [userId],
  );

  return {
    user: userResult.rows[0],
    orders: ordersResult.rows.map((row) => ({
      orderId: row.order_id,
      status: row.status,
      createdAt: row.created_at,
      total: parseFloat(row.total),
      itemCount: parseInt(row.item_count, 10),
    })),
  };
}

Summary: Key Takeaways

Install pg and @types/pg; load credentials from environment variables, never hardcode them
Use Pool for all web server code — it manages connection reuse and prevents exhaustion; use Client only for scripts
pool.query(sql, values) runs a query and automatically manages connection checkout/return
Always use parameterized queries ($1, $2, ...) — never string concatenation with user input
Parameterized queries prevent SQL injection by treating values as data, not SQL
Pass a TypeScript interface to pool.query<YourType>() to get typed rows
NUMERIC columns return as string in pg — parse with parseFloat() when needed
For transactions: pool.connect() → BEGIN → queries → COMMIT/ROLLBACK → client.release() in finally
Monitor pool health with pool.totalCount, pool.idleCount, pool.waitingCount

What's Next?

You can now query PostgreSQL from Node.js safely and efficiently. But writing raw SQL strings in TypeScript has tradeoffs — typos in column names aren't caught until runtime, and building dynamic queries gets verbose fast.

The next step is Query Builders: Knex.js — where you'll use a query builder that generates parameterized SQL from a JavaScript API, adds a migration system for schema versioning, and gives you more structure without hiding the SQL from you.

Quick Reference​

Version Information​

What You Need to Know First​

What We'll Cover in This Article​

What We'll Explain Along the Way​

Part 1: Installing and Configuring​

Environment Variables — Never Hardcode Credentials​

Part 2: Client vs Pool — Use Pool​

Client — One Connection​

Pool — The Right Choice for Web Servers​

Part 3: Running Queries​

Basic Query​

Destructuring the Result​

Part 4: Parameterized Queries — The Only Safe Way​

Parameterized Queries​

Parameterized Writes​

Part 5: TypeScript Types for Query Results​

Handling NUMERIC as Number​

Part 6: Transactions with Pool​

Part 7: Express Integration​

Common Misconceptions​

❌ Misconception: Creating a new Client per request is fine for small apps​

❌ Misconception: Parameterized queries are just a security best practice, not always necessary​

❌ Misconception: pg types match PostgreSQL types directly​

Troubleshooting Common Issues​

Problem: Error: connect ECONNREFUSED 127.0.0.1:5432​

Problem: Error: password authentication failed for user​

Problem: Pool exhaustion — requests hanging indefinitely​

Check Your Understanding​

Quick Quiz​

Hands-On Challenge​

Summary: Key Takeaways​

What's Next?​