$ show piccolo-orm/piccolo
Piccolo is an async-first Python ORM and query builder targeting PostgreSQL, SQLite, and CockroachDB. It provides a declarative table definition system, automatic schema migrations, a CLI for project scaffolding, user authentication, fixture dump/load, and Pydantic model generation. The codebase is well-structured with clean abstractions (column types, query delegates, engine abstraction, migration auto-generation) and an extensive test suite covering multiple database backends. It is actively maintained with frequent releases and supports recent Python and database versions.
The LLM identified 4 files worth highlighting for reuse:
ModelBuilder generates random instances of Piccolo Table classes for testing. It handles foreign keys, recursive relationships, and custom column defaults. The pattern is self-contained and can be extracted for any ORM-based testing to produce realistic fake data without external dependencies.
create_pydantic_model converts Piccolo Table definitions to Pydantic v2 models with automatic validation, nested models for foreign keys, and support for include/exclude columns. This is a reusable pattern for bridging ORM models with serialization/validation layers in any Python web framework.
QueryString is a central component for building parameterized SQL with automatic escaping. It supports operators, nesting, and raw SQL insertion. The pattern demonstrates safe SQL construction that can be reused in any database access layer to prevent injection while maintaining composability.
MigrationManager orchestrates schema changes by comparing DiffableTable snapshots and executing operations (add/drop tables, columns, constraints). It handles transactional execution, engine-specific DDL, and serialization of migration scripts. This pattern is a blueprint for building auto-migration systems in any ORM.