Intermediate 17 min read Backend

Database Integration

Backends persist state in databases. This concept covers the driver vs ORM choice, connection pooling, parameterized queries, transactions and migrations — the skills needed to integrate PostgreSQL or MySQL safely and at scale from Node.

SQLPostgreSQLconnection poolparameterized queriestransactionsmigrationsORM

Prerequisites: Node.js Runtime Web Frameworks: Express & Fastify

Introduction

What is this?

Database integration connects application code to a database through drivers (pg, mysql2), query builders (Knex) or ORMs (Prisma, Sequelize, TypeORM). Production-grade integration relies on pooled connections, parameterized queries to prevent SQL injection, transactions for atomic writes, and versioned migrations so schema changes are reviewable and repeatable.

Motivation

Why do you need it?

Parameterized queries keep untrusted input out of the SQL string, eliminating the most common SQL injection vector.
Connection pooling reuses expensive database connections so a burst of requests does not exhaust server connections.
Transactions give atomicity and rollback: a bank transfer either fully commits or fully reverts.
Migrations version the schema alongside the code, so every environment converges to the same structure.

Think about it

Mental model

Imagine

A bank teller window

Think of

A database connection pool

Opening a new window per customer (connection per query) is slow and crowds the bank. A pool keeps a handful of tellers busy and queues the rest, and a transaction is like handing the whole request to one teller who must finish all steps or the bank reverses them.

Mechanics

How it works

A pool (e.g. pg.Pool) opens a small set of connections lazily and hands them out on request; pool.query borrows one, runs, and returns it.
Query parameters are sent to the server separately from the SQL text, so the database treats them as values, never as code.
A transaction begins with BEGIN, runs multiple statements on the same connection, and ends with COMMIT or ROLLBACK.
Migrations are ordered SQL files applied exactly once, recorded in a migrations table so reruns are skipped.
An ORM maps tables to model classes and generates SQL, trading a little performance and control for productivity and type safety.

From HTTP request to database: pool, query, transaction and result.

Route handlerReceives validated request data

Acquire connectionBorrow from the pool or wait

Parameterized SQLValues bound, never concatenated

PostgreSQL executesPlans, runs, returns rows

Rows returnedDriver decodes into JS objects

Release connectionReturned to the pool

Reference

Key commands

npm install pg

Install the node-postgres driver.

added 4 packages in 1s

psql -U postgres -h localhost -d appdb

Open an interactive SQL shell against the database.

psql (16.3)\nType "help" for help.

createdb appdb

Create a new database with the CLI tool.

npx prisma migrate dev --name add_users

Generate and apply a migration from Prisma schema changes.

Applied migration: 20260501123456_add_users

SELECT * FROM users LIMIT 5;

Run a SQL query inside psql to inspect data.

 id | name | email\n----+------+------\n ...

Hands-on

Examples

Querying PostgreSQL with the pg Pool

const { Pool } = require("pg")
 
const pool = new Pool({
  connectionString: process.env.DATABASE_URL, // never hardcode
  max: 10,
  idleTimeoutMillis: 30000,
})
 
// Parameterized query: $1 binds the value server-side
async function getUserByEmail(email) {
  const { rows } = await pool.query(
    "SELECT id, name, email FROM users WHERE email = $1",
    [email]
  )
  return rows[0]
}
 
// Transaction: both inserts must succeed together
async function createUserWithProfile(user, profile) {
  const client = await pool.connect()
  try {
    await client.query("BEGIN")
    const { rows } = await client.query(
      "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
      [user.name, user.email]
    )
    const userId = rows[0].id
    await client.query(
      "INSERT INTO profiles (user_id, bio) VALUES ($1, $2)",
      [userId, profile.bio]
    )
    await client.query("COMMIT")
    return userId
  } catch (err) {
    await client.query("ROLLBACK")
    throw err
  } finally {
    client.release()
  }
}

