Async built-in connection pool

[perrysk-msft]

Is your feature request related to a problem? Please describe.

Currently, managing database connections efficiently is a challenge in high-performance applications. Without connection pooling, each query execution requires opening and closing a new connection, which adds significant overhead, increases latency, and can exhaust SQL Server’s connection limits. This is especially problematic for applications with frequent short-lived queries, as establishing a new connection each time is costly.

Describe the solution you'd like

Implement an async connection pool that:

• Reuses existing connections to reduce the overhead of repeatedly establishing new connections.
• Supports configurable pool size to balance resource utilization and performance.
• Provides automatic connection management, including connection validation and timeout handling.
• Allows concurrent connections to be efficiently managed in async applications.
• Gracefully handles connection failures by retrying or removing dead connections from the pool.

Example API:

import mssql-python

async def fetch_users():
    # Create a connection pool
    pool = await mssql-python.create_pool(
        dsn="...",
        min_size=5,  # Minimum connections in pool
        max_size=20, # Maximum connections in pool
        timeout=30   # Connection timeout in seconds
    )

    async with pool.acquire() as conn:  # Acquire a connection from the pool
        rows = await conn.fetch("SELECT * FROM users")  # Execute query
        return rows

async def main():
    users = await fetch_users()
    for user in users:
        print(user)

mssql-python.run(main())

Describe alternatives you've considered

• No connection pooling: Leads to excessive connection creation overhead.
• Using aioodbc’s pooling: Relies on threads rather than true async, limiting scalability.
• Manually managing a pool: Increases complexity for developers and does not optimize connection reuse effectively.