How to access databases from Delphi code

Most data access in Kitto^x is driven by YAML metadata — models, views, and controllers read and write records through the framework without any Delphi code. But when you write rules, custom tools, or background logic, you eventually need a TEFDBConnection directly. This page describes the three public APIs exposed by TKConfig (unit Kitto.Config) and when to use each.

TL;DR

API	Semantics	Ownership	When to use
TKConfig.Database	Default database, per-request cached	Borrowed (do not free)	The 99% case — any rule, tool or custom handler that uses the main DB
TKConfig.DatabaseFor(Name)	Named database, per-request cached	Borrowed (do not free)	Code that accesses a non-default database (reporting DB, auth DB, multi-tenant routing, etc.)
TKConfig.CreateStandaloneDBConnection(Name)	New isolated connection	Caller owns — must FreeAndNil	Exceptional cases: dynamic-credential authentication, design-time tooling, objects with lifetime longer than a request

TKConfig.Instance.CreateDBConnection(Name) (from earlier Kitto versions) is no longer public. Application code must pick one of the three APIs above.

Per-request connection cache

A single web request can touch the database from many places: the framework itself (loads, saves, lookups), business rules (BeforeFieldChange, AfterPersist, …), custom tools, report handlers, and so on. Historically each caller created its own TEFDBConnection wrapper — tens of wrappers per request, each one a thin layer over a pooled FireDAC connection but still allocated, configured from YAML, and then freed.

Since version 4.0.5 the framework keeps one cached TEFDBConnection per database per thread. The web engine creates the cache lazily on first access and clears it in the finally of the request handler. All callers inside the same request see the same wrapper instance for the same database name.

Concrete consequences:

• TKConfig.Database.StartTransaction in one rule and TKConfig.Database.CommitTransaction in another rule of the same request operate on the same transaction — as intuition expects.
• InstantObjects rules (via TInstantKittoConnector) share the connection with the rest of the request, so locks taken by the master save are held by the same session as the detail update.
• When you write LDBConnection := TKConfig.Database; you get a borrowed reference. Never call FreeAndNil(LDBConnection) on it — you would leave a dangling pointer in the cache for the next caller in the same request.

The FireDAC physical connection is not affected: pooling remains on at the TFDManager level (Pooled=True / POOL_MaximumItems), so the network handshake is paid once per pool item and reused across requests. The per-request cache saves the allocation of the Delphi wrapper objects (TEFDBFDConnection, TFDConnection, TFD*Options, config tree clones) that sit on top of the physical connection.

Default database: TKConfig.Database

This is the shortcut for the default database (the one named Main in Config.yaml, unless overridden by DefaultDatabaseName or by a top-level DatabaseRouter node).

uses
  EF.DB,
  Kitto.Config;

procedure TMyRule.AfterFieldChange(const AField: TKField;
  const AOldValue, ANewValue: Variant);
var
  LDBConnection: TEFDBConnection;
  LQuery: TEFDBQuery;
begin
  inherited;
  LDBConnection := TKConfig.Database;
  LQuery := LDBConnection.CreateDBQuery;
  try
    LQuery.CommandText :=
      'SELECT DESCRIPTION, START_DATE, END_DATE ' +
      'FROM CAMPAIGN WHERE ID = :ID';
    LQuery.Params.ParamByName('ID').Value := ANewValue;
    LQuery.Open;
    try
      if not LQuery.DataSet.IsEmpty then
        AField.ParentRecord.FieldByName('StartDate').AsDateTime :=
          LQuery.DataSet.FieldByName('START_DATE').AsDateTime;
    finally
      LQuery.Close;
    end;
  finally
    FreeAndNil(LQuery);  // Free the query — NEVER free LDBConnection
  end;
end;

Notice the pattern: the query has caller ownership and must be freed; the connection is borrowed from the cache and is released automatically when the request ends.

Named database: TKConfig.DatabaseFor(Name)

When your code needs a database other than the default — typically because the application is configured with multiple databases via database routing — use DatabaseFor with the database name:

LDBConnection := TKConfig.DatabaseFor('Reporting');
LQuery := LDBConnection.CreateDBQuery;
try
  LQuery.CommandText := 'SELECT * FROM DAILY_SUMMARY WHERE DAY = :DAY';
  LQuery.Params.ParamByName('DAY').AsDate := Date;
  LQuery.Open;
  try
    // ... consume the dataset
  finally
    LQuery.Close;
  end;
finally
  FreeAndNil(LQuery);
