RTE logo
← Back to FastAPI
Lesson 5.1 · Databases

Databases and ORMs in 15 Minutes

Before SQLModel, see what a database really does. Create a to-do table, add, read, change and delete rows with plain SQL in Python’s built-in sqlite3 - then see the two classic mistakes (no commit, SQL built with an f-string) and what an ORM writes for you.

Beginner20 min

What you will be able to do

  • Explain tables, rows, columns and primary keys
  • Write the four basic SQL commands
  • Know why commit matters
  • See SQL injection happen, and stop it with placeholders
  • Explain what an ORM does for you

The idea, in plain English

Until now our APIs kept data in a Python dict. Restart the server and it is gone. A database keeps data on disk, lets many requests read and write safely at the same time, and finds rows fast.

A relational database stores data in tables: columns (title, done) and rows (one per to-do). You talk to it in SQL, a small language with four main commands - INSERT, SELECT, UPDATE, DELETE. An ORM (object-relational mapper) lets you use Python classes and objects instead, and writes the SQL for you.

This short lesson uses SQLite - a whole database in one file, built into Python - so there is nothing to install. We write SQL by hand once, so you know what the ORM in the next lessons does behind the scenes. Python sqlite3 (SQLite 3.38), sqlite3 CLI 3.51, SQLModel 0.0.48.

Worked example: The to-do list from Module 1 as a real table in a file - first with SQL by hand, then the same with an ORM.

workflowFrom Python to the filestep 1 / 3
React Flow

1 - SQL by hand

You write each command. Rows come back as tuples, and done is 0 or 1.

SELECT
(2, 'Pay rent', 0)
missing row
None

The same request, by hand and with an ORM. Real results from our to-do table.

Words you will see in this module

Module 5 is about databases. These words come up in every lesson.

Small dictionary
TableData with fixed columns, like a spreadsheet sheet.
RowOne record in a table - one to-do.
ColumnOne field with a type - title TEXT.
Primary keyA column that is unique for every row, usually id.
SQLThe language you use to talk to a relational database.
CommitMake the changes permanent. Until then, other connections do not see them.
ORMA library that maps classes to tables and objects to rows, and writes the SQL.

An everyday example: a school register

A class register is a table:

  • The column headings - roll number, name, present - are the columns.
  • Each student is one row.
  • The roll number is the primary key: no two students share it.
  • Writing in pencil first and in ink at the end of the day is like a commit.
  • A school office that fills in the register for you, from what you tell it, is the ORM.

Example 1 - SQL by hand

sql_basics.py does all four jobs (CRUD) on one table:

  • sqlite3.connect("todos.db") opens the file (and creates it the first time).
  • CREATE TABLE describes the columns. id INTEGER PRIMARY KEY gets a new number for each row by itself.
  • INSERT adds rows. The ? are placeholders - the values go in a separate tuple.
  • conn.commit() makes the changes permanent.
  • SELECT reads rows - all of them, with WHERE as a filter, or one by id.
  • UPDATE and DELETE change rows; cur.rowcount says how many.
  • NOT NULL is a rule inside the database: a todo without a title was refused.
Example 1 - sql_basics.py
import sqlite3 conn = sqlite3.connect("todos.db") # a database is one file here # 1. Create a table: the columns and their types conn.execute(""" CREATE TABLE todo ( id INTEGER PRIMARY KEY, -- a unique number for each row title TEXT NOT NULL, done BOOLEAN NOT NULL DEFAULT 0 ) """) # 2. Insert rows (C of CRUD) - the ? are filled in safely conn.execute("INSERT INTO todo (title) VALUES (?)", ("Buy milk",)) conn.execute("INSERT INTO todo (title) VALUES (?)", ("Pay rent",)) conn.execute("INSERT INTO todo (title, done) VALUES (?, ?)", ("Call mom", True)) conn.commit() # make it permanent # 3. Read rows (R) print("all: ", conn.execute("SELECT id, title, done FROM todo").fetchall()) print("not done:", conn.execute("SELECT id, title FROM todo WHERE done = 0").fetchall()) print("one: ", conn.execute("SELECT * FROM todo WHERE id = ?", (2,)).fetchone()) print("missing: ", conn.execute("SELECT * FROM todo WHERE id = ?", (99,)).fetchone()) # 4. Update (U) and delete (D) cur = conn.execute("UPDATE todo SET done = 1 WHERE id = ?", (1,)) print("updated rows:", cur.rowcount) cur = conn.execute("DELETE FROM todo WHERE id = ?", (3,)) print("deleted rows:", cur.rowcount) conn.commit() print("after: ", conn.execute("SELECT * FROM todo").fetchall()) # 5. The database checks rules too try: conn.execute("INSERT INTO todo (title) VALUES (NULL)") except sqlite3.IntegrityError as e: print("refused: ", e) conn.close()
Output
all: [(1, 'Buy milk', 0), (2, 'Pay rent', 0), (3, 'Call mom', 1)] not done: [(1, 'Buy milk'), (2, 'Pay rent')] one: (2, 'Pay rent', 0) missing: None updated rows: 1 deleted rows: 1 after: [(1, 'Buy milk', 1), (2, 'Pay rent', 0)] refused: NOT NULL constraint failed: todo.title
Output - sqlite3 -header -column todos.db "SELECT * FROM todo;"
id title done -- -------- ---- 1 Buy milk 1 2 Pay rent 0

