# Piccolo ORM: async queries, table APIs, and ASGI scaffolding

> Piccolo ORM is a Python ORM and query builder with synchronous and asynchronous APIs, table-oriented queries, migrations, and an ASGI project generator. It suits Python developers who want direct query expressions and asyncio support without adopting Django's application conventions.

**piccolo-orm/piccolo** — A fast, user friendly ORM and query builder which supports asyncio.

- Repository: https://github.com/piccolo-orm/piccolo
- Website: https://piccolo-orm.com/
- Stars: 1,951 · Forks: 124
- Language: Python
- License: MIT
- Published: 2026-10-16 · Updated: 2026-10-16 · Language: en
- Canonical page: https://hysenlabs.com/projects/piccolo-orm-piccolo

## Piccolo ORM's async and synchronous data model

Piccolo solves the problem of expressing database operations without separating every application into async-only and blocking code paths. According to the README, it provides both synchronous and asynchronous support. That matters for Python applications built around `asyncio`, especially ASGI services, because table operations are shown with awaited expressions while retaining a conventional ORM model interface.

The project is aimed at Python developers who want an ORM, a query builder, or both. A query can be written directly against table columns, while application code can create a `Band` object, save it, retrieve it through `Band.objects()`, modify it, and save it again. The same example therefore covers two levels of use: explicit database operations for precise queries and an object-oriented interface for stateful record handling.

The installation metadata requires Python 3.10 or newer. That is the first adoption boundary to check because the formatting configuration also lists older Python targets, but the package metadata in `setup.py` is the relevant installation constraint. Piccolo is not limited to PostgreSQL. The package publishes optional extras for PostgreSQL and SQLite, so the first choice is which database engine the application actually needs.

## Piccolo query and object persistence flow

The clearest mechanism in Piccolo is its table-oriented query chain. A select begins from `Band.select()`, receives selected columns such as `Band.name`, and narrows results with `Band.popularity > 100`. Joins are expressed through related table attributes, including `Band.manager.name`. This keeps the database shape visible in Python rather than hiding it behind an intermediate query representation that the README does not describe.

Updates and deletes use the same table and predicate structure. `Band.update()` receives a dictionary mapping a table column to a value, while `Band.delete()` is followed by a `where` clause. The predicates remain readable database expressions such as `Band.name == 'Pythonistas'`.

For object persistence, `Band(name='C-Sharps', popularity=100)` constructs a model value and `await b.save()` persists it. Retrieval goes through `Band.objects().get(Band.name == 'Pythonistas')`. After changing `b.popularity`, another `save()` writes the change, and `remove()` deletes the object. Based on this flow, Piccolo can serve both application services that favor explicit SQL-like control and codebases that prefer model instances.

The README calls the project fully type annotated and mentions tab completion in IPython and VSCode. Those features may matter during editor adoption, but the repository excerpt does not provide typing coverage measurements or describe the exact protocol used for completion. The claim should therefore be treated as a documented feature, not as evidence of editor compatibility for every setup.

## Installing Piccolo and issuing the first table query

For PostgreSQL, install the package extra that carries the PostgreSQL driver. The README uses single quotes around the extra specification:

```bash
pip install 'piccolo[postgres]'
```

For SQLite, use the SQLite extra instead:

```bash
pip install 'piccolo[sqlite]'
```

If the project genuinely needs every optional dependency, the README offers an `all` extra. That choice increases the dependency set and is broader than a database-specific installation:

```bash
pip install 'piccolo[all]'
```

After a `Band` table has been defined and connected to the database in the project configuration, the README's first query example selects names above a popularity threshold. Database rows returned by the awaited select should match that predicate:

```python
await Band.select(
    Band.name
).where(
    Band.popularity > 100
)
```

The README does not include the table definition, database engine initialization, or configuration file, so it would be misleading to present `Band` as immediately runnable after installation alone. Those missing setup steps are covered by the linked getting-started documentation. The same constraint applies to `save()`, migrations, and ASGI generation: they operate on an application structure that the short README example does not reproduce in full.

## Piccolo app organization and migration boundaries

Piccolo organizes larger codebases with apps that the README describes as similar to Django apps. This is a useful middle ground between a standalone query library and a framework that supplies many application conventions. The repository also includes a built-in playground, authentication, migrations, a User model, and an admin interface as part of its wider feature set.

The boundaries matter. Piccolo itself is the ORM and query builder. Piccolo Admin is a separate project built on top of it, with a Vue.js frontend and REST backend. Piccolo API is also separate and provides utilities for exposing Piccolo tables as REST endpoints in ASGI applications, plus middleware for session authentication, token authentication, rate limiting, CSRF, and Content Security Policy.

