{"id":161,"library":"asyncpg","title":"asyncpg","description":"High-performance async PostgreSQL driver for Python/asyncio. Implements PostgreSQL binary protocol natively — ~5x faster than psycopg3 in benchmarks. Current version: 0.31.0 (Nov 2025). Still pre-1.0. NOT DB-API 2.0 compliant — uses $1/$2 placeholders not %s. No dict row support out of the box — returns Record objects. Major footgun: prepared statements break with pgbouncer in transaction/statement mode (Supabase, Neon poolers).","status":"active","version":"0.31.0","language":"python","source_language":"en","source_url":"https://github.com/MagicStack/asyncpg","tags":["asyncpg","postgresql","async","asyncio","python","database","fastapi"],"install":[{"cmd":"pip install asyncpg","lang":"bash","label":"Python"}],"dependencies":[],"imports":[{"note":"asyncpg uses PostgreSQL-native $1/$2/$3 positional placeholders. NOT %s (psycopg2 style) or ? (sqlite3 style). Using %s raises a syntax error or produces unexpected behavior.","wrong":"row = await conn.fetchrow(\n    'SELECT * FROM users WHERE id = %s',\n    42\n)  # SyntaxError or wrong results — must use $1 not %s","symbol":"connect","correct":"import asyncpg\nimport asyncio\n\nasync def main():\n    conn = await asyncpg.connect(\n        'postgresql://user:pass@localhost/mydb'\n    )\n    # $1, $2 placeholders — NOT %s\n    row = await conn.fetchrow(\n        'SELECT id, name FROM users WHERE id = $1',\n        42\n    )\n    print(row['name'])  # Record supports dict-style access\n    await conn.close()\n\nasyncio.run(main())"},{"note":"Always use create_pool() for web apps. Connecting per request adds 50-200ms latency. Pool manages connection lifecycle automatically.","wrong":"# Creating a new connection per request — don't do this\nasync def handle_request():\n    conn = await asyncpg.connect(...)  # expensive, no pooling\n    ...","symbol":"create_pool","correct":"import asyncpg\nimport asyncio\n\nasync def main():\n    pool = await asyncpg.create_pool(\n        'postgresql://user:pass@localhost/mydb',\n        min_size=2,\n        max_size=10\n    )\n    async with pool.acquire() as conn:\n        rows = await conn.fetch('SELECT * FROM users')\n        for row in rows:\n            print(dict(row))  # convert Record to dict\n    await pool.close()\n\nasyncio.run(main())"}],"quickstart":{"code":"# pip install asyncpg\nimport asyncpg\nimport asyncio\n\nasync def main():\n    # Connection pool for production\n    pool = await asyncpg.create_pool(\n        'postgresql://user:pass@localhost/mydb',\n        min_size=2,\n        max_size=10\n    )\n\n    async with pool.acquire() as conn:\n        # $1, $2 — not %s\n        await conn.execute(\n            'INSERT INTO users(name, email) VALUES($1, $2)',\n            'Alice', 'alice@example.com'\n        )\n\n        # fetchrow returns asyncpg.Record — dict-like\n        row = await conn.fetchrow(\n            'SELECT * FROM users WHERE name = $1', 'Alice'\n        )\n        print(row['name'])   # 'Alice'\n        print(dict(row))     # convert to plain dict\n\n        # fetch returns list of Records\n        rows = await conn.fetch('SELECT id, name FROM users')\n\n    await pool.close()\n\nasyncio.run(main())","lang":"python","description":"asyncpg connection pool with correct $1/$2 placeholders."},"warnings":[{"fix":"await conn.fetch('SELECT * FROM t WHERE id = $1 AND active = $2', id, True)","message":"Placeholders are $1/$2/$3 (PostgreSQL native) NOT %s (psycopg2) or ? (sqlite3). LLMs trained on psycopg2 code consistently generate %s placeholders which fail with asyncpg.","severity":"breaking","affected_versions":"all"},{"fix":"Use fetch() for multiple rows, fetchrow() for one row, fetchval() for a single value, execute() for DML without results.","message":"asyncpg is NOT DB-API 2.0 compliant. Code written for psycopg2/sqlite3 will not work without changes. No cursor objects, different method names (fetch/fetchrow/fetchval not execute/fetchone/fetchall).","severity":"breaking","affected_versions":"all"},{"fix":"Set statement_cache_size=0 when connecting: await asyncpg.connect('...', statement_cache_size=0). For SQLAlchemy: connect_args={'statement_cache_size': 0}","message":"Prepared statements break with pgbouncer in transaction/statement pool mode. Error: 'prepared statement asyncpg_stmt_X does not exist'. Affects Supabase transaction pooler (port 6543), Neon, and any pgbouncer setup.","severity":"breaking","affected_versions":"all"},{"fix":"Convert with dict(row) or [dict(r) for r in rows] if you need plain dicts.","message":"fetch() returns list of asyncpg.Record objects, not dicts. Record supports dict-style access (row['name']) but isinstance(row, dict) is False. Code that expects dicts breaks silently.","severity":"gotcha","affected_versions":"all"},{"fix":"Pin version in production: pip install asyncpg==0.31.0","message":"Still pre-1.0 (0.31.x). API stability not guaranteed across minor versions.","severity":"gotcha","affected_versions":"all"},{"fix":"Don't cache prepared statement objects across pool.acquire() calls.","message":"Prepared statements and cursors from Connection.prepare() become invalid once a connection is released back to the pool. Must re-prepare on next acquisition.","severity":"gotcha","affected_versions":"all"},{"fix":"Ensure the PostgreSQL server is running and accessible from the application's host/network. Verify the connection string (host, port) is correct. Check firewall rules.","message":"Connection failed: 'Connect call failed' (OSError: Errno 111) means the asyncpg client could not establish a network connection to the database server. This is typically due to the database not running, incorrect host/port in the connection string, or a firewall blocking the connection.","severity":"breaking","affected_versions":"all"},{"fix":"Ensure the PostgreSQL server is running, listening on the correct host and port, and is reachable from the application's network environment. Verify the database host, port, and any relevant firewall rules.","message":"asyncpg.create_pool or asyncpg.connect fails with 'OSError: [Errno 111] Connect call failed' if the PostgreSQL database server is not running, not listening on the specified host/port, or is unreachable due to network issues (e.g., firewall). This is a general network/database availability error, not specific to asyncpg's API or usage patterns.","severity":"breaking","affected_versions":"all"}],"env_vars":null,"last_verified":"2026-05-12T09:10:02.702Z","next_check":"2026-06-24T00:00:00.000Z","problems":[{"fix":"Ensure 'asyncpg' is installed by running 'pip install asyncpg' in the appropriate environment.","cause":"The 'asyncpg' module is not installed in the current Python environment.","error":"ModuleNotFoundError: No module named 'asyncpg'"},{"fix":"Wrap the 'async with' statement inside an 'async def' function and run it using an event loop.","cause":"Using 'async with' outside of an asynchronous function.","error":"SyntaxError: invalid syntax"},{"fix":"Ensure that each operation is awaited properly and avoid overlapping operations on the same connection.","cause":"Attempting to execute multiple operations concurrently on the same connection.","error":"asyncpg.InterfaceError: cannot perform operation: another operation is in progress"},{"fix":"Add '--hidden-import=asyncpg.pgproto.pgproto' to the PyInstaller command to include the required modules.","cause":"PyInstaller is not including all necessary asyncpg modules in the build.","error":"ImportError: cannot import name 'exceptions' from 'asyncpg'"},{"fix":"Ensure that the foreign key value exists in the referenced table before performing the insert or update operation.","cause":"Inserting or updating a record with a foreign key that does not exist in the referenced table.","error":"asyncpg.exceptions.ForeignKeyViolationError: insert or update on table \"table_name\" violates foreign key constraint"}],"ecosystem":"pypi","meta_description":null,"install_score":100,"install_tag":"verified","quickstart_score":0,"quickstart_tag":"stale","pypi_latest":null,"install_checks":{"last_tested":"2026-05-12","tag":"verified","tag_description":"installs cleanly on critical runtimes, fast import, recently tested","results":[{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.17,"mem_mb":7.3,"disk_size":"25.8M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.12,"mem_mb":7.3,"disk_size":"29M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.29,"mem_mb":8.2,"disk_size":"28.0M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.23,"mem_mb":8.2,"disk_size":"31M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.46,"mem_mb":8.4,"disk_size":"21.4M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.43,"mem_mb":8.4,"disk_size":"25M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.47,"mem_mb":8.8,"disk_size":"21.1M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.42,"mem_mb":8.8,"disk_size":"25M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.15,"mem_mb":7.1,"disk_size":"25.2M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.14,"mem_mb":7.2,"disk_size":"28M"}]},"quickstart_checks":{"last_tested":"2026-04-23","tag":"stale","tag_description":"widespread failures or data too old to trust","results":[{"runtime":"python:3.10-alpine","exit_code":1},{"runtime":"python:3.10-slim","exit_code":1},{"runtime":"python:3.11-alpine","exit_code":1},{"runtime":"python:3.11-slim","exit_code":1},{"runtime":"python:3.12-alpine","exit_code":1},{"runtime":"python:3.12-slim","exit_code":1},{"runtime":"python:3.13-alpine","exit_code":1},{"runtime":"python:3.13-slim","exit_code":1},{"runtime":"python:3.9-alpine","exit_code":1},{"runtime":"python:3.9-slim","exit_code":1}]}}