end;

Same ownership contract as Database: the connection is per-request cached, do not free it.

DatabaseFor('') and DatabaseFor(TKConfig.Instance.DatabaseName) are deduplicated: both return the same instance as TKConfig.Database. This means code that generically receives a database name (for example, a helper that takes ViewTable.DatabaseName as input) does the right thing whether the caller is on the default or a secondary database — no double-cached wrapper, no conflicting transactions.

Typical call sites using DatabaseFor

• Custom TKXDataToolController descendants that need to execute extra SQL outside the standard save pipeline
• Auto-complete / lookup queries that target a different database than the primary model
• Data loaders in enterprise controllers (Calendar, Chart, etc.) that read from a reporting DB
• Any rule that cross-references data from a routed model

Transactions

Because callers in the same request share the connection, a transaction started by one piece of code is visible to everything else running in that request. This is usually exactly what you want (master-detail atomicity), but it means you must balance StartTransaction / CommitTransaction (or RollbackTransaction) within the same logical scope. The framework-provided helpers do this for you:

TKConfig.Instance.InDBTransaction('Main',
  procedure (ADBConnection: TEFDBConnection)
  begin
    // All work inside this block is in one transaction.
    // Any exception rolls it back; successful exit commits.
    ADBConnection.ExecuteImmediate(...);
  end);

InDBConnection (no transaction) and InDBTransaction (transactional) both use the cached connection internally — no extra physical connection is created.

Standalone connections: CreateStandaloneDBConnection

A small number of scenarios cannot share the request-scoped cache. They need a connection they fully own and then dispose of. The public API for that is CreateStandaloneDBConnection(Name):

class function TKConfig.CreateStandaloneDBConnection(
  const ADatabaseName: string): TEFDBConnection; static;

The result is a brand-new TEFDBConnection, not registered in any cache. The caller is responsible for FreeAndNil(LDBConnection) when done. The name of the function explicitly signals ownership transfer.

When you genuinely need it

• Authentication with dynamic credentials. Authenticators like TKDBServerAuthenticator use %Auth:UserName% / %Auth:Password% macros in the connection string: the credentials change with every login attempt. A cached connection would freeze the first caller's credentials and let subsequent logins succeed without re-authenticating — a security hole. Each authentication attempt must build its own connection and dispose of it immediately.
• Design-time testing tools. Tools that test a DB configuration entered by the user in a form (for example, a connection wizard) should not pollute the application's runtime cache.
• Objects with a lifetime longer than a single request. Very rare in a KittoX app, but if you have a long-lived Delphi object that holds persistent resources (for example, a dedicated importer service), that object can own its own connection independently of the request cycle.

When you don't need it

If your code runs inside the request pipeline and is freed at the end of the request (rules, tools, controllers, helpers, most utility code), use Database or DatabaseFor. Creating a standalone connection in those cases just allocates a wrapper that the cache would have provided for free, and if you forget the FreeAndNil you leak it.

Background threads and non-web entry points

Code that runs outside the web request pipeline — background threads (email sender, scheduled jobs), console utilities, service workers — does not get the automatic ClearDatabase at end of work. Two options:

1. Use the cache explicitly: call TKConfig.Database / DatabaseFor normally, and call TKConfig.ClearDatabase (or TKConfig.DestroyInstance) at the end of the thread's work loop. The cache is per-thread, so each worker thread has its own isolated slot.
2. Own your own connection: call CreateStandaloneDBConnection in the thread constructor and FreeAndNil in the destructor. This is the pattern used by TEmailTimerThread in the sample email sender service, which holds its connection for the full lifetime of the thread.

Migration notes (4.0.5)

If you are upgrading from a previous Kitto release:

• Application code that used TKConfig.Instance.CreateDBConnection(Name) will not compile: the method is now protected. Replace it with TKConfig.DatabaseFor(Name) (cached, recommended) or TKConfig.CreateStandaloneDBConnection(Name) (if you genuinely need ownership).
• Any try … finally FreeAndNil(LDBConnection); end; that wrapped a connection obtained from TKConfig.Database or TKConfig.Instance.CreateDBConnection(Main) must be removed. The cache owns the connection; freeing it from the caller leaves a dangling pointer visible to other callers in the same request.
• TKConfig.Database and TKConfig.DatabaseFor(Name) always return borrowed references — the FreeAndNil(LQuery) around queries and commands stays, the FreeAndNil(LDBConnection) goes.

See the Release Notes for the full change log.