FastAPI with PostgreSQL and SQLModel

Use SQLModel with FastAPI when you want SQLAlchemy-backed persistence and Pydantic-shaped models to sit close together in a compact Python API service.

bun create better-fullstack@latest my-fastapi-app \
  --ecosystem python \
  --python-web-framework fastapi \
  --database postgres \
  --python-orm sqlmodel \
  --python-validation pydantic \
  --python-quality ruff \
  --python-testing pytest

The FastAPI PostgreSQL SQLModel template records the exact normalized selection and generated files.

Generated responsibility map

• FastAPI owns HTTP routing and dependency injection.
• SQLModel describes database-backed models and query sessions.
• PostgreSQL owns durable relational data.
• Pydantic validates data at the API edge.
• Ruff and pytest provide fast quality checks.
• uv is the default generated Python package manager.

Keep route functions small. Put session management in a dependency and persistence behavior in narrow repository or service functions.

from collections.abc import Generator

from sqlmodel import Session


def get_session() -> Generator[Session, None, None]:
    with Session(engine) as session:
        yield session

Table models and public schemas

SQLModel makes it possible to share fields, but a database table and a public response are not automatically the same contract. Separate creation and response schemas when fields have different trust levels.

from sqlmodel import Field, SQLModel


class ProjectBase(SQLModel):
    name: str = Field(min_length=2, max_length=80)


class Project(ProjectBase, table=True):
    id: int | None = Field(default=None, primary_key=True)
    owner_id: str = Field(index=True)


class ProjectCreate(ProjectBase):
    pass


class ProjectPublic(ProjectBase):
    id: int

Do not accept owner_id from a browser payload. Resolve it from authentication when auth is added.

PostgreSQL and migrations

Set the deployed DATABASE_URL explicitly and use Alembic migrations as reviewed schema history. Creating tables at startup is convenient for a local demonstration but is not a substitute for production migrations.

uv run alembic upgrade head
uv run pytest
uv run ruff check .

Use a separate database or transaction strategy for tests. Assertions should cover status codes and response contracts as well as persistence behavior.

SQLModel or SQLAlchemy?

SQLModel reduces repetition for conventional CRUD-shaped APIs. SQLAlchemy is the more direct choice when the domain uses advanced mappings or when the team wants persistence models clearly separate from validation schemas.

Next steps

• Inspect the SQLModel template record.
• Compare the existing FastAPI SQLAlchemy guide.
• Browse all starter templates.