> ## Documentation Index
> Fetch the complete documentation index at: /docs/llms.txt
> Use this file to discover all available pages before exploring further.

# database

# `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` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L78](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L78)</sup>

```python theme={null}
db_injector(func: Union[_DBMethod[T, P, R], _DBFunction[P, R]]) -> Union[_Method[T, P, R], _Function[P, R]]
```

### `generate_uuid_postgresql` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L97](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L97)</sup>

```python theme={null}
generate_uuid_postgresql(element: GenerateUUID, compiler: SQLCompiler, **kwargs: Any) -> str
```

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

### `generate_uuid_sqlite` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L108](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L108)</sup>

```python theme={null}
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` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L328](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L328)</sup>

```python theme={null}
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` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L426](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L426)</sup>

```python theme={null}
datetime_or_interval_add_postgresql(element: Union[date_add, interval_add, date_diff], compiler: SQLCompiler, **kwargs: Any) -> str
```

### `date_diff_seconds_postgresql` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L436](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L436)</sup>

```python theme={null}
date_diff_seconds_postgresql(element: date_diff_seconds, compiler: SQLCompiler, **kwargs: Any) -> str
```

### `current_timestamp_sqlite` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L508](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L508)</sup>

```python theme={null}
current_timestamp_sqlite(element: functions.now, compiler: SQLCompiler, **kwargs: Any) -> str
```

Generates the current timestamp for SQLite

### `date_add_sqlite` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L516](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L516)</sup>

```python theme={null}
date_add_sqlite(element: date_add, compiler: SQLCompiler, **kwargs: Any) -> str
```

### `interval_add_sqlite` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L524](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L524)</sup>

```python theme={null}
interval_add_sqlite(element: interval_add, compiler: SQLCompiler, **kwargs: Any) -> str
```

### `date_diff_sqlite` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L533](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L533)</sup>

```python theme={null}
date_diff_sqlite(element: date_diff, compiler: SQLCompiler, **kwargs: Any) -> str
```

### `date_diff_seconds_sqlite` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L540](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L540)</sup>

```python theme={null}
date_diff_seconds_sqlite(element: date_diff_seconds, compiler: SQLCompiler, **kwargs: Any) -> str
```

### `sqlite_json_operators` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L691](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L691)</sup>

```python theme={null}
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` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L710](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L710)</sup>

```python theme={null}
sqlite_greatest_as_max(element: greatest[Any], compiler: SQLCompiler, **kwargs: Any) -> str
```

### `sqlite_least_as_min` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L733](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L733)</sup>

```python theme={null}
sqlite_least_as_min(element: least[Any], compiler: SQLCompiler, **kwargs: Any) -> str
```

### `get_dialect` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L742](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L742)</sup>

```python theme={null}
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.

### `get_max_query_parameters` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L773](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L773)</sup>

```python theme={null}
get_max_query_parameters() -> int
```

The number of bind parameters a single statement may carry.

Both drivers reject a statement over the limit, so any query built from a
variable number of rows must batch itself under it.

## Classes

### `GenerateUUID` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L86](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L86)</sup>

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

### `Timestamp` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L134](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L134)</sup>

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` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L144](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L144)</sup>

```python theme={null}
load_dialect_impl(self, dialect: sa.Dialect) -> TypeEngine[Any]
```

#### `process_bind_param` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L156](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L156)</sup>

```python theme={null}
process_bind_param(self, value: Optional[datetime.datetime], dialect: sa.Dialect) -> Optional[datetime.datetime]
```

#### `process_result_value` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L171](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L171)</sup>

```python theme={null}
process_result_value(self, value: Optional[datetime.datetime], dialect: sa.Dialect) -> Optional[datetime.datetime]
```

### `UUID` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L184](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L184)</sup>

Platform-independent UUID type.

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

**Methods:**

#### `load_dialect_impl` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L196](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L196)</sup>

```python theme={null}
load_dialect_impl(self, dialect: sa.Dialect) -> TypeEngine[Any]
```

#### `process_bind_param` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L202](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L202)</sup>

```python theme={null}
process_bind_param(self, value: Optional[Union[str, uuid.UUID]], dialect: sa.Dialect) -> Optional[str]
```

#### `process_result_value` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L214](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L214)</sup>

```python theme={null}
process_result_value(self, value: Optional[Union[str, uuid.UUID]], dialect: sa.Dialect) -> Optional[uuid.UUID]
```

### `JSON` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L225](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L225)</sup>

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` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L239](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L239)</sup>

```python theme={null}
load_dialect_impl(self, dialect: sa.Dialect) -> TypeEngine[Any]
```

#### `process_bind_param` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L247](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L247)</sup>

```python theme={null}
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` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L268](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L268)</sup>

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

**Methods:**

#### `process_bind_param` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L307](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L307)</sup>

```python theme={null}
process_bind_param(self, value: Optional[T], dialect: sa.Dialect) -> Optional[str]
```

#### `process_result_value` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L321](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L321)</sup>

```python theme={null}
process_result_value(self, value: Optional[Any], dialect: sa.Dialect) -> Optional[T]
```

### `date_add` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L347](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L347)</sup>

Platform-independent way to add a timestamp and an interval

### `interval_add` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L366](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L366)</sup>

Platform-independent way to add two intervals.

### `date_diff` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L385](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L385)</sup>

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

### `date_diff_seconds` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L402](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L402)</sup>

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

### `greatest` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L705](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L705)</sup>

### `least` <sup>[https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L728](https://github.com/PrefectHQ/prefect/blob/main/src/prefect/server/utilities/database.py#L728)</sup>