Reading the output

  • Rows come back as tuples: (1, 'Buy milk', 0). You must remember which position is which.
  • done came back as 0 or 1 - SQLite has no real true/false type.
  • A missing row is None, not an error - your API must turn it into 404.
  • rowcount 1 for the update and the delete; 0 would mean "no such row".
  • The sqlite3 command-line tool shows the same file - the data really is on disk.

Mistake 1: forgetting commit

  • We inserted "Never saved" and the same connection saw 3 rows.
  • We closed the connection without commit, opened it again: 2 rows. The row was gone, with no error.
  • Until commit, changes are only "in progress" for that connection.

Mistake 2: SQL from an f-string

find_unsafe puts the title straight into the SQL text. A normal title worked. Then we sent x' OR '1'='1:

  • The SQL became WHERE title = 'x' OR '1'='1' - true for every row. It returned ALL the todos.
  • This is SQL injection. With other input it can read other tables or delete data.
  • find_safe uses ? - the database treats the whole input as one value. Result: [] (no todo has that strange title).
  • Rule: never build SQL with f-strings or +. An ORM uses placeholders for you.
traps.py
import sqlite3 # Trap 1: forgetting commit conn = sqlite3.connect("todos.db") conn.execute("INSERT INTO todo (title) VALUES (?)", ("Never saved",)) print("same connection sees it:", conn.execute("SELECT COUNT(*) FROM todo").fetchone()[0], "rows") conn.close() # closed without commit conn = sqlite3.connect("todos.db") print("after reopening: ", conn.execute("SELECT COUNT(*) FROM todo").fetchone()[0], "rows") # Trap 2: building SQL with an f-string def find_unsafe(title: str): return conn.execute(f"SELECT * FROM todo WHERE title = '{title}'").fetchall() def find_safe(title: str): return conn.execute("SELECT * FROM todo WHERE title = ?", (title,)).fetchall() attack = "x' OR '1'='1" print("unsafe, normal: ", find_unsafe("Pay rent")) print("unsafe, attack: ", find_unsafe(attack)) print("safe, attack: ", find_safe(attack))
Output
same connection sees it: 3 rows after reopening: 2 rows unsafe, normal: [(2, 'Pay rent', 0)] unsafe, attack: [(1, 'Buy milk', 1), (2, 'Pay rent', 0)] safe, attack: []

Example 2 - the same with an ORM

orm_preview.py does it with SQLModel - the ORM we use in this module. Do not worry about the details; Lesson 5.2 explains each line. echo=True prints every SQL command:

  • class Todo(SQLModel, table=True) became CREATE TABLE todo (...).
  • session.add(Todo(title="Buy milk")) became INSERT INTO todo (title, done) VALUES (?, ?) - with placeholders.
  • select(Todo).where(Todo.done == False) became SELECT ... WHERE todo.done = 0.
  • The result was a Todo object with done=False - a real bool, not 0.
  • The last ROLLBACK is normal: the session closed after a read with nothing to keep.
Example 2 - orm_preview.py
from sqlmodel import Field, Session, SQLModel, create_engine, select class Todo(SQLModel, table=True): # a Python class = a table id: int | None = Field(default=None, primary_key=True) title: str done: bool = False engine = create_engine("sqlite:///orm.db", echo=True) # echo=True prints the SQL SQLModel.metadata.create_all(engine) with Session(engine) as session: session.add(Todo(title="Buy milk")) session.add(Todo(title="Call mom", done=True)) session.commit() open_todos = session.exec(select(Todo).where(Todo.done == False)).all() # noqa: E712 print("RESULT:", open_todos) print("TYPE: ", type(open_todos[0]).__name__, "- done is", repr(open_todos[0].done))
Output (time stamps removed from the log lines)
BEGIN (implicit) PRAGMA main.table_info("todo") [raw sql] () PRAGMA temp.table_info("todo") [raw sql] () CREATE TABLE todo ( id INTEGER NOT NULL, title VARCHAR NOT NULL, done BOOLEAN NOT NULL, PRIMARY KEY (id) ) [no key 0.00003s] () COMMIT BEGIN (implicit) INSERT INTO todo (title, done) VALUES (?, ?) RETURNING id [generated in 0.00005s (insertmanyvalues) 1/2 (ordered; batch not supported)] ('Buy milk', 0) INSERT INTO todo (title, done) VALUES (?, ?) RETURNING id [insertmanyvalues 2/2 (ordered; batch not supported)] ('Call mom', 1) COMMIT BEGIN (implicit) SELECT todo.id, todo.title, todo.done FROM todo WHERE todo.done = 0 [generated in 0.00006s] () RESULT: [Todo(done=False, id=1, title='Buy milk')] TYPE: Todo - done is False ROLLBACK