The pool keeps up to 10 connections open. Every value reaches the database through $N placeholders, so a user-provided email can never alter the SQL. The transaction uses a single dedicated connection so both inserts commit or roll back atomically, and client.release() in finally always returns the connection to the pool even on error.

Real world

In production

User/order/payment records are read and written through pooled, parameterized queries on PostgreSQL or MySQL.
ETL and reporting jobs stream large result sets with pg-query-stream to avoid loading everything into memory.
Prisma/TypeORM give type-safe data access with auto-generated migrations across dev, staging and prod.
Read replicas handle analytics traffic while the primary serves writes, balanced by the pool.

Guidelines

Best practices

Always use parameterized queries or an ORM — never string-concatenate user input into SQL.
Run queries within a request's lifetime and always release pooled connections (finally/release).
Keep transactions short and on a single connection; never await a network call inside a transaction.
Version the schema with migrations and run them in CI before deploy.
Set a pool max and idleTimeout, and surface pool errors with 'error' event listeners.

Watch out

Common mistakes

Building SQL with template strings and user input, creating injection vulnerabilities.
Forgetting client.release() after a transaction, leaking connections until the pool drains.
Holding a transaction open across a slow external API call, locking rows for seconds.
Writing migrations by hand without tracking what was applied, causing drift across environments.

Root cause analysis

Common issues

Error

remaining connection slots are reserved for non-replication superuser connections

Root cause

The database max_connections was exhausted, often by leaked connections.

Fix

Reduce pool max, release clients reliably, and check for unclosed transactions in pg_stat_activity.

Error

relation "users" does not exist

Root cause

The table was not created, or the migration was not applied to this database.

Fix

Run the migrations (npx prisma migrate deploy or the equivalent) against the right database.

Error

timeout exceeded when trying to connect

Root cause

The database is unreachable, restarting, or the pool is saturated.

Fix

Check connectivity (pg_isready), credentials and network rules; raise pool max if bursts spike.

When things break

Debugging tips

Use EXPLAIN ANALYZE on slow queries to see the plan and whether indexes are used.
Enable statement logging (log_statement = 'all') temporarily to see the exact SQL your ORM emits.
Query pg_stat_activity to spot idle-in-transaction connections blocking others.
Check pool exhaustion first when requests start timing out: it is usually leaked connections, not the database.

Interviews

Interview checkpoint

Practice

Hands-on lab

CRUD, pooling and transactions with PostgreSQL

45 min · intermediate

In progress

Objectives

Connect Node to PostgreSQL with the pg driver
Use a connection pool for concurrent queries
Write parameterized CRUD queries
Implement and test a transaction with rollback

CRUD, pooling and transactions with PostgreSQL

45 min · intermediate

0/5 steps

sandbox · Linux sandbox

Try these commands

Test yourself

Quick quiz

0/3

What is the correct way to filter users by a user-supplied email in node-postgres?

A COMMIT inside a transaction makes all statements in the transaction permanent; a ROLLBACK discards them.

You see 'remaining connection slots are reserved' errors after a deploy. What is the most likely cause?

Quick reference

Cheat sheet

node-postgres

pool.query(text, params)Run a parameterized query

pool.connect()Borrow a client for transactions

client.release()Return the client to the pool

BEGIN / COMMIT / ROLLBACKTransaction control

SQL essentials

SELECT ... WHERE col = $1Parameterized read

INSERT ... RETURNING idInsert and get the row

CREATE INDEX ON t (col)Speed up lookups

EXPLAIN ANALYZE SELECT ...Inspect the plan

Go deeper

Official documentation

node-postgres documentation

docs

PostgreSQL — SQL commands

docs

PostgreSQL — Connection pooling

docs

Prisma ORM documentation

docs

Database Integration

What is this?

Why do you need it?

Mental model

How it works

Key commands

Examples

Querying PostgreSQL with the pg Pool

In production

Best practices

Common mistakes

Common issues

Debugging tips

Interview checkpoint

Hands-on lab

CRUD, pooling and transactions with PostgreSQL

Quick quiz

Cheat sheet

Related topics

Official documentation