Back to Scaling Python Applications guides

TortoiseORM vs SQLAlchemy: An In-Depth Framework Comparison

Stanley Ulili

Updated on March 14, 2025

What is SQLAlchemy?
What is TortoiseORM?
TortoiseORM vs. SQLAlchemy: a quick comparison
Model definition
Query building
Relationship handling
Migration support
Type hints and validation
Raw SQL support
Testing support
Final thoughts

SQLAlchemy and TortoiseORM are popular Python ORMs, both simplifying database interactions but with different approaches.

SQLAlchemy is he most popular choice, known for its flexibility, deep customization, and support for synchronous and asynchronous workflows. It is well-suited for complex applications that require detailed management of database interactions.

TortoiseORM on the other hand, is built with an async-first approach, making it ideal for high-performance, modern applications. Inspired by Django’s ORM, it simplifies database interactions with minimal boilerplate while maintaining efficiency.

This article will break down their differences, strengths, and ideal use cases to help you decide which ORM best suits your project.

What is SQLAlchemy?

SQLAlchemy is Python’s leading database toolkit and ORM, offering profound control over relational databases.

It was built by Mike Bayer in 2005 and it combines a Core layer for precise SQL queries with an ORM layer that maps tables to Python classes.

Unlike simpler ORMs, SQLAlchemy extends SQL capabilities, providing high-level abstractions without obscuring control. It supports multiple databases, complex relationships, and advanced transactions, making it a strong choice for performance-driven applications.

What is TortoiseORM?

TortoiseORM is built for modern Python, embracing async programming from the ground up rather than as an afterthought. Inspired by Django’s ORM, it provides an intuitive model syntax and query patterns while fully supporting async/await, making it a strong choice for high-concurrency applications.

With a focus on developer experience, TortoiseORM keeps models concise, queries straightforward, and the API easy to use. Its lightweight design ensures efficient database operations, enhancing both development speed and maintainability.

TortoiseORM vs. SQLAlchemy: a quick comparison

Choosing between these ORMs affects both development efficiency and application performance. Each is designed with a distinct philosophy, making them better suited for different scenarios.

The following comparison highlights key differences to consider:

Feature	TortoiseORM	SQLAlchemy
Primary paradigm	Async-first with sync support	Sync-first with async support via SQLAlchemy 2.0
Learning curve	Gentle, Django-like syntax	Steeper, more concepts to master
Performance	Optimized for async workloads	Battle-tested performance, extensive optimization options
Query building	Intuitive query API with filtering	Comprehensive expression language, multiple query APIs
Migration support	Built-in via Aerich	Requires Alembic (first-party)
Relationship handling	Simple references with prefetch support	Sophisticated relationship loading with many strategies
Transaction management	Context-based transactions	Flexible session-based approach
Ecosystem	Growing, focused on async frameworks	Extensive, mature ecosystem with broad support
Model definition	Clean class-based models with field types	Declarative mapping or imperative table definitions
Raw SQL support	Basic support for raw queries	Comprehensive SQL expression language
Type hints	Native Python type annotations	Type hints via SQLAlchemy 2.0, stubs available
Database support	PostgreSQL, MySQL, SQLite, others via drivers	Extensive support for almost all SQL databases
Testing	In-memory SQLite for quick tests	Multiple testing approaches, including fixtures
Community size	Smaller but growing community	Large, established community with extensive resources

Model definition

The way an ORM defines models shapes the entire developer experience. A well-structured model should be clear, maintainable, and aligned with the application's needs.

SQLAlchemy and TortoiseORM take distinct approaches to model definition, reflecting their underlying philosophies.

SQLAlchemy embraces flexibility through its multi-layered architecture. Models typically follow the Declarative pattern, where tables and columns are explicitly defined with numerous configuration options. This design provides precise control but introduces more verbosity:

Copied!

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(100), unique=True, nullable=False)

    def __repr__(self):
        return f"<User(username='{self.username}', email='{self.email}')>"

# Setting up the database connection
engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

SQLAlchemy's approach gives you explicit control over table names, column types, and relationship configurations, but requires more code and understanding of SQLAlchemy's concepts.

TortoiseORM takes inspiration from Django's ORM, offering a more concise, declarative approach. Models are defined as classes with fields as attributes, using a more intuitive syntax that reduces boilerplate.

