SQLAlchemy

Environment

DATABASE_URL="postgresql+psycopg://user:password@host:6432/db?sslmode=verify-full&sslrootcert=system"
DATABASE_DIRECT_URL="postgresql+psycopg://user:password@host:5432/db?sslmode=verify-full&sslrootcert=system"

Note the driver prefix: SQLAlchemy wants postgresql+psycopg:// (psycopg 3) or postgresql+asyncpg://, not bare postgres://.

If you installed psycopg[binary] (the common quick-start install), sslrootcert=system can fail with certificate verify failed: the binary wheel bundles its own libpq/OpenSSL whose default CA paths usually do not exist on your machine. Use an explicit bundle path instead - sslrootcert=/etc/ssl/certs/ca-certificates.crt on Debian/Ubuntu, /etc/ssl/cert.pem on macOS - or install non-binary psycopg so it links your system libpq. See Troubleshooting.

Engine

import os
from sqlalchemy import create_engine

engine = create_engine(
    os.environ["DATABASE_URL"],
    pool_size=5,
    max_overflow=5,
    pool_pre_ping=True,
)

pool_pre_ping=True quietly replaces connections the server has timed out. Keep pool_size + max_overflow small - the plan budget is shared.

For asyncpg behind the pooled port, disable its prepared-statement cache:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    os.environ["DATABASE_URL"].replace("+psycopg", "+asyncpg"),
    connect_args={"statement_cache_size": 0},
)

Alembic

Point Alembic at the direct URL:

sqlalchemy.url = %(DATABASE_DIRECT_URL)s

or in env.py:

config.set_main_option("sqlalchemy.url", os.environ["DATABASE_DIRECT_URL"])

Pitfalls

• asyncpg + pooled URL without statement_cache_size=0 fails with prepared-statement errors.
• Long-running transactions hit the plan's idle-in-transaction timeout; commit or roll back promptly.
• sslmode is a libpq/psycopg parameter; for asyncpg use ssl=true in connect_args instead if you build URLs manually.