Databases

Both Lazarus and Delphi use the TDataSet abstraction (DB / Data.DB unit):

39.1 The dataset model

Both Lazarus and Delphi use the TDataSet abstraction (DB / Data.DB unit):

Connection (TSQLConnector / TFDConnection) ── Transaction
        │
   Query / Table (TSQLQuery / TFDQuery)   ← TDataSet: Open, Close, First, Next, Eof, FieldByName, Edit, Post, Delete
        │
   TDataSource
        │
   Data-aware controls (TDBGrid, TDBEdit, TDBNavigator) or LiveBindings

39.2 Database libraries

FPC/Lazarus (built in) SQLite, PostgreSQL, MySQL/MariaDB, Firebird/InterBase, MS SQL Server/Sybase, Oracle, ODBC

Delphi (Professional and up; Community Edition includes local/embedded drivers)

SQLite, InterBase, Firebird, PostgreSQL, MySQL, MS SQL Server, Oracle, ODBC, and more both (open source)

SQLite, PostgreSQL, MySQL, Firebird, Oracle, MS SQL, ...

UniDAC (Devart) both (commercial) most major databases ORM/SOA framework with direct SQL access and SQLite3 core

TBufDataset, TMemDataset / TFDMemTable, TClientDataSet

39.3 SQLdb with SQLite (Free Pascal)

In a Lazarus GUI, drop TSQLite3Connection, TSQLTransaction, TSQLQuery, TDataSource, and a TDBGrid on a form and connect them in the Object Inspector — no code needed to browse data. TSQLConnector selects the database type at run time via its ConnectorType property.

program SqliteDemo;
{$mode objfpc}{$H+}
uses
  SysUtils, sqldb, sqlite3conn;    // requires the SQLite library (sqlite3.dll / libsqlite3.so)

var
  Conn: TSQLite3Connection;
  Tran: TSQLTransaction;
  Q: TSQLQuery;
begin
  Conn := TSQLite3Connection.Create(nil);
  Tran := TSQLTransaction.Create(nil);
  Q := TSQLQuery.Create(nil);
  try
    Conn.DatabaseName := 'shop.db';
    Conn.Transaction := Tran;
    Tran.DataBase := Conn;
    Q.DataBase := Conn;
    Q.Transaction := Tran;
    Conn.Open;

    Conn.ExecuteDirect('CREATE TABLE IF NOT EXISTS products (' +
      'id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL)');
    Tran.Commit;

    Q.SQL.Text := 'INSERT INTO products (name, price) VALUES (:name, :price)';
    Q.ParamByName('name').AsString := 'Keyboard';     // parameters prevent SQL injection
    Q.ParamByName('price').AsFloat := 49.90;
    Q.ExecSQL;
    Tran.Commit;

    Q.SQL.Text := 'SELECT id, name, price FROM products ORDER BY name';
    Q.Open;
    while not Q.EOF do
    begin
      Writeln(Q.FieldByName('id').AsInteger:4, '  ',
              Q.FieldByName('name').AsString, '  ',
              Q.FieldByName('price').AsFloat:0:2);
      Q.Next;
    end;
    Q.Clos
…

39.4 FireDAC with SQLite (Delphi)

Transactions: Conn.StartTransaction; try ... Conn.Commit; except Conn.Rollback; raise; end;.

program FireDacDemo;
{$APPTYPE CONSOLE}
uses
  System.SysUtils, Data.DB,
  FireDAC.Stan.Intf, FireDAC.Stan.Def, FireDAC.Stan.Async, FireDAC.DApt,
  FireDAC.Phys, FireDAC.Phys.SQLite, FireDAC.ConsoleUI.Wait,
  FireDAC.Comp.Client;

var
  Conn: TFDConnection;
  Q: TFDQuery;
begin
  Conn := TFDConnection.Create(nil);
  Q := TFDQuery.Create(nil);
  try
    Conn.DriverName := 'SQLite';
    Conn.Params.Database := 'shop.db';
    Conn.Open;
    Conn.ExecSQL('CREATE TABLE IF NOT EXISTS products (' +
      'id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL)');
    Conn.ExecSQL('INSERT INTO products (name, price) VALUES (:n, :p)', ['Mouse', 19.5]);

    Q.Connection := Conn;
    Q.Open('SELECT id, name, price FROM products ORDER BY name');
    while not Q.Eof do
    begin
      Writeln(Format('%4d  %-20s %8.2f', [Q.FieldByName('id').AsInteger,
        Q.FieldByName('name').AsString, Q.FieldByName('price').AsFloat]));
      Q.Next;
    end;
  finally
    Q.Free;
    Conn.Free;
  end;
end.

39.5 Database best practices

Always use parameters — never concatenate user input into SQL.

Keep transactions short; commit or roll back explicitly.

Separate data access into a data module (TDataModule) or repository classes.

Open only the columns and rows you need; use LIMIT/paging for large tables.

Store connection settings outside the executable (INI/JSON, environment variables).