SQLAlchemy

Environment

DATABASE_URL="postgresql+psycopg://user:password@host:6432/db?sslmode=require"
DATABASE_DIRECT_URL="postgresql+psycopg://user:password@host:5432/db?sslmode=require"

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

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.