This separation can prevent an incorrect adoption decision. A team that needs database access inside an existing Starlette or FastAPI service may only need Piccolo. A team that also needs generated REST endpoints and middleware needs to evaluate Piccolo API. A team seeking an administration interface must account for Piccolo Admin as an additional component. The README does not state that these companion packages are installed by `pip install 'piccolo[all]'`, so their setup should be checked separately.

## Piccolo's ASGI scaffolding choices

Piccolo includes a project generator for ASGI applications. The documented command is `piccolo asgi new`, which creates a new web application using Piccolo as its ORM. This gives Python developers a concrete starting point for connecting the database layer with an HTTP framework.

The supported framework list is broad: Starlette, FastAPI, BlackSheep, Litestar, Ravyn, Lilya, Quart, Falcon, and Sanic. That breadth creates a framework choice at the moment the application is created. It does not imply that every generated application has the same routing, dependency injection, validation, middleware, or deployment behavior. The README names the supported frameworks but does not document generated file layouts or a flag for selecting one.

The command should therefore be used after deciding the web stack and database engine. Running it is more specific than installing every optional dependency. The README does not explain prompts, non-interactive behavior, project-name validation, or generated deployment files, so those points require confirmation in the project documentation before automation is based on the generator.

## Maintenance, upgrades, and Piccolo's MIT license

The repository is not archived, and its last push was on 2026-09-14. The listed releases include 1.36.0 on 2026-07-27, 1.35.0 on 2026-07-01, and 1.34.0 on 2026-05-11. This is evidence of recent repository activity and successive published versions, but it does not establish compatibility between adjacent releases.

The package reads its version from `piccolo.__VERSION__` through `setup.py`. Upgrade planning should account for application code that imports Piccolo APIs, database migrations, optional drivers, and any separate Piccolo API or Piccolo Admin deployment. The repository contains `CHANGES.rst`, but the README does not include migration notes or breaking-change policy, so no automatic upgrade path should be assumed.

Piccolo is distributed under the MIT License. The repository lists a `LICENSE` file and identifies MIT as the project license. That designation permits reuse and modification under the license's conditions, subject to its notice requirements. This is a statement about the declared license, not legal advice about a particular deployment or commercial use.

## Where Piccolo fits against Django-oriented conventions

Django is a real comparison point because the README maintains a page mapping common Django queries to Piccolo and describes Piccolo apps as similar to Django apps. Piccolo therefore targets developers who understand established ORM patterns while wanting table query expressions and optional ASGI project generation.

The ecosystem documentation also positions Piccolo Admin as an alternative to Django Admin and WordPress. Its documented architecture differs: Piccolo Admin uses a Vue.js frontend and a REST backend, with features for filtering, forms, bulk actions, media, multilingual content, mobile use, and multi-factor authentication. That is a separate administrative application, not merely the base ORM.

The available repository documentation does not provide a complete feature-by-feature Django comparison, performance measurement, migration compatibility report, or deployment guide. As a result, Piccolo should not be selected on the claim that it is faster than Django. The defensible comparison is architectural. Piccolo exposes direct table queries, supports awaited operations, and can scaffold one of several named ASGI frameworks, while the repository directly assists Django users with query translations.

## Conclusion

Adopt Piccolo when the application is Python-based, async access matters, and you want table query expressions plus a project generator for a supported ASGI framework. Do not choose it solely from the adjective "fast", because the available repository documentation gives no benchmark or workload comparison. First verify Python 3.10 or newer, select the PostgreSQL or SQLite extra, and check how Piccolo's tables and migrations fit the existing application before scaffolding `piccolo asgi new`.

## FAQ

### How do I install Piccolo ORM for PostgreSQL or SQLite?

Use `pip install 'piccolo[postgres]'` for PostgreSQL or `pip install 'piccolo[sqlite]'` for SQLite. The project also documents `pip install 'piccolo[all]'` for all optional dependencies.

### Does Piccolo ORM support asyncio and synchronous Python?

Yes. The README lists both sync and async support, and its examples use awaited calls such as `await b.save()` and `await Band.objects().get(...)`.

### Can Piccolo ORM create an ASGI web application?

Yes. The documented generator command is `piccolo asgi new`. The README says it scaffolds an application for supported frameworks including FastAPI, Starlette, Litestar, Quart, Falcon, Sanic, BlackSheep, Ravyn, and Lilya.

## Sources

- [License: MIT](https://github.com/piccolo-orm/piccolo/blob/master/LICENSE)
- [piccolo-orm/piccolo on GitHub](https://github.com/piccolo-orm/piccolo)
- [Project website](https://piccolo-orm.com/)
- [README](https://github.com/piccolo-orm/piccolo/blob/master/README.md)
- [Releases](https://github.com/piccolo-orm/piccolo/releases)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/piccolo-orm-piccolo