FireDAC Architecture in RAD Studio 10.4+: Connection Management and Operational Boundaries

The Problem: Unified Database Access without ORM Overhead

Engineering a multi-database application requires a balance between a unified API and the ability to leverage RDBMS-specific performance. The challenge in RAD Studio is managing 15+ different database drivers (Oracle, SQL Server, PostgreSQL, SQLite, etc.) while maintaining thread safety and preventing connection leaks without the heavy abstraction of a full Object-Relational Mapper (ORM).

Smallest Suitable Design

To minimize coupling, the application should interact only with the abstract FireDAC interfaces. The minimal architectural footprint consists of:

• TFDConnection: Manages the physical link and parameters. It acts as the gateway to the driver.
• TFDQuery / TFDCommand: Handles statement execution. Use Open for cursors (fetch on demand) and ExecSQL for DML/DDL (immediate execution).
• TFDTransaction: Defines the atomic scope. Nesting is supported via StartTransaction, Commit, and Rollback.
• TFDPhysDriver: The driver plugin loaded dynamically via FDDrivers.ini.
• TFDFormatOptions: Controls data mapping, such as StrsTrim (removing whitespace) and StrsEmpty2Null.

Trust and Data Boundaries

FireDAC operates primarily within the client process boundary:

• Credential Safety: Connection parameters stay in the client process; FireDAC does not log passwords to trace files.
• Execution Context: Driver DLLs run in-process. They share the same trust level and memory space as the application.
• Data Transfer: Data crosses the boundary from the DB server to the client via TFDDataSet buffers or TFDBatchMove for bulk operations.
• Pooling Isolation: TFDConnectionPool shares physical connections across threads but ensures logical transactions remain isolated per connection.

Operational Checks and Diagnostics

To maintain stability in production, implement the following monitoring checkpoints:

Check	Tool/Property	Target Metric
Connection Leaks	FDMonitor / FDEventAlerter	Active connections < MaxPoolSize
Memory Pressure	TFDQuery.FetchOptions.RowsetSize	Capped rows per round-trip
Query Performance	TFDTracer	Execution time < 1s
DB Semantics	TFDFormatOptions.StrsTrim	Match target DB null/empty behavior

Tracing Configuration

When debugging slow queries, configure TFDTracer with the following flags to capture the full lifecycle: [tfQPrepare, tfQExecute, tfQFetch, tfError]. This allows you to identify if the bottleneck is in the preparation, execution, or the fetching of large result sets.

Failure Modes and Mitigation

• Driver Mismatches: A mismatch between the client library (DLL) and the server version often results in Access Violations (AVs). Mitigation: Deploy the exact client libraries specified in the RAD Studio DocWiki compatibility matrix.
• Implicit Transaction Leaks: Setting AutoCommit=False without a corresponding Rollback in an exception handler leaves transactions open on the server. Mitigation: Always wrap transaction blocks in try...finally.
• SQLite Locking: Concurrent writers may encounter "Database is locked" errors. Mitigation: Configure PRAGMA journal_mode=WAL; and set a busy_timeout in the connection parameters.
• Unicode Truncation: In RAD Studio 11 Alexandria+, StrsTrim2Len defaults to True, which can silently truncate multi-byte characters. Mitigation: Set StrsTrim2Len := False when handling 4-byte UTF-8 (e.g., emojis).

Implementation Example: Thread-Safe Pooling

Run this logic within a multi-threaded environment (e.g., using TParallel.For) to verify pool stability. This requires the System.Threading and FireDAC.Comp.Client units.

var
  Pool: TFDConnectionPool;
begin
  Pool := TFDConnectionPool.Create(nil);
  Pool.MaxPoolSize := 50;
  Pool.CleanupTimeout := 300; // seconds
  
  TParallel.For(1, 100, procedure
  var
    Conn: TFDConnection;
    Qry: TFDQuery;
  begin
    // Acquire connection from pool
    Conn := Pool.GetConnection;
    try
      Qry := TFDQuery.Create(nil);
      try
        Qry.Connection := Conn;
        Qry.SQL.Text := 'SELECT 1'; // Generic check
        Qry.Open;
        Qry.Close;
      finally
        Qry.Free;
      end;
    finally
      // Return connection to pool
      Pool.ReleaseConnection(Conn);
    end;
  end);
end;

Design Constraints and Evolution

The current in-process driver architecture is suitable for standard desktop and server apps, but the design must change if the following occur:

• Sandboxed Environments: macOS App Store or Windows Store may block dynamic DLL loading. This requires static linking or approved drivers.
• Zero-Trust Networking: If mutual TLS (mTLS) is mandatory, you must move beyond core FireDAC abstractions and use driver-specific connection parameters.
• Distributed Scaling: Since pooling is per-process, multi-process deployments (e.g., ISAPI) require an external pooler like PgBouncer or ProxySQL.

Verification Checklist

1. Session Count: Run a stress test with 100 parallel tasks; verify that the DB server session count never exceeds MaxPoolSize.
2. Driver Validation: Use Tools > FireDAC > Explore to confirm FDDrivers.ini matches the server version.
3. Failure Injection: Kill the DB server mid-transaction; verify the application catches the exception and the pool recovers after PoolCleanupTimeout.
4. Unicode Round-trip: Insert a 4-byte UTF-8 character into an NVARCHAR column with StrsTrim2Len=False and verify it is read back without truncation.