Databases and SQL in one lesson

The support bot has been live on Crumb’s website for two weeks, and Arjun is proud of one small feature: it remembers every conversation. He stores them in a single file, conversations.json. Every time a message arrives, his code reads the whole file, adds the new message, and writes the whole file back.

On Tuesday, three things go wrong at once.

First, Kavya complains that the bot “forgot” her order number halfway through a chat. Arjun checks the file: her second message is simply not there. Second, the file has grown to 80 MB, and reading and rewriting it now takes about six seconds per message, so customers stare at a spinning dot. Third, Meera walks over with a question for her store managers’ meeting: “How many allergy questions did we get this month, per store? And which customers asked more than once?”

Arjun opens the file, scrolls, and starts writing a 40-line Python script to answer her. Halfway through, he realises he will need a different 40-line script for her next question, and the one after that.

Priya reads the code over his shoulder. “You have built a database,” she says. “A very slow one, that loses data, that can only answer questions you write a program for. Let us use a real one.”

How the JSON file lost Kavya’s message

Before the fix, Priya makes Arjun understand the bug, because it is a classic. Here is his saving code:

import json

def save_message(customer, text):
    with open("conversations.json") as f:
        data = json.load(f)                # 1. read the whole file
    data.append({"customer": customer, "text": text})
    with open("conversations.json", "w") as f:
        json.dump(data, f)                 # 2. write the whole file back

Now picture two messages arriving within the same second, from Kavya and from Rohan, handled by two copies of this code running at once:

1. Kavya’s request reads the file: 10,000 messages.
2. Rohan’s request reads the file: the same 10,000 messages.
3. Kavya’s request writes back 10,001 messages, including hers.
4. Rohan’s request writes back its 10,001 messages, including his, but not hers, because it read the file before she was added.

Kavya’s message is gone. Nothing crashed, nothing was logged. This is called a lost update, and any “read everything, change it, write everything” scheme suffers from it the moment two things happen at once. The six-second delay has the same root: to add one small message, the code reads and rewrites 80 MB.

“So what does a database do differently?” Arjun asks.

The ledger and the clerk

Priya uses the bakery’s back office. “Before computers, how did Crumb keep track of orders?”

“Meera had ledgers. One for customers, one for orders. Every page had the same columns: date, customer, items, amount.”

“And when she wanted to know how many Diwali hampers Baner sold?”

“She asked Mrs. Joshi, the bookkeeper, and Mrs. Joshi ran her finger down the ‘item’ column and the ‘store’ column and counted.”

“Your JSON file is a shoebox of receipts. Everything is in there, but every question means tipping out the whole box. A database is the ledgers and Mrs. Joshi. The ledgers are tables with fixed columns. Mrs. Joshi is the database engine: you ask her a precise question in a standard language, and she finds the answer, quickly, without you knowing how she organises her pages. And when two people hand her new entries at the same moment, she writes them one at a time, so nothing is lost.”

That language is SQL (Structured Query Language, pronounced “sequel” or “S-Q-L”). It has been the standard way to talk to relational databases for decades, and it is one of the most valuable skills in software.

Tables, rows and keys

Here is how Crumb’s conversations look as tables. Two tables, because customers and conversations are different kinds of thing:

customers

conversations

The words:

• A table holds one kind of thing. Each row is one item; each column is one property, with a fixed type (text, integer, date).
• A primary key (id) is a column whose value is unique for every row, so each row can be pointed to unambiguously. Two customers can both be called Kavya; they cannot both be customer 1.
• A foreign key (customer_id) is a column that points to a primary key in another table. Conversation 4 belongs to customer 1, Kavya. Instead of copying Kavya’s name and area into every conversation, we store them once and point to them. When she moves to Aundh, you change one row.

flowchart LR
    C["customers: id, name, area"] -- "one customer has many" --> V["conversations: id, customer_id, store, topic, question, created_at"]

Splitting data into tables like this, so each fact lives in exactly one place, is called normalisation. You do not need the theory yet; you need the habit: one table per kind of thing, linked by keys.

