hana-developer-cli-tool-example: Instructions file for GitHub Copilot

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.