Skip to content

psycopg Adapter

from sqlspec.adapters.psycopg import (
    PsycopgAsyncConfig,
    PsycopgAsyncDriver,
    PsycopgAsyncExceptionHandler,
    PsycopgConnectionParams,
    PsycopgDriverFeatures,
    PsycopgPoolParams,
    PsycopgSyncConfig,
    PsycopgSyncDriver,
    PsycopgSyncExceptionHandler,
)

Synchronous and asynchronous PostgreSQL adapters built on psycopg v3 and psycopg_pool. Inherited execution methods are documented on SyncDriverAdapterBase and AsyncDriverAdapterBase. See the PostgreSQL (psycopg) Setup Guide for setup and COPY streaming examples.

Configuration & Parameters

PsycopgSyncConfig

Bases: SyncDatabaseConfig[PsycopgSyncConnection, PsycopgConnectionPool, PsycopgSyncDriver]

Configuration for Psycopg synchronous database connections with direct field-based configuration.

PsycopgAsyncConfig

Bases: AsyncDatabaseConfig[PsycopgAsyncConnection, PsycopgAsyncConnectionPool, PsycopgAsyncDriver]

Configuration for Psycopg asynchronous database connections with direct field-based configuration.

PsycopgConnectionParams

Bases: TypedDict

Psycopg connection parameters.

Attributes:

Name Type Description
conninfo NotRequired[str]

PostgreSQL libpq connection URI or keyword/value conninfo string.
host NotRequired[str]

Database server hostname or Unix socket directory.
port NotRequired[int]

Database server TCP port (defaults to 5432).
user NotRequired[str]

PostgreSQL role name used for authentication.
password NotRequired[str]

Password or token for user.
dbname NotRequired[str]

Target PostgreSQL database name.
connect_timeout NotRequired[int]

Connection establishment timeout in seconds.
options NotRequired[str]

Command-line startup GUC options sent to the PostgreSQL backend.
application_name NotRequired[str]

Application identifier reported in pg_stat_activity.
sslmode NotRequired[PsycopgSSLMode]

libpq TLS verification mode (disable, allow, prefer, require, verify-ca, or verify-full).
sslcert NotRequired[str]

Path to the client TLS certificate file.
sslkey NotRequired[str]

Path to the client TLS private key file.
sslrootcert NotRequired[str]

Path to the trusted root CA certificate file.
autocommit NotRequired[bool]

Enable Psycopg autocommit mode outside explicit transactions.
prepare_threshold NotRequired[int | None]

Executions before server-side statement preparation, or None to disable prepared statements.
context NotRequired[PsycopgAdaptContext | None]

Custom Psycopg AdaptContext used for type adaptation.
row_factory NotRequired[PsycopgRowFactory[Any] | PsycopgAsyncRowFactory[Any] | None]

Row factory applied to raw Psycopg connections or cursors.
cursor_factory NotRequired[type[PsycopgCursor[Any] | PsycopgNativeAsyncCursor[Any]] | None]

Custom Psycopg Cursor or AsyncCursor class.
extra NotRequired[dict[str, Any]]

Additional libpq keyword parameters forwarded to connect.

PsycopgPoolParams

Bases: PsycopgConnectionParams

Psycopg pool parameters.

Attributes:

Name Type Description
min_size NotRequired[int]

Minimum number of connections kept open in ConnectionPool / AsyncConnectionPool.
max_size NotRequired[int]

Maximum number of connections allowed in the pool.
connection_class NotRequired[type[PsycopgConnection[Any] | PsycopgNativeAsyncConnection[Any]]]

Custom Psycopg Connection or AsyncConnection subclass.
name NotRequired[str]

Pool identifier used in logs and worker thread names.
timeout NotRequired[float]

Maximum wait time in seconds when acquiring a connection from the pool.
max_waiting NotRequired[int]

Maximum number of queued callers waiting for a connection (0 means no limit).
max_lifetime NotRequired[float]

Maximum connection age in seconds before recycling.
max_idle NotRequired[float]

Maximum idle duration in seconds before shrinking connections above min_size.
reconnect_timeout NotRequired[float]

Seconds to retry failed connection attempts before invoking reconnect_failed.
num_workers NotRequired[int]

Number of background maintenance workers run by psycopg_pool.
open NotRequired[bool | None]

