Lesson 9 of 108 min read

Databases for the Web

Share:WhatsAppLinkedIn

What you'll build

By the end of this lesson you will have a Postgres database with a users and a todos table, a Prisma schema that describes them, and a small script that creates, reads, updates, and deletes records. You will understand why parameterised queries are non-negotiable, and you will have connected the database to the Express API from lesson 8.

Concepts

Relational vs document databases

The two dominant categories of databases you will encounter as a web developer:

Relational databases (SQL), PostgreSQL, MySQL, SQLite. Data is stored in tables with named columns and typed values. Rows in different tables are linked via foreign keys. The database enforces the schema, you cannot store a string in a column declared as an integer.

users table
┌────┬──────────────┬──────────────────────┐
│ id │ name         │ email                │
├────┼──────────────┼──────────────────────┤
│  1 │ Priya Menon  │ priya@example.com    │
│  2 │ Rahul Gupta  │ rahul@example.com    │
└────┴──────────────┴──────────────────────┘

todos table
┌────┬──────────────────────┬──────────┬─────────┐
│ id │ text                 │ done     │ user_id │
├────┼──────────────────────┼──────────┼─────────┤
│  1 │ Learn Express        │ false    │       1 │
│  2 │ Set up Postgres      │ false    │       1 │
│  3 │ Write tests          │ false    │       2 │
└────┴──────────────────────┴──────────┴─────────┘

Document databases (NoSQL), MongoDB, Firestore. Data is stored as JSON-like documents in collections. No fixed schema, each document in a collection can have different fields. Great for data that varies a lot between records, but joins between collections are expensive and often have to be done in application code.

For most web apps, especially those with related entities like users, posts, comments, and orders, a relational database is the better default. Postgres is free, open-source, battle-tested, and handles millions of rows comfortably on a small server.

SQL basics

You do not need to memorise every SQL keyword, but you do need to understand the four core operations (CREATE, SELECT, INSERT, UPDATE, DELETE) because any ORM generates these under the hood.

-- Create a table
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE todos (
  id      SERIAL PRIMARY KEY,
  text    TEXT NOT NULL,
  done    BOOLEAN NOT NULL DEFAULT FALSE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Insert a row
INSERT INTO users (name, email) VALUES ('Priya Menon', 'priya@example.com');

-- Select with a filter
SELECT id, text, done FROM todos WHERE user_id = 1 AND done = false;

-- Update
UPDATE todos SET done = true WHERE id = 1;

-- Delete
DELETE FROM todos WHERE id = 3;

-- Join, get all todos with the owner's name
SELECT todos.id, todos.text, users.name AS owner
FROM todos
JOIN users ON todos.user_id = users.id;

SERIAL PRIMARY KEY creates an auto-incrementing integer column. REFERENCES users(id) is a foreign key, Postgres will refuse to insert a todo whose user_id does not exist in the users table.

Parameterised queries, the only safe way

Never concatenate user input into a SQL string. This is an SQL injection vulnerability: an attacker can end your query early and run arbitrary SQL.

// DANGEROUS, never do this
const unsafe = `SELECT * FROM users WHERE email = '${userInput}'`;
// If userInput is: admin@x.com' OR '1'='1
// The query becomes: SELECT * FROM users WHERE email = 'admin@x.com' OR '1'='1'
// That returns every row in the table

// SAFE, parameterised query (using node-postgres)
const { rows } = await db.query(
  "SELECT * FROM users WHERE email = $1",
  [userInput]  // the library escapes this safely
);

Every database library in every language has a parameterised query API. Use it without exception, even for inputs you think are safe.

Prisma ORM

An ORM (Object-Relational Mapper) lets you write JavaScript to query the database instead of raw SQL. Prisma is the most widely used ORM in the Node ecosystem. You define your schema in a schema.prisma file, and Prisma generates a type-safe client.

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
  todos     Todo[]
}

