🐘 Setting up PostgreSQL

Why PostgreSQL?

PostgreSQL — everyone just says "Postgres" — is a free, open-source relational database. Relational means your data lives in tables of rows and columns with enforced relationships between them, and you talk to it in SQL. Postgres has been in active development for over three decades, and it has earned a reputation for correctness: it does exactly what the SQL standard says, and it does not lose your data.

Think of a database as a meticulous librarian. You hand it a precise request ("every order placed by customer 42, newest first") and it returns exactly those records, quickly, no matter how many millions of rows sit on the shelves. PostgreSQL is a particularly capable librarian — it understands advanced queries, complex data types, and even JSON documents.

What makes it a great default

How Node.js & Postgres Fit Together

Node.js is exceptional at waiting well. Its event loop can juggle thousands of in-flight requests, and most of what a web server does is wait — for the network, the filesystem, and the database. A database query is the classic "go do this and tell me when you're done" operation, so it maps perfectly onto Node's asynchronous, async/await style.

Because the whole stack is JavaScript — React on the front end, Node on the back end, and query results arriving as plain JS objects — you stop context-switching between languages. That single-language flow is one of the biggest productivity wins of the full-stack JS approach.

Installing PostgreSQL

Pick one path below based on your machine. Docker is the most reproducible choice if you already have Docker installed, because it never pollutes your system and is trivial to reset.

Windows

1. Download the interactive installer from the official Windows download page (EnterpriseDB build).
2. Run it and follow the wizard. Write down the password you set for the built-in postgres superuser.
3. Keep the defaults: PostgreSQL Server, pgAdmin (a GUI), Command Line Tools, and port 5432.
4. When it finishes, search the Start menu for SQL Shell (psql) to open a terminal into your server.

macOS (Homebrew)

# Install PostgreSQL (version 16 shown; use the current major version)
brew install postgresql@16

# Start it now and on every login
brew services start postgresql@16

# Verify the server answers
psql --version

Prefer a GUI? Postgres.app is a drag-to-Applications alternative — open it and click Initialize.

Linux (Ubuntu / Debian)

sudo apt update
sudo apt install postgresql postgresql-contrib

# Confirm the service is running
sudo systemctl status postgresql
sudo systemctl start postgresql   # if it wasn't already

Docker (reproducible, disposable)

# Run PostgreSQL 16 in a container, published on the standard port
docker run --name my-postgres \
  -e POSTGRES_PASSWORD=change_me_in_real_life \
  -p 5432:5432 \
  -d postgres:16

# Open a psql session inside the container
docker exec -it my-postgres psql -U postgres

Your First Database & User

The postgres superuser is your root key — powerful and dangerous. Best practice is to create an application-specific database and a dedicated user with only the privileges that app needs. That way a leaked app password can't drop every database on the server.

Open a psql shell (via SQL Shell on Windows, or the commands above), then run:

-- 1. Create a database for the app
CREATE DATABASE myapp_development;

-- 2. Create a dedicated, least-privilege user with a real password
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'supersecret_dev_pw';

-- 3. Let that user work inside the database
GRANT ALL PRIVILEGES ON DATABASE myapp_development TO myapp_user;

-- 4. Connect to the new database (psql meta-command)
\c myapp_development

-- 5. In Postgres 15+, also grant rights on the public schema
GRANT ALL ON SCHEMA public TO myapp_user;

Now create a table and a couple of test rows so we have something to query from Node:

-- A tiny table with an auto-incrementing id and a timestamp
CREATE TABLE items (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ  DEFAULT NOW()
);

INSERT INTO items (name) VALUES ('First item'), ('Second item');

SELECT * FROM items;
--  id |    name     |          created_at
-- ----+-------------+-------------------------------
--   1 | First item  | 2026-07-31 10:15:02.11+00
--   2 | Second item | 2026-07-31 10:15:02.11+00

\q  -- exit psql

If you see those two rows, your database is alive and reachable. Time to connect Node.

Credentials in Environment Variables

Hardcoding a database password into your source code is one of the most common — and most costly — security mistakes. Anyone who reads the repo (or your public GitHub) gets your credentials. The fix is to load configuration from the environment at runtime.

Set up a fresh Node project and install the driver plus dotenv, which loads a local .env file into process.env:

mkdir postgres-node-demo && cd postgres-node-demo
npm init -y
npm install pg dotenv express

Create a .env file in the project root:

