Connecting an application to a database is the first integration task on every backend project. The actual connection is three lines; everything else — pooling, TLS, credential hygiene, retry logic — is where real production bugs live. This guide covers the right patterns for Python, Node.js, and Go in 2026.
What changed in 2026
- psycopg3 is now the default Postgres driver for Python — psycopg2 still works but new projects should start on psycopg3 for async support and binary protocol.
- SQLAlchemy 2.x style is universal — the legacy 1.x
Query API is removed in 2.x; all new code uses select() + session.execute().
- Prisma 6 landed for Node — schema-first ORM with a stable TypeScript API; Drizzle is the lighter alternative.
- Cloud databases enforce TLS by default — if your local connection string works without TLS, it likely won't on RDS, Supabase, or PlanetScale.
Connect with Python (psycopg3 + Postgres)
import os
import psycopg
DATABASE_URL = os.environ["DATABASE_URL"]
# e.g. postgresql://user:pass@localhost:5432/mydb?sslmode=require
with psycopg.connect(DATABASE_URL) as conn:
with conn.cursor() as cur:
cur.execute("SELECT version()")
row = cur.fetchone()
print(row[0])
For async (FastAPI, aiohttp):
import asyncio
import os
import psycopg
async def main():
async with await psycopg.AsyncConnection.connect(
os.environ["DATABASE_URL"]
) as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT now()")
print(await cur.fetchone())
asyncio.run(main())
Connection pooling with SQLAlchemy
from sqlalchemy import create_engine, text
import os
engine = create_engine(
os.environ["DATABASE_URL"],
pool_size=5,
max_overflow=10,
pool_pre_ping=True, # health-checks before handing out a connection
)
with engine.connect() as conn:
result = conn.execute(text("SELECT 1"))
print(result.scalar())
pool_pre_ping=True is the single most useful default — it drops stale connections that cloud providers close after ~10 minutes of idle.
Connect with Node.js (postgres / pg)
Using the lightweight postgres (slonik-style) package:
import postgres from "postgres";
const sql = postgres(process.env.DATABASE_URL!, {
ssl: "require",
max: 10, // pool size
idle_timeout: 20,
});
const rows = await sql`SELECT now() AS ts`;
console.log(rows[0].ts);
await sql.end();
Or with the classic pg package and a Pool:
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const { rows } = await pool.query("SELECT $1::text AS msg", ["hello"]);
console.log(rows[0].msg);
Connect with Go (database/sql + pgx)
package main
import (
"context"
"database/sql"
"fmt"
"os"
_ "github.com/jackc/pgx/v5/stdlib"
)
func main() {
db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
var ts string
if err := db.QueryRowContext(context.Background(),
"SELECT now()::text").Scan(&ts); err != nil {
panic(err)
}
fmt.Println(ts)
}
Driver and pooling comparison
| Language |
Driver |
ORM option |
Async? |
| Python |
psycopg3 |
SQLAlchemy 2.x |
Yes |
| Python |
asyncpg |
SQLAlchemy + asyncpg |
Yes (native) |
| Node.js |
postgres / pg |
Prisma 6, Drizzle |
Yes |
| Go |
pgx v5 |
sqlc, ent |
Yes |
| Rust |
sqlx |
SeaORM |
Yes |
How to pick
- Python + Postgres? psycopg3 for raw queries; SQLAlchemy 2.x if you want migrations and an ORM.
- Node + Postgres?
postgres package for lightweight; Prisma 6 for schema-first typed queries.
- High concurrency (>1 000 RPS)? asyncpg (Python) or pgx native pool (Go) outperform the synchronous drivers.
- SQLite for local dev? Both SQLAlchemy and Prisma support it — use the same ORM as prod, just swap the URL.
Common mistakes
One connection per HTTP request. Each connection consumes ~5–10 MB on the DB server. On 100 concurrent requests you exhaust the DB connection limit in seconds. Always pool.
No pool_pre_ping / health check. Cloud databases drop idle connections. Without a health check the pool hands out a dead connection and the request fails with a cryptic error.
Storing credentials in .env committed to git. Even if the file is in .gitignore, it gets committed eventually. Use a secrets manager (AWS Secrets Manager, Doppler, Vault) or at minimum a .env.local that is gitignored.
Not setting a connect timeout. A misconfigured firewall will hang your application indefinitely without connect_timeout=5 (seconds) in the connection string.
What to skip
- Raw
sqlite3 in production — SQLite is fine for dev and tests; for any concurrent web workload use Postgres.
- Storing DSNs in code comments as "examples" — reviewers copy them verbatim.
- Using root DB credentials for the application — create a least-privilege application user.
FAQ
How do I handle database connection errors gracefully?
Wrap queries in try/except (Python) or check err != nil (Go), log the error with context, and return a 500 or retry with exponential back-off. Do not let DB errors reach the client as stack traces.
What is a good connection pool size?
A common formula: pool_size = (num_cpu_cores * 2) + 1. For most web apps 5–20 covers everything; larger pools congest the DB.
Should I use an ORM or raw SQL?
Raw SQL for complex queries; ORM for CRUD and migrations. Many teams use both: sqlc or Drizzle for performance paths, ORM for routine operations.
How do I test database code in CI?
Use Docker Compose in CI (GitHub Actions supports services: natively). Spin up Postgres, run migrations, run tests, tear down. Avoid mocking the DB for integration tests.
Where to go next