Following Meera’s question through SQL

Meera’s first question was: how many allergy questions per store? In SQL:

SELECT store, COUNT(*)
FROM conversations
WHERE topic = 'allergy'
GROUP BY store;

SQL is declarative: you describe the result you want, not the steps to compute it. But it helps to picture the steps the engine takes, using the five rows above:

1. FROM conversations: start with all 5 rows.
2. WHERE topic = 'allergy': keep only matching rows. Rows 1, 3 and 5 survive.
3. GROUP BY store: put survivors in piles by store. Baner: rows 1 and 3. Wakad: row 5.
4. SELECT store, COUNT(*): for each pile, output the store and the number of rows in it.

Result:

Four lines replaced Arjun’s 40-line script, and the next question is another four lines, not another script. And with an index on the topic column (a lookup structure, like the index at the back of a book), the engine does not even read rows it does not need, so the same query stays fast at five million rows.

Her second question, “who asked?”, needs data from both tables: names live in customers, questions in conversations. A JOIN combines rows from two tables wherever the keys match:

SELECT customers.name, customers.area, conversations.question
FROM conversations
JOIN customers ON customers.id = conversations.customer_id
WHERE conversations.topic = 'allergy';

For each allergy conversation, the engine finds the customer whose id equals the conversation’s customer_id, and glues the two rows together. Row 3 has customer_id 3, which matches Ananya. So the output pairs “Ananya (Aundh)” with the plum cake question.

The real code: SQLite from Python

You do not need to install anything to start. SQLite is a complete SQL database that lives in a single file and comes built into Python as the sqlite3 module. Work in your crumb-lab project from the setup lab.

Step 1: create the tables. Save as db_setup.py:

import sqlite3

conn = sqlite3.connect("crumb.db")  # creates the file if it does not exist

conn.executescript("""
CREATE TABLE IF NOT EXISTS customers (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    area  TEXT
);

CREATE TABLE IF NOT EXISTS conversations (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    store       TEXT NOT NULL,
    topic       TEXT NOT NULL,
    question    TEXT NOT NULL,
    created_at  TEXT NOT NULL
);
""")
print("Tables created")

sqlite3.connect opens (or creates) the database file. CREATE TABLE defines a table: each column’s name and type, plus rules like NOT NULL (must have a value) and PRIMARY KEY. IF NOT EXISTS makes the script safe to run twice. Run uv run db_setup.py; you should see Tables created and a new file, crumb.db.

Step 2: insert rows. Save as db_insert.py:

import sqlite3

conn = sqlite3.connect("crumb.db")

customers = [
    (1, "Kavya", "Baner"),
    (2, "Rohan", "Wakad"),
    (3, "Ananya", "Aundh"),
]
conversations = [
    (1, "Baner", "allergy", "Is the Diwali hamper nut-free?", "2026-11-02"),
    (2, "Wakad", "delivery", "Where is my order CR-20977?", "2026-11-02"),
    (3, "Baner", "allergy", "Does the plum cake contain egg?", "2026-11-03"),
    (1, "Baner", "refund", "My cake arrived squashed.", "2026-11-03"),
    (2, "Wakad", "allergy", "Any gluten in the kaju katli?", "2026-11-04"),
]

with conn:  # commits if everything succeeds, rolls back if anything fails
    conn.executemany(
        "INSERT INTO customers (id, name, area) VALUES (?, ?, ?)", customers)
    conn.executemany(
        "INSERT INTO conversations (customer_id, store, topic, question, created_at) "
        "VALUES (?, ?, ?, ?, ?)", conversations)

print("Rows inserted")

Two new ideas. The ? marks are placeholders: you pass the values separately and the database inserts them safely (you will see why this matters in a moment). And with conn: wraps the inserts in a transaction: either all of them are saved (committed), or, if any fails, none are. No half-written state. Run it; you should see Rows inserted. We did not give conversations an id; SQLite fills in 1 to 5 automatically for an INTEGER PRIMARY KEY.

Step 3: ask questions. Save as db_query.py:

import sqlite3

