Additional CRUD FastAPI - Demo - Real Database

Response Screen Shots:

FastAPI + PostgreSQL CRUD Tutorial

Complete student lab — code blocks are editable, copyable and downloadable.

Jump to Code

Overview — What students will build

A FastAPI application connected to PostgreSQL with full CRUD (Create, Read, Update, Delete). The tutorial includes project files, installation steps and run commands suitable for VS Code / local lab setup.

Step 1 — Install PostgreSQL

Install & create database fastapi_demo.

Step 2 — Python Dependencies

Install FastAPI, SQLAlchemy and psycopg2.

Step 3 — Project Files

database.py, models.py, schemas.py, main.py.

Step 4 — Run & Test

Run with uvicorn and test at /docs.

Installation & Database Setup

1. Install PostgreSQL

Download from postgresql.org. During install set a password (example uses admin123).

2. Create the database

CREATE DATABASE fastapi_demo;

3. Install Python packages

pip install fastapi uvicorn sqlalchemy psycopg2-binary pydantic

Tip: Use a virtual environment (recommended): python -m venv .venv and activate it before installing.

Project Structure

fastapi_postgres_crud/
├── database.py      # DB connection & session
├── models.py        # SQLAlchemy models
├── schemas.py       # Pydantic schemas
└── main.py          # FastAPI app with CRUD endpoints
        

Open the folder in VS Code. Create the 4 files below and paste the code (copy buttons provided).

Code Files (copy & use)

Click Copy to copy the file contents to clipboard, or Download to save as a file.

database.py

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

# Replace username:password@host/dbname as needed
# DATABASE_URL = "postgresql://postgres:admin123@localhost/fastapi_demo"
DATABASE_URL = "postgresql://postgres:admin123@localhost/postgres"  #Skip Step 2 Create Database

# create synchronous engine (SQLAlchemy ORM)
engine = create_engine(DATABASE_URL)

# each request will use a session from this factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()
          

models.py

from sqlalchemy import Column, Integer, String, Float, Boolean
from database import Base

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    price = Column(Float, nullable=False)
    description = Column(String)
    in_stock = Column(Boolean, default=True)
          

schemas.py

from pydantic import BaseModel
from typing import Optional

class ItemBase(BaseModel):
    name: str
    price: float
    description: Optional[str] = None
    in_stock: bool = True

class ItemCreate(ItemBase):
    pass

class Item(ItemBase):
    id: int

    class Config:
        orm_mode = True
          

main.py

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from database import SessionLocal, engine
import models
from models import Item
from schemas import ItemCreate, Item as ItemSchema

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

app = FastAPI(title="FastAPI + PostgreSQL CRUD Demo")

# Dependency: DB session per-request
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


# CREATE
@app.post("/items/", response_model=ItemSchema)
def create_item(item: ItemCreate, db: Session = Depends(get_db)):
    db_item = Item(**item.dict())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item


# READ ALL
@app.get("/items/", response_model=list[ItemSchema])
def read_items(db: Session = Depends(get_db)):
    return db.query(Item).all()


# READ ONE
@app.get("/items/{item_id}", response_model=ItemSchema)
def read_item(item_id: int, db: Session = Depends(get_db)):
    item = db.query(Item).filter(Item.id == item_id).first()
    if not item:
        raise HTTPException(404, "Item not found")
    return item


# UPDATE
@app.put("/items/{item_id}", response_model=ItemSchema)
def update_item(item_id: int, updated_item: ItemCreate, db: Session = Depends(get_db)):
    item = db.query(Item).filter(Item.id == item_id).first()
    if not item:
        raise HTTPException(404, "Item not found")

    for key, value in updated_item.dict().items():
        setattr(item, key, value)

    db.commit()
    db.refresh(item)
    return item


# DELETE
@app.delete("/items/{item_id}")
def delete_item(item_id: int, db: Session = Depends(get_db)):
    item = db.query(Item).filter(Item.id == item_id).first()
    if not item:
        raise HTTPException(404, "Item not found")

    db.delete(item)
    db.commit()

    return {"message": "Deleted successfully", "id": item_id}
          

Run & Test

Run the server

uvicorn main:app --reload

Open Swagger UI

Visit http://127.0.0.1:8000/docs to interact with the API.

If you get a DB connection error, check your DATABASE_URL in database.py and ensure PostgreSQL is running and listening on the host.

Student Exercises (optional)

1. Add a category column to models.py and update schemas + CRUD.
2. Add a query param to /items/ to filter by price range.
3. Implement pagination (limit, offset).

Quick Links

Code Files Main App Overview

Notes for Teacher

Use the /docs page to demonstrate live requests. Encourage students to alter and re-run for hands-on learning.

Responses

GET Method:

POST Method

PUT Method

DELETE Method

© FastAPI Lab — Paste this HTML into Blogger HTML view. Replace DB creds before publishing.