model Todo {
  id        Int      @id @default(autoincrement())
  text      String
  done      Boolean  @default(false)
  createdAt DateTime @default(now())
  userId    Int
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

After defining the schema, you run migrations and generate the client:

npx prisma migrate dev --name init   # creates the table in the database
npx prisma generate                  # generates the TypeScript/JS client

Then query with plain JavaScript:

const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();

// Create a user
const user = await prisma.user.create({
  data: { name: "Priya Menon", email: "priya@example.com" }
});

// Find all incomplete todos for a user
const todos = await prisma.todo.findMany({
  where: { userId: user.id, done: false },
  orderBy: { createdAt: "desc" }
});

// Update a todo
await prisma.todo.update({
  where: { id: 1 },
  data: { done: true }
});

// Delete a todo
await prisma.todo.delete({ where: { id: 1 } });

Prisma generates queries like SELECT * FROM todos WHERE "userId" = $1 AND done = $2, always parameterised, never vulnerable to injection.

Hands-on

Add a database to the Express API from lesson 8. You need a running Postgres instance. If you do not have one locally, the fastest option is docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres (requires Docker Desktop) or a free Neon database at neon.tech.

Step 1: Install Prisma.

cd todo-api   # the Express project from lesson 8
npm install @prisma/client
npm install prisma --save-dev
npx prisma init

This creates prisma/schema.prisma and a .env file. Open .env and set:

DATABASE_URL="postgresql://postgres:secret@localhost:5432/todoapp"

Step 2: Define the schema. Replace prisma/schema.prisma with the schema shown in the Concepts section above.

Step 3: Run the migration.

npx prisma migrate dev --name init

This creates the tables in Postgres. Run npx prisma studio to open a browser-based GUI where you can view and edit data, useful for debugging.

Step 4: Replace the in-memory array in server.js.

const express = require("express");
const cors = require("cors");
const { PrismaClient } = require("@prisma/client");

const app = express();
const prisma = new PrismaClient();

app.use(cors());
app.use(express.json());

// GET /todos, for now, return all todos (in production, filter by logged-in user)
app.get("/todos", async (req, res) => {
  try {
    const todos = await prisma.todo.findMany({ orderBy: { createdAt: "desc" } });
    res.json(todos);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// POST /todos
app.post("/todos", async (req, res) => {
  const { text, userId } = req.body;
  if (!text || !userId) {
    return res.status(400).json({ error: "text and userId are required" });
  }
  try {
    const todo = await prisma.todo.create({ data: { text, userId: Number(userId) } });
    res.status(201).json(todo);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// PATCH /todos/:id
app.patch("/todos/:id", async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const { text, done } = req.body;
  try {
    const todo = await prisma.todo.update({
      where: { id },
      data: { ...(text !== undefined && { text }), ...(done !== undefined && { done: Boolean(done) }) }
    });
    res.json(todo);
  } catch (e) {
    if (e.code === "P2025") return res.status(404).json({ error: "Not found" });
    res.status(500).json({ error: e.message });
  }
});

// DELETE /todos/:id
app.delete("/todos/:id", async (req, res) => {
  const id = parseInt(req.params.id, 10);
  try {
    await prisma.todo.delete({ where: { id } });
    res.status(204).send();
  } catch (e) {
    if (e.code === "P2025") return res.status(404).json({ error: "Not found" });
    res.status(500).json({ error: e.message });
  }
});

app.listen(3001, () => console.log("API on http://localhost:3001"));

Start the server and run the same curl commands from lesson 8. Everything behaves identically, but now the data persists in Postgres across server restarts.

Common pitfalls

  • Storing the database password in your code. Never hard-code credentials. Put them in environment variables (.env file locally, server environment variables in production). Add .env to .gitignore immediately.
  • Not using parameterised queries. Even if your app is not public yet, building the injection-safe habit from the start means you never accidentally ship a vulnerability. Every Prisma query is parameterised automatically.
  • Forgetting to handle Prisma's P2025 error. This code means "record not found". If you do not catch it, an update or delete on a missing record will crash your server with a 500 instead of returning a clean 404.
  • Running migrations in production without a backup. Migrations can drop columns. Always back up your database before running prisma migrate deploy in production.
  • Not closing the Prisma client. In scripts (not servers), call await prisma.$disconnect() at the end, or Node will hang waiting for the connection pool to close.

What to try next

  1. Add a User model to the Express API. Create POST /users to create a user, and change GET /todos to accept a userId query parameter and return only that user's todos.
  2. Write a raw SQL query using Prisma's $queryRaw method. Try SELECT COUNT(*) FROM todos WHERE done = false and log the result. This teaches you that Prisma and raw SQL co-exist, use Prisma for most queries and raw SQL when you need something Prisma cannot express.
  3. Install the Prisma VS Code extension. It adds syntax highlighting and auto-formatting to .prisma files, and shows you the generated SQL for each Prisma query in the Output panel.

Test Your Knowledge

Take a quick quiz on this lesson

Start Quiz →

Prefer watching over reading?

Subscribe for free.

Subscribe on YouTube