prefect.server.utilities.database

Utilities for interacting with Prefect REST API database and ORM layer.

Prefect supports both SQLite and Postgres. Many of these utilities allow the Prefect REST API to seamlessly switch between the two.

Functions

db_injector ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L77" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

db_injector(func: Union[_DBMethod[T, P, R], _DBFunction[P, R]]) -> Union[_Method[T, P, R], _Function[P, R]]

generate_uuid_postgresql ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L96" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

generate_uuid_postgresql(element: GenerateUUID, compiler: SQLCompiler, **kwargs: Any) -> str

Generates a random UUID in Postgres; requires the pgcrypto extension.

generate_uuid_sqlite ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L107" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

generate_uuid_sqlite(element: GenerateUUID, compiler: SQLCompiler, **kwargs: Any) -> str

Generates a random UUID in other databases (SQLite) by concatenating bytes in a way that approximates a UUID hex representation. This is sufficient for our purposes of having a random client-generated ID that is compatible with a UUID spec.

bindparams_from_clause ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L327" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

bindparams_from_clause(query: sa.ClauseElement) -> dict[str, sa.BindParameter[Any]]

Retrieve all non-anonymous bind parameters defined in a SQL clause

datetime_or_interval_add_postgresql ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L425" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

datetime_or_interval_add_postgresql(element: Union[date_add, interval_add, date_diff], compiler: SQLCompiler, **kwargs: Any) -> str

date_diff_seconds_postgresql ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L435" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

date_diff_seconds_postgresql(element: date_diff_seconds, compiler: SQLCompiler, **kwargs: Any) -> str

current_timestamp_sqlite ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L507" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

current_timestamp_sqlite(element: functions.now, compiler: SQLCompiler, **kwargs: Any) -> str

Generates the current timestamp for SQLite

date_add_sqlite ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L515" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

date_add_sqlite(element: date_add, compiler: SQLCompiler, **kwargs: Any) -> str

interval_add_sqlite ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L523" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

interval_add_sqlite(element: interval_add, compiler: SQLCompiler, **kwargs: Any) -> str

date_diff_sqlite ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L532" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

date_diff_sqlite(element: date_diff, compiler: SQLCompiler, **kwargs: Any) -> str

date_diff_seconds_sqlite ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L539" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

date_diff_seconds_sqlite(element: date_diff_seconds, compiler: SQLCompiler, **kwargs: Any) -> str

sqlite_json_operators ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L690" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

sqlite_json_operators(element: sa.BinaryExpression[Any], compiler: SQLCompiler, override_operator: Optional[OperatorType] = None, **kwargs: Any) -> str

Intercept the PostgreSQL-only JSON / JSONB operators and translate them to SQLite

sqlite_greatest_as_max ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L709" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

sqlite_greatest_as_max(element: greatest[Any], compiler: SQLCompiler, **kwargs: Any) -> str

sqlite_least_as_min ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L732" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

sqlite_least_as_min(element: least[Any], compiler: SQLCompiler, **kwargs: Any) -> str

get_dialect ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L741" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

get_dialect(obj: Union[str, Session, sa.Engine]) -> type[sa.Dialect]

Get the dialect of a session, engine, or connection url.

Primary use case is figuring out whether the Prefect REST API is communicating with SQLite or Postgres.

Classes

GenerateUUID ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L85" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

Platform-independent UUID default generator. Note the actual functionality for this class is specified in the compiles-decorated functions below

Timestamp ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L133" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

TypeDecorator that ensures that timestamps have a timezone.

For SQLite, all timestamps are converted to UTC (since they are stored as naive timestamps without timezones) and recovered as UTC.

Methods:

load_dialect_impl ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L143" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

load_dialect_impl(self, dialect: sa.Dialect) -> TypeEngine[Any]

process_bind_param ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L155" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

process_bind_param(self, value: Optional[datetime.datetime], dialect: sa.Dialect) -> Optional[datetime.datetime]

process_result_value ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L170" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

process_result_value(self, value: Optional[datetime.datetime], dialect: sa.Dialect) -> Optional[datetime.datetime]

UUID ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L183" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

Platform-independent UUID type.

Uses PostgreSQL's UUID type, otherwise uses CHAR(36), storing as stringified hex values with hyphens.

Methods:

load_dialect_impl ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L195" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

load_dialect_impl(self, dialect: sa.Dialect) -> TypeEngine[Any]

process_bind_param ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L201" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

process_bind_param(self, value: Optional[Union[str, uuid.UUID]], dialect: sa.Dialect) -> Optional[str]

process_result_value ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L213" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

process_result_value(self, value: Optional[Union[str, uuid.UUID]], dialect: sa.Dialect) -> Optional[uuid.UUID]

JSON ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L224" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

JSON type that returns SQLAlchemy's dialect-specific JSON types, where possible. Uses generic JSON otherwise.

The "base" type is postgresql.JSONB to expose useful methods prior to SQL compilation

Methods:

load_dialect_impl ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L238" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

load_dialect_impl(self, dialect: sa.Dialect) -> TypeEngine[Any]

process_bind_param ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L246" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

process_bind_param(self, value: Optional[Any], dialect: sa.Dialect) -> Optional[Any]

Prepares the given value to be used as a JSON field in a parameter binding

Pydantic ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L267" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

A pydantic type that converts inserted parameters to json and converts read values to the pydantic type.

Methods:

process_bind_param ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L306" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

process_bind_param(self, value: Optional[T], dialect: sa.Dialect) -> Optional[str]

process_result_value ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L320" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

process_result_value(self, value: Optional[Any], dialect: sa.Dialect) -> Optional[T]

date_add ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L346" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

Platform-independent way to add a timestamp and an interval

interval_add ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L365" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

Platform-independent way to add two intervals.

date_diff ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L384" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

Platform-independent difference of two timestamps. Computes d1 - d2.

date_diff_seconds ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L401" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

Platform-independent calculation of the number of seconds between two timestamps or from 'now'

greatest ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L704" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}

least ^{<a href="https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L727" target="_blank"><Icon icon="github" style="width: 14px; height: 14px;" /></a>}