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.
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.
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.
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 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.
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.
The result object from pool.query() has several useful properties:
result.rowsโ array of row objects, each key is a column nameresult.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:
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.
$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.
arrives at Express
from pre-established set
result returned
ready for next request
7. UrbanCart Schema
Before writing CRUD routes, you need tables. Here's the core UrbanCart schema โ three tables that power the entire store.
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 *
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
DELETE /api/products/:id
Wiring routes in app.js
Test the full CRUD cycle:
GET /api/productsโ list products (try?page=1&limit=5)GET /api/products/1โ fetch Wireless HeadphonesPOST /api/productsโ create a new product with a JSON bodyPATCH /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
pool.query() return in node-postgres?RETURNING * do in a PostgreSQL INSERT?You completed the Database Integration quiz.
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 โ