Copied!

from tortoise import fields, models, Tortoise

class User(models.Model):
    id = fields.IntField(pk=True)
    username = fields.CharField(50, unique=True)
    email = fields.CharField(100, unique=True)

    def __str__(self):
        return f"{self.username} ({self.email})"

    class Meta:
        table = "users"

# Setting up the database connection
async def init_db():
    await Tortoise.init(
        db_url='sqlite://example.db',
        modules={'models': ['__main__']}
    )
    await Tortoise.generate_schemas()

TortoiseORM's syntax is more intuitive if you're familiar with Django, with fields directly corresponding to database columns and sensible defaults that reduce configuration overhead.

Query building

The approach to building and executing queries is where these ORMs show their distinct philosophies.

SQLAlchemy provides multiple query APIs, including a powerful SQL Expression Language that enables complex queries with fine-grained control.

The ORM layer builds on this foundation, offering high-level query methods and direct access to underlying SQL constructs.

Copied!

from sqlalchemy import select, func

session = Session()

# Using ORM query API
users = session.query(User).filter(User.username.like('j%')).all()

# Using SQL Expression Language with ORM
stmt = select(User).where(User.username.like('j%'))
users = session.execute(stmt).scalars().all()

# Aggregations
user_count = session.query(func.count(User.id)).scalar()

session.close()

SQLAlchemy queries can be constructed incrementally and offer precise control over joins, filtering, and result handling. This power comes with a learning curve, as you need to understand concepts like sessions, query construction, and execution flow.

TortoiseORM focuses on a more straightforward, method-chaining approach inspired by Django's QuerySet API. This design prioritizes readability and ease of use, especially for common operations:

Copied!

# Fetching users with filtering
users = await User.filter(username__startswith='j').all()

# Counting users
user_count = await User.all().count()

# Working with related models
users_with_posts = await User.filter(posts__isnull=False).distinct()

# Aggregations
from tortoise.functions import Count
result = await User.annotate(post_count=Count('posts')).filter(post_count__gt=5)

TortoiseORM's query syntax is more intuitive, especially if you are coming from Django.

The async/await pattern integrates naturally with modern Python applications, while filter lookups (like username__startswith) provide a clean way to express conditions.

Transaction management

Another key difference between these ORMs is how they handle transactions. Transaction management is important for maintaining data integrity and performance, and SQLAlchemy and TortoiseORM take distinct approaches.

SQLAlchemy uses a session-based approach, where a session represents a transaction and serves as the primary interface for database operations.

This design offers flexibility but requires careful management.

Copied!

session = Session()
try:
    user1 = User(username='alice', email='alice@example.com')
    user2 = User(username='bob', email='bob@example.com')

    session.add(user1)
    session.add(user2)

    session.commit()
except:
    session.rollback()
    raise
finally:
    session.close()

For more structured transaction handling, SQLAlchemy provides context managers:

Copied!

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)

with Session() as session:
    with session.begin():
        user = User(username='charlie', email='charlie@example.com')
        session.add(user)
    # Auto-commits if no exceptions, auto-rollback otherwise

SQLAlchemy's session concept offers powerful features like identity map, unit of work, and lazy loading, but requires understanding these patterns to use effectively.

TortoiseORM simplifies transaction management with a straightforward context manager approach that aligns well with Python's async/await syntax:

Copied!

async with in_transaction() as transaction:
    user = User(username='alice', email='alice@example.com')
    await user.save()

    profile = Profile(user=user, bio='Python developer')
    await profile.save()
    # Auto-commits if no exceptions, auto-rollback otherwise

For more explicit control, you can use the transaction object:

Copied!

from tortoise.transactions import in_transaction

async with in_transaction() as transaction:
    user = await User.create(username='bob', email='bob@example.com')

    # Deciding to rollback based on business logic
    if some_condition:
        await transaction.rollback()
        return

    # Continue with more operations
    await Profile.create(user=user, bio='Database expert')

TortoiseORM's approach is more straightforward, especially in async contexts, making transaction management feel more Pythonic and reducing the likelihood of errors.

Relationship handling

Managing relationships between models is critical to any ORM, as it determines how efficiently related data can be accessed and manipulated.

