Post-Django Era: Comprehensive Guide to SQLAlchemy 2.0, Tortoise ORM, and Piccolo for Modern Async Development

From Django's Safe Harbor to the Async Frontier: A Developer's Journey

"Leaving Django's comfort zone for the async frontier" — Author's Note

Recently, while working on the TechDistill project, I encountered a pivotal decision point that many Python developers face today. TechDistill aggregates multiple high-quality technical information sources and leverages large language models (LLMs) for deep organization and summarization, significantly reducing the mental burden of filtering and reading for users amid rapidly iterating daily technical news.

During development, I found traditional Django solutions too heavyweight. While FastAPI offered simplicity and ease of use, its scattered tooling felt chaotic and disorganized. This led me to recall LiteStar, a framework I had previously shared in articles, and consider its potential for new AI-related projects.

As a developer who has spent years immersed in the Django ecosystem, I've always respected Django's all-in-one solution: stable, comprehensive, and essentially defining Python web development standards. However, as the web moves into the asynchronous era, and especially after deeply experiencing LiteStar—a framework pursuing ultimate architecture and performance—I realized it was time to adopt LiteStar for new AI-related projects. Stepping outside the Django ecosystem meant告别 the mature and user-friendly Django ORM.

In modern async web development, ORM (Object-Relational Mapping) selection is no longer a framework-bundled afterthought but a deep game-changer affecting code cleanliness, type safety, and engineering ceilings.

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

The Contenders

SQLAlchemy 2.0: The Industrial Machine of the Database World

If Python's database realm has a temple, SQLAlchemy is undoubtedly enshrined within it. After a long 1.x era, the 2.0 version release marked its official embrace of strong type annotations and native async support.

With 11.7k stars on GitHub, SQLAlchemy remains the first-choice ORM in the Python ecosystem.

Core Philosophy: Data Mapper pattern. It decouples in-memory objects from database table structures, giving developers extremely high operational precision.

Killer Feature: Unbeatable ecosystem and Alembic migration tool.

Drawback: Steepest learning curve, slightly cumbersome configuration.

Tortoise ORM: The Gentle Home for Django Veterans

If you're accustomed to Django's Model.objects.filter() syntax, Tortoise will make you feel unprecedentedly comfortable.

This project has developed over a considerable period, boasting 5.5k+ stars on GitHub—a mature project by any measure.

Core Philosophy: Active Record pattern. The model itself contains data operation logic.

Killer Feature: Zero learning cost (for Django users).

Drawback: Limited flexibility, struggles somewhat with large-scale complex queries.

Piccolo: The Prisma Challenger from the Future

This is a niche gem with modest GitHub stars (approximately 1.9k) but stunningly elegant design.

It feels somewhat like Prisma and drizzle in the TypeScript ecosystem.

Core Philosophy: Modern, lightweight, type-safety first.

Killer Feature: Extremely modern chain syntax and built-in Piccolo Admin.

Drawback: Smaller community ecosystem; you may fight battles alone when encountering bugs in production.

Code Comparison: Let's Get Practical

For intuitive comparison, we designed a classic scenario: one-to-many relationship querying between User and Post entities.

Modeling Comparison

SQLAlchemy 2.0 mandates type annotations, making it 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 delivers the familiar taste, almost 1:1 reproducing Django model syntax:

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

Piccolo achieves ultimate simplicity, born for async and type safety with very modern syntax:

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

CRUD Operations Comparison

SQLAlchemy (Explicit Session Mode):

# 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 (Chain API):

If you miss Django's syntax, Tortoise is prepared 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 Chain):

Chain calls with syntax approaching native SQL.

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

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

Migration Capabilities: The Unsung Hero

A good ORM cannot exist without excellent migration capabilities. All these ORMs offer solid database migration functionality—detailed comparison below:

PS: Alembic is reportedly the tool you can always trust. Although initial configuration seems slightly complex, it handles any strange database change requirements.

Making the Decision: A Comprehensive Evaluation

To help everyone quickly decide, I've compiled this comprehensive assessment table:

Other Critical Consideration Factors

Type Hinting

• SQLAlchemy 2.0: Through Mapped types, IDEs achieve true autocomplete, extremely beneficial for large project development.
• Piccolo: Native type safety without requiring additional Mypy plugins.
• Tortoise: Although supported, type inference breaks under certain dynamic queries.

Performance and Speed

