So far, everything our programs remember disappears when they stop. A real AI app has to remember a lot: users, conversations, documents, which prompts were sent, what the model answered, how many tokens it cost, and whether the user clicked thumbs up. That's the job of a database, and it's the next step in becoming a real engineer. This post is core.
We'll learn it by doing. Python comes with a small database called SQLite built in, so there's nothing to install, and almost everything we learn carries straight over to Postgres, the database most production AI apps use. It also links back to earlier posts: indexes are the trees from Post 16 and the speedups from Post 18, a good schema looks like the Pydantic models from Post 10, and tests can use a throwaway database like the fixtures from Post 13.
Why not just use files?
You could store everything in a JSON file, or in a Python dictionary that you save now and then. For small things, that works. But it breaks down quickly:
Searching means loading and scanning everything.
Two users at once can overwrite each other's changes.
A crash halfway through writing can leave a corrupted file.
Nothing checks your data. A typo in a field name just silently creates a new field.
A database solves all four. It stores data in an organized way, finds things fast, lets many users work at once, survives crashes, and enforces rules about what's allowed in.
Tables, rows, and keys
Most databases you'll use are relational. Data lives in tables, like spreadsheets with strict rules. Each row is one record, and each column is one kind of fact about it.
Tables connect through keys. A primary key is a column that uniquely identifies each row, usually a number called id. A foreign key is a column that points at a row in another table. Here's the structure for a small chat app:
A user has an
idand an email.A conversation belongs to a user, so it holds a
user_idthat points at a user.A message belongs to a conversation, so it holds a
conversation_id.
Notice that a message doesn't copy the user's email. It points at the conversation, which points at the user. Each fact lives in exactly one place, so if the email changes, you change it once.
The language for talking to relational databases is SQL, which is short for Structured Query Language. It's been around for decades, it's the same idea everywhere, and it's among the most valuable skills you can add.
SQLite: a database with nothing to install
SQLite keeps a whole database in a single file (or entirely in memory), and Python includes it:
import sqlite3 conn = sqlite3.connect(":memory:") # a throwaway database that lives in RAM Use ":memory:" for experiments and tests, or a filename like "app.db" to keep the data on disk. SQLite is a great fit for learning, testing, and small apps. For a real service with many users writing at once, you'd move to Postgres, which we'll cover below.
Designing a schema
A schema is the structure of your tables. Here's one for our chat app:
CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, created_at TEXT NOT NULL ); CREATE TABLE conversations ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), title TEXT, created_at TEXT NOT NULL ); CREATE TABLE messages ( id INTEGER PRIMARY KEY, conversation_id INTEGER NOT NULL REFERENCES conversations(id), role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')), content TEXT NOT NULL, tokens INTEGER, created_at TEXT NOT NULL ); By convention, SQL keywords are written in capitals, though it isn't required. The pieces on each column are constraints, rules the database enforces so bad data can't get in:
PRIMARY KEYmarks the unique identifier.NOT NULLmeans the value is required.UNIQUEmeans no two rows can share the value. Here, no two users with the same email.REFERENCES users(id)makes it a foreign key.CHECK (...)lets you write your own rule. Here, a message's role must be one of three values.
Constraints are the database version of the validation that Pydantic did in Post 10, and they have a big advantage: they protect your data no matter which program writes to it. Let's load some data and watch them work. Here's a connection with a schema, and some rows:
conn = sqlite3.connect(":memory:") conn.execute("PRAGMA foreign_keys = ON") conn.executescript(SCHEMA) # the three CREATE TABLE statements above conn.executemany( "INSERT INTO users (id, email, created_at) VALUES (?, ?, ?)", [ (1, "asha@example.com", "2026-10-01T09:00:00Z"), (2, "ravi@example.com", "2026-10-02T10:30:00Z"), (3, "meera@example.com", "2026-10-03T08:15:00Z"), ], ) (The conversations and messages are inserted the same way. The full runnable file is in the mini project at the end.) Now try to break the rules:
conn.execute("INSERT INTO messages (conversation_id, role, content, created_at) VALUES (1, 'robot', 'x', 'x')") # IntegrityError: CHECK constraint failed: role IN ('user', 'assistant', 'system') conn.execute("INSERT INTO users (email, created_at) VALUES (NULL, 'x')") # IntegrityError: NOT NULL constraint failed: users.email conn.execute("INSERT INTO users (email, created_at) VALUES ('asha@example.com', 'x')") # IntegrityError: UNIQUE constraint failed: users.email Every one of those is rejected, with a clear message.
Two SQLite surprises
SQLite has a couple of quirks that catch people out, and it's better to meet them now.
Foreign keys are off by default. See the PRAGMA foreign_keys = ON line? SQLite doesn't enforce foreign keys unless you turn them on, for each new connection. With it on, inserting a message for a conversation that doesn't exist fails:
IntegrityError: FOREIGN KEY constraint failed With it off, the "orphan" row is accepted quietly. When I tried it, the database happily stored a message pointing at conversation 999, which doesn't exist. Postgres always enforces them, so this is just an SQLite habit to build: turn them on.
Types are loose. SQLite will store text in an INTEGER column without complaint:
conn.execute("CREATE TABLE t (n INTEGER)") conn.execute("INSERT INTO t VALUES ('abc')") conn.execute("INSERT INTO t VALUES ('42')") print(conn.execute("SELECT n, typeof(n) FROM t").fetchall()) # [('abc', 'text'), (42, 'integer')] The '42' was converted to a number, but 'abc' stayed text, in a column declared as an integer. If you want strict types, add the STRICT keyword to the end of the table definition (CREATE TABLE t (n INTEGER) STRICT), and the same insert fails with a clear error. Postgres is strict by default.
Getting data in and out safely
You've seen ? in the code above. Those are placeholders, and they're one of the most important habits in this whole post.
SQL injection
It's tempting to build a query with an f-string, since you know f-strings well by now. Never do this with data that came from outside your program. Here's why:
evil = "nobody' OR '1'='1" rows = conn.execute(f"SELECT email FROM users WHERE email = '{evil}'").fetchall() print(rows) # [('asha@example.com',), ('meera@example.com',), ('ravi@example.com',)] The query that actually ran was:
SELECT email FROM users WHERE email = 'nobody' OR '1'='1' The text we pasted in broke out of the quotes and added its own condition, '1'='1', which is always true. It returned every user. A malicious person can use this trick to read data they shouldn't, change it, or delete it. It's called SQL injection, and it's one of the oldest and most damaging security bugs in software. This matters even more now: if your app lets a model write or influence a query, the model's output is untrusted input too.
The fix is to pass values separately, so the database treats them as data and never as code:
rows = conn.execute("SELECT email FROM users WHERE email = ?", (evil,)).fetchall() print(rows) # [] The database looks for a user whose email is literally nobody' OR '1'='1, finds none, and returns nothing. Always use placeholders. (The symbol varies by database. SQLite uses ?, and the common Postgres library, psycopg, uses %s. The principle is the same.)
Reading rows
By default, rows come back as plain tuples. Setting a row_factory lets you access columns by name, which is much more readable:
conn.row_factory = sqlite3.Row row = conn.execute("SELECT id, email FROM users WHERE id = 1").fetchone() print(row["email"], dict(row)) # asha@example.com {'id': 1, 'email': 'asha@example.com'} Asking better questions
Here's the data we'll query. Three users, three conversations, and eight messages:
| User | Conversation | Messages |
|---|---|---|
| asha | "Python help" | 4 (about generators) |
| asha | "Trip ideas" | 2 |
| ravi | "Resume review" | 2 (one has no token count recorded) |
| meera | (none) |
The basics: WHERE, ORDER BY, LIMIT
SELECT id, role, content FROM messages WHERE conversation_id = 1 AND role = 'user' ORDER BY created_at DESC LIMIT 5; (3, 'user', 'Show an example') (1, 'user', 'How do generators work?') WHERE filters rows, ORDER BY sorts them (DESC for newest first), and LIMIT caps how many come back.
Aggregates and GROUP BY
Functions like COUNT, SUM, AVG, MIN, and MAX summarize rows. GROUP BY does it per group:
SELECT role, COUNT(*) AS n, SUM(tokens) AS total_tokens, AVG(tokens) AS avg_tokens FROM messages GROUP BY role; ('assistant', 4, 24, 8.0) ('user', 4, 20, 5.0) HAVING is like WHERE, but for groups, after they've been counted. Which users have more than one conversation?
SELECT user_id, COUNT(*) AS conversations FROM conversations GROUP BY user_id HAVING COUNT(*) > 1; (1, 2) Joins: combining tables
Because each fact lives in one table, answering real questions means combining tables with a JOIN. "How many tokens has each user used?" needs all three:
SELECT u.email, SUM(m.tokens) AS total_tokens FROM users u JOIN conversations c ON c.user_id = u.id JOIN messages m ON m.conversation_id = c.id GROUP BY u.email ORDER BY total_tokens DESC; ('asha@example.com', 40) ('ravi@example.com', 4) (u, c, and m are short aliases for the table names.) Each ON says how two tables line up.
Notice something. meera is missing. She has no conversations, so a plain JOIN (called an inner join) drops her. If you want to keep every user, even those with nothing to match, use a LEFT JOIN:
SELECT u.email, c.title FROM users u LEFT JOIN conversations c ON c.user_id = u.id; ('asha@example.com', 'Python help') ('asha@example.com', 'Trip ideas') ('ravi@example.com', 'Resume review') ('meera@example.com', None) Meera shows up, with None (SQL's NULL) where the conversation would be. And that gives you a handy trick for finding things that don't have a match, such as users who've never started a chat:
SELECT u.email FROM users u LEFT JOIN conversations c ON c.user_id = u.id WHERE c.id IS NULL; Making queries readable: CTEs
A CTE (common table expression), written with WITH, gives a name to an intermediate result, like a variable. It makes a long query much easier to read:
WITH counts AS ( SELECT conversation_id, COUNT(*) AS n FROM messages GROUP BY conversation_id ) SELECT c.title, counts.n FROM counts JOIN conversations c ON c.id = counts.conversation_id WHERE counts.n >= 2 ORDER BY counts.n DESC; Window functions
Here's a question that comes up constantly: what's the latest message in each conversation? GROUP BY can't easily give you the whole row. A window function can, by numbering the rows within each group:
SELECT conversation_id, role, content FROM ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_at DESC) AS rn FROM messages m ) WHERE rn = 1; (1, 'assistant', 'Here is a small example.') (2, 'assistant', 'Day 1: Alfama and the castle.') (3, 'assistant', 'Lead with your strongest project.') PARTITION BY splits the rows into groups, ORDER BY sorts within each group, and ROW_NUMBER() numbers them, so rn = 1 is the latest one in each group.
NULL is not a value
NULL means "unknown or missing," and it behaves in surprising ways. One of Ravi's messages has no token count, so it's NULL:
SELECT COUNT(*) FROM messages WHERE tokens = NULL; -- 0 (!) SELECT COUNT(*) FROM messages WHERE tokens IS NULL; -- 1 Nothing is ever equal to NULL, not even NULL. You have to ask with IS NULL. Aggregates also skip NULLs:
SELECT COUNT(*), COUNT(tokens), SUM(tokens), AVG(tokens) FROM messages; -- 8, 7, 44, 6.2857... There are 8 messages, but COUNT(tokens) counts only the 7 that have a value, and the average is over those 7. That can quietly skew your numbers. When a column can be NULL, always ask yourself what the missing rows should mean.
Indexes: why queries are fast
Remember Post 18. A search through a list is O(n), and a search through a balanced tree is O(log n). A database faces the same choice. Without help, finding all the messages in one conversation means reading every row in the table: a full table scan, O(n). An index is an extra structure, usually a balanced tree (Post 16), that lets the database jump straight to the matching rows.
You can ask the database how it plans to run a query. In SQLite that's EXPLAIN QUERY PLAN. I filled a messages table with 200,000 rows spread over 2,000 conversations, and ran this:
SELECT id, content FROM messages WHERE conversation_id = 777 ORDER BY created_at; Without an index, the plan is:
SCAN messages USE TEMP B-TREE FOR ORDER BY SCAN means every row gets read. It took about 15 milliseconds per query. Then I created an index:
CREATE INDEX idx_messages_conversation ON messages (conversation_id); SEARCH messages USING INDEX idx_messages_conversation (conversation_id=?) USE TEMP B-TREE FOR ORDER BY SEARCH ... USING INDEX means it jumped straight to the right rows. About 0.11 milliseconds. But there's still a sort step, USE TEMP B-TREE FOR ORDER BY. A composite index over two columns fixes that, because it keeps the rows sorted by the second column inside each value of the first:
CREATE INDEX idx_messages_conv_time ON messages (conversation_id, created_at); SEARCH messages USING INDEX idx_messages_conv_time (conversation_id=?) Now the sort is gone, because the index already delivers rows in order. About 0.09 milliseconds, which is roughly 170 times faster than the scan, on a table that's still small by real standards. (Your numbers will differ.)
Rules of thumb for indexes
Index the columns you filter, join, and sort by. Foreign key columns like
conversation_idare classic candidates.Primary keys and
UNIQUEcolumns are indexed automatically.Column order matters in a composite index. An index on
(conversation_id, created_at)helps queries that filter onconversation_id, or on both. It doesn't help a query that filters only oncreated_at.Indexes aren't free. Every insert, update, and delete has to update every index too. I inserted 200,000 rows into a three-column table with no indexes, and then with three indexes. The indexed one took about 4 times longer (0.67 seconds versus 0.16). They also take space. So don't index everything. Index what your real queries need.
Wrapping a column in a function can defeat the index. Look at what happens with email lookups. This uses the index:
SELECT id FROM users WHERE email = 'User5@Example.com' -> SEARCH users USING COVERING INDEX idx_users_email (email=?) But if you make the search case-insensitive with lower(), the database can no longer use the index on email:
SELECT id FROM users WHERE lower(email) = 'user5@example.com' -> SCAN users The fix is an expression index, which indexes the lowercased value itself. After CREATE INDEX idx_users_email_lower ON users (lower(email)), the same query shows SEARCH users USING INDEX idx_users_email_lower.
- Searching inside text can't use a normal index. A pattern like
LIKE '%word%'has to look at every row. For real text search, databases have dedicated full-text search features.
EXPLAIN is the database's version of the profiler from Post 15. When a query is slow, look at the plan first, instead of guessing.
Transactions: all or nothing
Imagine saving a chat turn: you store the user's message, call the model, then store the model's reply. What if the model call fails after the first insert? You'd have a question with no answer sitting in your database.
A transaction groups several statements so that either all of them take effect, or none do. In Python's sqlite3, the connection itself works as a context manager (Post 8): it commits if the block finishes normally and rolls back if an exception escapes.
def save_exchange(conn, conversation_id, question, answer_func): with conn: # commit on success, roll back on any error conn.execute( "INSERT INTO messages (conversation_id, role, content) VALUES (?, 'user', ?)", (conversation_id, question), ) answer = answer_func(question) conn.execute( "INSERT INTO messages (conversation_id, role, content) VALUES (?, 'assistant', ?)", (conversation_id, answer), ) When I ran it once successfully, there were 2 messages in the table. Then I ran it with a model function that raises an error. The error propagated, and the count was still 2. The user message, which had already been inserted inside the block, was rolled back along with everything else.
(Note that with conn: doesn't close the connection. It only ends the transaction.)
A warning, though. There's a design trap hiding in this example. Don't hold a transaction open while you wait on something slow, such as a model call that takes 30 seconds. An open transaction can hold locks that block other users. A better pattern is to call the model first, then open a transaction that just saves the results, which is quick. The add_exchange method in the mini project does exactly that.
ACID
You'll hear transactions described with the word ACID:
Atomic: all or nothing, as above.
Consistent: the rules (constraints) always hold. A transaction that would break one is rejected.
Isolated: transactions running at the same time don't see each other's half-finished work.
Durable: once committed, the data survives a crash or power cut.
That's a lot of safety you'd have to build yourself with files.
Concurrency and lost updates
Isolation matters when two things happen at once. Here's a bug that catches people. Imagine a counter, and two workers that each read it, add one, and write it back:
a = conn.execute("SELECT n FROM counter").fetchone()[0] # worker A reads 0 b = conn.execute("SELECT n FROM counter").fetchone()[0] # worker B reads 0 conn.execute("UPDATE counter SET n = ?", (a + 1,)) # A writes 1 conn.execute("UPDATE counter SET n = ?", (b + 1,)) # B writes 1 (A's update is lost!) Two increments happened, but the counter says 1. This "read, compute in Python, write back" pattern is a race condition, just like the thread problem in Post 14. The fix is to let the database do the arithmetic in one atomic statement:
UPDATE counter SET n = n + 1; Run that twice, and you get 2. This is the right way to handle things like usage quotas. Say each user has some credits:
Related posts
Why Linear Agent Chains Break in Production Banking (And How We Built Deterministic State Graphs with LangGraph + Redis)
95% mutation score, and a review still found a cross-tenant leak: Fuzzphony 0.11
Bitnami 映像搬家後：漏改 exporter 的坑，以及用原映像加上 pgvector
How to Shadow-Test a New AI Model Before Switching Production Traffic
You Blocked the Database Port in the Firewall and Docker Opened It Anyway
