Databases

Edit connections visually

KIDE^x's Config Editor has a dedicated Databases panel that edits these connections visually and validates them with a Test Connection button before you run the app. The New Project Wizard also adds the matching data-access driver units to UseKitto.pas based on the drivers you select.

A single Kitto^x application can work on multiple databases, of different kinds. The Databases node in Config.yaml contains one or more subnodes, each containing the connection params to a database. The subnode called Main is the one used by default for database access. Give a different name to each subnode as in the following example:

yaml

Databases:
  Main: ADO
    Connection:
      .............................
  OTHER: ADO
    Connection:
    ..............................

Adapters

An adapter is a wrapper that allows Kitto^x to connect to a particular database (or a family of databases) through a particular data access library. Currently Kitto^x has four adapters:

DBX to use Delphi's DBExpress data access layer.
ADO to use Microsoft's ADO.
FD to use Delphi's FireDac data access layer.
ODAC to use Devart's Oracle Data Access Components — Oracle only, optional (requires an ODAC license; see Oracle via Devart ODAC below).

More adapters can be developed and plugged in as needed.

The adapter is specified by name as the value of each database's subnode.

Database types

Each adapter detects the type of database it is using and uses this information to affect the behaviour of Kitto^x, for example to generate slightly different SQL statements depending on the particular dialect the database understands.

Delimited identifiers (`DelimitedIdent`)

By default Kitto^x emits table and column names as-is in the SQL it generates. This works for the usual identifiers, but fails when a table or column name contains spaces (or other characters that are illegal in a bare SQL identifier) — typical of legacy / imported databases such as Northwind (Order Details, Alphabetical list of products, …). The result is a syntax error like "Incorrect syntax near the keyword 'of'".

Set DelimitedIdent: True on a database subnode (sibling of Connection) to make the SQL builder wrap every identifier in the delimiters of that database's dialect:

yaml

Databases:
  Main: FD
    DelimitedIdent: True        # opt-in, per database
    Connection:
      DriverID: MSSQL
      Server: 127.0.0.1, 1433
      Database: Northwind
      # ...

The flag is opt-in and per-database (default False): databases without it keep the exact previous behaviour. The delimiter is chosen automatically from the dialect:

Database	Delimiter	Example
SQL Server	`[ ]`	`[Order Details]`
PostgreSQL / Firebird / Oracle / ANSI	`" "`	`"Order Details"`
MySQL / MariaDB	` `	`Order Details`

Multi-part names are split on the dot and each part is delimited separately, so a schema/catalog prefix is handled correctly: dbo.Order Details → [dbo].[Order Details] (never [dbo.Order Details]).

Prerequisites and limitations

Exact case required (PostgreSQL / Oracle / Firebird). Delimiting with " " makes identifiers case-sensitive: "Customers" is not the same object as customers. When DelimitedIdent is on, the PhysicalName of each Model/field must match the spelling stored in the database exactly. (On SQL Server, [ ] does not change case sensitivity, which follows the collation.)
Free SQL is never delimited. Anything you author yourself — field Expression, DefaultFilter, DefaultSorting, LookupFilter, filter-panel Expression / ExpressionTemplate, and %...% macros — is emitted verbatim. If such an expression references an identifier with spaces, you delimit it yourself in the YAML (for example [Order Date] >= '{value}').
Parameter names. The fix delimits identifiers in the generated SQL; query parameter names are unaffected.

Connection subnode

You specify each database's settings inside a Connection subnode whose structure depends on the adapter. For example, the ADO adapter accepts a single ConnectionString subnode with all params in the typical ADO format:

Example:

yaml

Databases:
  Main: ADO
    Connection:
      ConnectionString: >
        Provider=SQLOLEDB.1;User ID=sa;Password=xxx;
        Initial Catalog=MyDatabase;Data Source=%COMPUTERNAME%

DBX allows for specifying params as direct subnodes:

yaml

Databases:
  Main: DBX
    Connection:
      DriverName: DevartSQLServer
      HostName: %COMPUTERNAME%
      DataBase: MyDatabase
      User_Name: sa
      Password: xxx
      GetDriverFunc: getSQLDriverSQLServer
      LibraryName: dbexpsda40.dll
      VendorLib: sqloledb.dll

FD allows for specifying params as direct subnodes, specific to DriverID:

yaml

