How to Define New Database Models Using SQLAlchemy 2.0 in the FastAPI Boilerplate

To define new database models using SQLAlchemy 2.0 in the FastAPI boilerplate, inherit from the shared Base class located in src/app/core/db/database.py, annotate attributes with Mapped[] and mapped_column(), and declare a mandatory __tablename__ attribute.

The benavlabs/fastapi-boilerplate repository ships with a modern SQLAlchemy 2.0 setup that implements the declarative-as-dataclass pattern via MappedAsDataclass. This architecture allows you to define database schemas using Python type hints while automatically gaining dataclass-style constructors and representation methods, eliminating boilerplate code typically associated with traditional declarative bases.

Understanding the SQLAlchemy 2.0 Base Configuration

All database models in this boilerplate inherit from a centralized Base class defined in src/app/core/db/database.py. This base class combines DeclarativeBase with MappedAsDataclass, enabling you to use standard type annotations for column declarations while preserving dataclass behavior such as automatic __init__ generation and equality comparisons.

Because Base inherits from MappedAsDataclass, you instantiate models using keyword arguments that match your column names. SQLAlchemy automatically maps these values to the underlying table rows when you add the instance to an async session.

Step-by-Step Guide to Creating a New Model

1. Import the Shared Base Class

Begin every new model file by importing the Base class from the core database module. This ensures your model registers with the same metadata used by Alembic migrations.

from ..core.db.database import Base

2. Declare the Table Name and Columns

Every model must define a __tablename__ attribute. Use Mapped[type] annotations combined with mapped_column() to declare columns. The mapped_column() function accepts SQLAlchemy types, constraints, and default values.

from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column

class Category(Base):
    __tablename__ = "category"
    
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, init=False)
    name: Mapped[str] = mapped_column(String(50), unique=True, index=True)

3. Configure Primary Keys and Defaults

For primary keys, set init=False to exclude them from the dataclass constructor, as the database auto-generates these values. Use default_factory for dynamic defaults like timestamps or UUIDs rather than static default values.

from datetime import UTC, datetime
from sqlalchemy import DateTime

created_at: Mapped[datetime] = mapped_column(
    DateTime(timezone=True), 
    default_factory=lambda: datetime.now(UTC),
    init=False
)

Complete Examples from the Codebase

Minimal Category Model

The following example creates a lookup table with an auto-incrementing primary key and an indexed name column. Save this as src/app/models/category.py.

# src/app/models/category.py

from datetime import UTC, datetime

from sqlalchemy import DateTime, String
from sqlalchemy.orm import Mapped, mapped_column

from ..core.db.database import Base


class Category(Base):
    __tablename__ = "category"

    id: Mapped[int] = mapped_column(autoincrement=True, primary_key=True, init=False)

    name: Mapped[str] = mapped_column(String(50), unique=True, index=True)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default_factory=lambda: datetime.now(UTC), init=False
    )

Model with Foreign Keys and UUID

This example demonstrates a model referencing the User table and using UUID v7 for public identifiers. Save this as src/app/models/article.py.

# src/app/models/article.py

import uuid as uuid_pkg
from datetime import UTC, datetime

from sqlalchemy import DateTime, ForeignKey, String, UUID
from sqlalchemy.orm import Mapped, mapped_column
from uuid6 import uuid7

from ..core.db.database import Base


class Article(Base):
    __tablename__ = "article"

    id: Mapped[int] = mapped_column(autoincrement=True, primary_key=True, init=False)

    author_id: Mapped[int] = mapped_column(ForeignKey("user.id"), index=True)

    title: Mapped[str] = mapped_column(String(150))
    content: Mapped[str] = mapped_column(String)

    uuid: Mapped[uuid_pkg.UUID] = mapped_column(
        UUID(as_uuid=True), default_factory=uuid7, unique=True, init=False
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default_factory=lambda: datetime.now(UTC), init=False
    )
    updated_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None, init=False
    )

Reference Implementations

Study the existing models to understand advanced patterns:

• src/app/models/user.py: Demonstrates uuid7 generation, soft-delete flags, timezone-aware timestamps, and foreign keys to the Tier table.
• src/app/models/tier.py: Shows a simple reference table with unique name constraints.
• src/app/models/post.py: Illustrates relationships to User, UUID fields, and optional media URLs.

Generating Database Migrations

After you define new database models using SQLAlchemy 2.0, generate Alembic migrations to reflect the schema changes in your PostgreSQL database. Run the following command from the project root:

alembic revision --autogenerate -m "Add category and article models"

Review the generated migration script in the alembic/versions directory before applying it with alembic upgrade head.

Summary

• Inherit from Base: Import the shared base from src/app/core/db/database.py to ensure proper metadata registration.
• Use Mapped annotations: Declare all columns using Mapped[type] with mapped_column() for constraints and defaults.
• Set __tablename__: Every model requires this attribute for the declarative mapper.
• Leverage dataclass behavior: The MappedAsDataclass mixin provides automatic constructors; use init=False for database-generated columns.
• Generate migrations: Run alembic revision --autogenerate after creating or modifying models.

Frequently Asked Questions

Where is the Base class defined in this boilerplate?

The Base class is defined in src/app/core/db/database.py. It inherits from both DeclarativeBase and MappedAsDataclass, providing the foundation for SQLAlchemy 2.0's type-annotated declarative syntax while adding dataclass functionality.

How do I create foreign key relationships between models?

Use ForeignKey("table.column") inside mapped_column(), as shown in the Article example with mapped_column(ForeignKey("user.id"), index=True). Ensure the referenced table name matches the __tablename__ attribute of the target model.

Why does the Base class use MappedAsDataclass?

MappedAsDataclass allows SQLAlchemy 2.0 models to behave like standard Python dataclasses, automatically generating __init__, __repr__, and __eq__ methods. This eliminates repetitive boilerplate while maintaining full ORM functionality.

Can I use the standard @dataclass decorator on these models?

No, you should not apply the @dataclass decorator to models inheriting from this Base. The MappedAsDataclass mixin already implements dataclass behavior, and adding the decorator would create conflicts with SQLAlchemy's descriptor instrumentation.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}