SQLAlchemy offers sophisticated relationship management with multiple loading strategies, providing precise control over how related objects are loaded and accessed.

Copied!

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

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)

    # Define relationship to Post
    posts = relationship("Post", back_populates="author", lazy="select")

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    title = Column(String(100))
    content = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))

    # Define relationship to User
    author = relationship("User", back_populates="posts")

SQLAlchemy supports multiple loading strategies:

lazy="select": Default lazy loading (separate query when accessed)
lazy="joined": Eager loading with JOIN
lazy="subquery": Eager loading with subquery
lazy="selectin": Eager loading with SELECT IN

You can also load relationships dynamically:

Copied!

# Get user with posts loaded via JOIN
user = session.query(User).options(joinedload(User.posts)).filter(User.id == 1).first()

# Get user with posts loaded via separate IN query
user = session.query(User).options(selectinload(User.posts)).filter(User.id == 1).first()

This flexibility allows for optimizing database access patterns for different scenarios, reducing the number of queries, and improving performance.

TortoiseORM takes a more straightforward relationship approach, using field references with prefetch support to manage related objects.

Copied!

from tortoise import fields, models

class User(models.Model):
    id = fields.IntField(pk=True)
    username = fields.CharField(50, unique=True)

    # Reverse relationship reference
    posts = fields.ReverseRelation["Post"]

class Post(models.Model):
    id = fields.IntField(pk=True)
    title = fields.CharField(100)
    content = fields.TextField()

    # Foreign key to User
    author = fields.ForeignKeyField("models.User", related_name="posts")

TortoiseORM handles relationship loading through prefetching:

Copied!

# Basic query - will require separate queries for posts
user = await User.get(id=1)
await user.posts.all()  # Executes additional query

# Prefetched query - loads related posts in one go
user = await User.get(id=1).prefetch_related("posts")
posts = await user.posts  # No additional query needed

# Filtering with relationships
users_with_posts = await User.filter(posts__title__contains="Python")

While TortoiseORM's approach is more straightforward, it offers fewer optimization strategies than SQLAlchemy. However, prefetch_related provides a clean way to avoid the N+1 query problem that often affects ORM performance.

Migration support

Database schema evolution is important for application maintenance, and both ORMs offer different approaches to managing migrations.

SQLAlchemy doesn't include built-in migration capabilities, but it's typically paired with Alembic, a first-party migration tool developed by the same team. Alembic provides powerful features for managing schema changes, including automatic migration generation and complex migration paths.

Copied!

# Example Alembic migration script
"""add user table

Revision ID: 1a1e39a41f30
Revises: 
Create Date: 2023-03-12 14:35:17.383088

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers
revision = '1a1e39a41f30'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('username', sa.String(length=50), nullable=False),
        sa.Column('email', sa.String(length=100), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email'),
        sa.UniqueConstraint('username')
    )

def downgrade():
    op.drop_table('users')

Using Alembic requires additional setup and learning, but it provides industrial-strength migration capabilities:

Copied!

# Create a new migration
alembic revision --autogenerate -m "add user table"

# Apply migrations
alembic upgrade head

# Revert to a specific migration
alembic downgrade 1a1e39a41f30

TortoiseORM takes a more integrated approach with Aerich, its companion migration tool designed specifically for TortoiseORM models. Aerich simplifies the migration workflow while maintaining the necessary flexibility for schema evolution.

Copied!

# Configuration for Aerich in tortoise config
TORTOISE_ORM = {
    "connections": {"default": "sqlite://db.sqlite3"},
    "apps": {
        "models": {
            "models": ["app.models", "aerich.models"],
            "default_connection": "default",
        },
    },
}

Aerich commands are straightforward and aligned with TortoiseORM's design philosophy:

Copied!

# Initialize migrations
aerich init -t app.config.TORTOISE_ORM

# Create a new migration
aerich migrate --name add_user_table

# Apply migrations
aerich upgrade

# Revert migrations
aerich downgrade

The migration files are also quite readable:

Copied!

# Example Aerich migration
from tortoise import BaseDBAsyncClient

async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE "users" (
            "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            "username" VARCHAR(50) NOT NULL UNIQUE,
            "email" VARCHAR(100) NOT NULL UNIQUE
        );
    """