Databases:
  FDMSSQL: FD
    Connection:
      DriverID: MSSQL
      Server: %COMPUTERNAME%\MSSQL2K8R2
      ApplicationName: %APPTITLE%
      Database: HelloKitto
      OSAuthent: Yes
      Isolation: RepeatableRead
    
  FDFb: FD
    Connection:
      DriverID: FB
      # Alias of database defined in databases.conf (Firebird 3.0+)
      # or Aliases.conf (Firebird 2.5)
      Database: HELLOKITTO
      User_Name: SYSDBA
      Password: masterkey
      Server: localhost
      CharacterSet: UTF8
      Protocol: TCPIP

  FDPG: FD
    Connection:
      DriverID: PG
      Server: localhost
      Port: 5432
      Database: hellokitto
      User_Name: kittox_admin
      Password: ********
      CharacterSet: UTF8
      # Schema sets the PostgreSQL search_path so unqualified table names
      # resolve to this schema. Defaults to 'public' if omitted.
      Schema: hellokitto
      ApplicationName: %APPTITLE%
      POOL_MaximumItems: 50

  FDOra: FD
    Connection:
      DriverID: Ora
      # EZ Connect string: host:port/service_name (Oracle XE 21c PDB = xepdb1)
      Database: localhost:1521/xepdb1
      User_Name: HELLOKITTO
      Password: ********
      CharacterSet: AL32UTF8        # full Unicode client charset
      BooleanFormat: Integer        # Oracle has no native boolean → map to NUMBER(1) (0/1)
      MetaDefSchema: HELLOKITTO      # default schema for FireDAC metadata lookups
      ApplicationName: %APPTITLE%
      POOL_MaximumItems: 50

PostgreSQL Schema and `search_path`

PostgreSQL groups database objects into schemas (the equivalent of SQL Server schemas, not databases). When the SQL refers to an unqualified table (for example KITTO_USERS rather than taskitto.KITTO_USERS), PostgreSQL resolves the name through the session-level search_path.

The Schema parameter in the Connection subnode is honored by the FD adapter in two ways:

It is passed to FireDAC as MetaDefSchema, which scopes metadata queries (e.g. TFDMetaInfoQuery) to that schema.
After every physical connection is opened, the framework executes SET search_path TO {Schema}, public on that connection. This makes all unqualified names resolve to your schema first, with public as fallback. It happens via TEFDBFDConnection.AfterConnectionOpen, which the FireDAC pool calls for every physical connection it creates — so the search_path is guaranteed on every session, including pooled ones.

If you need a different setup (different role, different search_path order, custom session GUCs), you can set them at the database level once with:

sql

ALTER ROLE kittox_admin SET search_path TO myschema, public;

or use the more general AfterOpenCommandText hook below.

Oracle (`DriverID: Ora`)

The FD adapter connects to Oracle through the Ora FireDAC driver (native OCI client — oci.dll must be reachable, e.g. via the Oracle Instant Client or a full Oracle home on the PATH). A few connection parameters are specific to Oracle:

Database — an EZ Connect string host:port/service_name. For Oracle XE 21c the pluggable database (PDB) is xepdb1, so the value is typically localhost:1521/xepdb1.
CharacterSet: AL32UTF8 — the full-Unicode client charset; use it so accented / non-ASCII data round-trips correctly.
BooleanFormat: Integer — Oracle has no native boolean type, so KittoX maps Boolean model fields to NUMBER(1) (0/1). With this set, the %DB.TRUE% / %DB.FALSE% macros expand to 1 / 0.
MetaDefSchema — the default schema for FireDAC metadata lookups. Set it to the connecting user's schema (e.g. HELLOKITTO) so unqualified table names resolve without extra qualification.

Listener binding

If FireDAC reports ORA-12541: TNS: no listener when connecting to localhost, the Oracle listener may be bound to a single hard-wired IP. Set HOST = 0.0.0.0 in listener.ora (so it listens on every interface, including localhost) and restart the listener service.

The HelloKitto and TasKitto examples ship a ready-to-use FireDAC_Oracle block in their Config.yaml (commented out by default — SQL Server stays the active database). Portable hand-written SQL in these examples uses the %DB.FROM_DUAL% and %DB.CONCAT% macros so the same YAML works on Oracle and every other backend.

Oracle via Devart ODAC (`ODAC` adapter)

Besides the FireDAC Ora driver above, Kitto^x provides an alternative Oracle back-end based on Devart ODAC (Oracle Data Access Components), for sites that own an ODAC license and prefer it to the OCI/FireDAC path. Select it by setting the database's adapter to ODAC:

yaml

