TortoiseORM vs SQLAlchemy: An In-Depth Framework Comparison
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:
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.
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.
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:
# 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.
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:
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:
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:
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.
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 JOINlazy="subquery": Eager loading with subquerylazy="selectin": Eager loading with SELECT IN
You can also load relationships dynamically:
# 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.
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:
# 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.
# 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:
# 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.
# 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:
# 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:
# 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.
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.
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.
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.
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.
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.
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:
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.
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
Make your mark
Join the writer's program
Are you a developer and love writing and sharing your knowledge with the world? Join our guest writing program and get paid for writing amazing technical guides. We'll get them to the right readers that will appreciate them.
Write for us
Build on top of Better Stack
Write a script, app or project on top of Better Stack and share it with the world. Make a public repository and share it with us at our email.
community@betterstack.comor submit a pull request and help us build better products for everyone.
See the full list of amazing projects on github