1. Connecting Express to PostgreSQL โ€” the pg library

Express doesn't talk to databases on its own. You need a driver โ€” a library that knows how to speak the PostgreSQL protocol. The standard choice for Node.js is node-postgres, published on npm as pg.

terminal Install dependencies

That installs two things: pg (the PostgreSQL driver) and dotenv (for loading environment variables from a .env file โ€” more on that shortly).

๐Ÿ”Œ

Client

A single connection to PostgreSQL. Good for one-off scripts. You open it, run queries, then close it manually. Not suitable for a web server that handles many requests simultaneously.

๐ŸŠ

Pool

A managed group of connections. When a request arrives, Pool grabs an available connection, runs the query, and returns it. No manual open/close. This is what you use in Express.

Rule of thumb: Always use Pool in web applications. A Pool efficiently handles concurrent requests without exhausting database connections. Client is for scripts and migrations.

2. Connection String & Config

To connect to PostgreSQL, you need five pieces of information: host, port, database name, username, and password. You can pass these as individual options or as a single connection string.

db/pool.js Two equivalent ways to configure
Connection string anatomy
postgresql://[user]:[password]@[host]:[port]/[database] โ”‚ โ”‚ โ”‚ โ”‚ โ”‚ urbancart_user supersecret localhost 5432 urbancart

Both forms work identically. Connection strings are convenient for cloud databases (Heroku, Supabase, Railway) that give you a single URL. Individual fields are easier to read and override per-environment.

๐Ÿ“š

For deep SQL knowledge โ€” CREATE TABLE, indexes, joins, aggregates โ€” check the Databases & SQL track. This lesson focuses on the Node.js integration layer.

3. Environment Variables

Never hardcode database credentials in your source code. If you commit a password to GitHub, it's public โ€” even if you delete it later (Git history remembers). The correct approach: store secrets in environment variables and load them at runtime.

.env Environment variables file โ€” NEVER commit this
Critical: Add .env to your .gitignore immediately. Run echo ".env" >> .gitignore when you create the file. Share a .env.example with dummy values so teammates know which variables are needed.
.gitignore Always ignore .env
db/pool.js Load .env and use process.env
Startup output (success)
Database connected at 2026-04-18T09:14:32.441Z

4. Your First Query โ€” pool.query()

pool.query() runs SQL against your database and returns a Promise. Use async/await to handle it cleanly. The resolved value is a result object โ€” the rows you care about live in result.rows.

routes/products.js GET /api/products โ€” fetch all products with pagination
GET /api/products?page=1&limit=3 โ†’ response
{ "data": [ { "id": 1, "name": "Wireless Headphones", "price": "49.99", "category": "Electronics", "stock": 124 }, { "id": 2, "name": "USB-C Hub", "price": "29.99", "category": "Electronics", "stock": 87 }, { "id": 3, "name": "Laptop Stand", "price": "39.99", "category": "Accessories", "stock": 56 } ], "page": 1, "limit": 3, "count": 3 }

The result object from pool.query() has several useful properties:

  • result.rows โ€” array of row objects, each key is a column name
  • result.rowCount โ€” number of rows returned (or affected for INSERT/UPDATE/DELETE)
  • result.fields โ€” metadata about the columns

5. Parameterized Queries

What happens if a user's input goes directly into your SQL string? Consider this dangerous pattern:

DANGEROUS โ€” never do this SQL injection vulnerability

The fix is parameterized queries. Instead of interpolating values into the SQL string, you use $1, $2, ... placeholders and pass the values as a separate array. PostgreSQL handles the escaping โ€” no injection possible.

routes/products.js GET /api/products/:id โ€” parameterized $1
GET /api/products/2 โ†’ response
{ "id": 2, "name": "USB-C Hub", "price": "29.99", "category": "Electronics", "stock": 87, "description": "7-in-1: HDMI, USB 3.0 x2, SD card, microSD, PD charging 100W", "created_at": "2026-01-15T08:30:00.000Z" }
๐Ÿ”ข

$1, $2, $3 ... placeholders

Each placeholder maps to the value at that index in the values array. $1 โ†’ first value, $2 โ†’ second, and so on. Positional, not named.

๐Ÿ›ก๏ธ

Why it's safe

PostgreSQL receives the SQL text and the values as separate packets. The values are never parsed as SQL โ€” no matter what the user sends, it's treated as plain data.

6. Connection Pooling

Opening a database connection is expensive โ€” it involves a TCP handshake, authentication, and session setup. For a web server handling hundreds of requests per second, opening a fresh connection per request would be disastrously slow.

