8. External SQL Database Access

Connect to Any RDBMS

The mORMot framework provides direct access to external SQL databases through the mormot.db.sql. units. These databases can be used standalone or integrated with the ORM via virtual tables.

8.1.1. SynDB Layer (mORMot 2: mormot.db.)

┌─────────────────────────────────────────────────────────────────┐ 
│                     mormot.db.sql.pas                            │
│              TSqlDBConnectionProperties (abstract)               │
│              TSqlDBConnection, TSqlDBStatement                   │
└─────────────────────────────────────────────────────────────────┘ 
                              │
      ┌───────────────────────┼───────────────────────┐
      │                       │                       │
      ▼                       ▼                       ▼
┌─────────────┐       ┌─────────────┐       ┌─────────────┐         
│   Direct    │       │    ODBC     │       │  TDataSet            │
│   Access    │       │   Access    │       │   Bridge             │
└─────────────┘       └─────────────┘       └─────────────┘         
      │                       │                       │
      ▼                       ▼                       ▼
  PostgreSQL              Any ODBC              FireDAC
  Oracle (OCI)            Driver                UniDAC
  SQLite3                                       NexusDB

8.1.2. Provider Units

8.1.3. Database Engines Supported

8.2. Connection Properties

8.2.1. Creating Connections

uses
  mormot.db.sql,
  mormot.db.sql.postgres;

var
  Props: TSqlDBPostgresConnectionProperties;
begin
  // PostgreSQL direct
  Props := TSqlDBPostgresConnectionProperties.Create(
    'localhost:5432', 'mydb', 'user', 'password');

  // Connection string alternative
  Props := TSqlDBPostgresConnectionProperties.Create(
    'host=localhost port=5432 dbname=mydb', '', 'user', 'password');
end;

8.2.2. Provider-Specific Examples

Oracle (direct OCI):

uses
  mormot.db.sql.oracle;

Props := TSqlDBOracleConnectionProperties.Create(
  'myserver/orcl', '', 'scott', 'tiger');

// With TNS alias
Props := TSqlDBOracleConnectionProperties.Create(
  'PROD_DB', '', 'user', 'password');

MS SQL Server (ODBC):

uses
  mormot.db.sql.odbc;

Props := TSqlDBOdbcConnectionProperties.Create(
  'Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb',
  '', 'user', 'password');

// Windows authentication
Props := TSqlDBOdbcConnectionProperties.Create(
  'Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=Yes',
  '', '', '');

Zeos (recommended for cross-database):

uses
  mormot.db.sql.zeos;

// PostgreSQL via Zeos
Props := TSqlDBZeosConnectionProperties.Create(
  'zdbc:postgresql://localhost:5432/mydb', '', 'user', 'password');

// MySQL via Zeos
Props := TSqlDBZeosConnectionProperties.Create(
  'zdbc:mysql://localhost:3306/mydb', '', 'user', 'password');

// Firebird via Zeos
Props := TSqlDBZeosConnectionProperties.Create(
  'zdbc:firebird://localhost/C:\Data\mydb.fdb', '', 'SYSDBA', 'masterkey');

8.2.3. Connection Properties Lifecycle

var
  Props: TSqlDBConnectionProperties;
begin
  // Create once, use throughout application
  Props := TSqlDBPostgresConnectionProperties.Create(...);
  try
    // Props manages connection pool internally
    UseDatabase(Props);
  finally
    Props.Free;  // Release all connections
  end;
end;

Important: Create TSqlDBConnectionProperties once per application. It manages connection pooling automatically.

8.3. Executing Queries

8.3.1. ISqlDBRows Interface

The simplest and safest pattern:

var
  Rows: ISqlDBRows;
begin
  Rows := Props.Execute(
    'SELECT * FROM Customer WHERE Country = ?', ['USA']);

  while Rows.Step do
    WriteLn(Rows['Name'], ' - ', Rows['City']);

  // No try..finally needed - interface auto-releases
end;

8.3.2. Statement-Based Access

For more control:

var
  Conn: TSqlDBConnection;
  Stmt: TSqlDBStatement;
begin
  Conn := Props.ThreadSafeConnection;  // Thread-safe connection
  Stmt := Conn.NewStatementPrepared(
    'SELECT * FROM Customer WHERE Country = ?', True);  // True = cache
  try
    Stmt.Bind(1, 'USA');
    Stmt.ExecutePrepared;

    while Stmt.Step do
      WriteLn(Stmt.ColumnUtf8(0));
  finally
    Stmt.Free;
  end;
end;

8.3.3. Late-Binding Access

For convenient (but slower) field access:

var
  Row: Variant;
begin
  with Props.Execute(
    'SELECT * FROM Customer WHERE Country = ?',
    ['USA'], @Row) do
  while Step do
    WriteLn(Row.Name, ' - ', Row.City);  // Late-binding!
end;

8.3.4. Direct JSON Output

Export query results directly to JSON:

var
  Json: RawUtf8;
begin
  Json := Props.Execute(
    'SELECT ID, Name, Email FROM Customer', []).FetchAllAsJson(True);
  // Returns: [{"ID":1,"Name":"John","Email":"john@example.com"},...]
