Home >  Programming Blog >  Python

Build a Python REST API with FastAPI and PostgreSQL: Step-by-Step Guide

by Didin J. on Jul 10, 2025 Build a Python REST API with FastAPI and PostgreSQL: Step-by-Step Guide

Learn how to build a Python REST API with FastAPI and PostgreSQL step-by-step. Create, read, update, and delete data using SQLAlchemy and Pydantic.

Table of Contents:

FastAPI has quickly become one of the most popular web frameworks in the Python ecosystem — and for good reason. It’s fast, intuitive, and comes with automatic Swagger documentation. When combined with PostgreSQL, a powerful and reliable relational database, it provides an excellent foundation for building scalable backend services and APIs.

In this step-by-step tutorial, you’ll learn how to build a simple CRUD REST API using FastAPI and PostgreSQL. We’ll use SQLAlchemy for object-relational mapping (ORM), Pydantic for data validation, and Uvicorn to run the development server.

By the end of this guide, you will have a fully functional REST API that allows you to create, read, update, and delete records from a PostgreSQL database — all with minimal boilerplate and maximum efficiency.

Prerequisites

Before we begin, make sure you have the following installed on your system:

  • Python 3.10 or higher
    You can check your version with python --version.

  • PostgreSQL
    Ensure PostgreSQL is installed and running. You’ll need a database and user credentials.

  • pip and virtualenv
    These are Python tools to manage packages and isolated environments.

  • Basic familiarity with Python
    You don’t need to be an expert, but having some experience with functions and modules will be helpful.

You’ll also need a terminal (Command Prompt, PowerShell, or Unix shell) and a code editor like VS Code, PyCharm, or Sublime Text.


Project Setup

Let’s start by setting up a clean working environment for your FastAPI project.

  1. Create a project directory: 

    mkdir fastapi-postgres-crud
cd fastapi-postgres-crud

  2. Create a virtual environment and activate it:

    python3 -m venv venv
source venv/bin/activate # On macOS/Linux
venv\Scripts\activate # On Windows

  3. Install the required packages: 

    pip install fastapi uvicorn sqlalchemy psycopg2-binary python-dotenv

    Explanation of packages:

    • fastapi: The core web framework.

    • uvicorn: ASGI server to run the app.

    • sqlalchemy: ORM to interact with PostgreSQL.

    • psycopg2-binary: PostgreSQL driver for Python.

    • python-dotenv: Load environment variables from a .env file.

  4. Create the project structure:

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

  5. Freeze your dependencies:

    pip freeze > requirements.txt

Initialize FastAPI App

Let’s get your FastAPI app up and running.

  1. Create app/main.py:

    from fastapi import FastAPI app = FastAPI() @app.get("/") def read_root(): return {"message": "Welcome to the FastAPI + PostgreSQL CRUD API"}

  2. Run the FastAPI app with Uvicorn:

    uvicorn app.main:app --reload

  3. Open in browser:

    Visit http://127.0.0.1:8000 to see the welcome message. Build a Python REST API with FastAPI and PostgreSQL: Step-by-Step Guide - welcome

  4. API Docs:


Configure PostgreSQL Connection and Create Models using SQLAlchemy

Configure PostgreSQL Connection

Let’s configure the database connection using SQLAlchemy and python-dotenv to manage environment variables securely.

1. Create a .env file in the root directory:

DATABASE_URL=postgresql://djamware:dj%40mw%40r3@localhost:5432/fastapi_db

Replace your_user, your_password, and your_db with your actual PostgreSQL credentials.

psql postgres -U djamware
postgres=> create database fastapi_db;
postgres=> \q

2. Create app/database.py:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv
import os

load_dotenv()  # Load environment variables from .env

DATABASE_URL = os.getenv("DATABASE_URL")

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

# Dependency to get DB session
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Create Models

Now let’s define a simple model. We’ll use a basic Item table with id, title, and description.

1. Create app/models.py:

from sqlalchemy import Column, Integer, String
from .database import Base

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    description = Column(String, index=True)

2. Create the database and table:

You can use Python to initialize the table:

# run this once in a script or interactive shell
from app.database import Base, engine
from app.models import Item

Base.metadata.create_all(bind=engine)

Or create a new script app/init_db.py:

from .database import Base, engine
from .models import Item

Base.metadata.create_all(bind=engine)

