The Post-Django Era: A Selection Guide for Three Async ORMs — SQLAlchemy 2.0, Tortoise, and Piccolo

Preface

From Django's safe harbor toward the async sea of stars

— Epigraph

In my recent spare time, I've been tinkering with the TechDistill project. You may have noticed that recent WeChat official account posts have all been "AI Tech Daily" — that's a product of the TechDistill project. Amid the various kinds of tech news iterating rapidly every day, developers and tech enthusiasts often face severe information overload. TechDistill aggregates multiple high-quality tech information sources and uses LLMs (large language models) for in-depth organization and summarization, thereby greatly reducing users' mental burden of filtering and reading.

Also while developing something new, I felt the traditional Django approach was too heavy. FastAPI's approach is simple and easy to use, but I don't like this kind of piecemeal toolkit — it gives a feeling of chaos and disorder. That's when I remembered LiteStar, which I'd written about and shared before…

As a developer steeped in the Django ecosystem for many years, I've always held a certain reverence for Django's all-in-one suite: stable, complete, almost defining the standard for Python web development. But as the web enters the async era, especially after I recently got a deep experience of LiteStar, a framework that pursues extreme architecture and performance, I realized it's time to use LiteStar on some new AI-related projects. And leaving the Django ecosystem means saying goodbye to the mature, easy-to-use Django ORM.

In modern async web development, choosing an ORM (Object-Relational Mapping) is no longer something bundled free with the framework, but a deep contest concerning code cleanliness, type safety, and the ceiling of engineering.

Today, let's talk about three representative choices in the current Python async ecosystem: SQLAlchemy 2.0, Tortoise ORM, and Piccolo.

The Contenders

SQLAlchemy 2.0: The Industrial Machine Tool of the Database World

If the Python database field has a temple, the one enshrined there is surely SQLAlchemy. After a long 1.x era, the release of version 2.0 marked its formal embrace of strong type annotations and native async.

SQLAlchemy has 11.7k stars on GitHub and is the first-choice ORM in the Python ecosystem.

• Core philosophy: Data Mapper (data mapping pattern). It decouples in-memory objects from database table structures, giving developers extremely high operational precision.
• Killer feature: An unmatched ecosystem and the Alembic migration tool.
• Downsides: The steepest learning curve; configuration is a bit cumbersome.

Tortoise ORM: The Gentle Haven for Django Veterans

If you're used to Django's Model.objects.filter() style, Tortoise will feel unprecedentedly familiar.

This project has also been developing for a long time now, with 5.5k+ stars on GitHub — it counts as a mature project.

• Core philosophy: Active Record (domain model pattern). The model itself contains the data operation logic.
• Killer feature: Zero learning cost (for Django users).
• Downsides: Limited flexibility; it struggles a bit under large-scale complex queries.

Piccolo: A Prisma Challenger from the Future

This is a niche gem with not that many GitHub Stars (about 1.9k), but an extremely stunning design.

It has a bit of that Prisma and drizzle feel from the TypeScript ecosystem.

• Core philosophy: Modern, lightweight, type-safety first.
• Killer feature: Highly modern chained syntax and built-in Piccolo Admin.
• Downsides: Smaller community ecosystem; when you hit a bug in production you may have to fight alone.

Straight to the Code

For an intuitive comparison, we designed a classic scenario: a one-to-many association query between users (User) and posts (Post).

Modeling Comparison

SQLAlchemy 2.0 mandates type annotations and is extremely IDE-friendly:

class User(Base):
    __tablename__ = "user"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    posts: Mapped[list["Post"]] = relationship(back_populates="user")

Tortoise has that familiar flavor, almost a 1:1 reproduction of Django model syntax.

class User(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=30)

Piccolo is concise to the extreme, born for async and type safety, with very modern syntax.

Model definitions inherit directly from Table

class User(Table):
    name = Varchar(length=30)

CRUD Comparison

SQLAlchemy (explicit Session pattern):

# Create
async with async_session() as session:
    session.add(User(name="Gemini"))
    await session.commit()

# Read
users = (await session.execute(select(User).where(User.name == "Gemini"))).scalars().all()

Tortoise (chained API):

If you miss Django's syntax, Tortoise was made for you. Model classes come with built-in CRUD methods.

# Create
await User.create(name="Gemini")

# Read
users = await User.filter(name="Gemini").all()

Piccolo (SQL-style chaining):

Chained calls, with syntax close to native SQL.

# Create
await User(name="Gemini").save()

# Read
users = await User.select().where(User.name == "Gemini").run()

Migration Capabilities

A good ORM can't do without excellent migration capabilities. These ORMs all have decent database migration features; see the table below for a detailed comparison.

PS: Alembic is said to be a tool you can always trust. Although the initial setup is a bit complex, it can handle any weird database change requirement.

How to Choose?

To help everyone make a quick decision, I've put together this comprehensive evaluation table:

Other Key Considerations

Type Hinting

• SQLAlchemy 2.0: Through the Mapped type, the IDE can achieve true point-and-click (autocompletion), extremely friendly for developing large projects.
• Piccolo: Native type safety; no extra Mypy plugin needed.
• Tortoise: Although supported, type inference can break under certain dynamic queries.

Performance and Speed

• SQLAlchemy: After 20 years of optimization, performance is very strong when handling large amounts of data.
• Piccolo: Uses an extremely lightweight underlying implementation; on simple CRUD it is usually faster than SQLAlchemy.
• Tortoise: Because it mimics Django with many layers of wrapping, performance is relatively the slowest of the three (though not strongly noticeable in ordinary applications).

Ecosystem

• SQLAlchemy: Unmatched. Almost all third-party tools (admin, encryption, geospatial) will support it first.
• Piccolo: Has its own Piccolo Admin, but fewer other ecosystem plugins.

Pitfall Guide

1. The myth of Star counts: Never blindly worship Stars. Piccolo is good, but 1.9k Stars means its ecosystem breadth is insufficient. If you're building a project involving tens of millions in funds, or one that needs complex database migrations, you must choose SQLAlchemy. Those 11.7k Stars buy you the tens of thousands of StackOverflow answers you can search when fixing bugs late at night.
2. The obsession with migrations: For web projects, the stability of the migration tool is above all else. Although Alembic is a bit more troublesome to configure, its ability to handle complex scenarios like changing field types and multi-database sync is something other native tools can hardly match.
3. The temptation of Admin: Piccolo Admin is genuinely slick, very much like the modern tools in the TypeScript ecosystem similar to Prisma and drizzle. But don't forget that SQLAlchemy's rich ecosystem also has similar admin interfaces, and an admin interface is only for debugging during local development — real delivery still requires building a real admin backend.

Wrap-up

For some new projects I've been wanting to do recently, I plan to try the LiteStar + SQLAlchemy 2.0 + Alembic combination, and I'll continue sharing the development experience then.

Since we've already left Django's comfort zone, don't go looking for another Django ORM substitute. Embrace SQLAlchemy's Data Mapper pattern. Although it's hard work up front, the code decoupling and command over complex business it brings make it all worthwhile.

I'm also quite optimistic about Piccolo, but the ecosystem doesn't seem mature enough yet — I'll keep watching for now.