Async built-in connection pool #8
Description
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(
conn="...",
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.