Piccolo ORM
Fast async Python ORM with a focus on type safety and developer experience — supports sync and async usage, generates migrations automatically, and provides a FastAPI/Starlette-native experience. Piccolo features: Table subclasses for models (class Agent(Table): name = Varchar()), query builder API (await Agent.select().where(Agent.active == True).run()), migrations via piccolo migrations new + piccolo migrations run, Piccolo Admin (React-based admin UI), and REST endpoint generation via piccolo_api. Uses asyncpg under the hood for PostgreSQL. CLI tools for project creation, schema inspection, and migration management. Designed as async-first alternative to SQLAlchemy for modern Python API services.
Score Breakdown
⚙ Agent Friendliness
🔒 Security
Piccolo uses parameterized queries preventing SQL injection. piccolo_api JWT auth for REST endpoints requires secure secret key management for agent services. Piccolo Admin default credentials must be changed in production; admin exposes full database access for agent tables.
⚡ Reliability
Best When
You're building a new FastAPI/Starlette agent service with PostgreSQL and want an async-first ORM with built-in migrations and admin — Piccolo provides a cohesive async-native Python data layer.
Avoid When
You're on MySQL/MSSQL, you need SQLAlchemy's extensive ecosystem, or your team is already proficient with Django ORM or SQLAlchemy.
Use Cases
- • Async FastAPI agent data layer — await Agent.select(Agent.name, Agent.status).where(Agent.user_id == user_id).run() in FastAPI async route; Piccolo async queries keep FastAPI event loop non-blocking for agent list endpoints
- • Type-safe agent queries with Piccolo — Agent.status == 'active' uses Python operators for type-safe query building; IDE autocomplete on Table columns prevents typo-based agent query bugs at development time
- • Agent database migrations with Piccolo CLI — piccolo migrations new generates migration from Table class changes; piccolo migrations run applies migrations; version-controlled agent schema evolution without manual SQL
- • Piccolo Admin for agent management — mount PiccoloAdmin with [AgentAdmin] for auto-generated React admin UI over agent tables; zero-config admin dashboard for agent platform operations
- • REST API auto-generation — piccolo_api TableSource(Agent) generates CRUD REST endpoints for Agent table; scaffolds agent API endpoints without manual FastAPI route writing for simple agent CRUD
Not For
- • Complex legacy databases — Piccolo works best with new schemas; introspecting and mapping complex existing agent databases with unusual constraints is less mature than SQLAlchemy's reflection
- • Non-PostgreSQL databases — Piccolo supports PostgreSQL and SQLite; for agent services on MySQL, Oracle, or MS SQL Server, use SQLAlchemy
- • Teams familiar with Django ORM or SQLAlchemy — Piccolo has different query API; learning curve for teams comfortable with Django migrations or SQLAlchemy Core; migration cost may not justify Piccolo's async benefits
Interface
Authentication
ORM library — connects to PostgreSQL via DB_URL. piccolo_api includes JWT auth for REST endpoints. Piccolo Admin has built-in session auth.
Pricing
Piccolo ORM is MIT licensed. Free for all use.
Agent Metadata
Known Gotchas
- ⚠ DB_URL environment variable is required — Piccolo reads connection from DB engine config or piccolo_conf.py; without piccolo_conf.py or environment variable, queries raise EngineNotFound; always configure engine via piccolo_conf.py or DB_URL before running agent service
- ⚠ Table classes must be registered for migrations — Piccolo migrations scan APP_MODULES in piccolo_conf.py to detect Table changes; Table classes in modules not registered are invisible to piccolo migrations new; unregistered agent Table classes cause missing migrations and schema drift
- ⚠ Piccolo queries are lazy by default — Agent.select().where(...) returns query object, not results; must call .run() or await it to execute; Agent.objects().where(...) also lazy; forgetting .run() in async agent handlers returns coroutine not data causing AttributeError downstream
- ⚠ run_sync() blocks asyncio event loop — Piccolo provides run_sync() for sync context; calling run_sync() inside async function blocks event loop; use await .run() inside async def agent handlers; run_sync() only for scripts, CLI, and non-async agent contexts
- ⚠ Piccolo Admin requires piccolo_admin package separately — piccolo-admin is separate pip package from piccolo-orm; missing installation causes ImportError when mounting PiccoloAdmin; piccolo-api is also separate; agent projects need multiple piccolo pip packages depending on features used
- ⚠ Migration dependencies require careful ordering — Piccolo migrations in multi-app agents need explicit dependencies list; migrations touching shared tables from different piccolo apps must declare cross-app dependencies; missing dependency causes migration to run out of order leaving agent database in inconsistent state
Alternatives
Full Evaluation Report
Detailed scoring breakdown, competitive positioning, security analysis, and improvement recommendations for Piccolo ORM.
Scores are editorial opinions as of 2026-03-06.