How to Integrate FastAPI with SQLAlchemy

Table of Contents

1. Prerequisites
2. Setting up the Project
3. Defining the Database Models
4. Configuring the Database Connection
5. Creating Database Sessions
6. Building API Endpoints with SQLAlchemy
7. Error Handling and Transactions
8. Best Practices
9. Conclusion
10. References

Prerequisites

• Basic knowledge of Python programming.
• Familiarity with FastAPI and SQLAlchemy concepts.
• Python 3.7 or higher installed on your system.

Setting up the Project

First, create a new directory for your project and navigate into it:

mkdir fastapi_sqlalchemy_project
cd fastapi_sqlalchemy_project

Create a virtual environment and activate it:

python -m venv venv
source venv/bin/activate  # For Linux/Mac
.\venv\Scripts\activate  # For Windows

Install the required packages:

pip install fastapi sqlalchemy uvicorn

Defining the Database Models

Let’s create a simple model for a User table. Create a file named models.py:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    email = Column(String, unique=True, index=True)

Configuring the Database Connection

Create a file named database.py to configure the database connection. Here, we’ll use SQLite for simplicity:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

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

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

Creating Database Sessions

In the database.py file, we’ve already created a session factory SessionLocal. To use it in our API endpoints, we can create a dependency function:

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

Building API Endpoints with SQLAlchemy

Create a file named main.py to define our API endpoints:

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

app = FastAPI()

@app.post("/users/")
def create_user(user: User, db: Session = Depends(get_db)):
    db_user = User(name=user.name, email=user.email)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

@app.get("/users/{user_id}")
def read_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(User).filter(User.id == user_id).first()
    return user

Error Handling and Transactions

When working with database operations, it’s important to handle errors and manage transactions properly. In the create_user endpoint above, if an error occurs during the db.commit() operation, SQLAlchemy will roll back the transaction automatically because we set autocommit=False in the session factory.

Best Practices

• Use Dependency Injection: As shown with the get_db function, using dependency injection makes it easier to manage database sessions and test your code.
• Separate Concerns: Keep your models, database configuration, and API endpoints in separate files for better code organization.
• Error Handling: Always handle potential database errors gracefully to provide meaningful feedback to the users.
• Use Transactions Wisely: Group related database operations in a single transaction to ensure data consistency.

Conclusion

Integrating FastAPI with SQLAlchemy provides a powerful combination for building high - performance web APIs with a reliable database backend. By following the steps outlined in this blog, you can set up a basic project, define database models, configure the database connection, create database sessions, and build API endpoints. Additionally, adhering to best practices will help you write clean, maintainable, and robust code.

References

• FastAPI official documentation: https://fastapi.tiangolo.com/
• SQLAlchemy official documentation: https://docs.sqlalchemy.org/