Integrating FastAPI with SQLAlchemy and Flyway

In today’s fast-paced development environment, developers seek tools that enable them to build fast and scalable APIs and ensure robust database management. FastAPI is one such framework gaining rapid popularity for building modern web applications due to its high performance and ease of use. When paired with SQLAlchemy for database ORM (Object Relational Mapping) and Flyway for database migrations, this combination becomes a powerhouse for managing complex database schemas, especially when using MySQL.

In this article, we will walk through integrating FastAPI, SQLAlchemy, and Flyway for managing a MySQL database. We’ll touch on key setup steps, schema migration, and how to leverage these tools to maintain a scalable, maintainable database architecture.

Why SQLAlchemy and Flyway?

Before we dive into the integration, it’s important to understand why these tools are an essential part of the stack:

• SQLAlchemy: A versatile SQL toolkit and ORM for Python, SQLAlchemy allows you to map Python classes to database tables, providing an intuitive way to interact with your database. It abstracts away the complexity of raw SQL queries, making it easy to work with complex data structures.
• Flyway: A powerful, open-source database migration tool, Flyway tracks changes to your database schema over time and provides a structured way to apply these changes. It is designed to work with various databases, including MySQL, and is ideal for managing database versions and ensuring consistency across environments.

Setting Up FastAPI, SQLAlchemy, and Flyway with MySQL

Here’s a step-by-step guide on how to integrate FastAPI with SQLAlchemy and Flyway for MySQL database management.

Step 1: Install the Required Dependencies

First, ensure that you have MySQL installed and running. Next, let’s install the necessary Python dependencies:

pip install fastapi uvicorn sqlalchemy pymysql        

• FastAPI: The web framework for building APIs.
• SQLAlchemy: The ORM for database interactions.
• PyMySQL: The MySQL database driver.

Additionally, download and install Flyway from the official Flyway site.

Step 2: Project Structure

Let’s organize the project into a structured layout for easier management:

myapp/
│
├── app/
│   ├── models.py      # SQLAlchemy models
│   ├── database.py   # DB connection
│   ├── main.py          # FastAPI app
├── sql/                     # SQL migration files
│   └── V1__init.sql     # Initial schema
└── flyway.conf          # Flyway configuration file        

Step 3: Configuring SQLAlchemy with MySQL

Let’s start by configuring the MySQL database connection in database.py:

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

DATABASE_URL = "mysql+pymysql://user:password@localhost:3306/dbname"

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()        

This configuration establishes a connection to your MySQL database using SQLAlchemy's create_engine and sets up a session for querying and interacting with the database.

Step 4: Defining SQLAlchemy Models

In models.py, we’ll define a simple User model to represent users in our database:

from sqlalchemy import Column, Integer, String
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)
        

This Python class will map directly to the users table in the MySQL database.

Step 5: Setting Up Flyway for Database Migrations

With the database connection and models in place, let’s configure Flyway for handling database migrations.

Configuring Flyway

Flyway needs a configuration file to connect to your MySQL database. Create a flyway.conf file in the root of your project with the following content:

flyway.url=jdbc:mysql://localhost:3306/dbname
flyway.user=user
flyway.password=password
flyway.locations=filesystem:./sql        

The flyway.locations setting tells Flyway where to look for migration files.

Creating an Initial Migration

Let’s create our first migration file, V1__init.sql, in the sql/ directory:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255) UNIQUE
);        

This SQL file defines the schema for the users table.

Running the Migration

To apply the migration to your MySQL database, run the following command from your project directory:

flyway migrate        

Flyway will apply the V1__init.sql migration to your database, creating the users table.

Step 6: Creating the FastAPI Application

Now, let’s build a simple FastAPI application that interacts with the users table. In main.py, we’ll define a route to fetch all users from the database:

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
from . import models, database

app = FastAPI()

@app.on_event("startup")
def startup():
    models.Base.metadata.create_all(bind=database.engine)

@app.get("/users/")
def read_users(db: Session = Depends(database.get_db)):
    users = db.query(models.User).all()
    return users        

With this setup, FastAPI will retrieve all users from the MySQL database via SQLAlchemy.

Step 7: Handling Future Migrations

To handle changes to the schema, such as adding a new column, create a new migration file in the sql/ directory:

-- V2__add_age_to_users.sql
ALTER TABLE users ADD COLUMN age INT;        

Run the Flyway migration command again:

flyway migrate        

Flyway will apply this new schema change to your database, ensuring version control and consistency.

Conclusion

Integrating FastAPI with SQLAlchemy and Flyway for managing a MySQL database provides a solid foundation for building scalable applications with clean and maintainable database management practices. SQLAlchemy allows you to work with database records using Python classes, while Flyway ensures your schema evolves smoothly over time with minimal hassle.

By following this guide, you now have a complete setup for building FastAPI applications with robust database management using MySQL. Whether you’re working on a simple web app or a large-scale system, this combination will give you the flexibility and control you need.