Great question — and you’re right: out of the box, Prefect Server (Prefect 3.x) only supports PostgreSQL and SQLite as the orchestration database. Oracle isn’t supported today. If you’d like to add Oracle as a supported backend, here’s the path to a viable PR, with the main components you’ll need to implement and the key places in the codebase to touch. I’ll also call out the biggest blockers to be aware of. High-level plan - Confirm an async path to Oracle in SQLAlchemy - Prefect’s server uses SQLAlchemy’s AsyncEngine/AsyncSession. You’ll need either: - A true async Oracle DBAPI supported by SQLAlchemy’s asyncio stack, or - To use SQLAlchemy’s async fallback to run a sync DBAPI in a threadpool (if supported for Oracle). - See: - SQLAlchemy asyncio docs - SQLAlchemy Oracle dialect - python-oracledb async guide - Add an Oracle database configuration (engine/session) - Implement an Oracle-specific configuration alongside Postgres/SQLite to create an async engine and sessions (or an async-fallback engine if a native async DBAPI is not available). - Code location: - src/prefect/server/database/configurations.py - You’ll wire up pool settings, connect args, and engine creation similar to existing backends. - Implement Oracle query components (dialect-specific SQL) - Prefect routes certain SQL constructs through “query components” to handle JSON, timestamps, inserts/merges, etc. You’ll need an Oracle implementation. - Code location: - src/prefect/server/database/query_components.py - Key areas to cover: - JSON construction/aggregation (JSON_OBJECT / JSON_ARRAYAGG) - Timestamp arithmetic and intervals - Upsert/merge semantics (Oracle MERGE) - Any JSON containment checks used by Prefect queries - Add an Oracle ORM/migrations branch - Prefect maintains dialect-specific Alembic migrations; you’ll add an “oracle” branch parallel to sqlite/postgresql and ensure types/indexes compile. - Code locations: - ORM config: src/prefect/server/database/orm_models.py - Migrations env + versions: src/prefect/server/database/_migrations - Things to adapt in migrations: - UUIDs (e.g., use SYS_GUID() or store as RAW(16)/CHAR(36)) - Timestamps with time zone - JSON columns (Oracle JSON or CLOB with JSON check) - Enums → VARCHAR + CHECK - Extension-dependent indexes (pg_trgm) → Oracle Text or alternatives - Wire the dialect into dependency injection - Teach Prefect how to instantiate your Oracle configuration, query components, and ORM config when it sees an Oracle connection URL. - Code location: - src/prefect/server/database/dependencies.py - Add Oracle-specific SQL compilation helpers (types/functions) - Prefect has utility compilers for UUIDs, timestamps, JSON ops, etc. Provide Oracle compiles where needed. - Code location: - src/prefect/server/utilities/database.py - Update settings and docs - Add allowed driver strings and connection URL guidance (e.g.,

oracle+oracledb://...

or appropriate async variant). - Code location: - src/prefect/settings/models/server/database.py - Tests and CI

- Add unit/integration tests for the Oracle path and, ideally, a CI job that spins up an Oracle XE container to run DB tests. - Code locations: - tests/server/database - CI setup: .github/workflows Key blockers and gotchas - Async driver availability and SQLAlchemy support - If there isn’t a first-class async SQLAlchemy dialect for Oracle, you’ll need to confirm SQLAlchemy’s supported “async fallback” (run sync driver in a threadpool) works reliably for Prefect’s load patterns. - Postgres-specific features in Prefect - LISTEN/NOTIFY: Prefect’s real-time features (e.g., certain event/automation flows) historically used Postgres’s NOTIFY/LISTEN. Oracle doesn’t have a drop-in replacement. You’ll need to: - Provide a polling-based alternative, or - Feature-flag/disable these bits when running on Oracle. - Trigram/text search (pg_trgm): You’ll need Oracle Text or alternative indexing strategies. - JSONB operators and containment: Re-express with Oracle JSON functions and checks. - Enums and array types: Map to supported Oracle constructs (VARCHAR + CHECK; avoid ARRAY). Practical sequence to get a prototype running 1) Engine/session: - Implement an Oracle configuration that returns an AsyncEngine/AsyncSession (using async DBAPI or SQLAlchemy’s async fallback). 2) Migrations: - Stand up an “oracle” versions branch; get core tables creating/dropping cleanly. 3) Queries: - Implement the minimal set of query components to run the API (JSON ops, timestamp math, inserts/merges). 4) Boot the API: - Start Prefect Server pointing at your Oracle URL via

PREFECT_API_DATABASE_CONNECTION_URL

, exercise CRUD flows/runs/artifacts. 5) Fill gaps: - Address any failures tied to Postgres-only paths (notifications, text search). 6) Tests/CI: - Add coverage and wire up an Oracle XE for CI runs. Helpful repo entry points - Database package: src/prefect/server/database - Configurations: configurations.py - Query components: query_components.py - ORM config: orm_models.py - DI wiring: dependencies.py - Migrations (env/versions): _migrations/ - DB utilities (custom compiles): utilities/database.py Recommendation - If you’re considering building this, I’d suggest first opening a GitHub Discussion to align on scope and approach and to confirm any architectural constraints (especially around async support and notifications). We can help review and guide a phased PR plan: - Open a Prefect discussion If you can share: - Which Oracle version/driver you plan to use - Whether you can rely on SQLAlchemy async-fallback vs. a native async DBAPI …I can sketch a minimal “AsyncOracleConfiguration” and the first migration file to get you started.