Then run it:

python -m app.init_db


Pydantic Schemas and CRUD Operations

Create Pydantic Schemas

Schemas define how data is sent and received via the API. Let’s define request and response models using Pydantic.

1. Create app/schemas.py:

from pydantic import BaseModel

class ItemBase(BaseModel):
    title: str
    description: str

class ItemCreate(ItemBase):
    pass

class ItemUpdate(ItemBase):
    pass

class Item(ItemBase):
    id: int

    class Config:
        orm_mode = True

  • ItemBase: Common fields shared between create and update.

  • ItemCreate and ItemUpdate: Inherit from ItemBase.

  • Item: Used for reading data, includes the id.

Create CRUD Operations

Now let’s encapsulate our database operations.

2. Create app/crud.py:

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

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

def get_item(db: Session, item_id: int):
    return db.query(models.Item).filter(models.Item.id == item_id).first()

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

def update_item(db: Session, item_id: int, item: schemas.ItemUpdate):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()
    if db_item:
        db_item.title = item.title
        db_item.description = item.description
        db.commit()
        db.refresh(db_item)
    return db_item

def delete_item(db: Session, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()
    if db_item:
        db.delete(db_item)
        db.commit()
    return db_item


Integrating CRUD Operations Into FastAPI Routes

Build CRUD Routes in FastAPI

You’ll now connect the database session, schemas, and CRUD functions into route handlers.

1. Update app/main.py:

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from . import models, schemas, crud
from .database import engine, get_db

models.Base.metadata.create_all(bind=engine)

app = FastAPI()

@app.get("/")
def read_root():
    return {"message": "Welcome to the FastAPI + PostgreSQL CRUD API"}

@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 = 10, db: Session = Depends(get_db)):
    return crud.get_items(db, skip=skip, limit=limit)

@app.get("/items/{item_id}", response_model=schemas.Item)
def read_item(item_id: int, db: Session = Depends(get_db)):
    db_item = crud.get_item(db, item_id=item_id)
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    return db_item

@app.put("/items/{item_id}", response_model=schemas.Item)
def update_item(item_id: int, item: schemas.ItemUpdate, db: Session = Depends(get_db)):
    db_item = crud.update_item(db, item_id=item_id, item=item)
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    return db_item

@app.delete("/items/{item_id}", response_model=schemas.Item)
def delete_item(item_id: int, db: Session = Depends(get_db)):
    db_item = crud.delete_item(db, item_id=item_id)
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    return db_item

Run and Test the API

1. Start the server:

uvicorn app.main:app --reload

2. Visit Swagger UI:

Open http://127.0.0.1:8000/docs in your browser.

  • You’ll see all the routes auto-documented.

  • Test creating, reading, updating, and deleting items.

3. Or test using curl/Postman

Example:

curl -X POST "http://127.0.0.1:8000/items/" -H "Content-Type: application/json" -d '{"title":"Book","description":"FastAPI Guide"}'


Conclusion + Key Takeaways

Conclusion

In this tutorial, you learned how to build a full-featured REST API using FastAPI and PostgreSQL with SQLAlchemy and Pydantic. From setting up the project and connecting to the database to creating models, schemas, and CRUD endpoints, you now have a solid foundation for developing modern and efficient APIs in Python.

Key Takeaways:

  • FastAPI simplifies API development with automatic docs and async support.

  • PostgreSQL is a powerful and reliable open-source relational database management system.

  • SQLAlchemy allows for expressive and flexible ORM-based database interactions.

  • Pydantic ensures data validation and serialization.

  • FastAPI's dependency injection system makes working with database sessions clean and scalable.

This architecture is easily extendable — you can add authentication (e.g., OAuth2), background tasks, WebSockets, and more as your application grows. You might also consider containerizing the app using Docker and applying database migrations using Alembic in production environments.

You can get the full source code on our GitHub.

=====

That's just the basics. If you need more deep learning about Python and the frameworks, you can take the following cheap course:

Thanks!

Related Articles

Programming Blog
JavaJavascriptHTML 5 TutorialCSS 3MongoDBIonic FrameworkGroovy and GrailsNode.jsReact NativeASP.NET CoreAngularVue.jsReact.jsFlutter TutorialMiscellaneousRustGolang (Go)Python
All Articles
Zizo Wireless