The database

tables, relations and enough SQL to read with

What it is

A database is where data survives a restart. You will not have to write SQL yourself, but you do have to read it: the query decides what data the application gets, and a mistake here shows up neither at startup nor in a traceback.

For reading, understanding tables, relations and four kinds of query is enough.

What it looks like in code

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL
);

CREATE TABLE subs (
    id       INTEGER PRIMARY KEY,
    user_id  INTEGER NOT NULL REFERENCES users(id),
    city     TEXT NOT NULL,
    UNIQUE (user_id, city)
);

Line by line:

• PRIMARY KEY — the main key, a unique number for the row.
• REFERENCES users(id) — a relation: user_id may only hold an id that exists in users. The database will not let a subscription be written for a user who doesn't exist.
• NOT NULL — the field is required.
• UNIQUE (user_id, city) — the pair is unique. This is the "you can't subscribe twice" rule, expressed in a way that cannot be worked around from the code.

Four kinds of query

SELECT city FROM subs WHERE user_id = 7;          -- read
INSERT INTO subs (user_id, city) VALUES (7, 'Oslo');  -- add
UPDATE subs SET city = 'Bergen' WHERE id = 3;     -- change
DELETE FROM subs WHERE id = 3;                     -- delete

The thing to watch while reading is WHERE. It decides how many rows a query touches. An UPDATE with no WHERE changes the whole table; a DELETE with no WHERE empties it. That is the most expensive typo in a backend.

JOIN — data from two tables

SELECT u.name, s.city
FROM users u
LEFT JOIN subs s ON s.user_id = u.id;

It reads as: take the users, attach each one's subscriptions, matching on user_id = id.

The difference matters: INNER JOIN quietly loses users with no subscriptions. If a report is missing rows, the kind of JOIN is the first thing to look at.

Parameters, not string concatenation

# not allowed — a security hole
conn.execute(f"SELECT city FROM subs WHERE user_id = {user_id}")

# the right way
conn.execute("SELECT city FROM subs WHERE user_id = ?", (user_id,))

The question mark is a slot for a value. The database substitutes it itself, and whatever arrives from the user stays a value rather than becoming part of the query.

This is the defence against SQL injection: without it the string 7; DROP TABLE subs in an address deletes the table. An AI usually writes parameters correctly, but when building a query out of pieces ("if a filter is set, add a condition") it slides into f-strings easily.

Transactions

with conn:
    conn.execute("INSERT INTO subs ...")
    conn.execute("UPDATE users SET sub_count = sub_count + 1 ...")

Either both lines run or neither does. Needed anywhere several changes must happen together: otherwise an error on the second query leaves the data half-changed.

What to tell the AI

Explain this query step by step: which tables the data comes
from, on what condition they are joined, how many rows come back.

The schema is: <CREATE TABLE>. Write a query: every city with
more than three subscribers, with the subscriber count.
Parameters through placeholders.

This UPDATE has no WHERE. Is that intentional or a mistake?

The last question is worth asking every time you see an UPDATE or a DELETE in a diff.

What to look for

UPDATE or DELETE with no WHERE. Check for it in every diff separately.

An f-string in a query. Injection. No exceptions.

INNER JOIN where LEFT was needed. It loses rows silently.

No NOT NULL and no UNIQUE. The rules have moved into the code and will be bypassed.

SELECT *. It returns every field, including the ones added tomorrow — and they travel out to the client (article 7.3).

Several changes with no transaction. An error halfway through leaves the data inconsistent.

Cheat sheet