peewee

Peewee is a lightweight, single-module Python ORM that has powered production workloads since 2010. It exposes a fluent, chainable query builder on top of active-record style models, with first-class support for SQLite, MySQL/MariaDB, PostgreSQL, and CockroachDB.

Version 4 adds native asyncio support built on standard async drivers (aiosqlite, asyncpg, aiomysql), and the bundled playhouse package supplies dozens of extensions — migrations, connection pooling, dataset/reflection helpers, and Flask/FastAPI/Pydantic integration — without adding required dependencies to the core library.

What You Get

• Active-record style Model classes with declarative Field definitions for SQLite, MySQL, MariaDB, PostgreSQL, and CockroachDB.
• A fluent, chainable query builder (select/where/join/group_by/order_by) that compiles to native SQL via a shared Node/Context primitive system.
• First-class asyncio support (playhouse.pwasyncio) built on aiosqlite, asyncpg, and aiomysql for async ORM usage without a separate API.
• Dozens of playhouse extensions: migrations, connection pooling, dataset/reflection utilities, Flask/FastAPI/Pydantic integration helpers, and SQLite-specific full-text search and JSON support.

Common Use Cases

• Building a Flask or FastAPI backend that needs a lightweight ORM without Django’s full-stack footprint.
• Rapid CLI scripts and data pipelines that read/write a local SQLite database through a Pythonic query API instead of raw SQL strings.
• Async web services that need non-blocking database access without switching to a heavier async-only ORM.

Under The Hood

Architecture - Peewee compiles queries through a small set of composable Node/Context primitives defined early in peewee.py (Node at line 766, Context at line 616) — every SQL-producing object (Table, Column, Expression, Function, Window, CTE) implements sql(ctx) against a shared Context that tracks scope, parentheses, and parameter binding, so the same builder underlies raw SQL(), the fluent Select/Update/Delete/Insert query objects (BaseQuery at 2097, Select at 2472), and the ORM’s Model layer built on top. Relations resolve through a separate prefetch/materialize subsystem that walks parent/child buckets after execution rather than joining eagerly by default, letting one-to-many prefetches stay efficient instead of producing a cartesian join. Database-specific behavior (SQLite, Postgres, MySQL, CockroachDB) is isolated into playhouse/*_ext.py modules and Database subclasses rather than branching inside the core, keeping the single 9,468-line peewee.py focused on the database-agnostic SQL/ORM layer.

Tech Stack - The library is pure Python 3 with zero hard dependencies — SQLite support comes from the stdlib sqlite3 module (or pysqlite3 if a newer bundled SQLite is preferred), while Postgres/MySQL drivers (psycopg2, psycopg3, pymysql/MySQLdb) are optional extras declared in pyproject.toml. An optional Cython extension (playhouse/_sqlite_udf.pyx, built via setup.py when Cython is available) accelerates a handful of SQLite user-defined functions. Version 4.x adds first-class asyncio support (playhouse/pwasyncio.py) built on standard async drivers (aiosqlite, asyncpg, aiomysql) bridged onto the sync core via greenlet, so async and sync code share the same query-building layer rather than duplicating it.

Code Quality - The tests/ directory alone is roughly 40,600 lines across 43 files with about 1,926 individual test functions (tests/models.py, tests/fields.py, tests/db_tests.py, plus per-backend suites for MySQL/Postgres/CockroachDB/SQLCipher), run via a dedicated runtests.py harness and CI — a test suite larger than the library itself. Error handling follows the DB-API 2.0 exception hierarchy (PeeweeException to DatabaseError to DataError/IntegrityError/OperationalError/ProgrammingError, around line 3510), so failures surface as familiar, catchable types rather than raw driver exceptions. The project also ships hand-maintained type stubs (peewee-stubs/*.pyi, packaged via package_data) and a typecheck/ directory, giving static analyzers signal despite the library itself being untyped Python.

API Design - Peewee’s public API reads as a fluent, chainable query builder — User.select().where(User.username == ‘x’).join(Tweet) — deliberately mirroring Django/SQLAlchemy idioms so the learning curve is shallow for anyone who has used either. Model definition is pure declarative Python (class attributes as Field instances, Meta.database for the connection), with zero required boilerplate beyond defining fields and a database instance. Documentation is unusually thorough for a single-module project: a full docs/ site hosted on ReadTheDocs, a dedicated quickstart, an “example twitter app” walkthrough, and three runnable example apps (blog, twitter, analytics) under examples/, plus per-topic guides for asyncio, migrations, and framework integration (Flask, FastAPI, Pydantic).