Lesson 2 - Models and Tables

Welcome to Models and Tables#

In Lesson 1 you connected your Task Manager to a real database: an engine pointed at a SQLite file, the tables created on startup, and a session handed to each request. But we glossed over the most important part — the shape of the data itself. This lesson is about design: what a row in your task table looks like, how each row gets a unique identity, and why the class that becomes a table should not be the same class your clients send and receive.

That last point is the one that separates a tidy API from a fragile one. By the end you’ll have a small, deliberate set of models — one for the table, and a couple of lightweight schemas for input — that make the CRUD code in the next lessons both shorter and safer.

By the end of this lesson, you will be able to:

• Define a database table by writing a SQLModel subclass with table=True
• Give each row a primary key that the database fills in automatically
• Separate your table model from the create and update schemas clients use
• Create the table on disk with SQLModel.metadata.create_all(engine)

You’ll build directly on the Task model and engine from Lesson 1. Let’s design the data.

A Table Model: One Class, One Table#

A table model is just a SQLModel subclass with one extra ingredient: the argument table=True. That single flag tells SQLModel “this class is not only a Pydantic model for validation — it’s a real database table, and each instance is a row.” Here is the Task Manager’s table, with the fields a task needs:

from sqlmodel import SQLModel, Field

class Task(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    title: str
    done: bool = False

Every typed attribute becomes a column. title is a required text column, done is a boolean column that defaults to False, and id is the table’s identity column (more on it in the next section). The class name, lowercased, becomes the table name — so this defines a table called task.

You can confirm exactly what SQLModel built by asking SQLite for the table’s definition after creating it. The columns line up one-to-one with the fields above:

CREATE TABLE task (
	title VARCHAR NOT NULL,
	done BOOLEAN NOT NULL,
	id INTEGER NOT NULL,
	PRIMARY KEY (id)
)

title maps to VARCHAR (text), done to BOOLEAN, and id to an INTEGER marked as the PRIMARY KEY. You wrote ordinary Python type hints; SQLModel translated them into a SQL table definition for you.

The Primary Key: Identity the Database Assigns#

Every row needs a way to be uniquely identified — so you can ask for “task 7” or update exactly one task without touching the others. That unique identifier is the primary key. In our model it’s the id field:

id: int | None = Field(default=None, primary_key=True)

Read this carefully, because it packs in three ideas:

• primary_key=True marks this column as the table’s primary key. For an integer primary key, SQLite (like most databases) will auto-assign the next value when you insert a row — you don’t pick the id yourself.
• default=None means the field starts out empty. A brand-new Task you create in Python has no id yet, because no row exists in the database to be assigned one.
• int | None is the type that makes both states honest: the id is None before the row is saved, and an int after.

So the id has a lifecycle. It is None while the object lives only in memory, and the database fills it in the moment you save. Let’s watch that happen. First, a fresh Task before it touches the database:

t = Task(title="Write the report")
print("id before insert:", t.id)
print("done default:    ", t.done)

id before insert: None
done default:     False

The id is None — it hasn’t been assigned yet — and done picked up its default of False. Now we save the object inside a session and refresh it, which reloads the row (including its new id) from the database:

from sqlmodel import Session

with Session(engine) as session:
    session.add(t)
    session.commit()
    session.refresh(t)
    print("id after commit:", t.id)

id after commit: 1

The same object now has id = 1. session.add staged the row, session.commit wrote it to disk and let the database assign the key, and session.refresh pulled that assigned value back into your Python object. (You’ll use exactly this add/commit/refresh rhythm in the next lesson — here we’re just proving where the id comes from.)

Separating the Table From the Schemas Clients Use#

It’s tempting to use the Task table model everywhere — accept it as request input, return it as output, done. But the table model is the wrong shape for input. It includes id, which clients must not set, and “required vs. optional” looks different when you’re creating a task than when you’re partially updating one. Reusing one class for all of these forces awkward compromises.

The clean approach is a small family of models built around a shared base. Start by pulling the fields common to every variant into a plain base model (no table=True):

from sqlmodel import SQLModel, Field

class TaskBase(SQLModel):
    title: str
    done: bool = False

Then derive each purpose-built class from it. The table model adds the primary key and becomes the actual table; the create schema is what clients send to make a new task; and the update schema is for partial edits, where every field is optional:

class Task(TaskBase, table=True):          # the database table
    id: int | None = Field(default=None, primary_key=True)

class TaskCreate(TaskBase):                # what clients send to create (no id)
    pass

class TaskUpdate(SQLModel):                # all optional, for partial updates
    title: str | None = None
    done: bool | None = None

Notice what each one cannot do, by design:

• TaskCreate inherits title and done but not id — there’s no way for a client to supply an id, because the field doesn’t exist on the schema they send. The database stays in charge of identity.
• TaskUpdate makes both fields ... | None = None, so a client can send just {"done": true} to mark a task done without re-sending the title. That’s the essence of a partial update (a PATCH): only the fields you include get changed.
• Task is the only class with table=True, so it’s the only one that becomes a table. The schemas are pure Pydantic models — validation shapes, never stored.

This is the same idea as response models from earlier modules, applied to a database: the model you persist and the models you expose at the edges of your API are deliberately different. Each schema says exactly what is and isn’t allowed for its job.

Creating the Table on Disk#

With the models defined, one line builds the table — the same create_all from Lesson 1. SQLModel.metadata is a registry of every table=True class you’ve defined, and create_all issues the SQL to create any that don’t yet exist:

from sqlmodel import create_engine

engine = create_engine("sqlite:///tasks.db")
SQLModel.metadata.create_all(engine)

print("tables defined:", list(SQLModel.metadata.tables.keys()))

tables defined: ['task']

Only task appears — TaskCreate and TaskUpdate are absent, exactly as intended, because they aren’t tables. To prove the table is real and ready, open a session and query it. On a fresh database it holds zero rows:

from sqlmodel import Session, select

with Session(engine) as session:
    rows = session.exec(select(Task)).all()
    print("rows in fresh table:", len(rows))

rows in fresh table: 0

A successful, empty result is the goal: the table exists, the session connected, and the query ran. Your data is now designed and its table is on disk — everything is in place for the create, read, update, and delete operations coming next.

Practice Exercises#

Exercise 1: Why is id typed int | None?#

The primary key is declared id: int | None = Field(default=None, primary_key=True). Why allow None at all, instead of just int?

Exercise 2: What stops a client from setting an id?#

A client POSTs a new task with a body that includes "id": 999. With TaskCreate as the request model, what happens to that id?

Exercise 3: Why are TaskUpdate’s fields all optional?#

TaskUpdate declares title: str | None = None and done: bool | None = None. Why make both optional when the table requires title?

Summary#

A table model is a SQLModel subclass with table=True; each typed field becomes a column, and the class name (lowercased) becomes the table. Its primary key — id: int | None = Field(default=None, primary_key=True) — is None until you save the row, at which point the database assigns it (you saw t.id go from None to 1 after commit and refresh). Crucially, you don’t expose that table model directly: a shared base holds the common fields, a create schema drops id so clients can’t set it, and an all-optional update schema supports partial edits. Finally, SQLModel.metadata.create_all(engine) builds the table on disk, and a fresh select(Task) returns zero rows — proof it’s ready.

Key Concepts#

• Table model — a SQLModel subclass with table=True; fields become columns, the class becomes a table.
• Primary key — Field(default=None, primary_key=True); identity the database auto-assigns on insert.
• int | None id — None in memory, an int once the row is committed and refreshed.
• Create schema — derived from the base, without id, so clients can’t choose an id.
• Update schema — all fields optional (... | None = None) for partial updates.
• create_all — turns your table=True classes into real tables on disk.

Why This Matters#

The model you store and the models you expose at your API’s edges have different jobs, and conflating them is a classic source of bugs and security holes — clients overwriting ids, updates forcing full payloads, internal fields leaking out. By splitting Task, TaskCreate, and TaskUpdate from a shared base, each request and response is constrained to exactly what’s appropriate, while the database keeps sole control of row identity. This separation is standard practice in production FastAPI codebases, and it makes the CRUD endpoints you write next both shorter and far harder to misuse.

Next Steps#

Continue Building Your Skills#

Your data is now designed: a task table with an auto-assigned primary key, and create and update schemas that constrain exactly what clients can send. Next you’ll put these models to work — inserting new tasks with the create schema, letting the database hand out ids, and reading rows back with select. The groundwork you laid here is what makes that code clean.