In a Delphi/FireDac/SQL setup how do you manage multiple database connections in a RAD way?

by

Summary

This postmortem examines a common database connection management challenge in Delphi/FireDac applications transitioning from SQL Server (with MARS) to PostgreSQL compatibility. The core issue involves multiple concurrent result sets on a single database connection, where the default FireDac behavior conflicts with RAD development patterns that encourage declarative component configuration.

The problem manifests when developers naively place multiple TFDQuery components on forms expecting them to coexist, leading to the cryptic error: “Connection is busy with results for another command”. The solution requires careful consideration of connection pooling, fetch modes, and component lifecycle management.

Root Cause

The root cause stems from misaligned assumptions between FireDac’s default behavior and RAD development expectations:

  • FireDac’s default fetch mode is fmOnDemand, which keeps cursors open for incremental data retrieval
  • MARS (Multiple Active Result Sets) on SQL Server allows parallel query execution on one connection
  • PostgreSQL lacks MARS support, enforcing sequential query execution per connection
  • RAD form design encourages drag-and-drop query components without explicit connection management

The fundamental mismatch is:

  • Developers expect concurrent query components to share connections seamlessly
  • Database drivers (especially PostgreSQL) require sequential execution or separate connections

Why This Happens in Real Systems

This architectural challenge emerges naturally in enterprise applications due to several factors:

Development Patterns:

  • Rapid Application Development encourages placing TFDQuery components directly on forms for quick binding
  • Lookup-heavy UIs require multiple simultaneous datasets (dropdowns, grids, master-detail views)
  • Legacy code migration often overlooks connection semantics when switching database backends

Technical Debt Accumulation:

  • Component-oriented thinking abstracts away connection lifecycle concerns
  • Default property settings favor performance (lazy fetching) over compatibility
  • Test environments using SQL Server mask issues until PostgreSQL deployment

Team Dynamics:

  • Junior developers follow IDE wizards and samples assuming they’re production-ready
  • Cross-database compatibility requirements emerge late in development cycles
  • Performance optimization discussions happen separately from connection management

Real-World Impact

The consequences of improper connection management cascade through the application stack:

User Experience:

  • Random application freezes when multiple queries conflict
  • Data inconsistency if partial fetch operations corrupt result sets
  • Poor performance from excessive connection creation/destruction overhead

System Reliability:

  • Connection pool exhaustion under load, causing timeout errors
  • Resource leaks from unclosed queries and connections accumulating over time
  • Deadlocks in complex forms with interdependent data loading

Maintenance Burden:

  • Heisenbug characteristics making issues difficult to reproduce and debug
  • Deployment anxiety when migrating between database platforms
  • Knowledge silos where only senior developers understand the workaround patterns

Example or Code

// Problematic pattern - shared connection with mixed fetch modes
procedure TForm1.LoadCustomerData;
begin
  // These queries will conflict on PostgreSQL without MARS
  qryCustomer.Open;                    // Leaves cursor open (fmOnDemand)
  qryLookupCountries.Open;             // Fails: "Connection is busy"
  qryOrderHistory.Open;                // Fails: "Connection is busy"
end;

// Working solution - separate connections
procedure TForm1.LoadCustomerDataFixed;
begin
  // Master data queries on primary connection (fetch all)
  qryCustomer.FetchOptions.Mode := fmAll;
  qryCustomer.Open;

  // Lookup data on secondary connection
  qryLookupCountries.Connection := FDConnection2;
  qryLookupCountries.FetchOptions.Mode := fmAll;
  qryLookupCountries.Open;

  // Large dataset on tertiary connection with on-demand fetch
  qryOrderHistory.Connection := FDConnection3;
  qryOrderHistory.FetchOptions.Mode := fmOnDemand;
  qryOrderHistory.Open;
end;

How Senior Engineers Fix It

Experienced developers address this through architectural discipline rather than per-component tweaks:

Connection Strategy:

  • Implement connection factories that categorize queries by usage patterns
  • Use connection pooling with appropriate size limits for different query types
  • Establish query hierarchies separating UI lookups from transactional operations

Application Architecture:

  • Centralize data access through repository patterns instead of scattered queries
  • Introduce mediator components that manage query coordination and sequencing
  • Leverage dataset providers for master-detail relationships with explicit refresh logic

Configuration Standards:

  • Enforce fetch mode policies through base query classes with consistent defaults
  • Automate connection assignment based on query metadata (size estimates, usage patterns)
  • Implement monitoring hooks to detect and alert on connection contention

Deployment Practices:

  • Database abstraction layers that handle connection semantics per backend
  • Comprehensive integration testing specifically targeting concurrent query scenarios
  • Performance baselining to catch regressions in connection efficiency

Why Juniors Miss It

The oversight occurs across multiple knowledge domains that junior developers haven’t fully integrated:

Tool Misunderstanding:

  • IDE designers make multi-query forms appear functional without runtime validation
  • Component property defaults seem reasonable but hide platform-specific limitations
  • Sample code typically demonstrates single-query scenarios, not complex forms

Architectural Blind Spots:

  • Resource management concepts don’t translate directly from single-threaded examples
  • Database driver differences between SQL Server and PostgreSQL aren’t immediately obvious
  • Fetch mode implications on connection state are poorly documented in RAD contexts

Debugging Limitations:

  • Error messages are cryptic and don’t clearly indicate the root cause
  • Testing environments using SQL Server mask the fundamental problem
  • Symptom treatment (retrying queries) rather than addressing concurrency design

Leave a Comment