README

delphi-wrapper-sqlite3

Lightweight cross-platform Delphi wrapper for SQLite. Dynamically loads the platform-native SQLite library at runtime.

Platform Support

Requirements

• Newer version of Delphi (uses inline variables, PUTF8Char)
• SQLite library available on the target platform (see table above)

Quick Start

Add Source\Delphi.SQLite3.pas to your project (ensure Source\ is in the include path for the .inc file).

uses Delphi.SQLite3;

var DB := TSQLite3.Create('mydata.db');
try
  DB.ExecSQL('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, name TEXT, price REAL)');
  DB.ExecSQL('INSERT INTO items (name, price) VALUES (:n, :p)', ['Widget', 9.99]);

  var Q := DB.Query('SELECT id, name, price FROM items');
  try
    while Q.Next do
      WriteLn(Q.AsInteger(0), ' | ', Q.AsString(1), ' | ', Q.AsFloat(2):0:2);
  finally
    Q.Free;
  end;
finally
  DB.Free;
end;

API Reference

TSQLite3

Database connection. Opens (or creates) a SQLite database file.

constructor Create(const ADbPath: string);

Execute

// DDL or DML with no result set.
procedure ExecSQL(const ASQL: string);
procedure ExecSQL(const ASQL: string; const AParams: array of const);

// Query returning a result set. Caller must free the returned object.
function Query(const ASQL: string): TSQLite3Query;
function Query(const ASQL: string; const AParams: array of const): TSQLite3Query;

The no-parameter ExecSQL runs every semicolon-separated statement in the string, so it can execute a full schema or migration script in one call:

DB.ExecSQL(
  'CREATE TABLE a (x INTEGER);' +
  'CREATE TABLE b (y INTEGER);' +
  'INSERT INTO b (y) VALUES (7)');

If a statement fails partway through, ExecSQL raises ESQLite3Error and stops at that statement; statements before it have already been applied (each auto-commits). The parameterized ExecSQL/Query overloads are single-statement -- positional array of const parameters cannot be split across multiple statements, so pass one statement per call when binding parameters.

Scalar Helpers

Return the first column of the first row. Raise ESQLite3Error if the query returns no rows.

function QueryValue(const ASQL: string): string;
function QueryValue(const ASQL: string; const AParams: array of const): string;
function QueryInt(const ASQL: string): Integer;
function QueryInt(const ASQL: string; const AParams: array of const): Integer;

Transactions

procedure StartTransaction;
procedure Commit;
procedure Rollback;

Metadata

function Changes: Integer;          // rows affected by last INSERT/UPDATE/DELETE
function LastInsertRowId: Int64;    // rowid of most recent INSERT
class function LibVersion: string;  // e.g. '3.39.4'
class function IsAvailable: Boolean;// True if the platform SQLite library can be loaded

TSQLite3Query

Forward-only cursor returned by TSQLite3.Query. Call Next to advance; read columns by 0-based index.

function Next: Boolean;             // advance to next row; False when done
function EOF: Boolean;              // True after the last row

property ColumnCount: Integer;
function ColumnName(AIndex: Integer): string;
function IndexOf(const AName: string): Integer;  // -1 if not found (case-insensitive)

function AsString(AIndex: Integer): string;
function AsInteger(AIndex: Integer): Integer;
function AsInt64(AIndex: Integer): Int64;
function AsFloat(AIndex: Integer): Double;
function AsBlob(AIndex: Integer): TBytes;
function IsNull(AIndex: Integer): Boolean;

Parameter Binding

Parameters use :name placeholders in SQL and array of const values passed positionally. Supported Delphi types:

DB.ExecSQL('INSERT INTO t (name, amount, active) VALUES (:n, :a, :f)', ['Bolt', 3.50, True]);
DB.ExecSQL('UPDATE t SET name = :n WHERE id = :id', ['Nut', 42]);
DB.ExecSQL('INSERT INTO t (optional) VALUES (:v)', [nil]);  // NULL

BLOB Parameters

A TBytes value cannot ride through an array of const directly, so wrap it with the Blob() factory function to bind it as a BLOB. An empty TBytes binds a zero-length BLOB (distinct from NULL); read the value back with TSQLite3Query.AsBlob.

var Data: TBytes := [$DE, $AD, $BE, $EF];
DB.ExecSQL('INSERT INTO t (payload) VALUES (:d)', [Blob(Data)]);

var Q := DB.Query('SELECT payload FROM t WHERE id = :id', [1]);
try
  if Q.Next then
    Data := Q.AsBlob(0);
finally
  Q.Free;
end;

On read, AsBlob returns an empty TBytes (nil) for both a zero-length BLOB and a NULL column, because TBytes has no null state. If you need to tell the two apart, use IsNull (or typeof(col) in SQL); a zero-length BLOB reports IsNull = False while a NULL column reports IsNull = True.

Error Handling

All errors raise ESQLite3Error with the SQLite error code:

type
  ESQLite3Error = class(Exception)
    property ErrorCode: Integer read FErrorCode;
  end;