Databases:
  Main: ODAC
    Connection:
      # EZ Connect string: host:port/service_name (Oracle XE 21c PDB = xepdb1)
      Server: localhost:1521/xepdb1
      User_Name: HELLOKITTO
      Password: 12345
      # Direct Oracle Net mode: connect without a locally installed Oracle
      # client (requires ODAC Professional). Set to False to use OCI.
      Direct: True
      Charset: AL32UTF8         # connection character set
      ConnectMode: Normal       # Normal (default), SysDBA or SysOper
      Schema: HELLOKITTO        # optional: ALTER SESSION SET CURRENT_SCHEMA on connect

Oracle-specific connection parameters:

Server — an EZ Connect string host:port/service_name (the same value FireDAC puts in Database). For Oracle XE 21c the PDB is xepdb1, so it is typically localhost:1521/xepdb1.
Direct (default True) — ODAC direct mode talks to Oracle over the network without a locally installed Oracle client. It requires ODAC Professional; set it to False to connect through the OCI client (oci.dll).
ConnectMode — Normal (default), SysDBA or SysOper.
Charset — the connection character set (e.g. AL32UTF8 for full Unicode).
Schema — optional; when set, the framework runs ALTER SESSION SET CURRENT_SCHEMA = {Schema} after connecting.

The ODAC adapter reuses the same Oracle dialect as the FireDAC path, so limit-clause pagination, %DB.FROM_DUAL% and the %DB.TRUE% / %DB.FALSE% macros (booleans bound as NUMBER(1)) behave identically to DriverID: Ora.

Optional, commercial library — not in the core package

ODAC is a third-party commercial product, so its adapter unit EF.DB.ODAC is not part of the KittoX core package (unlike DBX/ADO/FD, which ship with Delphi). To enable it, add EF.DB.ODAC to your application's UseKitto.pas and add the ODAC library path to the project; the unit self-registers the ODAC adapter in its initialization (unit order in the uses clause is irrelevant). Without ODAC installed, Oracle still works through the FireDAC Ora driver above. ODAC: https://www.devart.com/odac/

The HelloKitto and TasKitto examples ship a ready-to-use ODAC_Oracle connection block in their Config.yaml. It is not active by default: to switch to it, uncomment the EF.DB.ODAC line in Source/UseKitto.pas and select the database with DefaultDatabaseName: ODAC_Oracle.

FireDAC driver registration

When using the FD (FireDAC) adapter, the physical driver unit for the target database must be compiled into your application. There are two approaches:

Approach 1: Explicit uses in UseKitto.pas (recommended for clarity)

Add the FireDAC driver units directly in your UseKitto.pas:

pascal

uses
  Kitto.Html.All,
  EF.DB.FD,
  // FireDAC drivers — include only the ones you need
  FireDAC.Phys.MSSQL, FireDAC.Phys.MSSQLMeta,   // SQL Server
  FireDAC.Phys.IBBase, FireDAC.Phys.FB,           // Firebird
  // FireDAC.Phys.PG, FireDAC.Phys.PGWrapper,     // PostgreSQL
  // FireDAC.Phys.MySQL, FireDAC.Phys.MySQLWrapper,// MySQL
  // FireDAC.Phys.Oracle, FireDAC.Phys.OracleMeta, // Oracle
  // FireDAC.Phys.SQLite, FireDAC.Phys.SQLiteWrapper,// SQLite
  Kitto.Auth.DB;

This is the approach used by the HelloKitto and TasKitto examples.

Approach 2: Conditional defines in the project (used by KEmployee)

The EF.DB.FD unit includes driver units conditionally via {$IFDEF} directives. Add the appropriate define to your project options (Project → Options → Delphi Compiler → Conditional defines):

Define	Enables
`IBFB_SUPPORT`	Firebird and InterBase (`FireDAC.Phys.FB`, `FireDAC.Phys.IB`)
`MSSQL_SUPPORT`	SQL Server (`FireDAC.Phys.MSSQL`)
`ORACLE_SUPPORT`	Oracle (`FireDAC.Phys.Oracle`)
`PGSQL_SUPPORT`	PostgreSQL (`FireDAC.Phys.PG`)
`MYSQL_SUPPORT`	MySQL (`FireDAC.Phys.MySQL`)
`SQLITE_SUPPORT`	SQLite (`FireDAC.Phys.SQLite`)

SQL Server ODBC driver and query isolation

When using the FD (FireDAC) adapter with DriverID: MSSQL, KittoX has to deal with a regression that affects recent Microsoft ODBC drivers and breaks server-side rule cascades that iterate a result set while triggering nested queries.

The regression

FireDAC normally picks the newest installed SQL Server ODBC driver. On a recent Delphi install that is Microsoft ODBC Driver 17 for SQL Server or Microsoft ODBC Driver 18 for SQL Server. The legacy SQL Server Native Client 11.0 (deprecated by Microsoft, no longer shipped with new SQL Server installs) is the driver KittoX has historically run on.