How Pool works
HTTP Request
arrives at Express
โ†’
Pool grabs idle connection
from pre-established set
โ†’
Query runs
result returned
โ†’
Connection returned
ready for next request
db/pool.js Pool configuration options
Pool configuration explained
max: 10 โ†’ Keep up to 10 connections open simultaneously. โ†’ If all 10 are busy, new queries queue up until one is free. โ†’ PostgreSQL's default max_connections is 100 โ€” don't exceed it across all pools. idleTimeoutMillis: 30000 โ†’ A connection unused for 30 seconds is closed to save resources. connectionTimeoutMillis: 2000 โ†’ If the pool can't provide a connection in 2s, reject with an error (instead of queueing indefinitely).
Production tip: On serverless platforms (Vercel, AWS Lambda), each function instance creates its own pool. Use PgBouncer or Supabase's connection pooler to avoid overwhelming PostgreSQL's max_connections limit.

7. UrbanCart Schema

Before writing CRUD routes, you need tables. Here's the core UrbanCart schema โ€” three tables that power the entire store.

db/schema.sql UrbanCart โ€” products, users, orders
Run schema in psql
psql -U urbancart_user -d urbancart -f db/schema.sql CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 4
๐Ÿ›’
UrbanCart Domain
Why these four tables?

products is the catalogue. users stores accounts with hashed passwords (never plain text โ€” that's covered in Authentication & Authorization). orders records a purchase by a user with a total and status. order_items is the join table โ€” each order contains one or more products at the price they were purchased at (so future price changes don't alter order history).

8. CRUD with a Real Database

Now the full set of Express routes wired to PostgreSQL. These follow REST conventions from the previous lesson and use parameterized queries throughout.

POST /api/products โ€” INSERT with RETURNING *

routes/products.js Create a new product
POST /api/products โ†’ 201 Created
{ "id": 5, "name": "Ergonomic Mouse", "description": "Vertical grip, 6 buttons, USB receiver", "price": "34.99", "category": "Accessories", "stock": 200, "image_url": null, "created_at": "2026-04-18T09:22:11.004Z" }
RETURNING * tells PostgreSQL to send back the inserted row โ€” including the auto-generated id and created_at timestamp. Without it, a successful INSERT returns nothing and you'd have to run a second SELECT.

PATCH /api/products/:id โ€” UPDATE with parameterized values

routes/products.js Update price and stock
PATCH /api/products/2 body: {"price": 24.99} โ†’ response
{ "id": 2, "name": "USB-C Hub", "price": "24.99", "category": "Electronics", "stock": 87, "created_at": "2026-01-15T08:30:00.000Z" }
COALESCE($1, price) means: use the new value if provided, otherwise keep the existing column value. This makes PATCH truly partial โ€” you only need to send the fields you want to change.

DELETE /api/products/:id

routes/products.js Delete a product
DELETE /api/products/5 โ†’ response
{ "message": "Product deleted", "deleted": { "id": 5, "name": "Ergonomic Mouse" } }

Wiring routes in app.js

app.js Mount the products router
Server startup
Database connected at 2026-04-18T09:14:32.441Z UrbanCart API running on http://localhost:3000
๐Ÿงช
Testing your routes
Use curl or Postman to test all five endpoints

Test the full CRUD cycle:

  • GET /api/products โ€” list products (try ?page=1&limit=5)
  • GET /api/products/1 โ€” fetch Wireless Headphones
  • POST /api/products โ€” create a new product with a JSON body
  • PATCH /api/products/1 โ€” update price: {"price": 44.99}
  • DELETE /api/products/5 โ€” remove the product you just created
๐Ÿ“š

Want to go deeper? JOINs across orders and order_items, GROUP BY for revenue reports, indexes for fast lookups โ€” all of that is in the Databases & SQL track, which covers 34 lessons on SQL using the same UrbanCart-style domain.

Quiz

Question 1 of 5
Why should you use parameterized queries instead of string concatenation?
Question 2 of 5
What does pool.query() return in node-postgres?
Question 3 of 5
Where should database credentials be stored?
Question 4 of 5
What is connection pooling?
Question 5 of 5
What does RETURNING * do in a PostgreSQL INSERT?
0
/ 5
Great job!

You completed the Database Integration quiz.

Next Lesson

Authentication & Authorization โ†’
Your database is live. Now add JWT-based login so only the right users can touch UrbanCart's data.
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

Your Express API now talks to a real database.
Let's talk about your real career.

Join the Best AI Programs โ€” three 5-month courses, fully online, with placement assistance. Week 1 is free.

Explore our AI-era courses โ†’