FastAPI and Database Integration

How to Integrate a Database with FastAPI: A Step-by-Step Guide

Learn how to bridge your FastAPI backend with professional databases like MySQL and PostgreSQL using SQLAlchemy ORM.

When building a backend with FastAPI, choosing the right database strategy is crucial for performance and scalability. While local SQLite is excellent for development and testing, production environments usually require robust systems like MySQL or PostgreSQL.

In this guide, I will show you how to connect a FastAPI application to a MySQL database using SQLAlchemy, an Object-Relational Mapper (ORM) that allows you to interact with your database using Python classes instead of writing raw SQL commands.

Prerequisites

Run this command in your terminal first:

pip install fastapi sqlalchemy pymysql pydantic[email] uvicorn

1

Define Database Configuration (database.py)

The first step is to establish the connection. We use create_engine to define the database URL and sessionmaker to handle database transactions.

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

# Database URL Format: mysql+pymysql://username:password@host:port/database_name
# For SQLite, use: "sqlite:///./test.db"
SQLALCHEMY_DATABASE_URL = "mysql+pymysql://user:password@localhost:3306/db_name"

engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

# Dependency to get the database session in routes
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

2

Define Database Models (models.py)

Models represent the structure of your database tables. Here, we define a User table and a Profile table linked by a foreign key.

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from .database import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, index=True)
    password = Column(String(255))
    role = Column(String(50))

    # Relationship to Profile
    profile = relationship("Profile", back_populates="user", uselist=False)

class Profile(Base):
    __tablename__ = "profiles"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), nullable=False)
    email = Column(String(255), nullable=False)
    description = Column(String(500), nullable=False)

    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)

    # Back-reference to User
    user = relationship("User", back_populates="profile")

3

Define Pydantic Schemas (schemas.py)

While Models define the database, Schemas validate the data coming in from the client and the data sent back as a response.

from pydantic import BaseModel, EmailStr

class ProfileBase(BaseModel):
    name: str
    email: EmailStr
    description: str

class ProfileCreate(ProfileBase):
    pass

class SignupRequest(BaseModel):
    username: str
    password: str

    model_config = {
        "from_attributes": True
    }

4

Integrate with Main Application (main.py)

Finally, connect everything in your main script. This script initializes the tables and defines the API endpoints using the get_db dependency.

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

# Create the database tables on startup
models.Base.metadata.create_all(bind=engine)

app = FastAPI()

@app.post("/signup")
def create_user(user: schemas.SignupRequest, db: Session = Depends(get_db)):
    db_user = models.User(username=user.username, password=user.password)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return {"message": "User created successfully", "id": db_user.id}

Conclusion

Using SQLAlchemy ORM with FastAPI is the industry standard because it significantly reduces complexity. Instead of writing raw SQL strings, you interact with data using Python objects, making your code safer, cleaner, and easier to scale.

Visit Github →