Prisma Connection Management

Status: Active
Last Updated: 2026-08-26
Category: Databases
Prerequisites: ../research/prisma.md, database-selection.md, schema-overview.md
Tags: Prisma, connection pool, PgBouncer, Neon, Data Proxy, secrets, telemetry

Summary

This article defines how fogserv.cloud wires Prisma into its stack, keeps DATABASE_URL and PRISMA_* secrets consistent across environments via dotenvx, avoids connection saturation through pooling, and ensures every connection change links to a Forgejo ticket with telemetry captured.

Context / Why This Matters

Agents rely on Prisma for all data access. Without a disciplined pooling and rotation strategy, long-running agent tasks saturate the PostgreSQL connection limit, cause Too many clients errors, and leak credentials into logs. Consistency with secrets management (dotenvx / Dotenvx) prevents drift between development, preview, and production.

Implementation / Core Content

Connection Strategy

• Define DATABASE_URL per environment (.env, .env.production, Dotenvx store) rather than hardcoding URLs.
• Generate the Prisma client (prisma generate) and keep migrations (prisma/migrations/) in Git with the code using them.
• Before any agent workload touches production, verify the connection string through dotenvx run --env-file=.env -- prisma validate.

Pooling

• Enable pooling before high-concurrency agent tasks:
  • PgBouncer (transaction mode) for self-hosted PostgreSQL.
  • Neon built-in pool (connection endpoint) for managed databases.
  • Prisma Data Proxy for serverless / long-running agent patterns; reduces direct DB connections and provides query caching.
• Monitor pool saturation with pg_stat_activity or Neon metrics; link spikes to Forgejo tickets.

Security & Lifecycle

• Separate read-only agent roles from writer roles; store each credential set in Dotenvx.
• Rotate credentials on a schedule; document which workload uses which secret so rotations are auditable.
• Use Prisma Data Proxy or PgBouncer to centralize pooling and reduce the number of clients holding direct DB secrets.

Migration & Schema Hygiene

• Use prisma migrate dev in development; prisma migrate deploy in production (never dev in prod).
• For data migrations that alter existing rows, apply the expand/contract pattern: add new column, backfill, update app code, drop old column.
• Review prisma/migrations/ lock file (migration_lock.toml) to confirm state before deploying.

Practical Examples

# Verify environment connection before agent run
dotenvx run --env-file=.env.production -- \
  npx prisma db pull --schema=prisma/schema.prisma

# Generate client after schema update
npx prisma generate

# Deploy migrations in production safely
npx prisma migrate deploy --preview-feature
dotenvx run --env-file=.env.production -- \
  npx prisma migrate deploy

Common Pitfalls & Troubleshooting

• Never run migrate dev in production; it rewrites migration history and can corrupt shadow database checks.
• Connection leaks: Close Prisma clients (prisma.$disconnect()) after batch agent jobs.
• Pool exhaustion: If agents run in parallel, set DATABASE_URL with ?pool_timeout=30 or use PgBouncer transaction mode; direct connections scale poorly.
• Credential drift: A change to .env without updating Dotenvx store means production uses stale secrets; log each rotation in a Forgejo ticket.

Next Steps / Ops Actions

• Link recent pool saturation or migration errors to Forgejo Issues for retrospective analysis.
• Add telemetry links (pg_stat_activity, query latency, pool health) to ../monitoring/ docs.
• Verify all prisma CLI calls run through dotenvx run -- for consistent secrets loading.

Sources & Related Articles

• ../research/prisma.md
• database-selection.md
• prisma-migrations-guide.md
• Prisma Migrate docs — Development and production
• Prisma — Expand/contract data migration
• Prisma Data Proxy documentation
• ../sysadmin/dotenvx.md
• ../agentic/ai-server-management.md

Change Log

2026-08-26

• Expanded to full production-quality format (Status, Last Updated, Category, Prerequisites, Tags, all sections) per kb-build-plan.md Task 6.
• Added pooling strategy (PgBouncer, Neon, Data Proxy), security lifecycle, migration hygiene, practical commands, pitfalls, sources.