Even with MARS=Yes (Multiple Active Result Sets) enabled, the ODBC Driver 17/18 does not isolate sibling TFDQuery statements that share a TFDConnection. When one TFDQuery is open and a second one is opened on the same connection — for example because an AfterFieldChange rule on the parent record opens its own query while the framework is loading derived reference fields — the underlying ODBC statement gets rebound and the first query's Fields list is silently invalidated. The first query's FieldByName then fails with EDatabaseError 'Field "X" not found' even though the SELECT explicitly listed the column. The Native Client 11.0 does not have this regression.

This affects code patterns that are common in ported Kitto1 applications: a rule opens a TFDQuery, iterates fields, and at every assignment to another record field (which fires FieldChanged and triggers nested cascades / nested rules) it then keeps reading from its own dataset.

The fix — per-query `TFDConnection`

Every TEFDBQuery acquired via TKConfig.Database.CreateDBQuery runs on its own private TFDConnection, acquired from a dedicated FireDAC pooled connection definition (TFDManager.AddConnectionDef is registered automatically at connection open). Because the FireDAC pool reuses physical connections across acquisitions, the cost is negligible.

This isolates each query at the TFDConnection level, so MARS effectiveness is no longer required. The same code now works on Native Client 11, ODBC Driver 17, and ODBC Driver 18.

The fix preserves transactional semantics: when the parent wrapper's TFDConnection is currently inside a transaction (InTransaction = True), the query reuses the shared connection so it stays inside the transaction.

The fix is unconditional — no YAML configuration knobs to set, FireDAC keeps auto-detecting the ODBC driver as before.

Firebird version compatibility

The FD adapter targeting Firebird (DriverID: FB) is compatible with all Firebird versions from 2.5 up to the latest 5.x. The KEmployee example is verified against Firebird 5.0 64-bit on the standard EMPLOYEE.FDB sample database shipped with the Firebird installer.

Notes for Firebird 3.0+ servers:

Wire encryption — Firebird 3.0+ enables wire encryption by default. FireDAC's native client (fbclient.dll) handles it transparently; no additional configuration is required on the KittoX side.
Authentication plugins — Firebird 3.0+ uses Srp / Srp256 by default. The default SYSDBA account works out of the box. Legacy applications that still rely on Legacy_Auth need AuthServer = Srp, Srp256, Legacy_Auth and UserManager = Srp, Legacy_UserManager in firebird.conf.
Database aliases — Aliases moved from Aliases.conf (2.5) to databases.conf (3.0+). Existing connection strings keep working as long as the alias is declared in whichever file the running server uses.
64-bit deployment — Match the Firebird server bitness with the application: Firebird 5.0 64-bit requires Win64 builds of the KittoX app. All KittoX example projects (KEmployee.dproj, KEmployeeISAPI.dproj, KEmployeeDesktop.dproj) default to Win64; the Apache module (mod_kemployee.dproj) stays Win32 for compatibility with 32-bit Apache builds.

All projects must have the same defines

When you have multiple deployment projects for the same application (Standalone, Desktop, ISAPI, Apache), make sure all .dproj files include the same conditional defines. Otherwise the driver will be registered in one project but not in another, causing "Object factory missing" errors at runtime.

AfterOpenCommandText

If specified, the value of Connection/!AfterOpenCommandText is executed as a SQL command each time a connection is made. Useful if special connection setup is needed. The command text may contain macros.

Accessing the database at runtime

Kitto^x exposes two entry points for obtaining a database connection from Delphi code. They differ in ownership and lifetime.

`TKConfig.Database` — cached per-request (recommended for rules and handlers)

Returns the per-thread cached connection for the Main database. The connection is created lazily on first access during a web request and released automatically by the engine at the end of the request (TKWebEngine.SimpleHandleRequest calls TKConfig.ClearDatabase in its finally block).

pascal

// In a Rule, a custom handler, or any per-request code:
var
  LQuery: TEFDBQuery;
begin
  LQuery := TKConfig.Database.CreateDBQuery;
  try
    LQuery.CommandText := 'SELECT COUNT(*) FROM CUSTOMERS WHERE ACTIVE = 1';
    LQuery.Open;
    Result := LQuery.DataSet.Fields[0].AsInteger;
  finally
    FreeAndNil(LQuery);
  end;
end;

Do NOT FreeAndNil the connection

