Delphi FireDAC Connection Pooling: An Architecture Note for Bounded Concurrency

The Problem: Unbounded Connections and Leaked Handles

A Delphi service that creates a database connection per request, or shares a single TFDConnection across threads, will eventually exhaust the database connection limit, leak handles when exceptions occur, and fail unpredictably after a network or database restart. The practical takeaway is to bound concurrency with one pooled FireDAC connection per logical request, wrap each unit of work in an explicit transaction, and connect with a least-privilege database identity.

Requirements: Bounded Concurrency and Short Atomic Work

Before choosing a design, bound the problem. The service must support a known maximum of concurrent database operations, each request must perform a short atomic unit of work, and no request state may leak into another. These three constraints rule out long-running background transactions, cross-request temp tables, and shared query objects.

The Smallest Suitable Design

The smallest design that meets the requirements uses a pooled FireDAC physical connection acquired once per logical request, an explicit transaction boundary, parameterized SQL, and a single least-privilege service account. The logical TFDConnection is created, used, and released inside the request scope; the underlying physical connection returns to the pool.

Connection Definition

Enable pooling in the FireDAC connection definition. The example below uses a PostgreSQL driver identifier; substitute the driver ID for your database. Exact property names and defaults vary by Delphi version and driver, so verify them against your documentation before production use.

[DB_Service]
DriverID=PG
Pooled=True
POOL_MaximumItems=50
POOL_CleanupTimeout=30000
LoginTimeout=15
Server=db.internal
Database=appdb
User_Name=svc_app_rw
Password=********

Run this configuration in the service process, not in a client tool. The service account needs only the privileges required for the operations it performs. POOL_MaximumItems bounds physical connections per process; it does not bound total connections across multiple service instances.

Request-Scoped Usage Pattern

A representative pattern acquires a connection, opens a transaction, executes parameterized commands, and commits or rolls back inside a try/finally block:

var
  Conn: TFDConnection;
  Qry: TFDQuery;
begin
  Conn := TFDConnection.Create(nil);
  try
    Conn.ConnectionDefName := 'DB_Service';
    Conn.Connected := True;
    Qry := TFDQuery.Create(nil);
    try
      Qry.Connection := Conn;
      Conn.StartTransaction;
      try
        Qry.SQL.Text := 'UPDATE accounts SET balance = balance + :amt WHERE id = :id';
        Qry.ParamByName('amt').AsCurrency := 100;
        Qry.ParamByName('id').AsInteger := 42;
        Qry.ExecSQL;
        Conn.Commit;
      except
        Conn.Rollback;
        raise;
      end;
    finally
      Qry.Free;
    end;
  finally
    Conn.Free;
  end;
end;

This pattern is illustrative, not tested output. The critical rule is that TFDConnection, TFDQuery, and transaction objects are never shared across threads. Each request owns its objects for the duration of the call.

Trust and Data Boundaries

Authentication and authorization belong at the service edge, not inside SQL strings. Once a request is authenticated, pass tenant or user context as data parameters. Never concatenate user input into SQL text. Protect connection strings and secrets using your platform's secure configuration store, and avoid logging parameter values or result rows.

Where practical, use separate read-only and read-write database accounts. A reporting endpoint that only selects should connect with an account that cannot insert, update, or delete. This limits the blast radius if the service edge is compromised.

Operational Checks

Instrument the service to record connection acquire time, release time, transaction duration, error counts, and the number of open logical connections. During load, compare these counters with the database session list. On PostgreSQL, for example, a monitoring role can run:

SELECT pid, state, query_start FROM pg_stat_activity WHERE datname = 'appdb';

Run this on the database host with a role that has sufficient privileges to see other sessions. The check is to confirm that the number of database sessions stays within the pool bound and that no session remains in an idle in transaction state after requests complete.

Load and Failure Testing

• Pool saturation: Send more concurrent requests than POOL_MaximumItems and verify that requests queue or fail cleanly rather than opening unbounded connections.
• Database restart: Restart the database during a controlled run and confirm stale sockets are replaced or requests fail cleanly without leaving open transactions.
• Deadlock and timeout: Force a deadlock or statement timeout and verify the service rolls back and releases the connection.

Failure Modes and Conditions That Change the Design

Common failure modes include leaked connections from missing try/finally blocks, pool exhaustion under bursts, stale sockets after network or database restarts, long transactions that hold locks, driver-specific pooling differences, and unsafe retries that duplicate non-idempotent writes. A retry after a network failure must be safe to repeat; if it is not, use an idempotency key (a unique request identifier the database can check) or an outbox table (a durable local record of intended writes).

When to Move Beyond This Design

Change the design if you need multiple processes or hosts, many tenants, read replicas, row-level security (database-enforced access control per row), ephemeral or serverless instances, strict cross-service consistency, or the database enforces a low connection cap. In those cases, consider a gateway or middleware connection pool, per-tenant credentials or row-level security, read/write splitting, a queue or outbox pattern, or a dedicated connection broker. Distributed transactions or MSDTC (Microsoft Distributed Transaction Coordinator) add operational complexity and should be avoided unless truly required.

Limitations and Verification

FireDAC pooling and transaction defaults vary by Delphi version and driver. Verify exact property names, cleanup behavior, and whether closing a logical connection immediately returns the physical one to the pool. Pooling does not make the database scalable; total connections across all processes can still exceed database limits. Do not rely on session state such as SET ROLE or temporary tables on a pooled connection unless reset is guaranteed.

To check the result, run a small concurrency test with more requests than the pool size, assert that database connections remain bounded, and confirm no leaks after forced exceptions. Review the FireDAC connection-definition and driver documentation for your exact Delphi version before enabling pooling in production.