FireDAC Data-Layer Architecture for RAD Studio Delphi and C++Builder Apps

When a Delphi or C++Builder team ships a desktop application or Windows service that must persist data, the data-access layer is the first architectural decision that affects security, maintainability, and runtime stability. RAD Studio includes FireDAC, a cross-database framework that eliminates the need for third-party stacks, but choosing it responsibly requires understanding its trust boundaries, connection model, and version-specific behaviors. The useful takeaway: build your design around a single data module owning parameterized TFDQuery components, draw pooled TFDConnections from TFDManager for worker threads, and keep the UI bound to TFDMemTable so the main thread never holds a live connection. This pattern satisfies the core requirements—SQL isolation, safe input handling, and thread discipline—while keeping the design small enough to audit and change.

Requirements that shape the design

Before opening the component palette, clarify what the application must do:

• FireDAC supports many drivers (MSSQL, MySQL, PostgreSQL, Oracle, SQLite, etc.), but which client libraries are required and whether they link statically or dynamically depends on your RAD Studio release. Check the docwiki for your installed version before committing to a database.
• Queries should live in a data-access module, not embedded in form or frame events. This lets you review, unit-test, and change SQL without touching UI code.
• Every piece of data that originates outside your code—user entries, file reads, network payloads—is untrusted. It must enter SQL only through parameter placeholders, never through string concatenation.
• In VCL and FMX, a long-running query on the main thread freezes the entire application window. Database work must happen off the UI thread.
• For services or apps with many concurrent users, each login costs time and socket resources. Connection pooling amortizes this cost, but only if connections are released promptly.

Smallest suitable design

Three pieces cover the majority of desktop and service applications:

1. A dedicated data module (a descendant of TDataModule) holds one TFDConnection or a named connection definition managed by TFDManager. All SQL code, and the components that execute it, live in or are owned by this module.
2. One parameterized TFDQuery per use case. Rather than one shared query whose SQL text is rewritten at runtime, give each logical operation its own named query. This makes the SQL self-documenting and easier to audit for injection risk.
3. TFDMemTable at the UI boundary. When the UI needs to display, edit, or cache data without a live connection, bind controls or business logic to a TFDMemTable. The data module populates it from the live connection and pushes changes back when appropriate.

A minimal parameterized query, executed inside a data module method and not in a form event handler, looks like this:

FDQuery1.SQL.Text := 'SELECT id, name, status FROM orders WHERE customer_id = :cust_id';
FDQuery1.ParamByName('cust_id').AsInteger := CustomerId;
FDQuery1.Open;

The placeholder :cust_id is bound by FireDAC and sent to the database as a value. This is the difference between a safe query and an injection hole. Note: AsInteger forces type coercion; if the input might not be an integer, validate before binding, then parameterize anyway.

Trust and data boundaries

Two boundaries deserve explicit statement:

• Everything arriving from users, files, or network calls is untrusted data. It enters SQL only through parameters. Never build SQL by concatenating strings, even for values that should be numeric—validate first, then parameterize.
• A TFDConnection is not safe to share across threads concurrently. The rule of thumb is one connection per thread, acquired late and released early. UI code never touches a connection directly; it asks the data module or a worker for results.

Result sets cross trust boundaries too. A value read from the database that later gets embedded into another query, a shell command, or a file path crosses a new trust boundary and needs the same parameterized discipline.

Connection pooling for services and multi-threaded workers

For a Windows service or any design with multiple worker threads, define the connection with pooling enabled so threads reuse open connections instead of paying a login cost per operation. A connection definition in an FDConnectionDefs.ini file might look like:

[MySQLServer]
DriverID=MSSQL
Server=dbserver
Database=appdb
User_Name=appuser
Password=***
Pooled=true
POOL_MaximumItems=20

Each worker creates a TFDConnection using that definition name, sets Connected := True, performs its data operations, and frees the component—returning the connection to the pool. The risk to watch is pool exhaustion: a worker that holds a connection while doing slow non-database work starves other threads. Keep the acquire-to-release window tight, and size POOL_MaximumItems against your database's actual connection limit.

Operational checks

Before and after deployment, verify behavior rather than assuming it:

• Run a proof query passing hostile input (quotes, semicolons, comment markers) as a parameter value and confirm it is treated as a literal string, not SQL.
• Run parallel workers and confirm connections are reused and released—no growth in open sessions on the database side, no timeout errors in the app.
• Confirm long queries run on background threads; the main thread should only receive completed datasets.
• Confirm your database server version against the FireDAC driver support matrix for your installed RAD Studio release. This is version-sensitive; check the release notes and docwiki rather than relying on memory.

Failure modes to plan for

• UI freeze. A query accidentally left on the main thread. Symptom: the window stops repainting. Fix: move the call into a worker thread with its own pooled connection.
• Pool exhaustion. Connections leaked or held too long. Symptom: threads block waiting for a connection, then time out. Fix: audit acquire/release pairs; ensure connections are freed in finally blocks.
• Component soup. Mixing FireDAC with legacy BDE or dbExpress components in the same module. It works technically but doubles maintenance cost. Migrate deliberately, one module at a time.
• Driver/client mismatch. A database upgrade breaks the FireDAC driver link. Static versus dynamic driver linking behavior varies by release, so re-verify after any database or IDE upgrade.

What would change this design

Revisit the architecture if any of these become true:

• You need a middle tier or REST API—move data access server-side and keep FireDAC out of the client.
• You need change notifications or heavy offline sync—FireDAC's cached updates or a dedicated sync layer may be more appropriate.
• You outgrow a single database and need cross-database queries—FireDAC's local SQL can join across sources, but that is a deliberate feature adoption, not an accident.

Until then, a data module, parameterized per-use-case queries, pooled per-thread connections, and TFDMemTable at the UI edge is a small, supported, and auditable foundation.