Intermediate 17 min

Databases and Authentication

Persist data, hash passwords and protect routes with JWT.

What you will learn

Connect a database
Hash passwords
Verify a JWT in middleware

An in-memory array disappears when the server restarts. Real APIs store data in a database and identify users. This lesson shows the standard building blocks: a database connection, password hashing and token-based authentication.

Connecting to a database

PostgreSQL with the pg package is a solid default. Always use parameterized queries: values are sent separately from the SQL so user input can never change the query's meaning (this prevents SQL injection).

npm install pg

import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

app.get("/tasks/:id", async (req, res, next) => {
  try {
    const { rows } = await pool.query("SELECT * FROM tasks WHERE id = $1", [req.params.id]);
    if (!rows.length) return res.status(404).json({ error: "Not found" });
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
});

// NEVER build SQL by string concatenation:
// pool.query("SELECT * FROM tasks WHERE id = " + req.params.id)   // injectable!

If you prefer an ORM, look at Prisma or Drizzle, which give you typed models and migrations.

Hashing passwords

Never store passwords in plain text. Store a slow, salted hash so a leaked database does not reveal them. bcrypt (or argon2) is designed for exactly this.

import bcrypt from "bcrypt";

app.post("/register", async (req, res, next) => {
  try {
    const { email, password } = req.body;
    if (!email || !password || password.length < 8)
      return res.status(400).json({ error: "email and 8+ char password required" });
    const hash = await bcrypt.hash(password, 12);
    await pool.query("INSERT INTO users (email, password_hash) VALUES ($1, $2)", [email, hash]);
    res.status(201).json({ ok: true });
  } catch (err) { next(err); }
});

Logging in with a JWT

After verifying the password, issue a JSON Web Token: a signed string containing the user id. The client sends it back in the Authorization header on later requests, and the server verifies the signature without a database lookup.

import jwt from "jsonwebtoken";

app.post("/login", async (req, res, next) => {
  try {
    const { email, password } = req.body;
    const { rows } = await pool.query("SELECT * FROM users WHERE email = $1", [email]);
    const user = rows[0];
    const ok = user && (await bcrypt.compare(password, user.password_hash));
    if (!ok) return res.status(401).json({ error: "Invalid credentials" });
    const token = jwt.sign({ sub: user.id }, process.env.JWT_SECRET, { expiresIn: "1h" });
    res.json({ token });
  } catch (err) { next(err); }
});

function auth(req, res, next) {
  const header = req.get("authorization") || "";
  const token = header.startsWith("Bearer ") ? header.slice(7) : null;
  if (!token) return res.status(401).json({ error: "Missing token" });
  try {
    req.userId = jwt.verify(token, process.env.JWT_SECRET).sub;
    next();
  } catch {
    res.status(401).json({ error: "Invalid or expired token" });
  }
}

app.get("/me", auth, async (req, res) => {
  const { rows } = await pool.query("SELECT id, email FROM users WHERE id = $1", [req.userId]);
  res.json(rows[0]);
});

Security checklist

Keep secrets in environment variables, always use HTTPS, add helmet and rate limiting (express-rate-limit) on login, return the same message for wrong email and wrong password, and never log passwords or tokens.

Try it yourself

Write an authorizeOwner middleware that loads a task and returns 403 unless task.user_id equals req.userId.

Show solution

async function authorizeOwner(req, res, next) {
  const { rows } = await pool.query("SELECT user_id FROM tasks WHERE id = $1", [req.params.id]);
  if (!rows.length) return res.status(404).json({ error: "Not found" });
  if (rows[0].user_id !== req.userId) return res.status(403).json({ error: "Forbidden" });
  next();
}