Piccolo: A fast, async ORM for Python (updated)

Episode Deep Dive

Guest Introduction and Background

Daniel Townsend is the creator of Piccolo ORM, a modern, async-first ORM for Python that takes advantage of many newer language features (async and await, type annotations, and more). He has over a decade of experience coding in Python, much of it spent using Django and other frameworks in startup and agency environments. Building on that background, Daniel set out to create a tool that streamlines data access and modern development practices in Python while remaining approachable to both novices and seasoned developers.

What to Know If You're New to Python

If you’re new to Python and want to make the most of this episode, focus on how Python allows you to write code that can be both simple and powerful. Understanding these few items will help you follow along with the ORM and async discussions:

• Python's async/await keywords: They let you run multiple tasks concurrently, especially when waiting on external resources.
• Modern Python features like type annotations and f-strings: They make code easier to read and debug.
• How classes define data models in many Python ORMs (including Piccolo) to map Python code to database tables.

Key Points and Takeaways

1. Piccolo ORM as a Modern, Async-First Solution Piccolo is built to leverage Python’s newer language features such as async and await. It draws on design inspiration from Django but optimizes for today’s async workflows, particularly in areas like web development or data pipelines that need better throughput.
   • Links and Tools:
     • Piccolo ORM GitHub
     • Django Project
     • FastAPI
2. Bridging Async and Sync Worlds One standout feature is the ability to run Piccolo in both async and sync contexts (.run() vs. .run_sync()). This design means you don’t have to rewrite your entire application if only part of it needs async capabilities. Instead, you can “cap” the async call where it makes sense.
   • Links and Tools:
     • asyncio library
3. Flexible Query Building and Syntax Piccolo’s query syntax is explicit and Pythonic. You can use Python operators like <, >, == for filtering, giving you the benefits of type-checking and auto-completion while avoiding the string-based lookups that are common in other ORMs.
   • Links and Tools:
     • SQLAlchemy
     • Pydantic
4. Performance Advantages via Set-Based Operations Rather than loading objects one by one to update them, you can run set-based database operations (e.g., band.update().set(...)) to modify or delete multiple rows in a single query. This can greatly reduce network overhead and improve performance when working on large datasets.
   • Tools Mentioned:
     • asyncpg (for fast Postgres access)
5. Batteries-Included Admin Interface Piccolo ships with a robust admin site powered by an async-compatible backend (e.g., Starlette) and a Vue.js frontend. Developers can quickly stand up a web-based dashboard for CRUD operations and data inspections without writing front-end boilerplate.
   • Tools Mentioned:
     • Vue.js
     • Starlette
6. Migrations Modeled After Django Inspired by Django’s migration approach, Piccolo allows you to define your schema in Python and auto-generate migration files in properly formatted Python code. This approach ensures clarity and reduces the risk of manual mistakes.
   • Related Tools:
     • Django Migrations Docs
7. Admin Security and Middleware Features Piccolo Admin layers in security measures such as session authentication, CSRF protection, and rate limiting. These out-of-the-box features mean you can give non-developers access to a data dashboard quickly while helping safeguard your database.
   • Tools and Middlewares:
     • Piccolo API (sister project)
8. ASCII and the Rise of the Modern Python Web Stack Daniel explained how ASCII-based frameworks (e.g., Starlette, FastAPI, and others) help developers compose multiple microframeworks within the same project. Piccolo embraces ASCII to stay compatible with these ecosystems, giving you freedom of choice in your architecture.
   • Links and Tools:
     • ASGI Spec
     • Blacksheep
9. Type Annotations for Better Refactoring and Editor Support Piccolo heavily uses Python typing to provide better auto-completion, type checking, and clarity. This drastically cuts down on runtime errors and speeds up development by leveraging editor hints and linting tools.
   • Related Tools:
     • mypy
     • VS Code for deep type support
10. Simplicity for Rapid Prototyping and Production Combining SQLite for quick local setup with async Postgres for production means teams can build proofs-of-concept or small internal tools rapidly, yet still be able to transition to a performant setup when scaling. Piccolo’s CLI also auto-scaffolds example apps, accelerating development.

• Tools Mentioned:
  • SQLite
  • PostgreSQL

Interesting Quotes and Stories

• On Python 3.6+ Ecosystem: “It felt like Python 3.6 closed the door on whether we should still use Python 2 because by then you had async, type annotations, data classes…it just felt like the Python community knocked it out of the park.” – Daniel Townsend
• On Using Async Properly: “It’s about throughput. If you have a hundred or a thousand users waiting at once, async means you can handle more connections without spinning up a lot more servers.” – Daniel Townsend

Key Definitions and Terms

• ORM (Object-Relational Mapping): A technique (or library) to map database tables to classes in a programming language, so you interact with objects rather than SQL directly.
• Async/Await: Keywords in Python for asynchronous programming, allowing multiple tasks to progress without waiting for each other in a blocking fashion.
• ASGI (Asynchronous Server Gateway Interface): A specification for Python web servers that supports async frameworks and concurrency.
• Set-Based Operations: Running queries that operate on multiple rows at once (e.g., UPDATE or DELETE statements with filters) rather than iterating over each row.

Learning Resources

Below are some courses that can help you deepen your Python skills and understanding of web and async concepts.

• Python for Absolute Beginners: An excellent starting point if you’re new to coding or new to Python.
• Rock Solid Python with Python Typing: Dive deep into Python typing to supercharge code reliability and refactoring.
• Async Techniques and Examples in Python

Overall Takeaway

Piccolo ORM showcases how Python’s evolving language features—especially async, type hints, and meta-programming—can bring new levels of clarity, performance, and development speed. With its user-friendly CLI, robust admin dashboard, and focus on bridging both sync and async worlds, Piccolo appeals to a wide range of projects. Whether you’re spinning up a new web application, migrating from Django, or looking to modernize data access in Python, Piccolo provides a compelling, batteries-included path forward.