end;

8.4. Field Types

8.4.1. TSqlDBFieldType

8.4.2. Column Access Methods

// Type-safe access
Value := Stmt.ColumnInt(0);       // Int64
Value := Stmt.ColumnDouble(1);    // Double
Value := Stmt.ColumnCurrency(2);  // Currency
Value := Stmt.ColumnUtf8(3);      // RawUtf8
Value := Stmt.ColumnDateTime(4);  // TDateTime
Value := Stmt.ColumnBlob(5);      // RawByteString

// Null checking
if Stmt.ColumnNull(0) then
  WriteLn('Value is NULL');

// Variant access (auto-conversion)
Value := Stmt.Column[0];  // Returns Variant
Value := Stmt['ColumnName'];  // By name

8.5. Parameter Binding

8.5.1. Positional Parameters

// Use ? for positional parameters
Stmt := Conn.NewStatementPrepared(
  'INSERT INTO Customer (Name, Email, Age) VALUES (?, ?, ?)', True);
Stmt.Bind(1, 'John Doe');
Stmt.Bind(2, 'john@example.com');
Stmt.Bind(3, 30);
Stmt.ExecutePrepared;

8.5.2. Array Binding (Bulk Insert)

High-performance bulk operations:

// PostgreSQL, Oracle, and MSSQL support array binding
Stmt := Conn.NewStatementPrepared(
  'INSERT INTO Log (Timestamp, Message) VALUES (?, ?)', True);

Stmt.BindArray(1, DateTimes);   // TDateTimeDynArray
Stmt.BindArray(2, Messages);    // TRawUtf8DynArray
Stmt.ExecutePrepared;           // Single network roundtrip!

8.5.3. BLOB Parameters

var
  Data: RawByteString;
begin
  Data := StringFromFile('image.png');
  Stmt.Bind(1, ftBlob, Data);  // Explicit type
  Stmt.ExecutePrepared;
end;

8.6. Transactions

8.6.1. Explicit Transactions

var
  Conn: TSqlDBConnection;
begin
  Conn := Props.ThreadSafeConnection;
  Conn.StartTransaction;
  try
    Conn.Execute('UPDATE Account SET Balance = Balance - 100 WHERE ID = 1');
    Conn.Execute('UPDATE Account SET Balance = Balance + 100 WHERE ID = 2');
    Conn.Commit;
  except
    Conn.Rollback;
    raise;
  end;
end;

8.6.2. Nested Transactions

// Most databases don't support true nested transactions
// mORMot uses SAVEPOINTs where available
Conn.StartTransaction;      // Begin
try
  Conn.StartTransaction;    // SAVEPOINT (if supported)
  try
    // Operations
    Conn.Commit;            // RELEASE SAVEPOINT
  except
    Conn.Rollback;          // ROLLBACK TO SAVEPOINT
    raise;
  end;
  Conn.Commit;              // COMMIT
except
  Conn.Rollback;            // ROLLBACK
end;

8.7. ORM Integration

8.7.1. Mapping TOrm to External Database

uses
  mormot.orm.sql,
  mormot.db.sql.postgres;

var
  Props: TSqlDBPostgresConnectionProperties;
  Model: TOrmModel;
  Server: TRestServerDB;
begin
  // Create connection properties
  Props := TSqlDBPostgresConnectionProperties.Create(
    'localhost:5432', 'mydb', 'user', 'password');

  // Create model
  Model := TOrmModel.Create([TOrmCustomer, TOrmOrder]);

  // Map ORM classes to external database
  OrmMapExternal(Model, TOrmCustomer, Props);
  OrmMapExternal(Model, TOrmOrder, Props);

  // Create server (SQLite3 for other tables)
  Server := TRestServerDB.Create(Model, ':memory:');

  // Now ORM operations go to PostgreSQL
  Server.Orm.Add(Customer, True);
end;

8.7.2. Table and Field Name Mapping

// Custom table name
OrmMapExternal(Model, TOrmCustomer, Props, 'CUSTOMERS');

// Custom field mapping
OrmMapExternal(Model, TOrmCustomer, Props, 'CUSTOMERS')
  .MapField('Name', 'CUSTOMER_NAME')
  .MapField('Email', 'EMAIL_ADDRESS');

8.7.3. Auto-Create Tables

// mORMot can create tables in external database
Props.UseFastCreateMissingFields := True;
Server.CreateMissingTables;  // Creates in PostgreSQL

8.8. Multi-Database Scenarios

8.8.1. Mixing Storage Backends

var
  PostgresProps, OracleProps: TSqlDBConnectionProperties;
begin
  // Different databases for different tables
  PostgresProps := TSqlDBPostgresConnectionProperties.Create(...);
  OracleProps := TSqlDBOracleConnectionProperties.Create(...);

  Model := TOrmModel.Create([
    TOrmUser,     // Internal SQLite3
    TOrmProduct,  // PostgreSQL
    TOrmLegacy    // Oracle
  ]);

  OrmMapExternal(Model, TOrmProduct, PostgresProps);
  OrmMapExternal(Model, TOrmLegacy, OracleProps);

  Server := TRestServerDB.Create(Model, 'users.db3');