async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        DROP TABLE "users";
    """

Aerich's tight integration with TortoiseORM offers a smoother migration experience, especially for applications that don't require the advanced features provided by Alembic.

Type hints and validation

Modern Python development relies heavily on type annotations, and both ORMs have taken different approaches to supporting this important feature.

SQLAlchemy has evolved its typing support over time, with version 2.0 introducing comprehensive type hints that integrate with mypy and other type checkers. These type hints provide better IDE completion and catch potential errors earlier in development.

Copied!

from sqlalchemy import Column, Integer, String, ForeignKey, select
from sqlalchemy.orm import declarative_base, relationship, Session
from typing import List, Optional

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id: int = Column(Integer, primary_key=True)
    name: str = Column(String(50), nullable=False)

    # Typed relationship
    posts: List["Post"] = relationship("Post", back_populates="author")

class Post(Base):
    __tablename__ = "posts"

    id: int = Column(Integer, primary_key=True)
    title: str = Column(String(100), nullable=False)
    user_id: int = Column(Integer, ForeignKey("users.id"))

    # Typed relationship with Optional
    author: Optional[User] = relationship("User", back_populates="posts")

# Type-safe query execution
def get_user_by_name(session: Session, name: str) -> Optional[User]:
    stmt = select(User).where(User.name == name)
    return session.execute(stmt).scalar_one_or_none()

SQLAlchemy's typing system is powerful but can be complex, especially when dealing with result sets and query returns.

TortoiseORM was initially designed with typing in mind, offering native Python annotations that work smoothly with standard type checkers. This integration makes models and queries more self-documenting and helps catch type-related errors early.

Copied!

from tortoise import fields, models
from tortoise.queryset import QuerySet
from typing import List, Optional

class User(models.Model):
    id: int = fields.IntField(pk=True)
    name: str = fields.CharField(50, null=False)

    # Typed reverse relation
    posts: fields.ReverseRelation["Post"]

class Post(models.Model):
    id: int = fields.IntField(pk=True)
    title: str = fields.CharField(100, null=False)

    # Typed foreign key
    author: fields.ForeignKeyRelation[User] = fields.ForeignKeyField(
        "models.User", related_name="posts"
    )

# Type-safe query functions
async def get_user_by_name(name: str) -> Optional[User]:
    return await User.get_or_none(name=name)

async def get_users_with_posts() -> List[User]:
    return await User.filter(posts__isnull=False).distinct()

TortoiseORM's approach to typing feels more natural in modern Python codebases, with field definitions that directly map to their Python types and methods that return correctly typed results.

Raw SQL support

Sometimes ORMs can't express every query you need, making raw SQL support essential for complex database operations.

SQLAlchemy excels at bridging the gap between ORM abstractions and raw SQL. Its expression language can represent virtually any SQL operation, and when that's not enough, it provides direct text SQL execution with proper parameter binding.

Copied!

from sqlalchemy import text

# Executing raw SQL with parameters
with engine.connect() as conn:
    result = conn.execute(
        text("SELECT * FROM users WHERE username LIKE :pattern"),
        {"pattern": "a%"}
    )
    for row in result:
        print(row)

# Mixing ORM and raw SQL
from sqlalchemy import select, text
stmt = select(User).where(text("username LIKE 'a%'"))
users = session.execute(stmt).scalars().all()

# Complex raw query with result mapping
stmt = text("""
    SELECT u.id, u.username, COUNT(p.id) as post_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    GROUP BY u.id, u.username
    HAVING COUNT(p.id) > :min_posts
""")

result = session.execute(stmt, {"min_posts": 5})
for row in result:
    print(f"User {row.username} has {row.post_count} posts")

SQLAlchemy's approach allows smooth integration of raw SQL with ORM queries, making it possible to leverage database-specific features while maintaining the benefits of the ORM.

TortoiseORM provides more straightforward but effective support for raw queries. While not as comprehensive as SQLAlchemy, it offers the essentials for most use cases.

Copied!

from tortoise.expressions import RawSQL

# Basic raw query
users = await User.filter(RawSQL("username LIKE 'a%'"))

# Raw SQL for more complex cases
from tortoise.backends.base.client import BaseDBAsyncClient
from tortoise import connections

connection: BaseDBAsyncClient = connections.get("default")
results = await connection.execute_query("""
    SELECT u.id, u.username, COUNT(p.id) as post_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    GROUP BY u.id, u.username
    HAVING COUNT(p.id) > $1
