🏗️ Defining Database Models

What a Model Is

A model is a Python class that maps to one database table. Each attribute of the class becomes a column, and each instance of the class becomes a row. Define the class once and every record of that type shares the same shape.

💡 A useful analogy: A model is an architect's blueprint. The blueprint (the class) describes rooms, dimensions, and materials once; every house built from it (each row) has the same layout even though the families living inside differ. Get the blueprint right and thousands of well-formed records follow for free.

Good model design pays off for the life of the project. A missing constraint or a poorly chosen key is cheap to fix on day one and painful to fix once real data has piled up on top of it.

Anatomy of a Model

Here is a complete model in the modern SQLAlchemy 2.0 style, where the Python type annotation drives the column type. Mapped[str] means a non-null string; Mapped[str | None] means the column is nullable.

from datetime import datetime, timezone
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.extensions import db


class User(db.Model):
    __tablename__ = "users"                       # explicit table name

    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(80), unique=True)
    email: Mapped[str] = mapped_column(String(120), unique=True, index=True)
    created_at: Mapped[datetime] = mapped_column(
        default=lambda: datetime.now(timezone.utc)
    )

    # A relationship, not a column — one user has many posts
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

    def __repr__(self) -> str:
        return f"<User {self.username}>"

    def to_dict(self) -> dict:
        return {"id": self.id, "username": self.username, "email": self.email}

Every model is built from the same handful of parts:

• Base class — inherit from db.Model.
• Table name — set with __tablename__, or let SQLAlchemy derive one.
• Columns — annotated attributes using mapped_column().
• Relationships — links to other models via relationship().
• Methods — __repr__ for readable debugging plus any helpers like to_dict().

Columns & Constraints

A column has a type and optional constraints — rules the database enforces on every value. Choosing the right type (and the tightest reasonable constraints) is how you push data-integrity guarantees down into the database itself.

Constraints can stack. This email column must be present, unique, and is indexed for the frequent "find user by email" query at login:

email: Mapped[str] = mapped_column(
    String(120),
    unique=True,
    index=True,
)

Primary Keys

Every table needs a primary key: a column (or set of columns) that uniquely identifies each row. You have three common choices.

Auto-incrementing integer (the default)

id: Mapped[int] = mapped_column(primary_key=True)

Simple, compact, and fast. This is the right choice the vast majority of the time.

UUID

import uuid
from sqlalchemy import String

id: Mapped[str] = mapped_column(
    String(36),
    primary_key=True,
    default=lambda: str(uuid.uuid4()),
)

UUIDs are unguessable and can be generated by many machines without collisions — handy for distributed systems or when you don't want to leak how many records exist by exposing sequential IDs in URLs.

Composite key

from sqlalchemy import ForeignKey

class Enrollment(db.Model):
    student_id: Mapped[int] = mapped_column(ForeignKey("students.id"), primary_key=True)
    course_id: Mapped[int] = mapped_column(ForeignKey("courses.id"), primary_key=True)
    enrolled_on: Mapped[datetime] = mapped_column(default=lambda: datetime.now(timezone.utc))

Two columns marked primary_key=True form a composite key: the pair must be unique. This pattern is common in the join tables that sit between many-to-many relationships.

Default Values

A default fills a column in when you don't supply a value. There is one subtlety that trips up nearly everyone.

Static defaults

is_active: Mapped[bool] = mapped_column(default=True)
role: Mapped[str] = mapped_column(String(20), default="user")

Dynamic defaults — pass a callable, not a call

# RIGHT — pass the function; it runs once per row at insert time
created_at: Mapped[datetime] = mapped_column(
    default=lambda: datetime.now(timezone.utc)
)

# WRONG — this calls the function ONCE at import time and freezes that
# single timestamp onto every future row.
# created_at: Mapped[datetime] = mapped_column(default=datetime.now(timezone.utc))

Python default vs. server default

from sqlalchemy import func

# Evaluated in Python when the object is created
created_at: Mapped[datetime] = mapped_column(default=lambda: datetime.now(timezone.utc))

# Evaluated by the DATABASE — good when other apps insert rows too
created_at: Mapped[datetime] = mapped_column(server_default=func.now())

