FastAPI has quickly become a favorite among Python developers for building high-performance APIs. When paired with PostgreSQL, a powerful and reliable relational database, it forms a solid foundation for modern backend applications.
In this guide, you’ll learn how to integrate FastAPI with PostgreSQL step by step — from setup to database connections, CRUD operations, and production-ready best practices.
FastAPI + PostgreSQL Overview
FastAPI is a modern Python web framework designed for speed, simplicity, and scalability. It is built on top of Starlette and Pydantic, which means you get async support, automatic validation, and API documentation out of the box.
PostgreSQL, on the other hand, is an enterprise-grade open-source database known for:
- Strong data integrity
- Advanced indexing
- Excellent performance for complex queries
- Long-term reliability
Together, FastAPI + PostgreSQL is a common stack for SaaS products, internal tools, and high-traffic APIs.
Prerequisites
Before starting, make sure you have:
- Python 3.9+
- PostgreSQL installed and running
Basic understanding of SQL and Python
Step 1: Project Setup
Create a virtual environment and install the required dependencies:
pip install fastapi uvicorn sqlalchemy psycopg2-binary python-dotenv
Why these packages?
- fastapi: Web framework
- uvicorn: ASGI server
- sqlalchemy: ORM for database operations
- psycopg2-binary: PostgreSQL driver
python-dotenv: Manage environment variables
Step 2: Configure PostgreSQL Database
Create a PostgreSQL database:
CREATE DATABASE fastapi_db;
Create a .env file to store credentials securely:
DATABASE_URL=postgresql://username:password@localhost:5432/fastapi_db
This approach keeps sensitive data out of your codebase.
Step 3: Database Connection in FastAPI
Create a database.py file:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
import os
from dotenv import load_dotenv
load_dotenv()
DATABASE_URL = os.getenv(“DATABASE_URL”)
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)
Base = declarative_base()
This setup:
- Establishes a PostgreSQL connection
- Creates a reusable database session
- Defines a base class for models
Step 4: Create a Model
Create a models.py file:
from sqlalchemy import Column, Integer, String
from database import Base
class User(Base):
__tablename__ = “users”
id = Column(Integer, primary_key=True, index=True)
name = Column(String, nullable=False)
email = Column(String, unique=True, index=True)
Create tables:
from database import engine
from models import Base
Base.metadata.create_all(bind=engine)
Step 5: CRUD Operations with FastAPI
Dependency for Database Session
from database import SessionLocal
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
Create (POST)
from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
from models import User
app = FastAPI()
@app.post(“/users/”)
def create_user(name: str, email: str, db: Session = Depends(get_db)):
user = User(name=name, email=email)
db.add(user)
db.commit()
db.refresh(user)
return user
Read (GET)
@app.get(“/users/”)
def get_users(db: Session = Depends(get_db)):
return db.query(User).all()
Update (PUT)
@app.put(“/users/{user_id}”)
def update_user(user_id: int, name: str, db: Session = Depends(get_db)):
user = db.query(User).filter(User.id == user_id).first()
if not user:
return {“error”: “User not found”}
user.name = name
db.commit()
return user
Delete (DELETE)
@app.delete(“/users/{user_id}”)
def delete_user(user_id: int, db: Session = Depends(get_db)):
user = db.query(User).filter(User.id == user_id).first()
if not user:
return {“error”: “User not found”}
db.delete(user)
db.commit()
return {“message”: “User deleted”}
Step 6: Run the Application
Start the server:
uvicorn main:app –reload
Visit:
- API Docs: http://127.0.0.1:8000/docs
- Redoc: http://127.0.0.1:8000/redoc
FastAPI automatically generates interactive documentation — a big productivity win.
