Building RESTful APIs in Python with FastAPI and SQLAlchemy

Matthew

In today’s software world, RESTful APIs are the backbone of most modern applications — from mobile apps and web frontends to microservices.
If you’re a Python developer, you can build a robust API fast with FastAPI and SQLAlchemy.

This post walks you through:

  1. What a RESTful API is
  2. How to build one in Python using FastAPI
  3. How to add a database layer with SQLAlchemy for real CRUD functionality

🚀 What Is a RESTful API?

REST (Representational State Transfer) defines how systems communicate over HTTP.
Each endpoint represents a resource (like users or posts) and uses standard HTTP methods:

MethodActionExample
GETRetrieve data/posts
POSTCreate new data/posts
PUTUpdate data/posts/1
DELETEDelete data/posts/1

Responses are usually in JSON, making them easy to consume from frontend frameworks like React or mobile apps.

⚙️ Setting Up the Project

Create a virtual environment and install dependencies:

python -m venv venv
source venv/bin/activate  # or venv\Scripts\activate on Windows
pip install fastapi uvicorn sqlalchemy

🧱 Basic REST API with FastAPI

Here’s a simple version without a database:

# main.py
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel

app = FastAPI()

posts = []

class Post(BaseModel):
    id: int
    title: str
    content: str

@app.get("/posts")
def get_posts():
    return posts

@app.post("/posts", status_code=201)
def create_post(post: Post):
    posts.append(post.dict())
    return post

Run it:

uvicorn main:app --reload<br>

Then go to http://127.0.0.1:8000/docs to test your endpoints in the interactive Swagger UI.

💾 Adding a Real Database with SQLAlchemy

Let’s upgrade this to a real database-backed API using SQLAlchemy.

Install dependencies:

pip install sqlalchemy

1️⃣ Set up the Database Engine and Session

# database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./blog.db"

engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

2️⃣ Create the Model

# models.py
from sqlalchemy import Column, Integer, String
from database import Base

class Post(Base):
    __tablename__ = "posts"

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

3️⃣ Create Pydantic Schemas

# schemas.py
from pydantic import BaseModel

class PostBase(BaseModel):
    title: str
    content: str

class PostCreate(PostBase):
    pass

class PostResponse(PostBase):
    id: int

    class Config:
        orm_mode = True

4️⃣ CRUD Operations and Routes

# main.py
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
import models, schemas
from database import SessionLocal, engine

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

app = FastAPI()

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

@app.post("/posts", response_model=schemas.PostResponse)
def create_post(post: schemas.PostCreate, db: Session = Depends(get_db)):
    db_post = models.Post(title=post.title, content=post.content)
    db.add(db_post)
    db.commit()
    db.refresh(db_post)
    return db_post

@app.get("/posts", response_model=list[schemas.PostResponse])
def get_posts(db: Session = Depends(get_db)):
    return db.query(models.Post).all()

@app.get("/posts/{post_id}", response_model=schemas.PostResponse)
def get_post(post_id: int, db: Session = Depends(get_db)):
    post = db.query(models.Post).filter(models.Post.id == post_id).first()
    if not post:
        raise HTTPException(status_code=404, detail="Post not found")
    return post

@app.put("/posts/{post_id}", response_model=schemas.PostResponse)
def update_post(post_id: int, updated_post: schemas.PostCreate, db: Session = Depends(get_db)):
    post = db.query(models.Post).filter(models.Post.id == post_id).first()
    if not post:
        raise HTTPException(status_code=404, detail="Post not found")
    post.title = updated_post.title
    post.content = updated_post.content
    db.commit()
    db.refresh(post)
    return post

@app.delete("/posts/{post_id}", status_code=204)
def delete_post(post_id: int, db: Session = Depends(get_db)):
    post = db.query(models.Post).filter(models.Post.id == post_id).first()
    if not post:
        raise HTTPException(status_code=404, detail="Post not found")
    db.delete(post)
    db.commit()

5️⃣ Run the API

Start the server:

uvicorn main:app --reload

Visit:

  • Docs: http://127.0.0.1:8000/docs
  • DB: blog.db (SQLite file created automatically)

🧩 Summary

You now have:

  • ✅ A RESTful API with FastAPI
  • ✅ Real CRUD operations using SQLAlchemy
  • ✅ Auto-generated docs with Swagger
  • ✅ A ready-to-extend structure (add authentication, pagination, etc.)

🔮 Next Steps

Once you have CRUD working, try adding:

  • JWT authentication with fastapi.security
  • Alembic for database migrations
  • Docker to containerize your API
  • React or Vue frontend to consume the API

💡 Takeaway

FastAPI + SQLAlchemy gives you everything you need for modern API development:

  • Performance
  • Type safety
  • Automatic validation and documentation
  • Clean, testable architecture

Build small, build clean, and iterate — your first production-grade Python API is just a few commands away.

Related Posts

Leave a Reply