12.3. Models

Table of Contents

Why Models Matter

In backend development, a model is a Python class that represents something stored in your database, for example a user, a blog post, or an order.

You will use models to:

Describe the shape of your data, for example which columns a table has.
Enforce types, for example that age is an integer and email is a string.
Connect Python code with SQL without writing SQL for every small operation.

In the context of an ORM (Object Relational Mapper) like SQLAlchemy, models are your main bridge between Python objects and database tables.

A model class in an ORM usually represents one database table.
Each instance of the model class usually represents one row in that table.

This chapter focuses on ORM models themselves, not on how to execute queries or manage sessions. Those have their own chapters.

Models in an ORM: The Big Picture

With an ORM such as SQLAlchemy, you usually:

Define models as Python classes.
Let the ORM map those classes to database tables.
Use model instances like normal Python objects.
Let the ORM convert object operations into SQL under the hood.

A simple mental mapping:

Concept	ORM / Python world	Database world
Data structure	Model class	Table
One record	Model instance	Row
Attribute	Field / column attribute	Column
Relationship	Relationship attribute	Foreign key + joins

You will see examples using SQLAlchemy 2.x with Declarative Mapping, which is one of the most common patterns in Python backends today.

Basic SQLAlchemy Model Structure

SQLAlchemy models use a declarative base class. This base handles the mapping logic for all your models.

python

from typing import Optional
from datetime import datetime
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String, Integer, DateTime
class Base(DeclarativeBase):
    """Base class for all ORM models."""
    pass
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    full_name: Mapped[Optional[str]] = mapped_column(String(200))
    created_at: Mapped[datetime] = mapped_column(
        DateTime, nullable=False
    )

Explanation of important pieces:

Base is the common parent of all models.
__tablename__ tells SQLAlchemy which table name to use.
Mapped[...] and mapped_column(...) describe columns and types.
Type hints (like Mapped[int]) let SQLAlchemy know the Python type and also help with IDEs and type checkers.

Every ORM model that maps to a table should:

Inherit from the declarative Base.
Define __tablename__.
Have at least one primary key column.

Primary Keys and Identity

Every table needs a primary key. In ORM models, that is usually a column called id.

python

from sqlalchemy import Integer
from sqlalchemy.orm import Mapped, mapped_column
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    # ... other columns

Common patterns:

Pattern	Example	Notes
Integer autoincrement key	`id: Mapped[int] = mapped_column(primary_key=True)`	Most common and simplest
UUID key	`id: Mapped[uuid.UUID] = mapped_column(Uuid(as_uuid=True), primary_key=True)`	Good for distributed systems
Composite key	Multiple columns set as `primary_key=True`	Less common, more advanced

The primary key is how SQLAlchemy tracks each row / object. Without it, the ORM cannot reliably update or delete records.

Defining Columns and Types

Model attributes mapped with mapped_column become columns. Columns have:

A Python type, through Mapped[...].
A database type, through mapped_column(<db_type>, ...).
Additional options, such as nullable, unique, default, etc.

python

from sqlalchemy import String, Boolean, Integer, Text, DateTime, func
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(
        String(255),
        unique=True,
        nullable=False,
        index=True,
    )
    is_active: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        default=True,
    )
    age: Mapped[int] = mapped_column(
        Integer,
        nullable=True,
    )
    bio: Mapped[str] = mapped_column(
        Text,
        nullable=False,
        server_default="",
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        server_default=func.now(),  # database default
    )

Useful mapped_column keyword arguments:

Argument	What it does
`primary_key`	Marks column as part of the primary key
`nullable`	Controls whether column can be NULL in the database
`unique`	Adds a uniqueness constraint
`index`	Creates an index on the column
`default`	Python-side default, used when constructing an object
`server_default`	Database-side default, used when inserting without a value
`autoincrement`	Controls auto increment behavior for integer primary keys

