FastAPI and SQLAlchemy: Building a Full-Stack REST API

What is FastAPI?

FastAPI is an asynchronous web framework found in Python that allows you to build RESTful APIs effortlessly and efficiently. One of its standout features is its automatic generation of OpenAPI documentation based on your code, making it simple to create and understand API endpoints. FastAPI is built on top of Starlette for the web parts and Pydantic for the data parts, meaning it guarantees high performance and validation.

What is SQLAlchemy?

SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapping (ORM) system for Python. It provides an expressive API that allows developers to interact with relational databases using Python objects, streamlining database manipulation processes. With SQLAlchemy, you can write high-level Python code for complex queries, manage transactions effectively, and utilize its excellent support for various database management systems.

Project Structure

When building a REST API with FastAPI and SQLAlchemy, it’s essential to maintain an organized project structure. A typical directory layout might look like this:

/myproject
  ├── app/
  │   ├── main.py
  │   ├── models.py
  │   ├── schemas.py
  │   ├── crud.py
  │   ├── database.py
  │   └── routers/
  │       └── items.py
  ├── requirements.txt
  └── README.md

main.py: Entry point where you initialize the FastAPI application.
models.py: Contains SQLAlchemy ORM models.
schemas.py: Pydantic models for request and response validation.
crud.py: Functions to handle database operations.
database.py: Database configuration and session creation.
routers/: Directory for different API route files.

Setting Up the Environment

To begin building your FastAPI application, you must first create a virtual environment and install the necessary dependencies. You can use pip to install FastAPI and SQLAlchemy.

mkdir myproject
cd myproject
python3 -m venv venv
source venv/bin/activate
pip install fastapi[all] sqlalchemy uvicorn

The uvicorn server serves as the ASGI server for running your FastAPI application.

Database Configuration

To configure the database connection, you’ll need to establish a database URL in the database.py file.

# app/database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"  # Change to desired database

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

In this example, we used SQLite, a lightweight database suitable for development. Change the URL for other databases such as PostgreSQL or MySQL.

Defining Models

Next, you will need to define your database models in models.py.

# app/models.py
from sqlalchemy import Column, Integer, String
from .database import Base

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    description = Column(String, index=True)
    price = Column(Integer)

Here, the Item class defines the database schema. You can add additional fields as needed.

Creating Pydantic Schemas

After defining models, create Pydantic schemas in schemas.py for request validation.

# app/schemas.py
from pydantic import BaseModel

class ItemBase(BaseModel):
    name: str
    description: str
    price: int

class ItemCreate(ItemBase):
    pass

class Item(ItemBase):
    id: int

    class Config:
        orm_mode = True

The Config class enables reading from SQLAlchemy models by setting orm_mode to True.

CRUD Operations

Create a crud.py file to handle the logic for creating, reading, updating, and deleting items.

# app/crud.py
from sqlalchemy.orm import Session
from . import models, schemas

def create_item(db: Session, item: schemas.ItemCreate):
    db_item = models.Item(**item.dict())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

def get_items(db: Session, skip: int = 0, limit: int = 10):
    return db.query(models.Item).offset(skip).limit(limit).all()

def get_item(db: Session, item_id: int):
    return db.query(models.Item).filter(models.Item.id == item_id).first()

These functions communicate directly with the database, allowing you to interact with your models easily.

Setting Up Routers

Define the routes in a new file in the routers/ directory to keep your code organized.

# app/routers/items.py
from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from .. import crud, models, schemas
from ..database import SessionLocal

router = APIRouter()

# Dependency for DB session
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@router.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)

@router.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

@router.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

In this setup, each API endpoint corresponds to a function in the crud.py file. FastAPI facilitates dependency injection by using Depends.

Integrating Routers and Running the Application

Finally, hook up your router in main.py:

# app/main.py
from fastapi import FastAPI
from .routers import items
from .database import engine, Base

Base.metadata.create_all(bind=engine)

app = FastAPI()

app.include_router(items.router)

To run your FastAPI application, use the following command in the terminal:

uvicorn app.main:app --reload

This allows you to test your API endpoints by visiting http://127.0.0.1:8000/items/ in a web browser or using tools like Postman.

Testing with OpenAPI Documentation

FastAPI automatically creates a user-friendly, interactive API documentation interface accessible at http://127.0.0.1:8000/docs. This interface is powered by Swagger UI, allowing you to visualize and interact with your API directly from a browser—facilitating testing and exploration.

Best Practices

Environment Variables: Use environment variables to manage sensitive information like database URLs and API keys.
Exception Handling: Implement global exception handling to enhance user experience by returning meaningful error messages.
Data Validation: Take full advantage of Pydantic’s features for data validation upon incoming requests to ensure data integrity.
Testing: Write unit and integration tests for your API routes using libraries like pytest to improve reliability.
Asynchronous Support: Consider leveraging FastAPI’s asynchronous capabilities for I/O-bound tasks to improve performance under high loads.

Using FastAPI with SQLAlchemy to build a full-stack REST API provides a robust framework for creating performant, scalable applications. Its combination of asynchronous capabilities, automatic documentation generation, and a powerful ORM enriches the development experience. By adhering to the structured approach outlined above, you can create a high-quality API that serves as the backend for any modern web or mobile application.