""", [5])

# Working with raw results
for record in results[1]:
    print(f"User {record['username']} has {record['post_count']} posts")

While TortoiseORM's raw SQL capabilities are more limited than SQLAlchemy's, they cover the essentials needed for most applications, allowing you to escape the ORM when necessary.

Testing support

Both ORMs offer different approaches to testing database interactions.

SQLAlchemy's maturity shines in its testing support, offering multiple approaches to testing database code. The session-based design makes it easy to use transaction rollbacks to isolate tests, while connection pooling can be configured for optimal test performance.

Copied!

import pytest
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Base, User

# Fixture for test database
@pytest.fixture(scope="session")
def engine():
    return create_engine("sqlite:///:memory:")

@pytest.fixture(scope="session")
def tables(engine):
    Base.metadata.create_all(engine)
    yield
    Base.metadata.drop_all(engine)

@pytest.fixture
def session(engine, tables):
    connection = engine.connect()
    transaction = connection.begin()
    Session = sessionmaker(bind=connection)
    session = Session()

    yield session

    session.close()
    transaction.rollback()
    connection.close()

# Test using the fixtures
def test_create_user(session):
    user = User(username="testuser", email="test@example.com")
    session.add(user)
    session.commit()

    saved_user = session.query(User).filter_by(username="testuser").first()
    assert saved_user is not None
    assert saved_user.email == "test@example.com"

SQLAlchemy's flexible architecture supports various testing approaches, from in-memory databases to mocking at different layers. However, this flexibility means you must set up the testing infrastructure yourself.

TortoiseORM simplifies testing with built-in utilities specifically designed for its async nature. The test client makes it easy to set up and tear down test databases, while the in-memory SQLite support allows for fast test execution.

Copied!

import pytest
from tortoise.contrib.test import finalizer, initializer
from models import User

# Setup and teardown for tests
@pytest.fixture(scope="module")
def initialize_tests(request):
    initializer(["models"], db_url="sqlite://:memory:")
    request.addfinalizer(finalizer)

# Test using the fixture
@pytest.mark.asyncio
async def test_create_user(initialize_tests):
    user = await User.create(username="testuser", email="test@example.com")

    saved_user = await User.get(username="testuser")
    assert saved_user.id == user.id
    assert saved_user.email == "test@example.com"

TortoiseORM also provides a test client for integration testing:

Copied!

from tortoise.contrib.fastapi import register_tortoise
from fastapi.testclient import TestClient
from fastapi import FastAPI

app = FastAPI()
register_tortoise(
    app,
    db_url="sqlite://:memory:",
    modules={"models": ["models"]},
    generate_schemas=True,
)

client = TestClient(app)

def test_create_user_api():
    response = client.post(
        "/users/",
        json={"username": "testuser", "email": "test@example.com"}
    )
    assert response.status_code == 201
    assert response.json()["username"] == "testuser"

TortoiseORM's testing utilities are particularly well-suited for async applications, making it easier to test database interactions in frameworks like FastAPI or Starlette.

Final thoughts

This article compared TortoiseORM and SQLAlchemy to help you decide which fits your Python application needs.

TortoiseORM excels for async applications, with a clean API and Django-inspired models. SQLAlchemy remains the comprehensive solution with unmatched flexibility and a powerful query system, now with improved async support in version 2.0.

If you need modern async support and intuitive models, choose TortoiseORM. If you require maximum flexibility and extensive relationship features, SQLAlchemy is still an excellent choice.

Ultimately, both are high-quality options—choose based on your specific project requirements, team expertise, and development priorities.

Got an article suggestion? Let us know

Getting Started with HTTPX: Python's Modern HTTP Client

Discover how HTTPX modernizes Python HTTP requests with async support and HTTP/2 capabilities. This step-by-step guide covers everything from basic GET requests to advanced authentication patterns, timeout handling, and concurrent API calls—complete with practical code examples that will elevate your web applications' performance and reliability.

→