hana-developer-cli-tool-example connection-pooling-timeout-management.instructions.md

Database Connection Pooling and Timeout Management Guidelines

Use this guide when creating or modifying code that establishes, manages, or disconnects database connections. This applies to CLI commands (bin/), API routes (routes/), and tests (tests/).

Scope and Purpose

This guide establishes standards for:

• Configurable timeout values (connection, query, operation, disconnect)
• Graceful connection lifecycle management (establishment, usage, cleanup)
• Error handling and recovery patterns
• Connection pooling best practices
• Multi-database support (HANA, PostgreSQL, SQLite)

Critical Principles

1. No Hard-Coded Timeouts: All timeout values must be configurable via environment, constructor, or options parameters. Default values are acceptable; hard-coded values are not.

2. Proper Cleanup in All Paths: Connections must be explicitly closed/disconnected in success, error, and timeout scenarios. Use try/finally blocks to guarantee cleanup.

3. Graceful Degradation on Timeout: Don't kill the entire process (process.exit()) on operation timeouts. Instead, throw an exception that can be caught and handled appropriately by the caller or error middleware.

4. Connection Establishment Timeout: Explicitly set a timeout for creating new connections to prevent indefinite hangs if the database is unreachable or unresponsive.

5. Distinct Timeout Layers: Implement timeouts at three levels:

   • Connection timeout (establishing the connection)
   • Query/operation timeout (executing a single query or operation)
   • Overall operation timeout (entire command/route processing)

6. Retry Logic for Transient Failures: Implement exponential backoff for connection failures due to transient network issues, not permanent failures (authentication, invalid connection details).

7. Resource Cleanup on Exit: Ensure database connections are properly disconnected before CLI exit, web server shutdown, or test suite completion.

8. Connection State Transparency: Track and expose connection states (connecting, connected, executing, disconnecting) for debugging and monitoring.