Architecture Note: Minimal FireDAC Design for RadStudio Delphi Applications

Requirements

Typical goals for a database layer in a RadStudio Delphi VCL or FMX app are:

• Connect to one or more relational databases (e.g., PostgreSQL, MySQL, Oracle, SQLite).
• Execute queries and retrieve results with minimal latency.
• Handle transient errors (network blips, temporary unavailability) without crashing the UI.
• Keep credentials and connection details isolated from UI code.
• Optionally encrypt traffic with TLS/SSL.

Minimal Suitable Design

The smallest set of FireDAC components that satisfies the above is:

• TFDConnection – manages the physical connection and pooling.
• TFDQuery – prepares and executes SQL statements.
• TFDTransaction (optional) – groups multiple statements into a single transaction.
• TFDMonitor (optional) – logs command start/end and errors for operational visibility.

All other features (e.g., TFDStoredProc, TFDTable) can be added later if needed.

Example Configuration (Delphi IDE)

Place the components on a data module or form and set properties at design‑time or runtime. Below is a runtime snippet that you can paste into the module’s OnCreate event.

// TFDConnection setup
FDConnection1.ConnectionDefName := 'MyPGConn';
FDConnection1.Params.DriverID := 'PG';
FDConnection1.Params.Database := '';
FDConnection1.Params.User_Name := '';
FDConnection1.Params.Password := '';
FDConnection1.Params.Server := '';
FDConnection1.Params.Port := '';
// TLS/SSL – adjust to your environment
FDConnection1.Params.Add('SSLMode=Require');
FDConnection1.Params.Add('SSLRootCert=');
FDConnection1.LoginPrompt := False;
// Enable lightweight pooling (recommended for >1 concurrent user)
FDConnection1.Params.Add('Pooling=True');
FDConnection1.Params.Add('MaxPoolSize=20');
// Activate
FDConnection1.Connected := True;

// TFDQuery linking
FDQuery1.Connection := FDConnection1;
FDQuery1.SQL.Text := 'SELECT id, name FROM public.users WHERE active = :active';
FDQuery1.Params.ParamByName('active').AsBoolean := True;

// Optional monitoring
FDMonitor1.TraceFlags := [tfQry, tfConn, tfErr];
FDMonitor1.Enabled := True;

Replace placeholders (<dbname>, <host>, etc.) with actual values. No special OS permissions are required beyond the ability to open a TCP port to the database server.

Trust and Data Boundaries

FireDAC treats the connection string as a trust boundary:

• Credentials should never be hard‑coded in source control; store them in a secure vault or encrypted configuration file and read them at startup.
• The TFDConnection object owns the socket; UI code should only interact with TFDQuery (or TFDStoredProc) to keep data‑access logic separate from presentation.
• If you use TFDMonitor for logging, ensure that logs do not contain plain‑text passwords; FireDAC masks password fields by default, but verify that your logging sink does not inadvertently record them.

Operational Checks

After deployment, verify the following:

1. Connection succeeds: FDConnection1.Connected returns True and TFDMonitor shows a tfConn entry with "Connection opened".
2. A simple query returns expected rows: execute FDQuery1.Open and confirm that a TDataSource linked to a TDBGrid displays data.
3. TLS handshake works: with a network sniffer (e.g., Wireshark) verify that traffic to the database port is encrypted when SSLMode=Require is set.
4. Error handling: disable the network adapter temporarily; the TFDMonitor should log a tfErr entry and the OnError event of TFDConnection fires. Implement a retry loop (e.g., three attempts with exponential back‑off) inside the event handler to restore connectivity without user interaction.

Failure Modes

Common ways the minimal design can break:

• Exhausting the connection pool: if MaxPoolSize is too low for concurrent threads, subsequent TFDConnection.Connected calls will block or raise an exception. Monitor TFDMonitor for pool‑wait messages.
• Memory pressure from large result sets: fetching millions of rows into a TFDQuery can consume all available RAM. Mitigate by setting FDQuery1.FetchOptions.Mode := fmOnDemand or using FetchOptions.RowSetSize to stream.
• TLS handshake failure due to outdated OS libraries: on older Windows versions, the built‑in SSL may lack support for modern cipher suites, causing connection errors. Update the OS or ship a compatible OpenSSL DLL alongside the application.
• Version mismatch in DataSnap scenarios: if a DataSnap server uses a different FireDAC runtime version, ABI incompatibilities can raise access violations when passing TFDQuery objects. Keep client and server FireDAC versions identical.

When the Design Must Change

Re‑evaluate the architecture if any of the following conditions arise:

• The application needs to support dozens of simultaneous users with bursty traffic – consider a dedicated connection‑pool manager or external pooler (e.g., PgBouncer) and increase MaxPoolSize accordingly.
• Business logic requires complex multi‑step transactions across multiple schemas – introduce a TFDTransaction per unit of work and enforce explicit Commit/Rollback.
• Regulatory audits demand immutable logs of every SQL statement – replace TFDMonitor with a custom logging service that writes to a write‑once store and ensure it captures the full SQL text and parameters.
• The target platform lacks the required SSL libraries (e.g., minimal Linux containers) – switch to a pure‑Delphi TLS implementation or rely on database‑specific native encryption options.

By starting with the minimal component set, verifying connectivity and error paths, and then extending only when operational data shows a need, you keep the data layer simple, testable, and easy to maintain.