FastAPI & SQLAlchemy: MySQL Setup Guide

Python · FastAPI · MySQL · SQLAlchemy

Introduction

FastAPI is a strong fit for modern Python APIs. This walkthrough connects it to MySQL through SQLAlchemy and adds Pydantic models for request/response validation. By the end you have a working app with users, todos, and dependency-injected database sessions.

Guide overview

You will install dependencies, lay out a small app package, define the engine and models, add CRUD helpers, and expose REST endpoints with uvicorn.

Installation

pip install fastapi "uvicorn[standard]"
pip install python-dotenv
pip install sqlalchemy
pip install pymysql

That gives you FastAPI, an ASGI server, env loading, SQLAlchemy, and the MySQL driver used in the connection URL.

Requirements

• A running MySQL server and an empty database (create one named to match your .env).

Folder layout

app/
  __init__.py
  main.py
  database.py
  models.py
  schema.py
  crud.py
.env
requirements.txt

Database engine — app/database.py

import os

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

load_dotenv()

DB_URL = os.getenv("DB_URL")
engine = create_engine(DB_URL, echo=True)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

DB_URL should be a SQLAlchemy URL such as mysql+pymysql://user:pass@localhost:3306/dbname.

Models — app/models.py

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from .database import Base


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), index=True)
    email = Column(String(255), unique=True, index=True)
    todos = relationship("Todo", back_populates="owner")
    is_active = Column(Boolean, default=False)


class Todo(Base):
    __tablename__ = "todos"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(255), index=True)
    description = Column(String(255), index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))

    owner = relationship("User", back_populates="todos")

Pydantic schemas — app/schemas.py

Uses Pydantic v2 (model_config + from_attributes) so responses can be built from ORM objects.

from pydantic import BaseModel, ConfigDict


class TodoBase(BaseModel):
    title: str
    description: str | None = None


class TodoCreate(TodoBase):
    pass


class Todo(TodoBase):
    id: int
    owner_id: int

    model_config = ConfigDict(from_attributes=True)


class UserBase(BaseModel):
    email: str
    name: str


class UserCreate(UserBase):
    pass


class User(UserBase):
    id: int
    is_active: bool
    todos: list[Todo] = []

    model_config = ConfigDict(from_attributes=True)

CRUD helpers — app/crud.py

from sqlalchemy.orm import Session

from . import models, schemas


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 get_todos(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.Todo).offset(skip).limit(limit).all()


def create_user_todo(db: Session, todo: schemas.TodoCreate, user_id: int):
    db_todo = models.Todo(**todo.model_dump(), owner_id=user_id)
    db.add(db_todo)
    db.commit()
    db.refresh(db_todo)
    return db_todo

Each write path adds the row, commits, then refreshes so IDs and defaults come back from the database.

FastAPI app — app/main.py

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

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

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

app = FastAPI()


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


@app.post("/users/", response_model=schemas.User)
def post_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)):
    return crud.get_users(db, skip=skip, limit=limit)


@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.post("/users/{user_id}/todos/", response_model=schemas.Todo)
def post_todo_for_user(
    user_id: int, todo: schemas.TodoCreate, db: Session = Depends(get_db)
):
    return crud.create_user_todo(db=db, user_id=user_id, todo=todo)


@app.get("/todos/", response_model=list[schemas.Todo])
def read_todos(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    return crud.get_todos(db, skip=skip, limit=limit)

Note: create_all is fine for tutorials; in production prefer migrations (e.g. Alembic).

Environment — .env

Replace placeholders with your MySQL user, password, host, port, and database name:

DB_URL=mysql+pymysql://db_username:db_password@localhost:3306/db_name

requirements.txt

fastapi
uvicorn[standard]
python-dotenv
sqlalchemy
pymysql
pydantic>=2

Run

From the project root (parent of app/):

uvicorn app.main:app --reload

Open http://127.0.0.1:8000/docs for the interactive OpenAPI UI.

Conclusion

You now have FastAPI talking to MySQL through SQLAlchemy, with validated payloads and a small CRUD surface you can extend toward production patterns (migrations, settings objects, and tighter error handling).