feat: Built-in connection pooling

[teetangh]

Problem

Each adapter maintains a single database connection with no pooling. Under concurrent load — especially in server-side Dart or Flutter web deployments — this single connection becomes a bottleneck. Concurrent queries serialize on the one connection, and serverless/edge deployments quickly exhaust database connection limits when each isolate or instance opens its own connection.

The v0.4.0 auto-reconnect feature helps with dropped connections but does not address concurrency or connection reuse.

Desired API

final prisma = PrismaClient(
  adapter: PgAdapter(
    host: 'localhost',
    port: 5432,
    database: 'familiarise',
    // Connection pool configuration
    pool: PoolConfig(
      maxConnections: 10,       // Maximum concurrent connections
      minConnections: 2,        // Keep-alive minimum
      idleTimeout: Duration(minutes: 5),  // Close idle connections after 5m
      connectionTimeout: Duration(seconds: 30), // Max wait for a connection
      maxLifetime: Duration(hours: 1),    // Recycle connections after 1h
    ),
  ),
);

// Queries automatically acquire/release connections from the pool
final users = await prisma.client.user.findMany(); // uses pooled connection
final posts = await prisma.client.post.findMany(); // can run concurrently on another connection

Implementation approach

1. Create a ConnectionPool<C> class that manages a pool of SqlDriverAdapter instances.
   • Maintain a queue of available connections and a set of in-use connections.
   • When a query is requested, acquire an idle connection or create a new one (up to maxConnections).
   • When maxConnections is reached, queue the request and await a released connection.
2. Implement health checking: before handing out an idle connection, verify it is alive (e.g., SELECT 1).
3. Implement connection recycling: connections older than maxLifetime are closed and replaced.
4. Implement idle timeout: connections idle longer than idleTimeout are closed (down to minConnections).
5. Wire the pool into the adapter layer so it is transparent to the delegate/compiler layer.
6. For transactions, a connection is checked out for the duration of the transaction and returned on commit/rollback.
7. Add metrics: pool.activeConnections, pool.idleConnections, pool.waitingRequests for observability.
8. Add tests for:
   • Concurrent query execution across multiple pool connections
   • Connection exhaustion and queueing behavior
   • Idle timeout and connection recycling
   • Transaction connection pinning
   • Graceful shutdown (pool.close())

Impact

• Enables concurrent query execution — critical for server-side Dart and batch processing
• Prevents connection exhaustion in multi-isolate and serverless deployments
• Standard feature in production ORMs (HikariCP, pgBouncer, Prisma Data Proxy)
• Foundation for future features like read replicas and connection routing
• The v0.4.0 reconnect logic can be integrated into pool health checking for robustness