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

> Learn to define new database models with SQLAlchemy 2.0 in FastAPI boilerplate. Inherit from Base, use Mapped and mapped_column, and set __tablename__.

- Repository: [Benav Labs/fastapi-boilerplate](https://github.com/benavlabs/fastapi-boilerplate)
- Tags: how-to-guide
- Published: 2026-02-26

---

**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`](https://github.com/benavlabs/fastapi-boilerplate/blob/main/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`](https://github.com/benavlabs/fastapi-boilerplate/blob/main/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.

```python
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.

```python
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.

```python
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`](https://github.com/benavlabs/fastapi-boilerplate/blob/main/src/app/models/category.py).

```python

# 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`](https://github.com/benavlabs/fastapi-boilerplate/blob/main/src/app/models/article.py).

```python

# 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`](https://github.com/benavlabs/fastapi-boilerplate/blob/main/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`](https://github.com/benavlabs/fastapi-boilerplate/blob/main/src/app/models/tier.py)**: Shows a simple reference table with unique name constraints.
- **[`src/app/models/post.py`](https://github.com/benavlabs/fastapi-boilerplate/blob/main/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:

```bash
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`](https://github.com/benavlabs/fastapi-boilerplate/blob/main/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`](https://github.com/benavlabs/fastapi-boilerplate/blob/main/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.