SQL and PostgreSQL for backend developers

What you will do: run PostgreSQL on your own computer, create the three tables behind Brew & Bean (menu items, orders and order lines), fill them with data, and write the queries a backend needs: filtering, joining, totals, updates, transactions and indexes. At the end you will call the database safely from code.

Prerequisites: *What a backend does* and *Designing REST APIs*. No SQL knowledge is needed. You need Docker Desktop (easiest), or you can install PostgreSQL from postgresql.org.

Why a database? In the API lessons orders lived in a list in memory and vanished when the server restarted. A database stores data on disk, lets many requests share it safely and answers questions about it quickly.

A relational database stores data in tables. A table has columns (the fields and their types) and rows (the records). Tables are linked using keys:

- A primary key uniquely identifies a row (orders.id). - A foreign key in one table points at the primary key of another (order_items.order_id points to orders.id). The database refuses rows that point at nothing.

We use SQL (Structured Query Language) to ask the database to create, read, change and delete rows. PostgreSQL (often called Postgres) is a free, open-source relational database used by many companies, and the one this track uses.

Why a separate order_items table? An order has several items. Storing them in one text column would make totals, reports and updates painful. One row per line is the standard relational answer: it is a one-to-many relationship (one order, many lines).

docker run --name bb-postgres -e POSTGRES_PASSWORD=devpass -p 5432:5432 -d postgres:16
docker exec -it bb-postgres psql -U postgres -c "CREATE DATABASE brewbean;"
docker exec -it bb-postgres psql -U postgres -d brewbean

The password devpass is for local practice only. The -p 5432:5432 option makes the database reachable from your computer on the default port. Stop and start it later with docker stop bb-postgres and docker start bb-postgres.

CREATE TABLE menu_items (
    id        TEXT PRIMARY KEY,
    name      TEXT NOT NULL,
    category  TEXT NOT NULL,
    price     INTEGER NOT NULL CHECK (price > 0)
);

CREATE TABLE orders (
    id             INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name  TEXT NOT NULL,
    phone          TEXT NOT NULL,
    status         TEXT NOT NULL DEFAULT 'received'
                   CHECK (status IN ('received', 'preparing', 'ready', 'picked_up', 'cancelled')),
    pickup_time    TIMESTAMPTZ,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_id    INTEGER NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    item_id     TEXT NOT NULL REFERENCES menu_items (id),
    qty         INTEGER NOT NULL CHECK (qty > 0),
    unit_price  INTEGER NOT NULL,
    PRIMARY KEY (order_id, item_id)
);

The CHECK constraints and NOT NULL make the database itself refuse bad data (a price of 0, an unknown status). GENERATED ALWAYS AS IDENTITY auto-numbers the id. TIMESTAMPTZ stores a moment in time with its time zone handled for you; keep timestamps in this type.

INSERT INTO menu_items (id, name, category, price) VALUES
    ('espresso',      'Espresso',             'coffee', 120),
    ('cappuccino',    'Cappuccino',           'coffee', 180),
    ('cold-brew',     'Cold Brew',            'coffee', 220),
    ('filter-coffee', 'Filter Coffee',        'coffee',  80),
    ('masala-chai',   'Masala Chai',          'tea',     90),
    ('croissant',     'Butter Croissant',     'bakery', 150),
    ('banana-bread',  'Banana Bread',         'bakery', 130),
    ('veg-sandwich',  'Veg Grilled Sandwich', 'food',   160);

INSERT INTO orders (customer_name, phone) VALUES
    ('Asha',  '9876543210'),
    ('Ravi',  '9123456780'),
    ('Meera', '9988776655');

INSERT INTO order_items (order_id, item_id, qty, unit_price) VALUES
    (1, 'cappuccino',   1, 180),
    (1, 'croissant',    2, 150),
    (2, 'espresso',     2, 120),
    (2, 'banana-bread', 1, 130),
    (3, 'masala-chai',  3,  90),
    (3, 'croissant',    1, 150);

Run this on the fresh tables only: it assumes the three orders get ids 1, 2 and 3.

A JOIN puts related rows side by side. ON says how they match. An INNER JOIN (just JOIN) keeps only rows that match on both sides; a LEFT JOIN keeps every row from the left table and fills in NULL where nothing matches.

SELECT o.id AS order_id, o.customer_name, mi.name, oi.qty,
       oi.qty * oi.unit_price AS line_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN menu_items mi  ON mi.id = oi.item_id
