SQLAlchemy connection guide
SQLAlchemy PostgreSQL Connection String
Create a SQLAlchemy 2 PostgreSQL URL for psycopg 3, pass it through an environment variable, and verify the driver.
Use the connection value generated by SQLAlchemy, keep it in an environment variable, and test it before changing application code. The URI shape below is a template, not a credential to paste unchanged.
- URI scheme
- postgresql://
- Default PostgreSQL port
- 5432
- Credential handling
- Environment variable or secret manager
- First test
- python -c "from sqlalchemy import create_engine; import os; print(create_engine(os.environ['DATABASE_URL']).connect().exec_driver_sql('select 1').scalar())"
Connection string template
postgresql+psycopg://USER:PASSWORD@HOST:5432/DB_NAMEReplace every uppercase placeholder. Percent-encode reserved characters in URI usernames and passwords.
Environment variable
DATABASE_URL="postgresql+psycopg://USER:PASSWORD@HOST:5432/DB_NAME"Where to get the SQLAlchemy values
Use the postgresql+psycopg dialect for psycopg 3. Read the value from the environment and pass it to create_engine. The plain postgresql:// form leaves driver selection to SQLAlchemy.
The failure mode worth checking first
A raw string breaks when credentials contain @, /, or :. Use SQLAlchemy's URL.create API when credentials come from separate settings, or percent-encode them in a URI.
Test the connection
Run a cheap read-only command before migrations or application startup. That separates network, TLS, and authentication errors from framework configuration problems.
Connection check
python -c "from sqlalchemy import create_engine; import os; print(create_engine(os.environ['DATABASE_URL']).connect().exec_driver_sql('select 1').scalar())"Open it in PostgresGUI
Paste the PostgreSQL URI into PostgresGUI or enter the same host, port, database, user, password, and SSL mode as separate fields. A desktop connection is useful for checking the visible schemas and role permissions before debugging the application layer.
Questions that come up
Should the connection string be committed to Git?
No. Commit an example with placeholders and load the real value from an environment variable or secret manager.
Why does a password with @ or # break the URI?
Those characters have meaning inside a URL. Percent-encode the username and password, or use a structured connection API instead of assembling a URL by hand.
Do I need sslmode=require?
Most hosted PostgreSQL services require TLS. For strict certificate and hostname verification, use sslmode=verify-full with the provider's trusted root certificate when the client supports it.