Whether to open the pool immediately upon instantiation.
configure NotRequired[PsycopgConnectionCB[PsycopgSyncConnection] | PsycopgAsyncConnectionCB[PsycopgAsyncConnection] | None]

Callback run once on each new physical connection before entering the pool.
check NotRequired[PsycopgConnectionCB[PsycopgSyncConnection] | PsycopgAsyncConnectionCB[PsycopgAsyncConnection] | None]

Health-check callback run before lending out a pooled connection.
reset NotRequired[PsycopgConnectionCB[PsycopgSyncConnection] | PsycopgAsyncConnectionCB[PsycopgAsyncConnection] | None]

Reset callback run when returning a connection to the pool.
close_returns NotRequired[bool]

Make conn.close() return the connection to the pool instead of closing the socket.
reconnect_failed NotRequired[PsycopgConnectFailedCB | PsycopgAsyncConnectFailedCB | None]

Callback invoked when pool reconnection attempts exceed reconnect_timeout.
kwargs NotRequired[dict[str, Any]]

Explicit connection keyword mapping passed to psycopg.connect.
null_pool NotRequired[bool]

Use NullConnectionPool / AsyncNullConnectionPool so idle connections are never retained.

PsycopgDriverFeatures

Bases: TypedDict

Optional Psycopg driver feature flags, extension codecs, AlloyDB settings, and pooler modes.

Attributes:

Name Type Description
enable_pgvector NotRequired[bool]

Register pgvector adapters and promote the dialect when pgvector is installed.
enable_paradedb NotRequired[bool]

Detect pg_search and promote the SQL dialect to paradedb (defaults to True).
enable_pg_textsearch NotRequired[bool]

Detect pg_textsearch and promote the dialect for BM25 <@> ranking (defaults to True).
json_serializer NotRequired[Callable[[Any], str]]

Custom JSON serializer applied to statement parameters and Psycopg JSON dumpers.
json_deserializer NotRequired[Callable[[str], Any]]

Custom JSON deserializer applied to Psycopg JSON/JSONB loaders.
on_connection_create NotRequired[Callable[..., Any]]

Sync or async callback invoked with each raw Psycopg connection after internal setup.
enable_events NotRequired[bool]

Enable SQLSpec event channel pub/sub support (defaults to True when events is configured).
events_backend NotRequired[Literal['notify', 'notify_queue', 'poll_queue']]

Event transport backend (notify for LISTEN/NOTIFY, notify_queue, or poll_queue).
enable_alloydb NotRequired[bool]

Route sync connections through google-cloud-alloydb-connector (defaults to False).
alloydb_instance_uri NotRequired[str]

AlloyDB instance resource URI (projects/.../locations/.../clusters/.../instances/...).
enable_alloydb_iam_auth NotRequired[bool]

Use IAM database authentication with the AlloyDB Python Connector.
alloydb_ip_type NotRequired[str]

AlloyDB network path (PUBLIC, PRIVATE, or PSC; defaults to PRIVATE).
enable_alloydb_columnar NotRequired[bool]

Enable google_columnar_engine.enabled=on via startup options or per-transaction SET LOCAL.
pooler_mode NotRequired[Literal['session', 'transaction', 'statement', 'auto']]

External pooler compatibility mode; sets prepare_threshold=None in transaction or statement mode.
pgbouncer NotRequired[bool]

Alias for pooler_mode='transaction' disabling server-side prepared statements.
pgbouncer_mode NotRequired[bool]

Alias for pooler_mode='transaction' disabling server-side prepared statements.
direct_dsn NotRequired[str | Mapping[str, Any] | None]

Direct PostgreSQL DSN or connection mapping bypassing the pooler for migrations and LISTEN/NOTIFY.
null_pool NotRequired[bool]

Enable NullConnectionPool / AsyncNullConnectionPool for serverless or external pooler deployments.

Driver & Pool

PsycopgSyncDriver

Bases: PsycopgPipelineMixin, SyncDriverAdapterBase

PostgreSQL psycopg synchronous driver.

Provides synchronous database operations for PostgreSQL using psycopg3. Supports SQL statement execution with parameter binding, transaction management, result processing with column metadata, parameter style conversion, PostgreSQL arrays and JSON handling, COPY operations for bulk data transfer, and PostgreSQL-specific error handling.

