Katkat

Implement a Connection Pool

AirtableSoftware Engineer · Technical Screen

Last updated

This question evaluates a candidate's competence in resource management, object ownership and lifecycle handling, and safe reuse of pooled database connections.

You are given an existing DBConnection class that simulates a physical database connection. It exposes the following methods and must not be modified:

  • initialize()
  • query(sql: str)
  • close()

Implement the missing behavior for ConnectionPool and PoolConnection.

Skeleton:

class DBConnection:

  • __init__(self)
  • initialize(self)
  • query(self, sql: str) -> str
  • close(self) Destroys the physical connection.

class ConnectionPool:

  • __init__(self, max_conn: int)
  • get_connection(self) -> PoolConnection | None

class PoolConnection:

  • __init__(self, parent: ConnectionPool, db_conn: DBConnection)
  • query(self, sql: str)
  • close(self)

Requirements:

  1. ConnectionPool(max_conn)
  • Throw an error if max_conn <= 0.
  • Track how many connections are currently checked out.
  • Track previously used DBConnection instances that have been returned to the pool.
  1. ConnectionPool.get_connection()
  • If the number of active checked-out connections has reached max_conn, return None.
  • If an idle DBConnection is available in the pool, reuse it.
  • Otherwise create a new DBConnection, initialize it, and return it wrapped in a PoolConnection.
  1. PoolConnection.query(sql)
  • Forward the call to the underlying DBConnection.query(sql).
  • If this PoolConnection has already been closed, throw an error.
  1. PoolConnection.close()
  • Return the underlying DBConnection to the parent ConnectionPool so it can be reused later.
  • Do not destroy the underlying DBConnection when a PoolConnection is closed.
  • Prevent a closed PoolConnection from being queried again.
  • Prevent double-closing, or treat it as an error.

The goal is to implement a simple reusable connection pool with correct ownership and lifecycle handling.