try
  DB.ExecSQL('SELECT * FROM nonexistent');
except
  on E: ESQLite3Error do
    WriteLn('SQLite error ', E.ErrorCode, ': ', E.Message);
end;

Examples

Transactions

DB.StartTransaction;
try
  for var I := 1 to 1000 do
    DB.ExecSQL('INSERT INTO items (name) VALUES (:n)', ['Item ' + I.ToString]);
  DB.Commit;
except
  DB.Rollback;
  raise;
end;

Auto-increment IDs

DB.ExecSQL('INSERT INTO items (name) VALUES (:n)', ['New item']);
var NewId := DB.LastInsertRowId;
WriteLn('Inserted row ID: ', NewId);

Column Lookup by Name

var Q := DB.Query('SELECT id, name, price FROM items');
try
  var ColPrice := Q.IndexOf('price');
  while Q.Next do
    WriteLn(Q.AsFloat(ColPrice):0:2);
finally
  Q.Free;
end;

Check Availability

if TSQLite3.IsAvailable then
  WriteLn('SQLite ', TSQLite3.LibVersion)
else
  WriteLn('SQLite library not found on this platform');

Technical Notes

Calling Convention

The wrapper uses stdcall on Windows (matching winsqlite3.dll) and cdecl on all POSIX platforms. This is handled via the Delphi.SQLite3.cc.inc include file that expands to the correct convention for the target platform.

String Encoding

All strings are converted to UTF-8 before passing to SQLite and converted back to Delphi string (UTF-16) on read. The conversions use UTF8String and PUTF8Char for type safety.

Dynamic Loading

The library is loaded once on first use. On Windows this uses LoadLibrary / GetProcAddress; on POSIX platforms (Linux, macOS, iOS, Android) it uses dlopen / dlsym from Posix.Dlfcn. The module handle is freed in the unit finalization section.

If the library is not present, TSQLite3.Create raises ESQLite3Error. Use TSQLite3.IsAvailable to check without raising.

Thread Safety

SQLite itself is thread-safe (serialized mode by default). However, a single TSQLite3 instance and its associated TSQLite3Query objects should be used from one thread at a time, or the caller must serialize access. For multi-threaded applications, create a separate TSQLite3 connection per thread.

The one-time library load and symbol resolution (triggered lazily on first use of TSQLite3 or TSQLite3Backup) is internally synchronized, so it is safe for multiple threads to make their first call concurrently -- the library is loaded exactly once. After loading, the steady-state path is lock-free.

Demo Project

APIBackup -- Todo List with Backup/Restore

A VCL application in projects/APIBackup/ that demonstrates TSQLite3 and TSQLite3Backup working together. The form has a TStringGrid showing todo entries from todo.db3, an edit control with an Add button to insert rows, and Backup/Restore buttons.

• Create -- opens (or creates) todo.db3 on startup with WAL journal mode
• Read -- populates the grid from the todos table
• Insert -- adds a new row from the edit control
• Backup -- saves to todo.backup.db3 via TSQLite3Backup.BackupToFile (single-file output, no WAL sidecars)
• Restore -- loads from the backup via TSQLite3Backup.RestoreFromFile and refreshes the grid

The backup unit (Delphi.SQLite3.Backup.pas in source/) also serves as a reference for extending TSQLite3 via raw API access. It resolves sqlite3_backup_init/step/finish/remaining/pagecount at runtime using TSQLite3.GetAPIProc and TSQLite3.Handle, without modifying the core unit.

Tests

The test suite uses DUnitX and covers 69 tests:

Running Tests

Open Tests\Delphi.SQLite3.Tests.dpr in the Delphi IDE and create a project file, or build from the command line:

dcc64 -Q -B -NSSystem;System.Win;Winapi ^
  -I"Source" ^
  -E"Tests\Win64\Debug" -N"Tests\Win64\Debug" ^
  -U"<RADStudio>\lib\Win64\release;<RADStudio>\lib\Win64\debug" ^
  Tests\Delphi.SQLite3.Tests.dpr

Run:

Tests\Win64\Debug\Delphi.SQLite3.Tests.exe --exit:Continue

Project Layout

delphi-wrapper-sqlite3/
  source/
    Delphi.SQLite3.pas            -- core wrapper (TSQLite3, TSQLite3Query)
    Delphi.SQLite3.cc.inc         -- calling convention include (stdcall/cdecl)
    Delphi.SQLite3.Backup.pas     -- online backup/restore via raw API access
  test/
    Delphi.SQLite3.Tests.dpr      -- DUnitX test project (69 tests)
    Delphi.SQLite3.Test.pas       -- core wrapper tests
    Delphi.SQLite3.Backup.Test.pas-- backup/restore tests
  projects/
    APIBackup/                    -- VCL demo: todo list with backup/restore

Part of Continuous Delphi

This tool is part of the Continuous-Delphi ecosystem, focused on improving engineering discipline for long-lived Delphi systems.