SQLite embedded database. Full SQL, prepared statements, transactions, WAL mode, backup, and in-memory databases — zero configuration.

Overview

SQLite is the most widely deployed database engine in the world — self-contained, serverless, zero-configuration, and ACID-compliant. A full database is a single file. Supports joins, indexes, triggers, views, CTEs, window functions, JSON functions, and full-text search.

Typical workflow: Open → Execute DDL → Prepare statement → Bind → Step → Fetch → Close

Quick Start

uses sqlite;

var db := Open('/tmp/myapp.db');

{ Create table }
Execute(db, 'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');

{ Insert with prepared statement }
var stmt := Prepare(db, 'INSERT INTO users (name, age) VALUES (?, ?)');
BindText(stmt, 1, 'Alice');
BindInt(stmt, 2, 30);
Step(stmt);
FinalizeStmt(stmt);

{ Query scalar }
var count := QueryInt(db, 'SELECT COUNT(*) FROM users');

{ Iterate rows }
var s2 := Prepare(db, 'SELECT name, age FROM users ORDER BY name');
while Step(s2) do
begin
  var name := ColumnText(s2, 0);
  var age  := ColumnInt(s2, 1);
  Writeln(name + ' — ' + IntToStr(age));
end;
FinalizeStmt(s2);

Close(db);

Transactions

BeginTransaction(db);
try
begin
  Execute(db, 'INSERT INTO users (name, age) VALUES (''Bob'', 25)');
  Execute(db, 'INSERT INTO users (name, age) VALUES (''Carol'', 28)');
  Commit(db);
end
except
  Rollback(db);
end;

WAL Mode & Configuration

var db := Open('/tmp/app.db');
EnableWAL(db);          { better concurrency }
SetBusyTimeout(db, 5000);  { retry for 5 seconds if locked }
EnableForeignKeys(db); { enforce FK constraints }

Schema Helpers

var tables := GetTables(db);  { array of string }
var cols   := GetColumns(db, 'users');
if TableExists(db, 'users') then
  Writeln('users table found');

Backup & Serialization

{ Online backup (no lock required) }
Backup(db, '/tmp/backup.db');

{ In-memory export / import }
var blob    := Serialize(db);
var memdb   := Deserialize(blob);
Close(memdb);

API Reference

Open / Close

Function	Description
Open(FilePath)	Open or create a database file. Returns TDB.
OpenReadOnly(FilePath)	Open in read-only mode.
OpenMemory	Open an in-memory database (lost on close).
Close(DB)	Close database and free resources.

Execute & Query

Function	Description
Execute(DB, SQL)	Run SQL with no result set (DDL, DML).
Query(DB, SQL)	Run SQL and return all rows as TDBResult.
QueryScalar(DB, SQL)	Return first row, first column as string.
QueryInt(DB, SQL)	Return first row, first column as Int64.
QueryFloat(DB, SQL)	Return first row, first column as Double.

Prepared Statements

Function	Description
Prepare(DB, SQL)	Compile SQL into a TStmt. Use ? or ?1 ?2 as placeholders.
BindInt/Float/Text/Blob/Null(Stmt, Index, Value)	Bind parameter by 1-based index.
BindNamedInt/Text/Float(Stmt, Name, Value)	Bind by :name or @name.
Step(Stmt)	Execute one step. Returns True if a row is available.
ColumnInt/Float/Text/Blob(Stmt, Col)	Read column value (0-based).
ColumnIsNull/Name/Count(Stmt, Col)	Column metadata.
Reset(Stmt)	Reset for re-use with new bindings.
FinalizeStmt(Stmt)	Free the prepared statement.

Transactions

Procedure	Description
BeginTransaction(DB)	Begin deferred transaction.
BeginImmediate(DB)	Begin immediate transaction (acquires write lock early).
Commit(DB)	Commit transaction.
Rollback(DB)	Roll back transaction.

Row Metadata

Function	Description
LastInsertRowID(DB)	Row ID of the last INSERT.
Changes(DB)	Rows affected by last INSERT/UPDATE/DELETE.
TotalChanges(DB)	Total rows changed since connection opened.

Configuration & Backup

Function	Description
EnableWAL(DB)	Enable Write-Ahead Log for better read concurrency.
SetBusyTimeout(DB, Ms)	Retry on locked database for up to Ms milliseconds.
EnableForeignKeys(DB)	Enforce FOREIGN KEY constraints.
Backup(DB, DestPath)	Hot backup to a file without locking.
Serialize(DB)	Export database bytes (for in-memory export).
Deserialize(Data)	Import bytes into a new in-memory database.
GetDBInfo(DB)	Returns TDBInfo: page size, count, WAL mode, schema version.
SQLiteVersion	Returns the SQLite library version string.