conn = sqlite3.connect("crumb.db")

# 1. Read with a filter
rows = conn.execute(
    "SELECT id, store, question FROM conversations WHERE topic = ? ORDER BY created_at",
    ("allergy",),
).fetchall()
for row in rows:
    print(row)

# 2. Count per store: Meera's question
print()
for store, n in conn.execute(
    "SELECT store, COUNT(*) FROM conversations WHERE topic = 'allergy' GROUP BY store"
):
    print(store, n)

# 3. JOIN: who asked what?
print()
query = """
SELECT customers.name, customers.area, conversations.question
FROM conversations
JOIN customers ON customers.id = conversations.customer_id
WHERE conversations.topic = 'allergy'
"""
for name, area, question in conn.execute(query):
    print(f"{name} ({area}): {question}")

You should see:

(1, 'Baner', 'Is the Diwali hamper nut-free?')
(3, 'Baner', 'Does the plum cake contain egg?')
(5, 'Wakad', 'Any gluten in the kaju katli?')

Baner 2
Wakad 1

Kavya (Baner): Is the Diwali hamper nut-free?
Ananya (Aundh): Does the plum cake contain egg?
Rohan (Wakad): Any gluten in the kaju katli?

Each row comes back as a Python tuple. ORDER BY sorts; fetchall() collects every result row into a list. The other everyday commands follow the same shape: UPDATE customers SET area = ? WHERE id = ? changes rows, and DELETE FROM conversations WHERE id = ? removes them.

Where it breaks

Building SQL with f-strings: SQL injection. On Thursday, Arjun writes a “find customer” helper the quick way:

name = input("Customer name: ")
rows = conn.execute(f"SELECT id, name, area FROM customers WHERE name = '{name}'").fetchall()

A tester types x' OR '1'='1 as the name. The f-string glues it into the SQL, producing WHERE name = 'x' OR '1'='1', and since '1'='1' is always true, the query returns every customer: [(1, 'Kavya', 'Baner'), (2, 'Rohan', 'Wakad'), (3, 'Ananya', 'Aundh')]. With other inputs, an attacker could change or delete data. This is SQL injection, one of the oldest and most damaging security bugs on the web. The same query with a placeholder, conn.execute("SELECT id, name, area FROM customers WHERE name = ?", (name,)), returns [], because the input is treated as a plain value, never as SQL. Always use placeholders for values. This matters even more in AI apps: in Module 06 a model will choose the arguments to your functions, and in Module 24 you will see attackers try to make it choose malicious ones.

UPDATE or DELETE without WHERE. DELETE FROM conversations with no WHERE deletes every row. Write the WHERE first, and run the same condition as a SELECT to see what it matches before changing anything.

Forgetting to commit. Changes made outside a with conn: block (or without conn.commit()) can vanish when the program ends. Use the with block.

SQLite’s limits. SQLite is excellent for learning, prototypes, tests and small apps, and it is used inside phones and browsers everywhere. But it is one file on one machine and allows only one writer at a time. When the support bot runs on several servers handling many customers, you want a database server. The usual choice is PostgreSQL (“Postgres”): free, open source, extremely reliable, and it speaks almost the same SQL. You can run it in one command with the Docker skills from the last lesson (docker run -d -e POSTGRES_PASSWORD=dev -p 5432:5432 -v pgdata:/var/lib/postgresql/data postgres:16) and connect from Python with a library such as psycopg. The SQL you learned here transfers almost unchanged.

Why AI apps still need a boring database

Arjun asks the obvious question. “In the words lesson we had a vector database. Why do I need this kind as well?”

“Because most of what an AI app knows is not meaning, it is facts,” Priya says. “The model writes the reply. The database knows who the customer is.” Here is what a normal database holds in a real AI product:

• Users and accounts: who is talking, their preferences, their permissions.
• Conversation history: language model APIs are stateless, so your app must store past messages and send them again with each request (Module 02). That is exactly the table you just built.
• Business data the model looks up through tools: orders, delivery slots, stock. When the bot calls get_order("CR-20931") in Module 06, the function behind it runs a SQL query.
• Usage and cost logs: tokens and rupees per request, per customer, per feature, so you know what the AI is costing you (Modules 21 and 22).
• Feedback and evaluation results: thumbs up or down, reviewer corrections, test scores over time (Module 23).