end;

8.8.2. Cross-Database Queries

Thanks to SQLite3 virtual tables, you can JOIN across databases:

// This works even though Product is in PostgreSQL and User is in SQLite3!
Server.Orm.ExecuteList([TOrmProduct, TOrmUser],
  'SELECT Product.Name, User.Email ' +
  'FROM Product, User ' +
  'WHERE Product.OwnerID = User.ID');

8.9. Database-Specific Notes

8.9.1. PostgreSQL

// Direct libpq access (fastest)
uses mormot.db.sql.postgres;

Props := TSqlDBPostgresConnectionProperties.Create(
  'localhost:5432', 'dbname', 'user', 'pass');

// Connection string options
Props := TSqlDBPostgresConnectionProperties.Create(
  'host=localhost port=5432 dbname=mydb sslmode=require',
  '', 'user', 'pass');

Tips:

• Use array binding for bulk inserts
• PostgreSQL handles UTF-8 natively
• JSONB columns work well with TDocVariant

8.9.2. Oracle

// Direct OCI access (fastest for Oracle)
uses mormot.db.sql.oracle;

Props := TSqlDBOracleConnectionProperties.Create(
  'server/service', '', 'user', 'pass');

// Array binding for bulk operations
Props.ArrayBindingEnabled := True;

Tips:

• Use array binding (Oracle excels at this)
• Direct OCI is much faster than ODBC/OleDB
• Large BLOBs require special handling

8.9.3. MS SQL Server

// ODBC (recommended)
uses mormot.db.sql.odbc;

Props := TSqlDBOdbcConnectionProperties.Create(
  'Driver={ODBC Driver 17 for SQL Server};Server=.\SQLEXPRESS;Database=mydb',
  '', 'user', 'pass');

// OleDB alternative
uses mormot.db.sql.oledb;

Props := TSqlDBOleDBConnectionProperties.Create(
  'Provider=SQLNCLI11;Server=.\SQLEXPRESS;Database=mydb',
  '', 'user', 'pass');

Tips:

• Use ODBC Driver 17+ for best performance
• Windows authentication: Trusted_Connection=Yes
• MARS (Multiple Active Result Sets) may cause issues

8.9.4. MySQL/MariaDB

// Zeos (recommended)
uses mormot.db.sql.zeos;

Props := TSqlDBZeosConnectionProperties.Create(
  'zdbc:mysql://localhost:3306/mydb', '', 'user', 'pass');

// ODBC alternative
Props := TSqlDBOdbcConnectionProperties.Create(
  'Driver={MySQL ODBC 8.0 Driver};Server=localhost;Database=mydb',
  '', 'user', 'pass');

Tips:

• Use UTF-8 character set (SET NAMES utf8mb4)
• InnoDB engine for transactions
• MariaDB is generally faster than MySQL

8.10. Performance Tuning

8.10.1. Connection Pooling

// Connection pool is automatic
Props := TSqlDBPostgresConnectionProperties.Create(...);

// Configure pool size
Props.ConnectionPoolMaxCount := 20;  // Max connections
Props.ConnectionPoolTimeout := 60000;  // Timeout in ms

8.10.2. Statement Caching

// Enable statement caching (True = cache)
Stmt := Conn.NewStatementPrepared(SQL, True);

// Clear cache if needed
Conn.ClearStatementCache;

8.10.3. Batch Operations

// Use ExecuteNoResult for multiple statements
Conn.ExecuteNoResult('DELETE FROM TempTable');
Conn.ExecuteNoResult('INSERT INTO TempTable SELECT * FROM Source');

// Or use batch interface
Batch := TRestBatch.Create(Server.Orm, TOrmCustomer);
try
  for i := 1 to 10000 do
    Batch.Add(CreateCustomer(i), True);
  Server.Orm.BatchSend(Batch);  // Single transaction
finally
  Batch.Free;
end;

8.11. TDataSet Integration

For VCL/FMX applications that need TDataSet:

8.11.1. Read-Only DataSet

uses
  mormot.db.rad.ui.sql;

var
  DataSet: TSqlDBDataSet;
begin
  DataSet := TSqlDBDataSet.Create(Self);
  DataSet.Connection := Props;
  DataSet.SQL.Text := 'SELECT * FROM Customer';
  DataSet.Open;

  DBGrid1.DataSource.DataSet := DataSet;
end;

8.11.2. From ISqlDBRows

uses
  mormot.db.rad.ui.sql;

var
  Rows: ISqlDBRows;
begin
  Rows := Props.Execute('SELECT * FROM Customer', []);
  DBGrid1.DataSource.DataSet := ToDataSet(Self, Rows);
end;

8.12. Migration from mORMot 1

8.12.1. Unit Renames

8.12.2. Class Renames

8.12.3. API Changes

// mORMot 1
VirtualTableExternalRegister(Model, TSQLCustomer, Props);

// mORMot 2
OrmMapExternal(Model, TOrmCustomer, Props);

Next Chapter: External NoSQL Database Access (MongoDB)

Navigation