Defining the Project Model

Now that we understand the role of SQLModel, let's create our first database table: Project.

Our goal is to define a model that not only validates data coming in from API requests but also maps directly to a table in our Postgres database.

Install SQLModel

Add SQLModel to the project dependencies:

shell

uv add sqlmodel

The Project Model

Let's create a new file named models.py in our src/release_tracker directory. This is where we'll centralize all of our application's data models.

src/release_tracker/models.py

from datetime import UTC, datetime
from typing import Annotated

from pydantic import StringConstraints
from sqlalchemy import Column, DateTime
from sqlmodel import Field, SQLModel


def utc_now() -> datetime:
    return datetime.now(UTC)


ProjectName = Annotated[
    str,
    StringConstraints(strip_whitespace=True, min_length=2),
]


class ProjectBase(SQLModel):
    name: ProjectName = Field(unique=True)
    description: str | None = None


class Project(ProjectBase, table=True):
    __tablename__ = "projects"

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

    created_at: datetime = Field(
        default_factory=utc_now,
        sa_column=Column(DateTime(timezone=True), nullable=False),
    )

Breaking Down the Code

Let's look at a few key patterns we're using here:

1. Base Classes vs. Table Classes:

   We define a ProjectBase class that inherits from SQLModel but does not include table=True. This base class holds the attributes common to all representations of a project (like name and description).

   The Project class inherits from ProjectBase and adds table=True. This is the class that SQLAlchemy will use to create the projects table in our database. It includes attributes that the database generates or manages, like the primary key id, the autogenerated slug, and the created_at timestamp.

2. Pydantic Types:

   We use Annotated with Pydantic's StringConstraints to define a ProjectName type. This ensures that any name passed to our API is automatically stripped of leading/trailing whitespace and meets our minimum length requirement.

3. Native Field Constraints:

   By default, SQLModel guesses the appropriate database column type based on your Python type hint. It maps Python str types directly to VARCHAR columns in the database. Since we're using PostgreSQL (which efficiently handles unbounded VARCHAR and TEXT identically), we don't need to specify explicit length limits. We simply use Field(unique=True) for name and slug to enforce uniqueness.

   SQLModel also infers nullability from your type hints. Because name and slug are typed as strings (and not str | None), SQLModel automatically configures the underlying database columns as NOT NULL.

Defining Read and Write Schemas

Beyond the database table, our API needs schemas that describe what clients can send (when creating or updating) and what we send back (when reading). With SQLModel, we define those schemas directly in models.py alongside the model. They inherit from ProjectBase to avoid repeating fields.

Add these classes to src/release_tracker/models.py:

src/release_tracker/models.py

class ProjectCreate(ProjectBase):
    pass


class ProjectUpdate(SQLModel):
    name: ProjectName | None = None
    description: str | None = None


class ProjectRead(ProjectBase):
    id: int
    slug: str
    created_at: datetime

The three new classes plus Project give us four distinct shapes for one resource. Project is the only one with table=True and is what the database stores. The other three are pure SQLModel schemas (which behave like Pydantic BaseModels) used to validate API input and shape API output.

---

Verifying the Models

Quick smoke test to confirm the imports resolve and the models are well-formed:

shell

uv run python -c "from release_tracker import models; print(models.Project)"

You should see something like <class 'release_tracker.models.Project'>. If you get an ImportError, double-check the file path and the imports at the top of models.py.

Wiring ProjectRead Into the API

Our existing main.py defines its own local ProjectRead class. Now that we have the real one in models.py (with the new created_at field), we'll switch over and update the mock data to include created_at so the response model still validates.

Replace the imports and the ProjectRead / mock_database blocks in src/release_tracker/main.py:

src/release_tracker/main.py

from datetime import UTC, datetime

from fastapi import FastAPI, HTTPException, status

from .models import ProjectRead

app = FastAPI(title="Release Tracker API")

_seed_time = datetime(2026, 1, 1, tzinfo=UTC)

# A simple mock database for now
mock_database: dict[int, ProjectRead] = {
    1: ProjectRead(
        id=1,
        name="Frontend Redesign",
        slug="frontend-redesign",
        created_at=_seed_time,
    ),
    2: ProjectRead(id=2, name="API v2", slug="api-v2", created_at=_seed_time),
    3: ProjectRead(
        id=3,
        name="Database Migration",
        slug="database-migration",
        created_at=_seed_time,
    ),
}

We removed the local from pydantic import BaseModel import and the inline ProjectRead class. The route handlers (get_project, list_projects) stay unchanged.

With the model wired up and the mock data updated, our application knows what the data should look like. Next, we need to instruct our database to actually create the tables based on these blueprints. We'll do this using a database migration tool called Alembic.