PsycopgAsyncDriver

Bases: PsycopgPipelineMixin, AsyncDriverAdapterBase

PostgreSQL psycopg asynchronous driver.

Provides asynchronous database operations for PostgreSQL using psycopg3. Supports async SQL statement execution with parameter binding, async transaction management, async result processing with column metadata, parameter style conversion, PostgreSQL arrays and JSON handling, COPY operations for bulk data transfer, PostgreSQL-specific error handling, and async pub/sub support.

PsycopgSyncExceptionHandler

Bases: BaseSyncExceptionHandler

Context manager for handling PostgreSQL psycopg database exceptions.

Maps PostgreSQL SQLSTATE error codes to SQLSpec exceptions stored on pending_exception.

PsycopgAsyncExceptionHandler

Bases: BaseAsyncExceptionHandler

Async context manager for handling PostgreSQL psycopg database exceptions.

Maps PostgreSQL SQLSTATE error codes to SQLSpec exceptions stored on pending_exception.

Extension Stores

PsycopgSyncStore

Bases: BaseSQLSpecStore['PsycopgSyncConfig']

PostgreSQL session store using Psycopg sync driver.

Implements server-side session storage for Litestar using PostgreSQL via the synchronous Psycopg (psycopg3) driver. Uses Litestar's sync_to_thread utility to provide an async interface compatible with the Store protocol.

Provides efficient session management with
  • Sync operations wrapped for async compatibility
  • UPSERT support using ON CONFLICT
  • Automatic expiration handling
  • Efficient cleanup of expired sessions

Parameters:

Name Type Description Default
config PsycopgSyncConfig

PsycopgSyncConfig instance.

 required

PsycopgAsyncStore

Bases: BaseSQLSpecStore['PsycopgAsyncConfig']

PostgreSQL session store using Psycopg async driver.

Implements server-side session storage for Litestar using PostgreSQL via the Psycopg (psycopg3) async driver. Provides efficient session management with: - Native async PostgreSQL operations - UPSERT support using ON CONFLICT - Automatic expiration handling - Efficient cleanup of expired sessions

Parameters:

Name Type Description Default
config PsycopgAsyncConfig

PsycopgAsyncConfig instance.

 required

PsycopgSyncADKStore

Bases: BaseSyncADKStore['PsycopgSyncConfig']

PostgreSQL synchronous ADK store using Psycopg3 driver.

PsycopgAsyncADKStore

Bases: BaseAsyncADKStore['PsycopgAsyncConfig']

PostgreSQL ADK store using Psycopg3 async driver.

Implements session and event storage for Google Agent Development Kit using PostgreSQL via psycopg3 with native async/await support. Events are stored as a single JSONB blob (event_data) alongside indexed scalar columns for efficient querying.

Provides
  • Session state management with JSONB storage
  • Full-fidelity event storage via event_data JSONB column
  • Atomic append_event_and_update_state for durable session mutations
  • Microsecond-precision timestamps with TIMESTAMPTZ
  • Foreign key constraints with cascade delete
  • GIN indexes for JSONB queries
  • HOT updates with FILLFACTOR 80

Parameters:

Name Type Description Default
config PsycopgAsyncConfig

PsycopgAsyncConfig with extension_config["adk"] settings.

 required

PsycopgSyncADKMemoryStore

Bases: BaseSyncADKMemoryStore['PsycopgSyncConfig']

Psycopg sync ADK memory store implementation.

PsycopgAsyncADKMemoryStore

Bases: BaseAsyncADKMemoryStore['PsycopgAsyncConfig']

PostgreSQL ADK memory store using Psycopg3 async driver.

PsycopgSyncEventQueueStore

Bases: BaseEventQueueStore[PsycopgSyncConfig]

Queue DDL for psycopg synchronous configs.

PostgreSQL uses JSONB for efficient binary JSON storage with indexing support, and TIMESTAMPTZ for timezone-aware timestamps.

PsycopgAsyncEventQueueStore

Bases: BaseEventQueueStore[PsycopgAsyncConfig]

Queue DDL for psycopg async configs.

PostgreSQL uses JSONB for efficient binary JSON storage with indexing support, and TIMESTAMPTZ for timezone-aware timestamps.