ORDER BY o.id, mi.name;

o, oi and mi are short aliases so you can write o.id and avoid ambiguity.

GROUP BY collapses rows into groups, and aggregate functions (SUM, COUNT, AVG, MIN, MAX) calculate one value per group. This is how the server computes an order's total.

Order 1 is 180 + 2 x 150 = 480, matching the API lessons. WHERE filters rows before grouping; HAVING filters groups after it:

UPDATE orders SET status = 'preparing' WHERE id = 1 RETURNING id, status;

DELETE FROM order_items WHERE order_id = 3 AND item_id = 'croissant';

RETURNING (a Postgres feature) hands back the changed row, so your backend does not need a second query.

A transaction groups several statements so they either all happen or none do. Placing an order needs two inserts (the order and its lines). If the server crashed between them, you would have an order with no items. Transactions prevent that.

BEGIN;

WITH new_order AS (
    INSERT INTO orders (customer_name, phone)
    VALUES ('Kiran', '9000000001')
    RETURNING id
)
INSERT INTO order_items (order_id, item_id, qty, unit_price)
SELECT new_order.id, mi.id, 2, mi.price
FROM new_order, menu_items mi
WHERE mi.id = 'cold-brew';

COMMIT;

COMMIT makes it permanent. ROLLBACK undoes everything since BEGIN. The WITH ... RETURNING part passes the new order's id to the second insert. Note it also takes the price from menu_items, never from the client.

BEGIN;
DELETE FROM order_items;
SELECT count(*) FROM order_items;
ROLLBACK;
SELECT count(*) FROM order_items;

The first count is 0 inside the transaction; after ROLLBACK the rows are back.

Transactions give four guarantees, often remembered as ACID: Atomic (all or nothing), Consistent (constraints always hold), Isolated (concurrent transactions do not see each other's half-finished work) and Durable (committed data survives a crash). Interviewers ask about ACID often.

Without an index, finding rows with WHERE status = 'ready' means reading the entire table (a sequential scan). An index is a sorted lookup structure, like the index at the back of a book, that lets the database jump to matching rows.

CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_order_items_item_id ON order_items (item_id);

EXPLAIN SELECT * FROM orders WHERE status = 'ready';

Primary keys and unique constraints get an index automatically. Postgres does not index foreign key columns for you, so add one when you often join or filter on them (order_items.order_id is already covered by the start of its primary key).

With tiny tables Postgres prefers a sequential scan because it is cheaper, so do not be surprised. Indexes pay off on thousands of rows and more. They are not free: each one slows writes and uses disk, so add them for the queries you actually run, not on every column.

import psycopg

DB_URL = "postgresql://postgres:devpass@localhost:5432/brewbean"

def menu_by_category(category: str):
    with psycopg.connect(DB_URL) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT id, name, price FROM menu_items WHERE category = %s ORDER BY price",
                (category,),
            )
            return cur.fetchall()

print(menu_by_category("coffee"))

%s is a placeholder, and the value goes in the tuple as a separate argument. Prints a list of tuples such as [('filter-coffee', 'Filter Coffee', 80), ...].

import pg from "pg";

const pool = new pg.Pool({
  connectionString: "postgresql://postgres:devpass@localhost:5432/brewbean",
});

const { rows } = await pool.query(
  "SELECT id, name, price FROM menu_items WHERE category = $1 ORDER BY price",
  ["coffee"]
);
console.log(rows);
await pool.end();

Run with node db-test.mjs. In pg, placeholders are $1, $2 and the values go in an array. The .mjs extension lets you use import and top-level await.

In real projects you rarely write all SQL by hand: many teams use an ORM (next lesson, *ORMs and migrations*), but you still need to read SQL, because ORMs generate it. Common interview questions: the difference between INNER and LEFT JOIN; WHERE versus HAVING; primary versus foreign key; what an index is and its cost; what ACID means; how to prevent SQL injection; find the second highest price or the top 3 items per category (these use ORDER BY ... LIMIT ... OFFSET ... and window functions). Practise writing queries on paper as well as in a database.

Explain this PostgreSQL query step by step in plain English, as if to a student who knows basic programming. Tell me in which order the database evaluates the clauses, what the result looks like for my schema, and one way it could be wrong or slow.

Schema:
---
[paste your CREATE TABLE statements]
---

Query:
---
[paste query]
---

What to expect: A good answer walks through FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT and ORDER BY. Run the query yourself and compare.