Topic
Definition
A database connection is a stateful network session between an application and Postgres, typically reused from a bounded pool, configured with a connection URL, optional SSL, and timeout limits.
In simpler words
The app does not open a fresh line to Postgres for every request — it borrows one from a small, reused pool, using one connection string to know where and how to connect.
Before Nest or TypeORM enter the picture, understand what DATABASE_URL actually configures, why pooling exists, and what happens when an app misuses connections.
Definition
A Postgres connection URL encodes the protocol, credentials, host, port, database name, and optional query parameters needed to open a session in a single string.
In simpler words
DATABASE_URL is that one string — everything the driver needs to find and authenticate against the right database.
Shape: postgres://user:password@host:port/database?sslmode=require
This repo validates DATABASE_URL as a required string in packages/env (apiEnvSchema) and reads it in both AppModule and the TypeORM CLI data-source.ts.
Never commit real credentials. Prefer env files that are gitignored, and rotate anything that ever leaked into a screenshot or chat.
# local
DATABASE_URL=postgres://postgres:postgres@localhost:5432/course
# managed provider, often requires SSL
DATABASE_URL=postgres://user:pass@db.host:5432/course?sslmode=requireSame shape, different host/credentials/SSL requirement — never hardcode this, always read it from env.
Definition
A connection pool maintains a fixed-size set of already-open connections that requests borrow and return, rather than opening and closing a new connection per request.
In simpler words
Opening a Postgres connection is relatively slow and Postgres itself caps how many can exist at once — pooling avoids paying that cost on every request and avoids exceeding the cap.
Postgres enforces max_connections; each app instance, migration runner, and admin tool competes for that limit.
“too many clients already” / “remaining connection slots are reserved” means something is opening more connections than Postgres allows — often a leaked connection, an oversized pool, or too many app instances.
Intermediate math: if each Nest process opens pool max = 20, and you run 6 replicas, you already reserved 120 sessions before migrations or admin tools.
error: sorry, too many clients already
# Common causes:
# - pool max set too high across many app instances
# - a connection acquired but never released (leak)
# - serverless/lambda-style cold starts each opening a new poolThe fix is almost always pool sizing or a leak, not “add more Postgres.”
Postgres max_connections ≈ 100 (example)
Reserve ~10 for admin / migrations / monitoring
Budget for app: 90
Replicas: 3 → max per process ≤ 30
Safer: max per process = 10–15 and leave headroomSize the pool from the database upward, not from “feels slow so raise max.”
Definition
SSL/TLS on a database connection encrypts traffic between the app and Postgres, and timeout settings bound how long the app waits to acquire a connection or for a query to finish.
In simpler words
Managed Postgres providers usually require SSL; timeouts stop one slow query or one exhausted pool from hanging the whole app.
sslmode=require (or a provider-specific SSL config) is common outside local development, where an unencrypted connection would leak credentials and data.
rejectUnauthorized: true (with the provider CA) is stricter than turning verification off. Disabling verification is a common local shortcut that must not become a production habit.
A connection (acquire) timeout fails fast when the pool is exhausted instead of queueing requests forever; a statement timeout kills a runaway query.
{
max: 10, // pool size
connectionTimeoutMillis: 5000, // fail fast if pool is exhausted
idleTimeoutMillis: 30000, // release idle connections
ssl: { rejectUnauthorized: true } // prefer provider CA in prod
}Exact option names vary by driver, but every production Postgres client exposes these knobs.
// Wrong — no acquire timeout: requests wait indefinitely
{ max: 2 }
// Right — fail fast so the API returns 5xx/timeout instead of wedging
{ max: 10, connectionTimeoutMillis: 5000 }An exhausted pool without a timeout looks like a frozen API, not a clear database error.
Definition
A connection pooler (for example PgBouncer) sits between many short-lived clients and Postgres so application processes do not each open a large direct pool against the primary’s max_connections budget.
In simpler words
When you run many Nest replicas — or serverless functions that start often — a pooler is usually safer than giving every process a big pool.
Transaction pooling modes can break some session-level Postgres features; know what your pooler mode allows before enabling advanced session settings.
TypeORM’s DataSource/AppModule connection still owns an app-side pool. Even with a pooler, keep app max modest.
Migrations should ideally run as a single job with a small pool, not from every replica at boot with migrationsRun: true fighting each other.
// AppModule — runtime pool for Nest
TypeOrmModule.forRoot({ type: 'postgres', url: env.DATABASE_URL, synchronize: false })
// data-source.ts — CLI migrations (separate process, same URL)
export default new DataSource({ type: 'postgres', url, synchronize: false })Two processes can both open pools against the same database — count both when budgeting.
Test
At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥ 80% (enforced by the API) to save progress.
Checking your session…
10 questions · concept 3 · syntax 2 · practical 3 · logic 2