SQLite and UnQLite in Object Pascal: Talking to the Native C Libraries Directly
Bjørn T. Nøstdahl
~13 min read
Almost every tool I build for service engineers ends up needing to remember something between runs: a list of devices, a log of readings, a few settings. A folder of text files works until it does not, and at that point SQLite is the answer. It is a single file, it needs no server, and it is written in portable C with a small, stable API.
Both Delphi and Lazarus have data-access layers that can talk to SQLite for you. This article does not use them. It goes straight to the native library, sqlite3.dll on Windows and libsqlite3 elsewhere, with a Pascal unit of about fifteen function declarations and a thin wrapper around them. The payoff is a unit that compiles unchanged in Delphi and Free Pascal, no dependency on a vendor's component set, and full control over which version of SQLite you ship.
The schema
We store temperature readings, the same data as in the Lazarus viewer from an earlier article.
CREATE TABLE IF NOT EXISTS readings (
id INTEGER PRIMARY KEY,
sensor TEXT NOT NULL,
celsius REAL NOT NULL,
taken_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_readings_sensor ON readings (sensor, taken_at);
SQLite has no date type, so taken_at is ISO 8601 text. That sorts correctly, compares correctly, and survives being opened in any other tool. Decide this before the first row is written; migrating date formats later is miserable.
Getting the library
SQLite is distributed as source and as prebuilt binaries. On Windows, download the precompiled sqlite3.dll for your target, 32 or 64 bit, from the SQLite website, or build it yourself from the single-file amalgamation with any C compiler. The DLL goes next to your executable. On Linux, including the Raspberry Pi, the library is a package install away and usually already present, named libsqlite3.so.0. On macOS it ships with the system.
Pin the version you ship on Windows and record it in your dependency list. A bug report that says "it works on my machine" is usually two different versions of the same DLL.
Declaring the API
SQLite exposes a C API with the cdecl calling convention. Object Pascal can call it directly by declaring each function as external. Here is the subset this article needs, in a unit that compiles in Delphi and in Free Pascal.
unit SQLite3Api;
{$IFDEF FPC}{$mode delphi}{$ENDIF}
interface
const
{$IFDEF MSWINDOWS}
SQLiteLib = 'sqlite3.dll';
{$ELSE}
{$IFDEF DARWIN}
SQLiteLib = 'libsqlite3.dylib';
{$ELSE}
SQLiteLib = 'libsqlite3.so.0';
{$ENDIF}
{$ENDIF}
SQLITE_OK = 0;
SQLITE_ROW = 100;
SQLITE_DONE = 101;
SQLITE_OPEN_READWRITE = $00000002;
SQLITE_OPEN_CREATE = $00000004;
const
SQLITE_TRANSIENT: Pointer = Pointer(-1); // ask SQLite to copy bound text
type
PSQLite3 = Pointer;
PSQLite3Stmt = Pointer;
function sqlite3_open_v2(const FileName: PAnsiChar; var Db: PSQLite3;
Flags: Integer; Vfs: PAnsiChar): Integer; cdecl; external SQLiteLib;
function sqlite3_close(Db: PSQLite3): Integer; cdecl; external SQLiteLib;
function sqlite3_errmsg(Db: PSQLite3): PAnsiChar; cdecl; external SQLiteLib;
function sqlite3_exec(Db: PSQLite3; const Sql: PAnsiChar; Callback, Arg: Pointer;
ErrMsg: PPAnsiChar): Integer; cdecl; external SQLiteLib;
function sqlite3_prepare_v2(Db: PSQLite3; const Sql: PAnsiChar; NumBytes: Integer;
var Stmt: PSQLite3Stmt; Tail: PPAnsiChar): Integer; cdecl; external SQLiteLib;
function sqlite3_bind_parameter_index(Stmt: PSQLite3Stmt;
const Name: PAnsiChar): Integer; cdecl; external SQLiteLib;
function sqlite3_bind_text(Stmt: PSQLite3Stmt; Index: Integer; const Value: PAnsiChar;
NumBytes: Integer; Dtor: Pointer): Integer; cdecl; external SQLiteLib;
function sqlite3_bind_double(Stmt: PSQLite3Stmt; Index: Integer;
Value: Double): Integer; cdecl; external SQLiteLib;
function sqlite3_step(Stmt: PSQLite3Stmt): Integer; cdecl; external SQLiteLib;
function sqlite3_reset(Stmt: PSQLite3Stmt): Integer; cdecl; external SQLiteLib;
function sqlite3_column_text(Stmt: PSQLite3Stmt; Col: Integer): PAnsiChar; cdecl; external SQLiteLib;
function sqlite3_column_double(Stmt: PSQLite3Stmt; Col: Integer): Double; cdecl; external SQLiteLib;
function sqlite3_finalize(Stmt: PSQLite3Stmt): Integer; cdecl; external SQLiteLib;
implementation
end.
Three details carry the weight here. Every string crossing the boundary is a PAnsiChar holding UTF-8, because that is what SQLite speaks. sqlite3_open_v2 and sqlite3_prepare_v2 take a var parameter where C takes a pointer to a pointer. And SQLITE_TRANSIENT tells SQLite to copy bound text immediately, so the Pascal string can go out of scope the moment the bind call returns. Get that last one wrong and you have a bug that appears only when the garbage happens to land on the right address.
Free Pascal ships its own header translation for SQLite, and it is fine to use it on Lazarus. The reason to write these fifteen lines yourself is that the same unit then compiles in Delphi Community Edition without changes.
A thin wrapper
Raw handles and result codes are not what you want in application code. A small wrapper turns them into two classes: a database that executes SQL and prepares statements, and a statement that binds parameters, steps through rows and reads columns.
unit SQLiteDb;
{$IFDEF FPC}{$mode delphi}{$ENDIF}
interface
uses
SysUtils, SQLite3Api;
type
ESQLiteError = class(Exception);
TSQLiteStatement = class
private
FDb: PSQLite3;
FStmt: PSQLite3Stmt;
procedure Check(Code: Integer);
public
constructor Create(Db: PSQLite3; const Sql: string);
destructor Destroy; override;
procedure BindText(const Name, Value: string);
procedure BindDouble(const Name: string; Value: Double);
function Step: Boolean; // True while a row is available
procedure Reset;
function ColumnText(Col: Integer): string;
function ColumnDouble(Col: Integer): Double;
end;
TSQLiteDatabase = class
private
FDb: PSQLite3;
public
constructor Create(const FileName: string);
destructor Destroy; override;
procedure Exec(const Sql: string);
function Prepare(const Sql: string): TSQLiteStatement;
end;
implementation
function ToUtf8(const S: string): UTF8String;
begin
Result := UTF8Encode(S);
end;
constructor TSQLiteDatabase.Create(const FileName: string);
begin
inherited Create;
if sqlite3_open_v2(PAnsiChar(ToUtf8(FileName)), FDb,
SQLITE_OPEN_READWRITE or SQLITE_OPEN_CREATE, nil) <> SQLITE_OK then
raise ESQLiteError.Create(string(sqlite3_errmsg(FDb)));
end;
destructor TSQLiteDatabase.Destroy;
begin
if FDb <> nil then
sqlite3_close(FDb);
inherited;
end;
procedure TSQLiteDatabase.Exec(const Sql: string);
begin
if sqlite3_exec(FDb, PAnsiChar(ToUtf8(Sql)), nil, nil, nil) <> SQLITE_OK then
raise ESQLiteError.Create(string(sqlite3_errmsg(FDb)));
end;
function TSQLiteDatabase.Prepare(const Sql: string): TSQLiteStatement;
begin
Result := TSQLiteStatement.Create(FDb, Sql);
end;
constructor TSQLiteStatement.Create(Db: PSQLite3; const Sql: string);
begin
inherited Create;
FDb := Db;
Check(sqlite3_prepare_v2(FDb, PAnsiChar(ToUtf8(Sql)), -1, FStmt, nil));
end;
destructor TSQLiteStatement.Destroy;
begin
if FStmt <> nil then
sqlite3_finalize(FStmt);
inherited;
end;
procedure TSQLiteStatement.Check(Code: Integer);
begin
if Code <> SQLITE_OK then
raise ESQLiteError.Create(string(sqlite3_errmsg(FDb)));
end;
procedure TSQLiteStatement.BindText(const Name, Value: string);
var
U: UTF8String;
begin
U := ToUtf8(Value);
Check(sqlite3_bind_text(FStmt,
sqlite3_bind_parameter_index(FStmt, PAnsiChar(ToUtf8(Name))),
PAnsiChar(U), Length(U), SQLITE_TRANSIENT));
end;
procedure TSQLiteStatement.BindDouble(const Name: string; Value: Double);
begin
Check(sqlite3_bind_double(FStmt,
sqlite3_bind_parameter_index(FStmt, PAnsiChar(ToUtf8(Name))), Value));
end;
function TSQLiteStatement.Step: Boolean;
var
Code: Integer;
begin
Code := sqlite3_step(FStmt);
Result := Code = SQLITE_ROW;
if (Code <> SQLITE_ROW) and (Code <> SQLITE_DONE) then
Check(Code);
end;
procedure TSQLiteStatement.Reset;
begin
Check(sqlite3_reset(FStmt));
end;
function TSQLiteStatement.ColumnText(Col: Integer): string;
var
P: PAnsiChar;
begin
P := sqlite3_column_text(FStmt, Col);
if P = nil then
Result := ''
else
Result := UTF8ToString(P);
end;
function TSQLiteStatement.ColumnDouble(Col: Integer): Double;
begin
Result := sqlite3_column_double(FStmt, Col);
end;
end.
All the UTF-8 conversion lives in this one unit. Delphi strings are UTF-16 and Lazarus strings are UTF-8 by default, and the wrapper does not care, because UTF8Encode and UTF8ToString do the right thing on both compilers. A NULL column comes back as an empty string rather than a crash, which is the behaviour I want for telemetry.
Using it
With the wrapper in place, the application code reads the way database code should.
var
Db: TSQLiteDatabase;
Insert, Query: TSQLiteStatement;
begin
Db := TSQLiteDatabase.Create(DbPath);
try
Db.Exec('CREATE TABLE IF NOT EXISTS readings (' +
'id INTEGER PRIMARY KEY, sensor TEXT NOT NULL, ' +
'celsius REAL NOT NULL, taken_at TEXT NOT NULL)');
Db.Exec('BEGIN');
Insert := Db.Prepare('INSERT INTO readings (sensor, celsius, taken_at) ' +
'VALUES (:sensor, :celsius, :taken_at)');
try
Insert.BindText(':sensor', 'boiler');
Insert.BindDouble(':celsius', 58.2);
Insert.BindText(':taken_at', FormatDateTime('yyyy-mm-dd"T"hh:nn:ss', Now));
Insert.Step;
finally
Insert.Free;
end;
Db.Exec('COMMIT');
Query := Db.Prepare('SELECT taken_at, celsius FROM readings ' +
'WHERE sensor = :sensor ORDER BY taken_at');
try
Query.BindText(':sensor', 'boiler');
while Query.Step do
WriteLn(Query.ColumnText(0), ' ', Query.ColumnDouble(1):0:1);
finally
Query.Free;
end;
finally
Db.Free;
end;
end;
Two things to notice. The parameter names include the colon, because that is how SQLite names them, and sqlite3_bind_parameter_index resolves the name to a position so the code never counts question marks. And the insert is wrapped in BEGIN and COMMIT. SQLite commits every statement on its own if you do not, and each commit waits for the disk. Ten thousand inserts in one transaction take a fraction of a second; the same inserts without one can take minutes. For bulk work, prepare the statement once, then bind, step and Reset in a loop.
Native binding or component layer?
| Native sqlite3 binding | SQLdb (Lazarus) | Third-party layers (Zeos and others) | |
|---|---|---|---|
| Compilers | Delphi and Free Pascal, one unit | Free Pascal only | Delphi and Free Pascal |
| Dependencies | The SQLite library | The Free Pascal packages and the SQLite library | The SQLite library plus the component package |
| Data-aware controls | No | Yes, through TDataSet | Yes, through TDataSet |
| Control over the SQLite version and features | Full | Whatever the layer exposes | Whatever the layer exposes |
| Code you have to understand | Two small units | A component framework | A component framework |
| Learning curve | The SQLite C API | The dataset model | The dataset model |
| Best for | Service tools, embedded devices, code shared between compilers | Lazarus forms with grids | Projects that need several databases behind one API |
The honest trade-off is everything built on TDataSet. There is no TDBGrid, no data-aware edit boxes and no TDataSource to wire up. If your tool is mostly a grid over rows, a component layer is the pragmatic choice. What you get in return for going native is a dependency you understand completely: the whole data layer is two small units you can read in ten minutes, it compiles in Delphi and Free Pascal alike, it works the same on a Windows service tool and a Raspberry Pi, and when SQLite publishes a fix you drop in the new library and move on.
When tables are the wrong shape: UnQLite
Not every tool wants tables. Device configurations, cached API responses and settings blobs are documents, and forcing them into columns means either a schema that changes every release or a single TEXT column holding JSON, which is a document store with extra steps. For that kind of data there is a NoSQL equivalent of SQLite called UnQLite, and it shares almost everything I like about SQLite:
- Embedded and serverless, so nothing to install or run alongside your program.
- One database file on disk.
- Lightweight, cross-platform, and written in portable C.
- Transactional, with ACID guarantees and explicit begin, commit and rollback.
- Designed for local application storage, not for serving a network.
The difference is what goes in the file. Instead of SQL tables, UnQLite offers a key-value store and a document store that holds JSON, queried through its own small scripting language called Jx9. You put a document in under a key, or you let the document store collect them, and you get them back without a schema.
Talking to it from Pascal
If you have followed the SQLite half of this article, UnQLite holds no surprises: it is the same technique. Neither Delphi nor Lazarus ships components for it either, so integration goes through its C API. The library is a single C source file that you build into a DLL or shared library with any C compiler, and then you declare the handful of functions you need. There are community header translations on GitHub, but the key-value part is small enough to write yourself:
const
UNQLITE_OK = 0;
UNQLITE_OPEN_CREATE = $00000004;
{$IFDEF MSWINDOWS}
UnQLiteLib = 'unqlite.dll';
{$ELSE}
UnQLiteLib = 'libunqlite.so';
{$ENDIF}
type
PUnQLite = Pointer;
function unqlite_open(var pDb: PUnQLite; const zFilename: PAnsiChar;
iMode: Cardinal): Integer; cdecl; external UnQLiteLib;
function unqlite_close(pDb: PUnQLite): Integer; cdecl; external UnQLiteLib;
function unqlite_begin(pDb: PUnQLite): Integer; cdecl; external UnQLiteLib;
function unqlite_commit(pDb: PUnQLite): Integer; cdecl; external UnQLiteLib;
function unqlite_kv_store(pDb: PUnQLite; const pKey: Pointer; nKeyLen: Integer;
const pData: Pointer; nDataLen: Int64): Integer; cdecl; external UnQLiteLib;
function unqlite_kv_fetch(pDb: PUnQLite; const pKey: Pointer; nKeyLen: Integer;
pBuf: Pointer; var pBufLen: Int64): Integer; cdecl; external UnQLiteLib;
With those declarations, storing a reading as a JSON document under a key and reading it back is a few lines:
procedure StoreDocument(Db: PUnQLite; const Key, Json: AnsiString);
begin
if unqlite_kv_store(Db, PAnsiChar(Key), Length(Key),
PAnsiChar(Json), Length(Json)) <> UNQLITE_OK then
raise Exception.Create('UnQLite store failed for ' + string(Key));
end;
function FetchDocument(Db: PUnQLite; const Key: AnsiString): AnsiString;
var
Size: Int64;
begin
Size := 0; // first call with a nil buffer asks for the size
if unqlite_kv_fetch(Db, PAnsiChar(Key), Length(Key), nil, Size) <> UNQLITE_OK then
Exit('');
SetLength(Result, Size);
unqlite_kv_fetch(Db, PAnsiChar(Key), Length(Key), PAnsiChar(Result), Size);
end;
var
Db: PUnQLite;
begin
if unqlite_open(Db, PAnsiChar(AnsiString(DbPath)), UNQLITE_OPEN_CREATE) <> UNQLITE_OK then
raise Exception.Create('Could not open ' + DbPath);
try
unqlite_begin(Db);
StoreDocument(Db, 'reading:boiler:2024-06-25T07:15:00',
'{"sensor":"boiler","celsius":58.2,"takenAt":"2024-06-25T07:15:00"}');
unqlite_commit(Db);
WriteLn(FetchDocument(Db, 'reading:boiler:2024-06-25T07:15:00'));
finally
unqlite_close(Db);
end;
end;
Keys are plain bytes, so a prefix like reading:boiler: followed by an ISO timestamp gives you ordered, prefix-scannable storage without an index definition. The JSON itself is whatever your JSON library produces, which means the parsing code from the REST article works unchanged on the way back out.
SQLite or UnQLite?
| SQLite | UnQLite | |
|---|---|---|
| Data model | Tables, rows, columns and indexes | Key-value pairs and JSON documents |
| Query language | SQL | Key lookups, prefix scans, and Jx9 scripts for documents |
| Schema | Declared up front, migrated over time | None |
| Transactions | ACID, with WAL mode for concurrent readers | ACID, explicit begin and commit |
| Joins and aggregation | Yes | No, you write it in Jx9 or in Pascal |
| JSON | json_extract and friends on text columns | Native |
| Tooling | Every database browser there is | Almost none; your own code |
| Maturity | Everywhere since 2000 | Niche, with a far smaller community |
| Library | sqlite3.dll or libsqlite3, often already installed | Build it yourself from one C file |
| Pascal access | Native binding, SQLdb, third-party layers | Native binding only |
When to use what
| Your data | Reach for |
|---|---|
| Rows you query, filter, join and aggregate | SQLite |
| Readings, logs, anything you will chart or report on | SQLite |
| Settings, device configuration, cached API responses | UnQLite, or SQLite with a JSON column |
| Documents whose shape changes every release | UnQLite |
| A grid the user edits directly | SQLite behind a component layer |
| One codebase for Windows tools and a Raspberry Pi | SQLite through the native binding |
| Several programs writing at once over a network | Neither; a server database such as PostgreSQL |
| A customer who will open the file in another tool | SQLite |
When in doubt, SQLite. Before switching at all, check whether its JSON functions cover your case: a TEXT column with json_extract in the query is often enough, and it keeps everything in one library.
Habits that pay off in the field
- Always use parameters. Even in a desktop tool with no network, a sensor name with an apostrophe in it will break a concatenated SQL string. Parameters fix that and remove SQL injection as a class of bug.
- Put the database file in the user's data folder.
TPath.GetHomePathin Delphi orGetAppConfigDirin Free Pascal, never next to the executable, which is often read-only once installed. - Turn on WAL mode with
PRAGMA journal_mode=WALif the tool writes while another process reads. It makes SQLite far more forgiving. - Back up by copying the file while no transaction is open, or use the SQLite online backup API, which is three more functions to declare.
- Version the schema. Keep a
schema_versiontable and run migrations on startup. Future you will be grateful.
Questions for you
- Do you bind to the SQLite C API yourself, or do you use a component layer? What tipped you one way or the other?
- Have you been bitten by the UTF-8 boundary, by
SQLITE_TRANSIENT, or by a DLL version mismatch? I would like to collect the war stories. - Have you used UnQLite or another embedded NoSQL store from Pascal? Did losing the data-aware controls matter in practice?
Write to me at bjorn.nostdahl@nostdahl.com. Next up: leaving the local machine and calling REST APIs from both toolchains.