Why an ORM - and what it does not do

  • You work with classes and objects, and your editor knows the fields.
  • Values are converted both ways (bool, datetime) and SQL always uses placeholders.
  • The same code runs on SQLite today and PostgreSQL tomorrow (mostly).
  • It does NOT make you forget SQL: when something is slow or wrong, echo=True and the SQL are how you find out.
  • SQLite is perfect for learning and small apps. For many users at once, use PostgreSQL (Module 10).

SQL at a glance

Create

Add a row.

INSERT INTO todo (title) VALUES (?)
Read

Find rows.

SELECT * FROM todo WHERE done = 0
Update

Change rows.

UPDATE todo SET done = 1 WHERE id = ?
Delete

Remove rows.

DELETE FROM todo WHERE id = ?
Keep it

Make it permanent.

conn.commit()
Safe values

Never f-strings.

conn.execute("... WHERE id = ?", (todo_id,))

Try it yourself

The code does not change. Swap the content string and the program does something else entirely.

Use the sqlite3 tool

“Run sqlite3 todos.db and type .tables, .schema todo, then SELECT * FROM todo; - then .quit.”

Count and sort

“Try SELECT COUNT(*) FROM todo; and SELECT * FROM todo ORDER BY title DESC;”

Another attack

“What does find_unsafe do with the title x'; DROP TABLE todo; -- ? (sqlite3's execute refuses two commands - why is that only luck?)”

Echo more

“In orm_preview.py, add a session.delete(...) and commit. Which SQL does echo show?”

What usually goes wrong

Forgetting commit

The row was there in the same connection and gone after reopening.

Building SQL with f-strings

x' OR '1'='1 returned every row.

✗ conn.execute(f"SELECT * FROM todo WHERE title = '{title}'")
✓ conn.execute("SELECT * FROM todo WHERE title = ?", (title,))
Treating None as an error

fetchone() returns None for a missing row - check it and answer 404.

Practice

Write these yourself before opening anything. Getting them wrong first is most of how this sticks.

1.

With plain sqlite3, add a due TEXT column to a new table todo2, insert three to-dos with due dates like "2026-10-20", and write a function overdue(today: str) that returns the titles with due < today and done = 0 - using placeholders. Then print the rows with the sqlite3 tool.

Show hint

ISO dates as text sort correctly, so due < ? works. Remember conn.commit().

Key points

  • A table has columns with types and rows of data; the primary key identifies each row.
  • INSERT, SELECT, UPDATE and DELETE are create, read, update and delete.
  • Without commit, changes are lost.
  • Always pass values with placeholders - never build SQL from strings.
  • An ORM maps classes to tables, writes the SQL, and converts values.
  • echo=True shows the SQL an ORM sends.

Quick check before you move on

What did SQLite return for done?
0 or 1 - not True or False.
How many rows were there after reopening without commit?
2 - the uncommitted row was lost.
What did find_unsafe return for x' OR '1'='1?
Every row in the table.
Which SQL did session.add(Todo(...)) produce?
INSERT INTO todo (title, done) VALUES (?, ?) RETURNING id.

Interview questions

What is SQL injection and how do you prevent it?

Untrusted input changing the meaning of a SQL statement. Prevent it with parameterised queries (placeholders) or an ORM/query builder, never string formatting; add least-privilege database users as defence in depth.

What are the trade-offs of an ORM?

Faster development, type mapping, portability and safe parameters, versus hidden queries (N+1 problems), less control over complex SQL and an extra abstraction to learn. Keep SQL visible with logging and drop to SQL when needed.

Quiz

  1. 1.

    Why is the ? placeholder safe?

  2. 2.

    What does a missing row look like with fetchone()?

  3. 3.

    Why learn SQL if the ORM writes it?

Comments

Sign in to leave a comment. Your name and photo come from Google; nothing else is shared.

Loading comments...