Rule: When you set nullable=False on a column, your code must always provide a non-null value, or you must define a default (default or server_default), otherwise inserts will fail.

Optional vs Required Fields

Type hints and nullable often go together, but they are not the same thing.

Example:

python

from typing import Optional
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    # Required: cannot be NULL in DB
    username: Mapped[str] = mapped_column(String(50), nullable=False)
    # Optional: can be NULL
    middle_name: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)

Mapped[str] with nullable=False means:

Database column is NOT NULL.
In Python, attribute should never be None.

Mapped[Optional[str]] with nullable=True means:

Database column CAN be NULL.
In Python, attribute can be None.

If they do not match, it becomes confusing.

For example, this is inconsistent and should be avoided:

python

# Inconsistent: type says optional, DB says not nullable
name: Mapped[Optional[str]] = mapped_column(String(50), nullable=False)

Defaults and Automatic Values

You often want some values to be set automatically, for example timestamps.

Typical timestamp pattern:

python

from datetime import datetime
from sqlalchemy import DateTime, func
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        server_default=func.now(),      # default value when row is first inserted
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        server_default=func.now(),      # set on insert
        onupdate=func.now(),            # set on every update (ORM-level)
    )

server_default=func.now() tells the database to set a default value when no value is provided.

default= or onupdate= are handled by SQLAlchemy on the Python side.

Example of a Python-side default:

python

import uuid
from sqlalchemy import String
class ApiKey(Base):
    __tablename__ = "api_keys"
    id: Mapped[int] = mapped_column(primary_key=True)
    key: Mapped[str] = mapped_column(
        String(64),
        unique=True,
        nullable=False,
        default=lambda: uuid.uuid4().hex,  # Python generates default
    )

Example: User and Post Models

Here is a small but complete example of models for a simple blog.

python

from __future__ import annotations
from typing import List, Optional
from datetime import datetime
from sqlalchemy import String, Text, Integer, DateTime, ForeignKey, Boolean, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
    pass
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False)
    hashed_password: Mapped[str] = mapped_column(String(255), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        server_default=func.now(),
    )
    # Relationship: user has many posts
    posts: Mapped[List["Post"]] = relationship(
        back_populates="author",
        cascade="all, delete-orphan",
    )
class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    content: Mapped[str] = mapped_column(Text, nullable=False)
    # Foreign key to users.id
    author_id: Mapped[int] = mapped_column(
        ForeignKey("users.id"),
        nullable=False,
        index=True,
    )
    is_published: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime,
        nullable=False,
        server_default=func.now(),
    )
    # Relationship: post belongs to one user
    author: Mapped["User"] = relationship(
        back_populates="posts",
    )

Even if you have not studied relationships yet, you can already observe the model structure:

Both User and Post inherit from Base.
Each has a __tablename__.
Each has a primary key.
Columns are clearly described with types and constraints.
Relationships link models at the Python level, while foreign keys link tables at the database level.

Models vs Pydantic / Schema Models

In a typical backend with FastAPI and SQLAlchemy you often have two types of models:

ORM models

Map to database tables.
Used for querying and persisting data.
Often live in a models.py or db/models.py.

Schema / Pydantic models

Describe request and response shapes for the API.
Used for validation and serialization.
Often live in a schemas.py or api/schemas.py.

It is helpful to keep them separate, even if their fields look similar.

Example:

python

# ORM model (database)
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    hashed_password: Mapped[str] = mapped_column(String(255), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)

python

# Pydantic model (API request / response)
from pydantic import BaseModel, EmailStr
class UserCreate(BaseModel):
    email: EmailStr
    password: str
class UserRead(BaseModel):
    id: int
    email: EmailStr
    is_active: bool

The ORM model contains hashed_password, while the UserRead schema does not, so you never send the hash to the client.

Organizing Your Model Files

In real projects you quickly get many models. Organizing them from the beginning is helpful.

Common patterns:

