Integrating PostgreSQL with FastAPI - With and Without Docker

FastAPI is a modern, high-performance Python web framework that makes building APIs a breeze. In this tutorial, we’ll explore how to integrate PostgreSQL with FastAPI using both traditional local setup and Docker containers.

Project Structure

Before we begin, here’s the project structure we’ll be building:

fastapi-postgres/
├── app/
│   ├── __init__.py
│   ├── main.py
│   ├── database.py
│   ├── models.py
│   ├── schemas.py
│   └── crud.py
├── requirements.txt
├── Dockerfile
└── compose.yaml

Part 1: Without Docker (Local Setup)

Prerequisites

• Python 3.10+
• PostgreSQL installed locally
• Basic understanding of FastAPI and SQLAlchemy

Step 1: Install PostgreSQL Locally

macOS (using Homebrew)

brew install postgresql@15
brew services start postgresql@15

Ubuntu/Debian

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Windows

Download and install from postgresql.org

Step 2: Create a Database

# Connect to PostgreSQL
psql -U postgres

# Create a new database
CREATE DATABASE fastapi_db;

# Create a user (optional, you can use postgres)
CREATE USER fastapi_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE fastapi_db TO fastapi_user;

# Exit
\q

Step 3: Set Up Python Environment

# Create project directory
mkdir fastapi-postgres && cd fastapi-postgres

# Create virtual environment
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

# Install dependencies
pip install fastapi uvicorn sqlalchemy psycopg2-binary pydantic-settings

Create requirements.txt:

fastapi>=0.109.0
uvicorn>=0.27.0
sqlalchemy>=2.0.0
psycopg2-binary>=2.9.9
pydantic-settings>=2.1.0

Step 4: Create Database Configuration

Create app/database.py:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from pydantic_settings import BaseSettings


class Settings(BaseSettings):
    database_url: str = "postgresql://postgres:password@localhost:5432/fastapi_db"

    class Config:
        env_file = ".env"


settings = Settings()

engine = create_engine(settings.database_url)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


