Using FastAPI with SQLAlchemy

FastAPI is a modern, fast (high-performance) web framework for building APIs with Python 3.7+ based on standard Python type hints. SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python. In this article, we will explore how to integrate a relational database with FastAPI using SQLAlchemy, complete with code examples to guide you through the process.

1. Setting Up Your Environment

First, you’ll need to install the necessary dependencies. You can do this using pip:

pip install fastapi uvicorn sqlalchemy asyncpg psycopg2-binary

2. Creating the Project Structure

Here is a basic structure for your FastAPI project:

my_project/├── app/│   ├── __init__.py│   ├── main.py│   ├── models.py│   ├── crud.py│   ├── schemas.py│   └── database.py├── requirements.txt└── README.md

3. Configuring the Database Connection

Create a database.py file to set up the database connection and session.

Example: database.py

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmakerSQLALCHEMY_DATABASE_URL = "postgresql://user:password@localhost/dbname"engine = create_engine(SQLALCHEMY_DATABASE_URL)SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)Base = declarative_base()def get_db():    db = SessionLocal()    try:        yield db    finally:        db.close()

4. Creating Models

Define your database models in models.py. These models represent tables in your database.

Example: models.py

from sqlalchemy import Column, Integer, Stringfrom .database import Baseclass Item(Base):    __tablename__ = "items"    id = Column(Integer, primary_key=True, index=True)    name = Column(String, index=True)    description = Column(String, index=True)

5. Creating Pydantic Schemas

Use Pydantic schemas to validate and serialize data. Create these schemas in schemas.py.

Example: schemas.py

from pydantic import BaseModelclass ItemBase(BaseModel):    name: str    description: strclass ItemCreate(ItemBase):    passclass Item(ItemBase):    id: int    class Config:        orm_mode = True

6. Creating CRUD Operations

Define CRUD (Create, Read, Update, Delete) operations in crud.py.

Example: crud.py

from sqlalchemy.orm import Sessionfrom . import models, schemasdef get_item(db: Session, item_id: int):    return db.query(models.Item).filter(models.Item.id == item_id).first()def get_items(db: Session, skip: int = 0, limit: int = 10):    return db.query(models.Item).offset(skip).limit(limit).all()def create_item(db: Session, item: schemas.ItemCreate):    db_item = models.Item(name=item.name, description=item.description)    db.add(db_item)    db.commit()    db.refresh(db_item)    return db_item

7. Creating API Endpoints

Create your FastAPI endpoints in main.py to interact with the database.

Example: main.py

from fastapi import FastAPI, Depends, HTTPExceptionfrom sqlalchemy.orm import Sessionfrom . import crud, models, schemasfrom .database import SessionLocal, engine, get_dbmodels.Base.metadata.create_all(bind=engine)app = FastAPI()@app.post("/items/", response_model=schemas.Item)def create_item(item: schemas.ItemCreate, db: Session = Depends(get_db)):    return crud.create_item(db=db, item=item)@app.get("/items/{item_id}", response_model=schemas.Item)def read_item(item_id: int, db: Session = Depends(get_db)):    db_item = crud.get_item(db, item_id=item_id)    if db_item is None:        raise HTTPException(status_code=404, detail="Item not found")    return db_item@app.get("/items/", response_model=list[schemas.Item])def read_items(skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):    items = crud.get_items(db, skip=skip, limit=limit)    return items

8. Running the Application

You can run your FastAPI application using Uvicorn:

uvicorn app.main:app --reload

Navigate to http://127.0.0.1:8000 to access your API. FastAPI also provides an interactive API documentation interface at http://127.0.0.1:8000/docs

Conclusion

Integrating FastAPI with SQLAlchemy allows you to create powerful, high-performance APIs with a relational database backend. By following the steps outlined above, you can set up a robust FastAPI application with SQLAlchemy for efficient database interactions.