python prefect server database reference#

reference documentation for understanding how the Python prefect server implements its database layer. useful for ensuring our Zig implementation stores data compatibly.

contents#

• architecture.md - overall structure, abstraction layers, backend selection
• orm-models.md - SQLAlchemy models, relationships, custom types
• migrations.md - alembic setup, dialect-specific migrations
• query-patterns.md - dialect differences, transaction handling, connection pooling
• zig-compat-notes.md - comparison with our zig implementation, gaps to address

key insight#

the Python implementation uses a three-tier abstraction:

1. DatabaseConfiguration - connection/engine management (AsyncPostgresConfiguration, AioSqliteConfiguration)
2. QueryComponents - dialect-specific SQL generation (json handling, time series, updates)
3. ORMConfiguration - migration paths, upsert columns

this allows the same codebase to work with both SQLite and PostgreSQL by swapping implementations at runtime based on connection URL.

source location#

~/github.com/prefecthq/prefect/src/prefect/server/database/