DATABASE_URL

Anatomy of a DATABASE_URL

postgresql://  myuser : s3cret @ db.internal : 5432 / app_db ? sslmode=require
└─ scheme      └─ user  └─ pass  └─ host       └─ port └─ database └─ options

Every segment except scheme and host is optional, and every parser fills the gaps differently — libpq defaults the port to 5432 and the database to the username; some ORMs error instead. The query string carries driver-specific options: sslmode=require for Postgres, connection_limit and pgbouncer=true for Prisma, pool_timeout, schema, and so on. Treat the URL as driver input, not a portable format — the same string rarely works unchanged across two ORMs.

The postgres:// vs postgresql:// incident

The scheme prefix looks cosmetic until it takes your app down. SQLAlchemy 1.4 (March 2021) removed support for the postgres:// alias — only postgresql:// remained valid. Heroku, meanwhile, injects DATABASE_URL with the postgres:// prefix and does not let you edit it. The collision broke a wave of Django and Flask deployments mid-2021, and the standard fix is still rewriting the scheme at startup:

import os

url = os.environ["DATABASE_URL"]
if url.startswith("postgres://"):
    url = url.replace("postgres://", "postgresql://", 1)

The lesson generalizes: you don't control the exact string a platform injects, so normalize it in one place before handing it to your driver.

Why does a special character in the password break everything?

Because DATABASE_URL is a URI, and URI parsing is positional: @ separates credentials from host, : separates user from password, / starts the database name, # starts a fragment. A password containing any of those shifts every boundary after it — a p@ss password makes the parser treat ss as the start of your hostname. Prisma surfaces this as the P1013 "invalid connection string" error and its docs require percent-encoding; the same applies to every URI-consuming driver:

# password: p@ss/word  →  percent-encode @ (%40) and / (%2F)
DATABASE_URL="postgresql://app:p%40ss%2Fword@db.internal:5432/app_db"

# generate the encoding instead of doing it by hand
node -p "encodeURIComponent('p@ss/word')"
python3 -c "from urllib.parse import quote; print(quote('p@ss/word', safe=''))"

Keeping the credentials out of logs

DATABASE_URL is the rare secret that travels inside a value developers love to print. Three leak paths show up over and over: startup logging ("connecting to ..." with the full URL), driver exceptions that embed the connection string, and error trackers capturing environment or locals on crash. Defenses, in order of value: log only the host and database name (parse the URL first), confirm your error tracker scrubs values matching URL-credential patterns, and rotate the password on any leak — grepping old logs is not a containment strategy. For the broader threat model — .env files in git, secrets in CI logs, AI agents reading config off disk — see the environment variable security guide and how to share .env files securely.

One URL is often not enough

Serverless platforms multiplied the connection-string count. Postgres ships with max_connections = 100 by default, and a function platform that spins up one connection per concurrent invocation exhausts that in seconds. The now-standard topology is two URLs: the app's DATABASE_URL points at a pooler (PgBouncer, Supabase's port 6543, Neon's pooled endpoint), while a second variable — Prisma calls it directUrl — points at the real server for migrations, which need session-level features the pooler's transaction mode breaks. If you keep both in a .env file locally, name them so the unpooled one is impossible to grab by accident (DIRECT_URL, MIGRATE_DATABASE_URL).

When NOT to use a single URL

The one-string design is convenience, not gospel. libpq tools (psql, pg_dump) accept the discrete PGHOST / PGUSER / PGPASSWORD family, and IAM-based auth (RDS IAM tokens, Cloud SQL connectors) generates short-lived credentials that don't fit a static URL at all. If your platform offers workload identity to the database, prefer it — a DATABASE_URL with no password in it has nothing to leak.