Create a CRUD Example with FastAPI and MySQL Then Deploy using Docker and Docker Compose

Originally built 2028, FastAPI is a lightweight web framework for building HTTP-based service APIs in Python 3.8+.

It uses Pydantic and type hints to validate, serialize and deserialize data. It also automatically generates OpenAPI documentation for APIs built with it.

In this tutorial, we'll create a simple CRUD application using FastAPI and MySQL. We'll cover setting up the environment, defining models, and implementing CRUD (Create, Read, Update, Delete) operations.

Requirements

• Basic knowledge of Python
• Python package manager
• Python setup

Tools used

• uvicorn: Uvicorn is an ASGI web server implementation for Python.
• SQLAlchemy
• PyMysql
• Alembic

1- Setup the Environment

First, ensure you have Python and MySQL installed on your system. Then, create a virtual environment and install the necessary dependencies.

python3 -m venv venv
source venv/bin/activate
pip install fastapi uvicorn sqlalchemy pymysql alembic

2- Setting Up MySQL Database

Create a MySQL database for your blog.

CREATE DATABASE fastapi_blog;

In your project, create a file named database.py to handle the connection to MySQL.

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "mysql+pymysql://username:password@localhost:3306/fastapi_blog"

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

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

Replace username and password with your MySQL credentials.

3- Defining the Models

Next, define your models for the blog posts. Create a models.py file.

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

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(255), index=True)
    content = Column(Text, nullable=False)

In the following we will use Alembic which is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python.

Run Alembic to create the database table.

alembic init alembic

Edit alembic/env.py to include your database connection.

from myapp.database import Base
target_metadata = Base.metadata

Create a migration file and apply the migration.

alembic revision --autogenerate -m "create posts table"
alembic upgrade head

4- Creating the CRUD Operations

Now, let's implement the CRUD operations. Create a crud.py file.

from sqlalchemy.orm import Session
from .models import Post
from .schemas import PostCreate, PostUpdate

def get_post(db: Session, post_id: int):
    return db.query(Post).filter(Post.id == post_id).first()

def get_posts(db: Session, skip: int = 0, limit: int = 10):
    return db.query(Post).offset(skip).limit(limit).all()

def create_post(db: Session, post: PostCreate):
    db_post = Post(title=post.title, content=post.content)
    db.add(db_post)
    db.commit()
    db.refresh(db_post)
    return db_post

def update_post(db: Session, post_id: int, post: PostUpdate):
    db_post = get_post(db, post_id)
    if db_post:
        db_post.title = post.title
        db_post.content = post.content
        db.commit()
        db.refresh(db_post)
    return db_post

def delete_post(db: Session, post_id: int):
    db_post = get_post(db, post_id)
    if db_post:
        db.delete(db_post)
        db.commit()
    return db_post

5- Creating the Schemas

Create a schemas.py file to define your Pydantic models.

from pydantic import BaseModel

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

class PostCreate(PostBase):
    pass

class PostUpdate(PostBase):
    pass

class PostInDB(PostBase):
    id: int

    class Config:
        orm_mode = True

6- Building the API Endpoints

Now, create a main.py file to set up the FastAPI application and endpoints.

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

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

app = FastAPI()

@app.post("/posts/", response_model=schemas.PostInDB)
def create_post(post: schemas.PostCreate, db: Session = Depends(get_db)):
    return crud.create_post(db=db, post=post)

@app.get("/posts/", response_model=list[schemas.PostInDB])
def read_posts(skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):
    return crud.get_posts(db=db, skip=skip, limit=limit)

@app.get("/posts/{post_id}", response_model=schemas.PostInDB)
def read_post(post_id: int, db: Session = Depends(get_db)):
    db_post = crud.get_post(db=db, post_id=post_id)
    if db_post is None:
        raise HTTPException(status_code=404, detail="Post not found")
    return db_post

@app.put("/posts/{post_id}", response_model=schemas.PostInDB)
def update_post(post_id: int, post: schemas.PostUpdate, db: Session = Depends(get_db)):
    return crud.update_post(db=db, post_id=post_id, post=post)

@app.delete("/posts/{post_id}", response_model=schemas.PostInDB)
def delete_post(post_id: int, db: Session = Depends(get_db)):
    return crud.delete_post(db=db, post_id=post_id)

7- Running the Application

Finally, run the FastAPI application using Uvicorn.

uvicorn main:app --reload

Now, you can visit http://127.0.0.1:8000 and start testing your CRUD operations.

Deploy using Docker and Docker Compose

To deploy the FastAPI application using Docker and Docker Compose, follow these steps:

Create a Dockerfile

In the root directory of your project, create a Dockerfile to define the Docker image for your FastAPI application.

# Use an official Python runtime as a parent image
FROM python:3.10-slim

# Set the working directory
WORKDIR /app

# Copy the current directory contents into the container at /app
COPY . /app

# Install any needed packages specified in requirements.txt
RUN pip install --no-cache-dir -r requirements.txt

# Make port 8000 available to the world outside this container
EXPOSE 8000

# Run the application
CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8000"]

Create a requirements.txt File

Make sure you have a requirements.txt file that includes all the dependencies for your FastAPI application. If you don’t have it, you can create it with:

pip freeze > requirements.txt

Create a docker-compose.yml File

Next, create a docker-compose.yml file to define the services, including your FastAPI app and MySQL database.

version: '3.8'

services:
  db:
    image: mysql:8.0
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - mysql_data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: yourpassword
      MYSQL_DATABASE: fastapi_blog
      MYSQL_USER: youruser
      MYSQL_PASSWORD: yourpassword
    ports:
      - "3306:3306"

  web:
    build: .
    command: uvicorn main:app --host 0.0.0.0 --port 8000
    volumes:
      - .:/app
    ports:
      - "8000:8000"
    depends_on:
      - db
    environment:
      DATABASE_URL: "mysql+pymysql://youruser:yourpassword@db:3306/fastapi_blog"

volumes:
  mysql_data:

Replace youruser and yourpassword with your MySQL credentials.

Build and Run the Containers

Now that you have the Dockerfile and docker-compose.yml ready, you can build and start the containers.

docker-compose up --build

This command will build the Docker image for your FastAPI app and start both the FastAPI app and MySQL database as Docker containers.

Access your Application

Once the containers are up and running, you can access your FastAPI application by navigating to http://localhost:8000 in your web browser. The API will be accessible through this URL, and you can use tools like Postman or CURL to interact with it.

Running Migrations with Alembic

If you are using Alembic for database migrations, you'll need to run the migrations inside the container.

First, connect to the running web container:

docker-compose exec web bash

Then, run the migration commands:

alembic upgrade head

This will apply any pending migrations to your MySQL database.

Stopping the Containers

To stop the running containers, you can use:

docker-compose down

This command will stop and remove the containers but keep the data intact.

Cleaning Up

If you want to remove the containers, volumes, and networks created by Docker Compose, run:

docker-compose down --volumes --rmi all --remove-orphans

This will clean up everything, including the Docker images.

This simple blog app demonstrates how to use FastAPI with MySQL to perform basic CRUD operations. You can expand this by adding user authentication, validation, and more advanced features as needed.

Then we explain how to deploy your created FastAPI application with MySQL in a containerized environment, making it easy to manage and scale your application.