horse-database-pooling

Horse Database Pooling

1. The Multithreaded Database Rule

Horse handles each incoming HTTP request in a separate thread. Therefore, sharing a single database connection components (like a global TFDConnection or query component placed on a global DataModule/Controller) across requests will cause race conditions, data corruption, and Access Violations.

• Rule: Every route handler thread must instantiate its own database connection (or request one from a thread-safe connection pool) and free it immediately after use.

---

2. Setting Up FireDAC Connection Pooling

FireDAC provides built-in, highly optimized connection pooling. To configure and use it:

Phase A: Defining the Connection Definition (Global Setup)

Configure the connection definition globally in your application bootstrap (program block or startup class) using TFDManager:

uses
  FireDAC.Stan.Intf, FireDAC.Phys.PG, FireDAC.Comp.Client;

procedure SetupConnectionPool;
var
  LParams: TStrings;
begin
  LParams := TStringList.Create;
  try
    LParams.Values['DriverID'] := 'PG'; // PostgreSQL example
    LParams.Values['Server'] := 'localhost';
    LParams.Values['Database'] := 'my_db';
    LParams.Values['User_Name'] := 'postgres';
    LParams.Values['Password'] := 'secret';
    LParams.Values['Pooled'] := 'True'; // CRITICAL: Enables pooling
    LParams.Values['POOL_MaximumItems'] := '50'; // Set maximum connections in pool
    
    // Register the definition with FDManager
    FDManager.AddConnectionDef('MyPooledPGDef', 'PG', LParams);
  finally
    LParams.Free;
  end;
end;

Phase B: Acquiring and Releasing Connections in Handlers

In your route handlers, instantiate the TFDConnection locally, referencing the pooled definition name. Always protect resources with try...finally:

procedure GetUsersHandler(Req: THorseRequest; Res: THorseResponse; Next: TProc);
var
  LConnection: TFDConnection;
  LQuery: TFDQuery;
begin
  LConnection := TFDConnection.Create(nil);
  LQuery := TFDQuery.Create(nil);
  try
    // Reference the pooled definition - this will instantly pull a connection from the pool
    LConnection.ConnectionDefName := 'MyPooledPGDef';
    LConnection.Connected := True;
    
    LQuery.Connection := LConnection;
    LQuery.SQL.Text := 'SELECT id, name FROM users';
    LQuery.Open;
    
    // Johnson middleware takes ownership of the JSON object/array
    Res.Send(LQuery.ToJSONArray);
  finally
    LQuery.Free;
    LConnection.Free; // Automatically releases connection back to the pool
  end;
end;