Project size	Organization example
Very small	All models in `models.py`
Small / mid	Package like `app/models/` with multiple files
Large	Domain-based packages like `app/users/models.py`, `app/orders/models.py`

Example folder structure:

text

app/
  db/
    base.py          # defines Base and metadata
    session.py       # SQLAlchemy engine and session makers
  models/
    __init__.py      # imports all models so metadata sees them
    user.py
    post.py
    comment.py

A minimal base.py might look like:

python

from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
    pass

And models/__init__.py might re-export your models:

python

from app.db.base import Base
from .user import User
from .post import Post
from .comment import Comment
__all__ = ["Base", "User", "Post", "Comment"]

Common Modeling Mistakes and How to Avoid Them

Even beginners can avoid many problems by following some simple rules.

1. Forgetting a primary key

Incorrect:

python

class Product(Base):
    __tablename__ = "products"
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    price: Mapped[int] = mapped_column(Integer, nullable=False)

Here there is no primary key column. SQLAlchemy will complain or behave unpredictably.

Correct:

python

class Product(Base):
    __tablename__ = "products"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    price: Mapped[int] = mapped_column(Integer, nullable=False)

2. Mismatch between type and nullable

Incorrect:

python

# DB allows NULL, but Python type says str only
nickname: Mapped[str] = mapped_column(String(50), nullable=True)

Correct:

python

from typing import Optional
nickname: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)

3. Storing plain passwords

Never store passwords directly in models.

Incorrect:

python

password: Mapped[str] = mapped_column(String(255), nullable=False)

Correct pattern:

python

hashed_password: Mapped[str] = mapped_column(String(255), nullable=False)

The hashing logic belongs in your authentication code, not in the model itself, but the model should never expose a plain password column.

4. Using too generic types

Avoid using very generic types when you know better.

Instead of:

python

data: Mapped[str] = mapped_column(String)

Prefer:

python

data: Mapped[str] = mapped_column(String(255))

or use specific types like Text, JSON, Integer, Boolean, etc.

Simple End-to-End Example

This short example shows how you can define a model, create an instance, and understand what it represents, without going into session details.

python

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String, Integer
class Base(DeclarativeBase):
    pass
class TodoItem(Base):
    __tablename__ = "todo_items"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    completed: Mapped[bool] = mapped_column(default=False, nullable=False)
# Using the model in code (no DB interaction shown yet)
item = TodoItem(title="Buy milk")
print(item.id)         # None, until inserted into DB
print(item.title)      # "Buy milk"
print(item.completed)  # False, from default

Conceptually:

TodoItem is your table definition.
item is one row in that table as a Python object.
When you insert item into the database, SQLAlchemy will generate SQL like:

sql

INSERT INTO todo_items (title, completed) VALUES ('Buy milk', false);

You will learn how to perform that insert in the chapters about Database Sessions and Creating Records.

Summary

In this chapter you learned that:

Models are Python classes that represent database tables.
Each model usually maps to one table, and each instance to one row.
Models inherit from a declarative base and define __tablename__.
Columns are defined using Mapped[...] and mapped_column(...) with proper types and options.
Every model needs at least one primary key.
nullable, default, and server_default control how data is stored and validated.
ORM models are different from API schema models, and they usually live in different files.
Good structure and consistent type usage help keep your backend code clean and predictable.

With this foundation, you are ready to learn how to use models with sessions, queries, and relationships in the following chapters.

12.3. Models

Why Models Matter

Models in an ORM: The Big Picture

Basic SQLAlchemy Model Structure

Primary Keys and Identity

Defining Columns and Types

Optional vs Required Fields

Defaults and Automatic Values

Example: User and Post Models

Models vs Pydantic / Schema Models

Organizing Your Model Files

Common Modeling Mistakes and How to Avoid Them

1. Forgetting a primary key

2. Mismatch between type and nullable

3. Storing plain passwords

4. Using too generic types

Simple End-to-End Example

Summary

Comments

Where to Move