Testing FastAPI Endpoints: From Unit Tests to Integration Tests with PostgreSQL

A developer building CitizenApp with nine AI features advocates for integration tests with real PostgreSQL databases over mocked unit tests in FastAPI, arguing that mocking tests assumptions rather than code and misses critical bugs like transaction isolation issues. The developer shares a test fixture setup using transaction rollbacks and a multi-tenant workspace endpoint example to demonstrate production-grade testing practices.

I've shipped CitizenApp with nine AI features in production, and I can tell you with absolute certainty: bad tests in FastAPI will destroy your confidence faster than any outage. I'm not talking about unit tests that mock everything into oblivion—those are security theater. I'm talking about real tests that hit PostgreSQL, verify transactional behavior, and catch the subtle data corruption bugs that only appear under load. Here's what actually works, and why most FastAPI test guides miss the point entirely. Most tutorials show you how to mock the database. That's fine for toy projects. In production, I've watched carefully mocked unit tests pass while the actual endpoint corrupted customer data because of transaction isolation issues, cascading deletes, or constraint violations that only emerge with real schema interactions. The problem: when you mock everything, you're testing your assumptions, not your code. Your assumptions are usually wrong. I prefer integration tests that use a real PostgreSQL database or a container because they catch the bugs that matter. Yes, they're slower. No, you don't run them on every keystroke. But they're the difference between shipping with confidence and shipping with prayers. Start with the right fixtures. Here's my baseline setup: python tests/conftest.py import os import pytest from sqlalchemy import create engine, text from sqlalchemy.orm import sessionmaker, Session from sqlalchemy.pool import StaticPool from app.db import Base from app.main import app from fastapi.testclient import TestClient Use an in-memory SQLite for speed, OR a real Postgres test database I prefer Postgres because SQLite doesn't catch all constraint violations DATABASE URL = os.getenv "TEST DATABASE URL", "postgresql://postgres:password@localhost:5432/test db" engine = create engine DATABASE URL, echo=False, Set to True to see SQL statements TestingSessionLocal = sessionmaker autocommit=False, autoflush=False, bind=engine @pytest.fixture scope="session" def db engine : """Create tables once per test session.""" Base.metadata.create all bind=engine yield engine Base.metadata.drop all bind=engine @pytest.fixture def db session db engine : """ Create a new database session for each test. Rollback after the test to keep the database clean. """ connection = db engine.connect transaction = connection.begin session = TestingSessionLocal bind=connection yield session session.close transaction.rollback connection.close @pytest.fixture def client db session: Session : """Override the dependency injection to use our test session.""" def override get db : yield db session from app.main import get db app.dependency overrides get db = override get db yield TestClient app app.dependency overrides.clear Why this structure? The transaction rollback is critical. Each test runs inside a transaction that gets rolled back after the test completes. This keeps your test database clean without dropping tables between tests, and it's fast . Here's a real endpoint from CitizenApp—a multi-tenant API that creates workspaces: python app/api/workspaces.py from fastapi import APIRouter, Depends, HTTPException, status from sqlalchemy.orm import Session from app.db import get db from app.models import Workspace, User from app.schemas import WorkspaceCreate, WorkspaceResponse router = APIRouter @router.post "/workspaces", response model=WorkspaceResponse async def create workspace workspace: WorkspaceCreate, user: User = Depends get current user , db: Session = Depends get db , : """Create a workspace and assign the user as owner.""" Check for duplicate names within user's workspaces existing = db.query Workspace .filter Workspace.owner id == user.id, Workspace.name == workspace.name, .first if existing: raise HTTPException status code=status.HTTP 409 CONFLICT, detail="Workspace name already exists", new workspace = Workspace name=workspace.name, owner id=user.id, db.add new workspace db.commit db.refresh new workspace return new workspace Now, here's the test that catches real bugs: python tests/test workspaces.py import pytest from fastapi import status from app.models import User, Workspace from app.schemas import WorkspaceCreate @pytest.fixture def test user db session : """Create a test user.""" user = User email="test@example.com", hashed password="fake hash" db session.add user db session.commit db session.refresh user return user @pytest.fixture def auth header test user : """Return auth header for test user.""" In reality, you'd generate a valid JWT return {"Authorization": f"Bearer {test user.id}"} def test create workspace success client, test user, auth header : """Happy path: create a workspace.""" response = client.post "/workspaces", json={"name": "My First Workspace"}, headers=auth header, assert response.status code == status.HTTP 200 OK data = response.json assert data "name" == "My First Workspace" assert data "owner id" == test user.id def test create duplicate workspace name client, test user, auth header, db session : """This test catches a real bug: duplicate name in same user's workspace.""" Create first workspace workspace1 = Workspace name="Duplicate", owner id=test user.id db session.add workspace1 db session.commit Try to create duplicate response = client.post "/workspaces", json={"name": "Duplicate"}, headers=auth header, assert response.status code == status.HTTP 409 CONFLICT def test different users can have same workspace name client, db session, auth header : """Multi-tenant isolation: user A and user B can both have 'Workspace'.""" user b = User email="user b@example.com", hashed password="hash" db session.add user b db session.commit User A creates "My Workspace" response a = client.post "/workspaces", json={"name": "My Workspace"}, headers=auth header, assert response a.status code == status.HTTP 200 OK User B also creates "My Workspace" different owner id auth header b = {"Authorization": f"Bearer {user b.id}"} response b = client.post "/workspaces", json={"name": "My Workspace"}, headers=auth header b, assert response b.status code == status.HTTP 200 OK assert response a.json "id" = response b.json "id" When you have 9 AI features, you need to test variations efficiently: @pytest.mark.parametrize "workspace name,expected status", "Valid Workspace", 200 , "", 422 , Empty name "x" 256, 200 , Very long name assuming your schema allows it None, 422 , Missing field , def test workspace name validation client, test user, auth header, workspace name, expected status : """Test name validation with multiple inputs.""" response = client.post "/workspaces", json={"name": workspace name} if workspace name is not None else {}, headers=auth header, assert response.status code == expected status Here's where I burned an hour: if you're using async endpoints with asyncio in tests, the transaction rollback might not work as expected. FastAPI's TestClient runs async in a special way. My workaround: python @pytest.fixture def client db session: Session : """For async endpoints, ensure the session is thread-safe.""" import asyncio from sqlalchemy.orm import scoped session scoped = scoped session TestingSessionLocal def override get db : try: yield scoped finally: scoped.remove from app.main import get db app.dependency overrides get db = override get db yield TestClient app app.dependency overrides.clear Use GitHub Actions with a Postgres container: yaml .github/workflows/test.yml name: Tests on: push, pull request jobs: test: runs-on: ubuntu-latest services: postgres: image: postgres:15 env: POSTGRES PASSWORD: postgres POSTGRES DB: test db options: - --health-cmd pg isready --health-interval 10s --health-timeout 5s --health-retries 5 ports: - 5432:5432 steps: - uses: actions/checkout@v3 - uses: actions/setup-python@v4 with: python