Reach for server_default when rows might be inserted outside your Python code (a data-import script, another service) and you still want the default applied.

Model Relationships

Real data is connected: a user has posts, a post belongs to a category, students enroll in courses. SQLAlchemy lets you express these links as attributes so you can write user.posts instead of a hand-written JOIN.

One-to-many (the workhorse)

The child table holds a foreign key pointing at the parent. The relationship is declared on both sides with back_populates.

from sqlalchemy import ForeignKey, String, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship


class User(db.Model):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(80), unique=True)

    posts: Mapped[list["Post"]] = relationship(back_populates="author")


class Post(db.Model):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120))
    content: Mapped[str] = mapped_column(Text)

    # The foreign key column...
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    # ...and the object-level link back to the parent
    author: Mapped["User"] = relationship(back_populates="posts")

Now a_user.posts gives you a list of posts, and a_post.author gives you the user — no SQL in sight.

Many-to-many

When both sides can have many of the other, you need an association table in the middle. Students take many courses; each course has many students.

from sqlalchemy import Column, ForeignKey, Table

enrollments = Table(
    "enrollments",
    db.metadata,
    Column("student_id", ForeignKey("students.id"), primary_key=True),
    Column("course_id", ForeignKey("courses.id"), primary_key=True),
)


class Student(db.Model):
    __tablename__ = "students"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80))
    courses: Mapped[list["Course"]] = relationship(
        secondary=enrollments, back_populates="students"
    )


class Course(db.Model):
    __tablename__ = "courses"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120))
    students: Mapped[list["Student"]] = relationship(
        secondary=enrollments, back_populates="courses"
    )

One-to-one

A one-to-one is a one-to-many capped at one child. Set uselist=False so the attribute returns a single object rather than a list.

class User(db.Model):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    profile: Mapped["Profile"] = relationship(back_populates="user", uselist=False)


class Profile(db.Model):
    __tablename__ = "profiles"
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), unique=True)
    bio: Mapped[str | None] = mapped_column(Text)
    user: Mapped["User"] = relationship(back_populates="profile")

Mixins & Best Practices

As the model layer grows, a few habits keep it maintainable.

Share common columns with a mixin

Many tables want created_at and updated_at timestamps. Rather than copy them everywhere, define them once in a mixin and inherit it.

from datetime import datetime, timezone
from sqlalchemy.orm import Mapped, mapped_column


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(
        default=lambda: datetime.now(timezone.utc)
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
    )


class Post(TimestampMixin, db.Model):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120))
    # created_at and updated_at come from the mixin for free

Hands-on Exercise

from datetime import datetime, timezone
from sqlalchemy import Column, ForeignKey, String, Table, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.extensions import db


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(
        default=lambda: datetime.now(timezone.utc)
    )
    updated_at: Mapped[datetime] = mapped_column(
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
    )


post_tags = Table(
    "post_tags",
    db.metadata,
    Column("post_id", ForeignKey("posts.id"), primary_key=True),
    Column("tag_id", ForeignKey("tags.id"), primary_key=True),
)


class User(TimestampMixin, db.Model):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(80), unique=True, index=True)
    email: Mapped[str] = mapped_column(String(120), unique=True, index=True)
    posts: Mapped[list["Post"]] = relationship(back_populates="author")


class Post(TimestampMixin, db.Model):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120), index=True)
    body: Mapped[str] = mapped_column(Text)
    status: Mapped[str] = mapped_column(String(20), default="draft", index=True)

    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped["User"] = relationship(back_populates="posts")
    tags: Mapped[list["Tag"]] = relationship(
        secondary=post_tags, back_populates="posts"
    )


class Tag(db.Model):
    __tablename__ = "tags"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(40), unique=True)
    posts: Mapped[list["Post"]] = relationship(
        secondary=post_tags, back_populates="tags"
    )

Summary & Quiz

📚 Further Reading

• Flask-SQLAlchemy — Models & Tables
• SQLAlchemy 2.0 — Relationship Patterns
• SQLAlchemy 2.0 — Declarative Table Configuration

🚀 What's Next?

Your tables are designed and connected. Next you'll bring them to life with CRUD operations — creating, reading, updating, and deleting rows through the SQLAlchemy session.