• SQLAlchemy: After 20 years of optimization, performance is extremely strong when processing large data volumes.
• Piccolo: Uses extremely lightweight underlying implementation, typically faster than SQLAlchemy in simple CRUD operations.
• Tortoise: Because it mimics Django with multiple encapsulation layers, performance is relatively the slowest of the three (though barely perceptible in ordinary applications).

Ecosystem

• SQLAlchemy: Invincible. Almost every third-party tool (Admin, encryption, geospatial) prioritizes support for it.
• Piccolo: Has its own Piccolo Admin, but fewer ecosystem plugins otherwise.
• Tortoise: Moderate ecosystem, growing steadily.

Pitfall Avoidance Guide

The Star Count Fallacy

Never迷信 stars. Piccolo is excellent, but 1.9k stars means insufficient ecosystem breadth. If you're undertaking a project involving tens of millions in capital or requiring complex database migrations, definitely choose SQLAlchemy. Its 11.7k stars translate to thousands upon thousands of StackOverflow answers available when you're debugging bugs at midnight.

The Migration Obsession

For web projects, migration tool stability trumps everything else. Alembic's configuration may be slightly troublesome, but its ability to handle complex scenarios like field type modifications and multi-database synchronization is unmatched by other native tools.

The Admin Temptation

Piccolo Admin is genuinely handsome, resembling modern tools like Prisma and drizzle in the TypeScript ecosystem. But remember: SQLAlchemy's rich ecosystem also has similar admin interfaces. Moreover, admin interfaces are only for local development debugging—real delivery requires developing genuine management backends.

Practical Recommendations Based on Project Needs

Choose SQLAlchemy 2.0 When:

• You're building enterprise-grade applications requiring long-term maintenance
• Complex database migrations are anticipated
• Team members have varying skill levels (extensive documentation helps)
• You need maximum flexibility for unusual query patterns
• Type safety is critical for your codebase

Choose Tortoise ORM When:

• Your team has strong Django background
• You're building small to medium-sized applications
• Rapid prototyping is prioritized over long-term flexibility
• You prefer Active Record pattern over Data Mapper
• Migration requirements are straightforward

Choose Piccolo When:

• You're an independent developer or small team
• You value modern syntax and developer experience
• Your project doesn't require complex migrations
• You appreciate built-in admin interfaces
• You're comfortable troubleshooting with limited community resources

My Personal Decision and Future Plans

For my recent new projects, I plan to try the LiteStar + SQLAlchemy 2.0 + Alembic combination and will continue sharing development experiences.

Since we've already left Django's comfort zone, let's not seek a Django ORM replacement. Embrace SQLAlchemy's Data Mapper pattern—although initially challenging, the code decoupling and complex business control it brings makes everything worthwhile.

Additionally, I'm quite optimistic about Piccolo, though its ecosystem seems immature for now. I'll continue observing its development.

The Bigger Picture: Python's Async Evolution

This ORM selection decision reflects a broader transition in the Python ecosystem. As async/await becomes standard, developers must reconsider tools and patterns that served well in synchronous contexts but may not translate optimally to async environments.

The three ORMs discussed represent different philosophies:

• SQLAlchemy: Evolution of proven patterns with async support added
• Tortoise: Django-like experience rebuilt for async from ground up
• Piccolo: Modern approach unburdened by legacy compatibility

Each has merit depending on your specific context, team expertise, and project requirements.

Final Thoughts: Beyond the Technical Decision

Ultimately, ORM selection is not merely a technical choice but a strategic decision affecting:

• Team Productivity: Learning curves impact development velocity
• Long-term Maintenance: Migration tool quality affects operational burden
• Hiring and Onboarding: Popular tools ease recruitment
• Project Scalability: Some ORMs handle growth better than others

Take time to evaluate your specific needs, consider your team's background, and choose wisely. The "best" ORM is the one that best fits your context—not the one with the most stars or the newest features.

As the Python ecosystem continues evolving, stay informed about new developments while building on proven foundations. The async frontier offers exciting possibilities, but successful navigation requires both courage to explore and wisdom to choose appropriate tools.

WeChat Official Account: "Programming Laboratory"
Focus on exploring internet hot new technologies and team agile development practices, including architecture design, machine learning and data analysis algorithms, mobile development, Linux, web front-end and back-end development. Welcome to discuss technology together and share learning and practical experience.