Choosing the Right ORM for Your FastAPI Project

FastAPI is a high-performance framework based on Python type hints, but unlike many other frameworks, it does not come with a built-in ORM. Selecting the right database toolkit is essential for balancing performance with developer productivity.

SQLAlchemy: The Powerful but Complex Veteran

SQLAlchemy is widely considered the industry standard. It is a comprehensive “Python SQL Toolkit” that provides the full power and flexibility of SQL. However, this maturity comes with a steep learning curve. The library is designed with enterprise-level persistence patterns that can be overly complex for a straightforward workflow. For many, the main issue is that there are too many ways of doing the same thing, leading to confusion when tutorials or documentation offer differing parameters as defaults.

SQLModel: The Familiar Sibling with Limited Reach

SQLModel was created by the same author as FastAPI to reduce code duplication. It acts as a thin layer on top of SQLAlchemy and Pydantic, allowing a single class to serve as both a database table and a data validation model. While its design is intuitive and compatible with FastAPI, it is currently very basic and lacks a lot of functionality. This feature gap can make it hard to use for complex applications or projects requiring intricate table relationships.

Tortoise ORM: The Feature-Rich, Async-First Choice

Tortoise ORM is a modern, async-first ORM built from the ground up for the asyncio ecosystem. Inspired by the Django ORM, it provides a "clean, familiar Python interface" that is highly intuitive for many developers. Despite its lightweight feel, it is feature-rich, supporting ForeignKeyField, ManyToManyField, transactions, and a comprehensive Query API. For developers wanting a balance of feature completeness and an easy-to-use async interface, Tortoise is the premier choice.

Implementation: Building an Author & Book Relationship

Below are examples of how to define a one-to-many relationship where an Author can have multiple Books.

1. SQLAlchemy Example

SQLAlchemy uses explicit table mapping and relationship definitions.

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, DeclarativeBase

class Base(DeclarativeBase):
    pass

class Author(Base):
    __tablename__ = "author"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # Explicit relationship mapping
    books = relationship("Book", back_populates="author")

class Book(Base):
    __tablename__ = "book"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    author_id = Column(Integer, ForeignKey("author.id"))
    author = relationship("Author", back_populates="books")

2. SQLModel Example

SQLModel combines the database table and Pydantic model into a single class.

from typing import List, Optional
from sqlmodel import Field, Relationship, SQLModel

class Author(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    # Relationship attribute
    books: List["Book"] = Relationship(back_populates="author")

class Book(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str
    author_id: int = Field(foreign_key="author.id")
    author: Optional[Author] = Relationship(back_populates="books")

3. Tortoise ORM Example

Tortoise uses a declarative syntax that feels familiar and concise.

from tortoise import fields, models

class Author(models.Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=255)

class Book(models.Model):
    id = fields.IntField(pk=True)
    title = fields.CharField(max_length=255)
    # Simple ForeignKey definition inspired by Django
    author = fields.ForeignKeyField("models.Author", related_name="books")

Integration: Connecting to FastAPI

SQLAlchemy & SQLModel

These libraries typically integrate via FastAPI’s Dependency Injection system. You create a session generator that “yields” a database session for each request.

# Standard dependency injection for SQLModel/SQLAlchemy
def get_session():
    with Session(engine) as session:
        yield session

@app.post("/authors/")
def create_author(author: Author, session: Session = Depends(get_session)):
    session.add(author)
    session.commit()
    return author

Tortoise ORM

Tortoise provides a dedicated FastAPI interface class via register_tortoise. This helper handles the database lifecycle, automatic schema generation, and exception handling in one step.

from tortoise.contrib.fastapi import register_tortoise

# Streamlined registration in main.py
register_tortoise(
    app,
    db_url="sqlite://:memory:",
    modules={"models": ["models"]},
    generate_schemas=True, # Automatically creates tables
    add_exception_handlers=True,
)

Conclusion

While SQLAlchemy offers enterprise maturity and SQLModel offers proximity to the core FastAPI author, Tortoise ORM provides the most comfortable, feature-rich, and async-native experience. It allows you to build features quickly without having to “twist the libraries” to fit your needs.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Author: Marc Nealer Contact: Email Marc Nealer | Work with me on Upwork