And the two kinds of database are converging: the pgvector extension lets Postgres store and search embeddings too, so many teams keep their vectors in the same database as everything else (Module 08). For now, the lesson is simple: a vector database finds things by meaning; a relational database keeps the facts straight. Real apps need both.

Try it yourself

Using the crumb.db you built:

1. Write a query listing all conversations from customer 1 (Kavya), newest first.
2. Write a query that answers Meera’s other question: which customers have more than one conversation? (Hint: GROUP BY and a filter on the count, which uses HAVING instead of WHERE.)
3. Add a new conversation from Ananya about a refund, using placeholders, then rerun query 2.

import sqlite3

conn = sqlite3.connect("crumb.db")

# 1. Kavya's conversations, newest first
for row in conn.execute(
    "SELECT created_at, topic, question FROM conversations "
    "WHERE customer_id = ? ORDER BY created_at DESC", (1,)
):
    print(row)

# 2. Customers with more than one conversation
query = """
SELECT customers.name, COUNT(*) AS n
FROM conversations
JOIN customers ON customers.id = conversations.customer_id
GROUP BY customers.name
HAVING COUNT(*) > 1
"""
print(conn.execute(query).fetchall())

# 3. A new conversation, safely
with conn:
    conn.execute(
        "INSERT INTO conversations (customer_id, store, topic, question, created_at) "
        "VALUES (?, ?, ?, ?, ?)",
        (3, "Baner", "refund", "Can I get a refund for a late cake?", "2026-11-05"),
    )
print(conn.execute(query).fetchall())

Common mistakes

• Using a JSON or CSV file as a database for anything with concurrent users. Lost updates are silent. Use SQLite at minimum.
• Putting values into SQL with f-strings. Always use placeholders (? in SQLite, %s in psycopg). No exceptions, even for “internal” tools.
• Storing the same fact in many places. Keep the customer’s name in customers, and point to it. Copies drift apart.
• Running UPDATE or DELETE before SELECT-ing the same WHERE. Look before you change.
• Thinking the vector database replaces this one. It finds similar text. It does not keep orders, users or money straight.

Recap

• A relational database stores data in tables (rows and columns), linked by primary and foreign keys, and keeps it safe under concurrent writes.
• SQL asks for exactly the rows you want: SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY, plus INSERT, UPDATE, DELETE and JOIN.
• SQLite is built into Python and perfect for learning and small apps; Postgres is the usual choice for real multi-user apps.
• Always use placeholders and transactions: placeholders prevent SQL injection, transactions prevent half-written changes.
• AI apps need a normal database for users, conversation history, business data behind tools, cost logs and evaluation results.

On Friday, Meera has her numbers in the meeting, and a new one she did not ask for: Baner gets twice as many allergy questions as any other store, probably because it is the only one selling the Diwali hamper with cashews. She decides to print allergen cards for the counter.

“So what was wrong with the file?” she asks Arjun afterwards.

“It was a shoebox of receipts,” he says. “Every message meant tipping out the whole box and refilling it, and if two people did it at once, one of them lost their receipt. Kavya’s message was the lost one. Now it is a proper ledger with a bookkeeper: each message is one new line, nothing gets overwritten, and your questions are four-line queries instead of programs.”

“And the AI?”

“The AI writes the replies. The ledger remembers who said what.”

Priya closes her laptop. “That is your toolkit: the terminal, git, HTTP, Docker and SQL. Every one of them will come back. Next module, we sharpen the main tool of all: Python itself.”

Go deeper

• Python’s sqlite3 documentation: the module used above, including placeholders and transactions.
• SQLite documentation: the SQL that SQLite understands.
• PostgreSQL documentation: the tutorial section is an excellent, free SQL course.
• pgvector: vector search inside Postgres, which you will use in Module 08.