Inside a single request, multiple callers share the same connection reference returned by TKConfig.Database. Calling FreeAndNil on it would leave a dangling pointer in the per-thread cache that the next caller inside the same request would dereference. Free only the query or command you created, not the connection itself.

One useful side effect of the cache: a sequence like TKConfig.Database.StartTransaction; ... TKConfig.Database.ExecuteImmediate(...); ... TKConfig.Database.CommitTransaction; now runs on a single connection, so the transaction semantics hold as expected.

`TKConfig.Instance.CreateDBConnection(ADatabaseName)` — owned by the caller

Use this factory when you need a dedicated connection (different database, background thread outside the request lifecycle, long-lived object that survives multiple requests). The caller owns the returned connection and must release it with FreeAndNil.

pascal

var
  LDBConnection: TEFDBConnection;
  LQuery: TEFDBQuery;
begin
  LDBConnection := TKConfig.Instance.CreateDBConnection('Logging');
  try
    LQuery := LDBConnection.CreateDBQuery;
    try
      LQuery.CommandText := 'INSERT INTO AUDIT_LOG (MSG) VALUES (:MSG)';
      LQuery.Params.ParamByName('MSG').AsString := AMessage;
      LQuery.Execute;
    finally
      FreeAndNil(LQuery);
    end;
  finally
    FreeAndNil(LDBConnection);
  end;
end;

The framework itself uses CreateDBConnection internally in units like Kitto.Auth.DB, Kitto.AccessControl.DB, Kitto.Metadata.DataView, and Kitto.Html.Form — each one creates a connection, uses it in a local try/finally block, and frees it before returning. Application code should follow the same pattern whenever it needs a dedicated connection.

`TKConfig.Instance.InDBConnection` / `InDBTransaction` — RAII helpers

For ad-hoc blocks, the InDBConnection and InDBTransaction helpers take an anonymous procedure and handle creation, transaction scope, and cleanup automatically:

pascal

TKConfig.Instance.InDBTransaction('Main',
  procedure (ADBConnection: TEFDBConnection)
  begin
    ADBConnection.ExecuteImmediate('UPDATE ...');
    ADBConnection.ExecuteImmediate('INSERT ...');
    // Commit on success, Rollback on exception — handled internally
  end);

Deployment

Layout & Containers

Navigation

Data Display

Data Editing

Enterprise

Authentication

Tool Controllers

KIDEX: the KittoX IDE

Databases

Adapters

Database types

Delimited identifiers (`DelimitedIdent`)

Prerequisites and limitations

Connection subnode

PostgreSQL Schema and `search_path`

Oracle (`DriverID: Ora`)

Oracle via Devart ODAC (`ODAC` adapter)

FireDAC driver registration

SQL Server ODBC driver and query isolation

The regression

The fix — per-query `TFDConnection`

Firebird version compatibility

AfterOpenCommandText

Accessing the database at runtime

`TKConfig.Database` — cached per-request (recommended for rules and handlers)

`TKConfig.Instance.CreateDBConnection(ADatabaseName)` — owned by the caller

`TKConfig.Instance.InDBConnection` / `InDBTransaction` — RAII helpers

Databases ​

Adapters ​

Database types ​

Delimited identifiers (DelimitedIdent) ​

Prerequisites and limitations ​

Connection subnode ​

PostgreSQL Schema and search_path ​

Oracle (DriverID: Ora) ​

Oracle via Devart ODAC (ODAC adapter) ​

FireDAC driver registration ​

SQL Server ODBC driver and query isolation ​

The regression ​

The fix — per-query TFDConnection ​

Firebird version compatibility ​

AfterOpenCommandText ​

Accessing the database at runtime ​

TKConfig.Database — cached per-request (recommended for rules and handlers) ​

TKConfig.Instance.CreateDBConnection(ADatabaseName) — owned by the caller ​

TKConfig.Instance.InDBConnection / InDBTransaction — RAII helpers ​

Databases

Adapters

Database types

Delimited identifiers (`DelimitedIdent`)

Prerequisites and limitations

Connection subnode

PostgreSQL Schema and `search_path`

Oracle (`DriverID: Ora`)

Oracle via Devart ODAC (`ODAC` adapter)

FireDAC driver registration

SQL Server ODBC driver and query isolation

The regression

The fix — per-query `TFDConnection`

Firebird version compatibility

AfterOpenCommandText

Accessing the database at runtime

`TKConfig.Database` — cached per-request (recommended for rules and handlers)

`TKConfig.Instance.CreateDBConnection(ADatabaseName)` — owned by the caller

`TKConfig.Instance.InDBConnection` / `InDBTransaction` — RAII helpers