SQLite in Object Pascal: FireDAC and SQLdb Side by Side

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 both Delphi and Lazarus talk to it out of the box.

This article builds the same small database twice: once with FireDAC in Delphi, once with SQLdb in Lazarus. The goal is to show how similar the two are, and where they differ.

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.

FireDAC in Delphi

FireDAC is Delphi's universal data access layer. You can drop the components on a data module and configure them in the Object Inspector, but I prefer to see the setup in code.

uses
  FireDAC.Comp.Client, FireDAC.Stan.Def, FireDAC.Stan.Async,
  FireDAC.DApt, FireDAC.Phys.SQLite, FireDAC.Phys.SQLiteDef;

function OpenDatabase(const Path: string): TFDConnection;
begin
  Result := TFDConnection.Create(nil);
  Result.DriverName := 'SQLite';
  Result.Params.Database := Path;
  Result.Params.Add('LockingMode=Normal');
  Result.Params.Add('Synchronous=Normal');
  Result.Connected := True;
  Result.ExecSQL(
    'CREATE TABLE IF NOT EXISTS readings (' +
    'id INTEGER PRIMARY KEY, sensor TEXT NOT NULL, ' +
    'celsius REAL NOT NULL, taken_at TEXT NOT NULL)');
end;

procedure AddReading(Conn: TFDConnection; const Sensor: string; Celsius: Double);
var
  Query: TFDQuery;
begin
  Query := TFDQuery.Create(nil);
  try
    Query.Connection := Conn;
    Query.SQL.Text :=
      'INSERT INTO readings (sensor, celsius, taken_at) ' +
      'VALUES (:sensor, :celsius, :taken_at)';
    Query.ParamByName('sensor').AsString := Sensor;
    Query.ParamByName('celsius').AsFloat := Celsius;
    Query.ParamByName('taken_at').AsString :=
      FormatDateTime('yyyy-mm-dd"T"hh:nn:ss', Now);
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;

Two things to notice. The FireDAC.DApt unit must be in a uses clause somewhere in the project or TFDQuery will complain at runtime that no adapter is registered; the IDE adds it for you when you drop the components, but not when you write the code by hand. And on Windows, FireDAC links the SQLite engine statically by default, so there is no DLL to ship. One executable, one database file.

SQLdb in Lazarus

SQLdb is the data access layer that ships with Free Pascal. It has three moving parts instead of two: a connection, a transaction and a query. The transaction is explicit, which surprises people coming from FireDAC where it is implicit unless you ask for it.

uses
  SQLDB, SQLite3Conn;

type
  TReadingsDb = class
  private
    FConn: TSQLite3Connection;
    FTrans: TSQLTransaction;
  public
    constructor Create(const Path: string);
    destructor Destroy; override;
    procedure AddReading(const Sensor: string; Celsius: Double);
  end;

constructor TReadingsDb.Create(const Path: string);
begin
  inherited Create;
  FConn := TSQLite3Connection.Create(nil);
  FTrans := TSQLTransaction.Create(nil);
  FConn.DatabaseName := Path;
  FConn.Transaction := FTrans;
  FTrans.DataBase := FConn;
  FConn.Open;
  FConn.ExecuteDirect(
    'CREATE TABLE IF NOT EXISTS readings (' +
    'id INTEGER PRIMARY KEY, sensor TEXT NOT NULL, ' +
    'celsius REAL NOT NULL, taken_at TEXT NOT NULL)');
  FTrans.Commit;
end;

destructor TReadingsDb.Destroy;
begin
  FTrans.Free;
  FConn.Free;
  inherited;
end;

procedure TReadingsDb.AddReading(const Sensor: string; Celsius: Double);
var
  Query: TSQLQuery;
begin
  Query := TSQLQuery.Create(nil);
  try
    Query.DataBase := FConn;
    Query.Transaction := FTrans;
    Query.SQL.Text :=
      'INSERT INTO readings (sensor, celsius, taken_at) ' +
      'VALUES (:sensor, :celsius, :taken_at)';
    Query.Params.ParamByName('sensor').AsString := Sensor;
    Query.Params.ParamByName('celsius').AsFloat := Celsius;
    Query.Params.ParamByName('taken_at').AsString :=
      FormatDateTime('yyyy-mm-dd"T"hh:nn:ss', Now);
    Query.ExecSQL;
    FTrans.Commit;
  finally
    Query.Free;
  end;
end;

Forget the Commit and your insert silently disappears when the program closes. I did that on my first evening with SQLdb and spent a while wondering why the file never grew.

Unlike FireDAC, SQLdb loads the SQLite engine dynamically. On Windows you ship sqlite3.dll next to the executable; on Linux and the Raspberry Pi you install libsqlite3 with the package manager, and it is usually there already.

Reading rows: the part that is identical

Both libraries build on TDataSet, the same base class the VCL and LCL data-aware controls have used for decades. That means the reading side of the code is the same in both, give or take Params. in front of ParamByName.

Query.SQL.Text :=
  'SELECT sensor, celsius, taken_at FROM readings ' +
  'WHERE sensor = :sensor ORDER BY taken_at';
Query.ParamByName('sensor').AsString := 'boiler';
Query.Open;
while not Query.EOF do
begin
  WriteLn(Query.FieldByName('taken_at').AsString, '  ',
          Query.FieldByName('celsius').AsFloat:0:1);
  Query.Next;
end;
Query.Close;

Connect a TDataSource to the query and a TDBGrid to the data source and you have a live grid with no further code. The dataset model is old, but it is one of the reasons Pascal desktop tools get built so quickly.

Side by side

If your application only ever talks to SQLite, SQLdb is entirely enough. If it talks to three different database servers and needs connection pooling, FireDAC earns its price.

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.GetHomePath in Delphi or GetAppConfigDir in Free Pascal, never next to the executable, which is often read-only once installed.
• Turn on WAL mode with PRAGMA journal_mode=WAL if 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. Both libraries expose it.
• Version the schema. Keep a schema_version table and run migrations on startup. Future you will be grateful.

Questions for you

• Which do you prefer for local data in a desktop tool: SQLite, a folder of JSON files, or something else?
• FireDAC users: is there a feature you could not live without that SQLdb lacks? SQLdb users: have you ever hit the wall?
• How do you handle schema migrations in a Pascal application that has been installed on hundreds of machines?

Write to me at bjorn.nostdahl@nostdahl.com. Next up: leaving the local machine and calling REST APIs from both toolchains.