# .env  — never commit this file
DB_HOST=localhost
DB_PORT=5432
DB_NAME=myapp_development
DB_USER=myapp_user
DB_PASSWORD=supersecret_dev_pw

And a .gitignore so it never reaches version control:

# .gitignore
node_modules/
.env

Connecting from Node.js

The pg package gives you two ways to connect: a single Client, and a Pool that manages many reusable connections. For a web server, always use a Pool — a fresh connection per request is slow and will exhaust the database. (We dig into pooling deeply two lessons from now.)

A reusable database module

Encapsulate the pool in one file and export a thin query helper. Everything else imports this module rather than creating its own connections.

// db.js — the single source of database connections
require('dotenv').config();
const { Pool } = require('pg');

// The Pool reads DB_* from the environment. Never inline the password.
const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
});

// One-time smoke test so a bad config fails loudly at startup
pool.query('SELECT NOW()')
  .then((res) => console.log('✅ Connected to PostgreSQL at', res.rows[0].now))
  .catch((err) => console.error('❌ Database connection failed:', err.message));

module.exports = {
  // Thin wrapper — callers pass SQL text and a params array
  query: (text, params) => pool.query(text, params),
};

An Express endpoint that reads and writes

Notice every value coming from the outside world is passed as a parameter ($1), never glued into the SQL string. That single habit is your primary defense against SQL injection.

// app.js
const express = require('express');
const db = require('./db');

const app = express();
app.use(express.json());

// GET all items
app.get('/api/items', async (req, res) => {
  try {
    const result = await db.query('SELECT * FROM items ORDER BY id ASC');
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// POST a new item — user input flows in through $1, safely
app.post('/api/items', async (req, res) => {
  const { name } = req.body;
  if (!name) return res.status(400).json({ error: 'Name is required' });

  try {
    const result = await db.query(
      'INSERT INTO items (name) VALUES ($1) RETURNING *',
      [name]                       // ← values array, matched to $1
    );
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

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

// ❌ DANGEROUS — a value of "1; DROP TABLE items;" is catastrophic
const bad = `SELECT * FROM items WHERE id = ${req.params.id}`;

// ✅ SAFE — the driver sends the value separately from the SQL
const good = await db.query('SELECT * FROM items WHERE id = $1', [req.params.id]);

Run it

node app.js
# In another terminal:
curl http://localhost:3000/api/items
curl -X POST http://localhost:3000/api/items \
  -H "Content-Type: application/json" \
  -d '{"name":"Bought from the API"}'

✅ Connected to PostgreSQL at 2026-07-31T10:22:41.003Z
Server running on port 3000
[{"id":1,"name":"First item","created_at":"2026-07-31T10:15:02.110Z"}, ...]

When connecting won't work

Practice & Quiz

CREATE DATABASE bootcamp_dev;
CREATE USER bootcamp_user WITH ENCRYPTED PASSWORD 'dev_pw_123';
GRANT ALL PRIVILEGES ON DATABASE bootcamp_dev TO bootcamp_user;
\c bootcamp_dev
GRANT ALL ON SCHEMA public TO bootcamp_user;

CREATE TABLE notes (
    id         SERIAL PRIMARY KEY,
    body       TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO notes (body) VALUES ('My first note');
SELECT * FROM notes;

app.get('/api/items/:id', async (req, res) => {
  try {
    const result = await db.query(
      'SELECT * FROM items WHERE id = $1',
      [req.params.id]
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Item not found' });
    }
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

Best Practices & Pitfalls

✅ Do

• Create a dedicated, least-privilege user per app instead of using postgres
• Keep credentials in environment variables; git-ignore your .env
• Use a Pool for web servers and a thin shared db.js module
• Always use parameterized queries ($1, $2) for any external input
• Fail fast: run a SELECT NOW() at startup so misconfiguration surfaces immediately

❌ Don't

• Hardcode passwords or commit a real .env
• Build SQL by concatenating user input into the string
• Ship your app running as the postgres superuser
• Open a brand-new Client for every request

Summary

📚 Additional Resources

• node-postgres — Connecting
• PostgreSQL docs — Creating a database
• PostgreSQL docs — CREATE ROLE / users & privileges

🚀 What's Next?

Your app can talk to Postgres — now let's make it fluent. The next lesson, Using the pg Library, dives into executing every kind of query (SELECT, INSERT, UPDATE, DELETE), reading the result object, handling transactions, and mapping PostgreSQL types to JavaScript.