Queries you can read.

A scoped connection

use Icom\Database\Database;

$app->get('/notes', function (Database $db) {
    return Response::json(
        $db->table('notes')
            ->where('title', 'First note')
            ->orderBy('created_at', 'DESC')
            ->limit(20)
            ->get(['id', 'title'])
    );
});

Writes and transactions

$db->transaction(function (Database $db) {
    $db->table('notes')->insert([
        'id' => bin2hex(random_bytes(16)),
        'title' => 'First note',
        'created_at' => gmdate('c'),
    ]);
});

$db->table('notes')->where('id', $id)->update(['title' => 'Revised']);
$db->table('notes')->where('id', $id)->delete();

Safety and limits

Builder values are bound parameters. Table/column names, operators and sort directions are allowlisted; use trusted schema identifiers. update/delete require a WHERE condition and reject orderBy/limit on the same builder. Select explicit identifiers, repeat tenant/authorization filters and use an appropriate transaction or concurrency policy for bounded mutations. Raw query() is for developer-written SQL and still requires parameters for user data. Nested transactions are rejected. The builder has no relationship loader, joins or bulk upsert API. Optional Doctrine ORM provides entity mapping and relationships; see the ORM chapter. SQLite and MariaDB 11.8.6 through PDO MySQL are exercised by the test suite. Other engines and your actual production topology still require integration tests.

Migrations

php bin/icom migrate

// migrations/003_example.php
return static function (Database $db): void {
    $db->query('CREATE TABLE IF NOT EXISTS tags (name VARCHAR(80) PRIMARY KEY)');
};

Migration operations

Migrations run in lexical filename order, are recorded in icom_migrations and use a local file lock on SQLite or a database-scoped advisory lock on MySQL/MariaDB. SQLite DDL runs in a transaction; MySQL DDL implicitly commits, so use idempotent migrations and a verified backup. Run migrations through one deployment coordinator in a cluster. There is no automatic down/rollback migration command. migrate:status reads the ledger without executing files. Readiness checks SQL, migration status, an isolated cache roundtrip and required storage access.

Private SQLite backup and verification

Version 0.10 adds backup:create --output=/absolute/new.sqlite and backup:verify /absolute/existing.sqlite. Create the canonical private parent directory first with mode 0700 and choose a new filename outside every public path. Creation uses a consistent VACUUM INTO snapshot and verifies integrity/foreign keys before atomic no-overwrite publication; the file is mode 0600. Verification runs without application bootstrap and returns hashes and fixed counts, not data. Local POSIX filesystems only; memory/attached/MySQL sources, outer transactions, symlinks and live WAL verification artifacts are rejected. Preserve APP_KEY/configuration/uploads separately and exercise restore in a fresh isolated application. Point-in-time restore can revive revoked sessions, consumed recovery codes and queued effects; reconcile them before resuming real traffic. There is no automatic live restore.