def get_db():
    """Dependency that provides a database session."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Step 5: Define Models

Create app/models.py:

from sqlalchemy import Column, Integer, String, Boolean, DateTime, func
from .database import Base


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=False)
    name = Column(String, nullable=False)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())


class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True, nullable=False)
    description = Column(String, nullable=True)
    owner_id = Column(Integer, index=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

Step 6: Create Pydantic Schemas

Create app/schemas.py:

from pydantic import BaseModel, EmailStr
from datetime import datetime


# User schemas
class UserBase(BaseModel):
    email: EmailStr
    name: str


class UserCreate(UserBase):
    pass


class UserUpdate(BaseModel):
    name: str | None = None
    is_active: bool | None = None


class User(UserBase):
    id: int
    is_active: bool
    created_at: datetime
    updated_at: datetime | None = None

    class Config:
        from_attributes = True


# Item schemas
class ItemBase(BaseModel):
    title: str
    description: str | None = None


class ItemCreate(ItemBase):
    owner_id: int


class Item(ItemBase):
    id: int
    owner_id: int
    created_at: datetime

    class Config:
        from_attributes = True

Step 7: Implement CRUD Operations

Create app/crud.py:

from sqlalchemy.orm import Session
from . import models, schemas


# User CRUD
def get_user(db: Session, user_id: int):
    return db.query(models.User).filter(models.User.id == user_id).first()


def get_user_by_email(db: Session, email: str):
    return db.query(models.User).filter(models.User.email == email).first()


def get_users(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.User).offset(skip).limit(limit).all()


def create_user(db: Session, user: schemas.UserCreate):
    db_user = models.User(email=user.email, name=user.name)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user


def update_user(db: Session, user_id: int, user: schemas.UserUpdate):
    db_user = get_user(db, user_id)
    if db_user:
        update_data = user.model_dump(exclude_unset=True)
        for key, value in update_data.items():
            setattr(db_user, key, value)
        db.commit()
        db.refresh(db_user)
    return db_user


def delete_user(db: Session, user_id: int):
    db_user = get_user(db, user_id)
    if db_user:
        db.delete(db_user)
        db.commit()
        return True
    return False


# Item CRUD
def get_items(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.Item).offset(skip).limit(limit).all()


def create_item(db: Session, item: schemas.ItemCreate):
    db_item = models.Item(**item.model_dump())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

Step 8: Create FastAPI Application

Create app/main.py:

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session

from . import crud, models, schemas
from .database import engine, get_db

# Create database tables
models.Base.metadata.create_all(bind=engine)

app = FastAPI(
    title="FastAPI PostgreSQL Demo",
    description="A demo API with PostgreSQL integration",
    version="1.0.0",
)


@app.get("/")
def root():
    return {"message": "FastAPI with PostgreSQL"}


# User endpoints
@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    db_user = crud.get_user_by_email(db, email=user.email)
    if db_user:
        raise HTTPException(status_code=400, detail="Email already registered")
    return crud.create_user(db=db, user=user)


@app.get("/users/", response_model=list[schemas.User])
def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    users = crud.get_users(db, skip=skip, limit=limit)
    return users


@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = crud.get_user(db, user_id=user_id)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user


@app.patch("/users/{user_id}", response_model=schemas.User)
def update_user(user_id: int, user: schemas.UserUpdate, db: Session = Depends(get_db)):
    db_user = crud.update_user(db, user_id=user_id, user=user)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user


@app.delete("/users/{user_id}")
def delete_user(user_id: int, db: Session = Depends(get_db)):
    success = crud.delete_user(db, user_id=user_id)
    if not success:
        raise HTTPException(status_code=404, detail="User not found")
    return {"message": "User deleted successfully"}


# Item endpoints
@app.post("/items/", response_model=schemas.Item)
def create_item(item: schemas.ItemCreate, db: Session = Depends(get_db)):
    return crud.create_item(db=db, item=item)


@app.get("/items/", response_model=list[schemas.Item])
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    items = crud.get_items(db, skip=skip, limit=limit)
    return items

Create app/__init__.py:

# Empty file to make app a package

Step 9: Create Environment File

Create .env:

DATABASE_URL=postgresql://postgres:password@localhost:5432/fastapi_db

Step 10: Run the Application

uvicorn app.main:app --reload

Visit http://localhost:8000/docs to see the interactive API documentation.

---

Part 2: With Docker

Now let’s containerize the same application using Docker and Docker Compose.

Step 1: Create Dockerfile

Create Dockerfile:

FROM python:3.12-slim

WORKDIR /app

# Install system dependencies
RUN apt-get update && apt-get install -y \
    gcc \
    libpq-dev \
    && rm -rf /var/lib/apt/lists/*

# Copy requirements first for better caching
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

# Copy application code
COPY app/ ./app/

# Run the application
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]

Step 2: Create Docker Compose File

Create compose.yaml:

services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: fastapi_user
      POSTGRES_PASSWORD: fastapi_password
      POSTGRES_DB: fastapi_db
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U fastapi_user -d fastapi_db"]
      interval: 5s
      timeout: 5s
      retries: 5

  api:
    build: .
    ports:
      - "8000:8000"
    environment:
      DATABASE_URL: postgresql://fastapi_user:fastapi_password@db:5432/fastapi_db
    depends_on:
      db:
        condition: service_healthy
    volumes:
      - ./app:/app/app  # For development hot-reload
    command: uvicorn app.main:app --host 0.0.0.0 --port 8000 --reload

volumes:
  postgres_data:

Step 3: Update Database Configuration for Docker

The app/database.py we created earlier already supports environment variables, so it will automatically use the DATABASE_URL from Docker Compose.

Step 4: Run with Docker Compose

# Build and start containers
docker compose up -d --build

# View logs
docker compose logs -f api

# Stop containers
docker compose down

# Stop and remove volumes (reset database)
docker compose down -v

Visit http://localhost:8000/docs to access the API.

Step 5: Useful Docker Commands

# Execute commands in the running container
docker compose exec api python -c "from app.database import engine; print('DB connected!')"

# Access PostgreSQL directly
docker compose exec db psql -U fastapi_user -d fastapi_db

# View all tables
docker compose exec db psql -U fastapi_user -d fastapi_db -c "\dt"

# Rebuild only the API service
docker compose up -d --build api

---

Testing the API

Once running (either locally or with Docker), test the API:

# Create a user
curl -X POST "http://localhost:8000/users/" \
  -H "Content-Type: application/json" \
  -d '{"email": "john@example.com", "name": "John Doe"}'

# Get all users
curl "http://localhost:8000/users/"

# Get a specific user
curl "http://localhost:8000/users/1"

# Update a user
curl -X PATCH "http://localhost:8000/users/1" \
  -H "Content-Type: application/json" \
  -d '{"name": "John Smith"}'

# Create an item
curl -X POST "http://localhost:8000/items/" \
  -H "Content-Type: application/json" \
  -d '{"title": "My Item", "description": "A test item", "owner_id": 1}'

# Delete a user
curl -X DELETE "http://localhost:8000/users/1"

---

Comparison: Local vs Docker

Aspect	Local Setup	Docker Setup
Setup complexity	Higher (install PostgreSQL)	Lower (just Docker)
Portability	Machine-dependent	Runs anywhere
Development	Faster iteration	Slightly slower builds
Production parity	Varies	Consistent
Team onboarding	Manual setup	Single command
Database persistence	System PostgreSQL	Docker volume

When to Use Each

Use Local Setup when:

• You need maximum development speed
• You already have PostgreSQL installed
• You’re debugging database issues directly

Use Docker when:

• Working in a team (consistent environments)
• Deploying to production (same setup)
• You don’t want to install PostgreSQL locally
• You need to test with specific PostgreSQL versions

---

Next Steps

• Add Alembic for database migrations
• Implement authentication with JWT
• Add async SQLAlchemy for better performance
• Set up pytest for testing
• Add Redis for caching

Conclusion

We’ve built a complete FastAPI application with PostgreSQL integration, covering both local development and Docker-based setups. The Docker approach provides better portability and team collaboration, while local setup offers faster iteration during development.

Choose the approach that best fits your workflow, or use both: local for quick development and Docker for testing production-like environments.