PostgreSQL — asyncpg¶
sqlspec.adapters.asyncpg wraps MagicStack's compiled asyncpg driver, delivering low-latency binary-protocol execution, managed asyncio connection pooling, Cloud SQL and AlloyDB IAM connectors, LISTEN/NOTIFY events, and pgvector / ParadeDB ADK stores.
Overview & Capabilities¶
Choose AsyncpgConfig for pure-asyncio APIs and background workers that prioritize binary-protocol throughput, per-statement query timeouts, and automatic custom type codecs. If the same codebase also requires synchronous DB-API connections, see psycopg.
| Capability | Details |
|---|---|
| Adapter Config | AsyncpgConfig (sqlspec.adapters.asyncpg) |
| Execution Mode | Asynchronous (asyncio) |
| Pool Architecture | asyncpg.Pool (min_size, max_size, max_queries, max_inactive_connection_lifetime) |
| Cloud Connectors | Google Cloud SQL (enable_cloud_sql), AlloyDB (enable_alloydb), IAM (auth_token_provider) |
| Extensions | pgvector, ParadeDB pg_search BM25, pg_textsearch, LISTEN/NOTIFY event channels |
Installation & Quickstart¶
from sqlspec import SQLSpec
from sqlspec.adapters.asyncpg import AsyncpgConfig
spec = SQLSpec()
config = spec.add_config(
AsyncpgConfig(
connection_config={
"dsn": "postgresql://app:secret@localhost:5432/prod",
"min_size": 2,
"max_size": 20,
"command_timeout": 30.0,
},
driver_features={"pooler_mode": "session", "enable_pgvector": True},
)
)
Open request or task sessions with async with spec.provide_session(config) as session: and close the pool on shutdown via await config.close_pool().
Connection & Pool Configuration¶
Connection and Pool Parameters¶
Pass AsyncpgConnectionConfig and AsyncpgPoolConfig keys inside connection_config={...} on AsyncpgConfig:
| Key | Type | Default | Description |
|---|---|---|---|
dsn |
str |
- | PostgreSQL connection URI (postgresql://user:pass@host:port/dbname). |
host |
str or Sequence[str] |
- | Database hostname, socket directory, or sequence of failover hosts. |
port |
int or Sequence[int] |
5432 |
Server port or sequence of ports matching the hosts (defaults to 5432). |
user |
str |
- | PostgreSQL role name used for authentication. |
password |
str |
- | Password or dynamic token for user. |
auth_token_provider |
Callable[[], str] or Callable[[], Awaitable[str]] |
- | Callable or async callable returning a fresh password or IAM token for each new connection. |
database |
str |
- | Target PostgreSQL database name. |
ssl |
Any |
- | TLS mode string, boolean flag, or ssl.SSLContext instance. |
passfile |
str |
- | Path to a pgpass password file. |
service |
str |
- | Service section name in pg_service.conf. |
servicefile |
str |
- | Path to an alternate pg_service.conf file. |
direct_tls |
bool |
- | Initiate TLS negotiation directly without PostgreSQL SSLRequest startup. |
timeout |
float |
- | Connection establishment timeout in seconds (alias supported by asyncpg.connect). |
connect_timeout |
float |
- | Pool connection establishment timeout in seconds. |
command_timeout |
float |
- | Default per-command execution timeout in seconds. |
statement_cache_size |
int |
- | Maximum number of server-side prepared statements cached per connection (0 disables caching). |
max_cached_statement_lifetime |
int |
- | Maximum age in seconds before cached prepared statements are evicted. |
max_cacheable_statement_size |
int |
- | Maximum SQL byte length eligible for prepared statement caching. |
server_settings |
dict[str, str] |
- | Startup PostgreSQL run-time GUC settings (such as application_name or search_path). |
target_session_attrs |
AsyncpgTargetSessionAttrs |
- | Required server session role when connecting to multi-host targets (such as read-write). |
krbsrvname |
str |
- | Kerberos service principal name used for GSSAPI authentication. |
gsslib |
AsyncpgGSSLib |
- | GSSAPI library backend (gssapi or sspi on Windows). |
min_size |
int |
- | Minimum number of connections maintained in the pool. |
max_size |
int |
- | Maximum number of concurrent connections allowed in the pool. |
max_queries |
int |
- | Number of executed queries after which a pooled connection is closed and replaced. |
max_inactive_connection_lifetime |
float |
- | Idle duration in seconds before unused connections above min_size are closed. |
connect |
Callable[..., Awaitable[AsyncpgConnection]] |
- | Custom async callable invoked to establish raw asyncpg connections. |
setup |
Callable[[AsyncpgConnection], Awaitable[None]] |
- | Async callback invoked before each connection acquisition from the pool. |
init |
Callable[[AsyncpgConnection], Awaitable[None]] |
- | Async callback invoked once when a new physical connection is created. |
reset |
Callable[[AsyncpgConnection], Awaitable[None]] |
- | Async callback invoked when a connection is released back to the pool. |
loop |
AbstractEventLoop |
- | Explicit asyncio event loop passed to asyncpg.create_pool. |
connection_class |
type[AsyncpgConnection] |
- | Custom asyncpg.Connection subclass instantiated by the pool. |
record_class |
type[Record] |
- | Custom asyncpg.Record subclass returned by raw queries. |
extra |
dict[str, Any] |
- | Additional keyword arguments forwarded directly to asyncpg.create_pool. |
Driver Features¶
Pass AsyncpgDriverFeatures keys inside driver_features={...} on AsyncpgConfig:
| Key | Type | Default | Description |
|---|---|---|---|
json_serializer |
Callable[[Any], str] |
to_json |
Custom JSON serializer registered on json/jsonb codecs (defaults to to_json). |
json_deserializer |
Callable[[str], Any] |
from_json |
Custom JSON deserializer registered on json/jsonb codecs (defaults to from_json). |
enable_json_codecs |
bool |
True |
Automatically register json and jsonb codecs on new connections (defaults to True). |
enable_pgvector |
bool |
- | Register pgvector binary codecs and promote the dialect when pgvector is installed. |
enable_paradedb |
bool |
True |
Detect pg_search and promote the SQL dialect to paradedb (defaults to True). |
enable_pg_textsearch |
bool |
True |
Detect pg_textsearch and promote the SQL dialect for BM25 <@> ranking (defaults to True). |
enable_cloud_sql |
bool |
- | Route connections through cloud-sql-python-connector (mutually exclusive with enable_alloydb). |
cloud_sql_instance |
str |
- | Cloud SQL instance connection string in project:region:instance format. |
cloud_sql_enable_iam_auth |
bool |
- | Use IAM database authentication with the Cloud SQL Python Connector. |
cloud_sql_ip_type |
str |
PRIVATE |
Cloud SQL network path (PUBLIC, PRIVATE, or PSC; defaults to PRIVATE). |
enable_alloydb |
bool |
- | Route connections through google-cloud-alloydb-connector (mutually exclusive with enable_cloud_sql). |
alloydb_instance_uri |
str |
- | AlloyDB instance resource URI (projects/.../locations/.../clusters/.../instances/...). |
enable_alloydb_iam_auth |
bool |
- | Use IAM database authentication with the AlloyDB Python Connector. |
alloydb_ip_type |
str |
PRIVATE |
AlloyDB network path (PUBLIC, PRIVATE, or PSC; defaults to PRIVATE). |
enable_alloydb_columnar |
bool |
- | Enable google_columnar_engine.enable_columnar_scan=on at session startup or per transaction. |
enable_pgcopy_binary |
bool |
- | Enable binary COPY format for Arrow table ingestion. |
enable_events |
bool |
True |
Enable SQLSpec event channel pub/sub support (defaults to True when events is configured). |
events_backend |
Literal['notify', 'notify_queue', 'poll_queue'] |
- | Event transport backend (notify for LISTEN/NOTIFY, notify_queue, or poll_queue). |
on_connection_create |
Callable[[AsyncpgConnection], Awaitable[None]] |
- | Async hook invoked with each raw asyncpg connection after internal codec setup. |
reset_session_on_release |
bool |
False |
Clear cursors, session authorization, settings, listeners, session-level locks, plans, temp objects, and sequences when a connection is released (defaults to False). |
pooler_mode |
Literal['session', 'transaction', 'statement', 'auto'] or None |
- | External pooler compatibility mode; sets statement_cache_size=0 in transaction or statement mode. |
direct_dsn |
str or Mapping[str, Any] or None |
- | Direct PostgreSQL DSN or connection mapping bypassing the pooler for migrations and LISTEN/NOTIFY. |
tenant_strategy |
AsyncTenantStrategy or TenantStrategyShorthand or None |
- | Opt-in tenant strategy, or the "rls" shorthand, applied to every session and connection lease. |
tenant_session_baseline |
Mapping[str, str] |
- | Declared session settings replayed before each tenant checkout. |
type_codecs |
list[dict[str, Any]] |
- | Custom asyncpg type codec specifications registered on connection initialization. |
Type Mappings & Dialect Notes¶
| Database Type | Python Type | Notes / Feature Flag |
|---|---|---|
JSON / JSONB |
dict / list |
Registered via binary codecs when enable_json_codecs=True (default) using json_serializer and json_deserializer. |
UUID |
uuid.UUID / str |
Native binary UUID codec; coerces string UUID parameters automatically. |
VECTOR (pgvector) |
list[float] / numpy.ndarray |
Registered when enable_pgvector=True and pgvector is installed. |
ARRAY |
list[T] |
Native binary array encoding and decoding across nested dimensions. |
TSRANGE / TSTZRANGE / DATERANGE |
asyncpg.Range |
Native PostgreSQL range types with inclusive/exclusive bound inspection. |
BYTEA |
bytes / bytearray |
Binary wire transport without hex-encoding overhead. |
NUMERIC / DECIMAL |
decimal.Decimal |
Exact arbitrary-precision decimal representation. |
- Pooler Mode and Direct DSN: Set
driver_features={"pooler_mode": "transaction", "direct_dsn": "..."}when running behind PgBouncer or Supavisor in transaction or statement mode; SQLSpec disablesstatement_cache_sizeautomatically and usesconfig.derive_direct_config()for DDL migrations andLISTEN/NOTIFY. - Untyped Parameters Inside Polymorphic Functions: Because
asyncpgprepares statements over PostgreSQL's binary protocol, untyped parameters inside functions likejsonb_build_object(:k, :v)require an explicit SQL cast such asCAST(:k AS text)so PostgreSQL can resolve the parameter OID. - Binding JSON String Primitives: When
enable_json_codecs=True, Pythondict,list,int, andboolvalues serialize viajson_serializer, while barestrvalues are treated as pre-serialized JSON text. Passto_json("value")when inserting a scalar JSON string literal into ajsonbcolumn. - Binary COPY and Arrow Ingest:
session.load_from_arrow(...)streams Arrow batches into PostgreSQL via binaryCOPY ... FROM STDINfor high-throughput bulk loads, andLISTEN/NOTIFYchannels integrate directly withEventChannel. - Sequential StatementStack Execution:
asyncpg's binary protocol forbids overlapping operations on a single connection, sosession.execute_stack(stack)runs each step in order against native prepared statements instead of pipelining them.
Read Replicas and WAL Positions¶
Pass a reader DSN or sequence through replicas=. A reader override can also use host: Sequence[str] with matching port values and target_session_attrs="prefer-standby". Derivation keeps hooks, tenant setup, and explicit connection overrides. Open the reader with provide_replica_session(); use ordinary provide_session() for writes.
current_wal_lsn() captures a writer position. has_replayed_wal_lsn(lsn) checks whether a reader has reached it; wait_for_wal_lsn(lsn, timeout=...) bounds the wait. Capture after commit when the next read must include that write. Empty or malformed positions fail rather than being inserted into SQL text. Native read-only failures map to ReadOnlyTransactionError (25006). See Replica Operations.