Managing Database Connections
1. Configuring Connection Pool
| Param | Detail |
|---|---|
connection_limit | Default = num_cpus * 2 + 1 |
| URL | ?connection_limit=20 |
| Per-process | Each client has own pool |
2. Setting Connection Timeout
| Param | Detail |
|---|---|
connect_timeout | TCP handshake max (sec) |
| Default | 5 sec |
3. Configuring Pool Timeout
| Param | Detail |
|---|---|
pool_timeout | Wait for free conn (sec) |
| Default | 10 sec |
| Error | P2024 on exhaustion |
4. Managing Connection Lifecycle
| Phase | API |
|---|---|
| Open | Lazy; $connect() forces |
| Close | $disconnect() |
| Restart | Create new client |
5. Handling Connection Errors
| Code | Meaning |
|---|---|
P1001 | Can't reach DB |
P1002 | Timed out |
P1008 | Operations timed out |
P2024 | Pool exhausted |
6. Using Multiple Database Connections
| Pattern | Detail |
|---|---|
| Multi-tenant | One client per tenant URL |
| Read/write split | Separate clients |
| Cache pool | LRU map keyed by URL hash |
7. Implementing Connection Retry Logic
async function withRetry<T>(fn: () => Promise<T>, retries = 3): Promise<T> {
for (let attempt = 1; attempt <= retries; attempt++) {
try { return await fn(); }
catch (e: any) {
if (!["P1001","P1008","P2024"].includes(e.code) || attempt === retries) throw e;
await new Promise(r => setTimeout(r, 100 * 2 ** attempt));
}
}
throw new Error("unreachable");
}| Pattern | Detail |
|---|---|
| Exponential backoff | 100ms, 200ms, 400ms ... |
| Idempotency | Retry only safe ops |
8. Closing Connections
process.on("SIGTERM", async () => {
await prisma.$disconnect();
process.exit(0);
});| Aspect | Detail |
|---|---|
| Graceful | Drain pool before exit |
| Serverless | Usually unnecessary (runtime kills) |
9. Using Read Replicas
| Approach | Detail |
|---|---|
| Multiple clients | One per replica URL |
| Extension | @prisma/extension-read-replicas |
| Routing | $replica() returns read-only client |
10. Monitoring Connection Health
| Metric | Source |
|---|---|
| Pool size | prisma.$metrics.json() |
| Active queries | DB-side (pg_stat_activity) |
| Errors | Capture P10xx codes |