Documentation

Introduction

NDT DBF provides a query builder and raw SQL execution in one PHP file. This reference covers v0.3.1; examples use $db from the connection section.

Query builders are mutable: start with table() for a fresh query, or clone an existing builder before changing it. first() and pluck() use clones internally. withScope(), policy() and use() return a cloned DBF instance; retain their return value.

MySQL/MariaDB, PostgreSQL and SQLite support upsert and JSON operations. SQL Server and Oracle support connections and basic pagination; unsupported advanced operations throw. DBF does not include a model or migration layer.

Requirements

• PHP 8.1 or newer.
• PDO and a database driver: pdo_mysql, pdo_pgsql, pdo_sqlite, pdo_sqlsrv or pdo_oci.
• Database versions must support the SQL features you use. SQLite JSON operations require JSON functions.

The release CI covers PHP 8.1–8.5, with real MySQL 8.4 and PostgreSQL 16 integration tests. See the CI workflow and run results.

Installation

Single file

Download DBF.php v0.3.1 into your project.

<?php
require __DIR__ . '/DBF.php';
$db = new ndtan\DBF('sqlite::memory:');

Composer

composer require ndtan/dbf:0.3.1

<?php
require __DIR__ . '/vendor/autoload.php';
$db = new ndtan\DBF('mysql://user:password@127.0.0.1/app?charset=utf8mb4');

Connections

URI or PDO DSN

$db = new ndtan\DBF('sqlite::memory:');
$db = new ndtan\DBF('mysql://user:password@127.0.0.1:3306/app?charset=utf8mb4');
$db = new ndtan\DBF('pgsql://user:password@127.0.0.1:5432/app');
$db = new ndtan\DBF([
    'dsn' => 'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4',
    'username' => 'user',
    'password' => 'password',
]);

Percent-encode credentials in a URI. Use a DSN configuration array when credentials are separate, or when the driver needs its own DSN options.

Direct PDO DSNs support mysql:, pgsql:, sqlsrv: and oci:, as well as SQLite. For example, new ndtan\DBF('pgsql:host=localhost;dbname=app'). Supply separate credentials with the dsn array form.

Existing PDO

$pdo = new PDO('sqlite::memory:');
$db = new ndtan\DBF(['pdo' => $pdo]);

Read and write connections

$db = new ndtan\DBF([
    'write' => 'mysql://user:password@primary/app',
    'read' => 'mysql://user:password@replica/app',
    'routing' => 'auto',
]);

auto routes reads to the reader and writes to the writer. Transactions pin queries to the writer. In manual mode use $db->using('read') or $db->using('write'); writes still use the writer. using(null) resets to the writer. Read replicas can lag behind writes.

With no argument, DBF reads the NDTAN_DBF_URL environment variable. Keep credentials outside source control.

WHERE conditions

where(string $column, string $operator, mixed $value) takes three arguments. It does not accept an array or a grouping closure. Values are bound; column names and operators are validated.

AND and OR

$rows = $db->table('users')
    ->where('status', '=', 'active')
    ->where('email', 'LIKE', '%@ndtan.net')
    ->get();

$rows = $db->table('users')
    ->where('status', '=', 'active')
    ->orWhere('status', '=', 'vip')
    ->get();

SQL operator precedence applies: AND binds more tightly than OR. Use bound raw SQL for explicit nested predicate groups.

IN, BETWEEN and NULL

$users = $db->table('users')->whereIn('id', [1, 2, 3])->get();
$orders = $db->table('orders')->whereBetween('total', [100, 500])->get();
$missing = $db->table('users')->whereNull('deleted_at')->get();
$present = $db->table('users')->whereNull('email', not: true)->get();

whereIn() and whereBetween() accept not: true and or: true; whereNull() accepts the same flags. BETWEEN requires two values. An empty IN list matches no rows; an empty NOT IN list adds no restriction. Lists exceeding features.max_in_params throw LengthException.

Operators include =, !=, <>, <, <=, >, >=, LIKE and NOT LIKE. ILIKE and NOT ILIKE require PostgreSQL. Equality with null becomes IS NULL; inequality becomes IS NOT NULL.

Scope and soft-delete conditions are ANDed with the entire group of user predicates, so an OR condition cannot bypass those guards. Scope is a column-to-value map passed to withScope(), not an array syntax for where().

select(), groupBy() and having()

select(array $columns) accepts identifiers, qualified identifiers, aliases and supported aggregates: COUNT, SUM, AVG, MIN and MAX. Arbitrary SQL expressions belong in raw SQL.

$rows = $db->table('users')->select(['id', 'email'])->get();

$groups = $db->table('orders')
    ->select(['user_id', 'COUNT(*) AS cnt'])
    ->groupBy(['user_id'])
    ->having('COUNT(*)', '>', 1)
    ->get();

groupBy() takes an array. Use the aggregate expression in having() for portable SQL; alias support varies by database.

Insert rows

insert(array $data): int returns the last insert ID as an integer. Use a schema with an auto-generated integer key; driver behavior for generated IDs varies.

$id = $db->table('users')->insert([
    'email' => 'a@ndtan.net',
    'status' => 'active',
]);

insertMany()

$ids = $db->table('users')->insertMany([
    ['email' => 'p1@ndtan.net', 'status' => 'active'],
    ['email' => 'p2@ndtan.net', 'status' => 'vip'],
]);

insertMany(array $rows): array inserts rows individually inside one transaction and returns their actual insert IDs in input order. A failure rolls back the batch. This favors correct IDs and atomicity over bulk-insert performance. Every row must have the same columns in the same order. Transaction support is required: MySQL/MariaDB, PostgreSQL or SQLite.

insertGet()

$row = $db->table('users')->insertGet(
    ['email' => 'b@ndtan.net', 'status' => 'vip'],
    ['id', 'email']
);

PostgreSQL and SQLite use RETURNING. The fallback on other drivers selects by an integer column named id; use a schema that matches that assumption. Inserts do not inherit scope values automatically.

Delete and restore

delete(): int returns affected rows. It performs a soft delete only when the feature is enabled and the configured column exists. Otherwise it physically deletes rows.

$affected = $db->table('users')->where('id', '=', $id)->delete();
$restored = $db->table('users')->where('id', '=', $id)->restore();
$removed = $db->table('users')->where('id', '=', $id)->forceDelete();

restore() requires soft-delete configuration and its column. forceDelete() physically deletes matching rows. Supply a WHERE condition: DBF does not automatically forbid deleting every row.

upsert()

upsert(array $data, array $conflict, array $updateColumns): int inserts a row or updates a conflicting row. The conflict columns need a real unique constraint.

$affected = $db->table('users')->upsert(
    ['email' => 'a@ndtan.net', 'status' => 'vip'],
    conflict: ['email'],
    updateColumns: ['status']
);

• MySQL/MariaDB use ON DUPLICATE KEY UPDATE. Any unique key can trigger the update, not only the named conflict columns.
• PostgreSQL and SQLite use ON CONFLICT.
• SQL Server and Oracle are unsupported and throw.

Update columns must exist in the insert data. An empty update list preserves the existing row. Affected counts follow the database driver and are not a portable inserted-versus-updated indicator. Scopes do not constrain conflict handling.

Soft delete

Enable features.soft_delete and create the configured column in your schema. The default column is deleted_at. Without that column, delete() performs a physical delete.

$db = new ndtan\DBF([
    'type' => 'sqlite',
    'database' => 'app.sqlite',
    'features' => [
        'soft_delete' => [
            'enabled' => true,
            'column' => 'deleted_at',
            'mode' => 'timestamp',
        ],
    ],
]);

$active = $db->table('users')->get();
$all = $db->table('users')->withTrashed()->get();
$deleted = $db->table('users')->onlyTrashed()->get();

Timestamp mode uses NULL for active rows and a timestamp for deleted rows. Flag mode uses 0 for active rows and deleted_value (default 1) for deleted rows. Use restore() to restore rows and forceDelete() to physically remove them.

Automatic filtering applies to builder reads. Raw SQL must include its own soft-delete conditions. With joins, do not assume related tables receive their own soft-delete filters.

Aggregates and pluck

$sum = $db->table('orders')->sum('total');
$avg = $db->table('orders')->avg('total');
$min = $db->table('orders')->min('total');
$max = $db->table('orders')->max('total');
$count = $db->table('users')->count();

$emails = $db->table('users')->pluck('email');
$map = $db->table('users')->pluck('email', 'id');

count() returns an integer. Other aggregates return driver values, which may be numeric strings or NULL for an empty set. pluck() returns a list, or a map when a key column is provided; duplicate keys overwrite earlier values. It uses a clone so the original selection stays intact.

count() honors joins and grouping: a grouped query counts its result groups. Scalar helpers such as sum() and avg() throw when grouping produces multiple result rows. Select an aggregate alias and fetch the groups instead.

$totals = $db->table('orders')
    ->select(['user_id', 'SUM(total) AS total_sum'])
    ->groupBy(['user_id'])
    ->get();

Keyset pagination

getKeyset(?string $cursor, string $key): array returns ['data' => $rows, 'next' => $cursor].

$page1 = $db->table('posts')
    ->select(['id', 'title'])
    ->orderBy('id', 'desc')
    ->limit(50)
    ->getKeyset(null, 'id');

if ($page1['next'] !== null) {
    $page2 = $db->table('posts')
        ->select(['id', 'title'])
        ->orderBy('id', 'desc')
        ->limit(50)
        ->getKeyset($page1['next'], 'id');
}

Use one unqualified, non-null unique key, select that key, order only by that key and set a positive limit. ASC and DESC are supported; multiple sort keys, offsets and grouped queries are not supported. Keep the same filters and direction across pages. Invalid cursors throw.

next === null ends pagination. DBF fetches one extra row to determine whether another page exists. Cursors are encoded positions, not authorization tokens or signed values; validate request input and enforce access control on every request.

Streaming and chunks

$stream = $db->table('users')->orderBy('id')->stream();
foreach ($stream as $user) {
    processUser($user);
}
unset($stream);

$db->table('users')->orderBy('id')->chunk(500, function (array $rows) {
    foreach ($rows as $row) {
        processUser($row);
    }
});

$db->table('users')->chunkById(500, function (array $rows) {
    foreach ($rows as $row) {
        processUser($row);
    }
});

stream() yields associative rows lazily. PDO buffering depends on the driver. The cursor closes when the generator finishes or is destroyed; release a retained generator after stopping early.

chunk() uses offsets and requires a positive size and deterministic order. Changes to the result set during iteration may skip or repeat rows. chunkById(int $size, callable $callback, string $key = 'id') uses an ascending unique key and replaces existing order and offset. It supports deleting processed rows; do not change the pagination key while iterating. Return false from a chunk callback to stop.

Transactions

tx(callable $callback, int $attempts = 3): mixed commits on success, rolls back on failure and returns the callback result. Queries inside the transaction use the writer.

$orderId = $db->tx(function (ndtan\DBF $tx) {
    $id = $tx->table('orders')->insert(['user_id' => 10, 'total' => 200]);
    $tx->table('order_items')->insert(['order_id' => $id, 'sku' => 'A', 'qty' => 1]);
    return $id;
}, attempts: 3);

attempts must be positive. Only recognized retryable database errors trigger a retry of the outer transaction. Other errors are rethrown immediately; exhausted retries rethrow the last exception. Do not put email, HTTP calls or other non-transactional side effects inside a callback that may run again.

Nested transactions

$db->tx(function (ndtan\DBF $tx) {
    $tx->table('users')->where('id', '=', 10)->update(['status' => 'active']);
    $tx->tx(function (ndtan\DBF $nested) {
        $nested->table('audit')->insert(['message' => 'User activated']);
    });
});

Nested calls use savepoints on MySQL, PostgreSQL and SQLite. tx() is unsupported on SQL Server and Oracle and throws. Readonly mode and test mode also reject transactions. A nested call does not retry independently. DBF must own the transaction; do not mix tx() with transaction control on an injected PDO connection. Statements such as MySQL DDL may commit implicitly.

Raw SQL

Bind values and keep the SQL itself trusted. Raw SQL does not automatically add builder scopes or soft-delete filters.

$rows = $db->selectRaw(
    'SELECT id, email FROM users WHERE status = ? AND (email = ? OR email = ?)',
    ['active', 'a@ndtan.net', 'b@ndtan.net']
);

$affected = $db->execute(
    'UPDATE users SET status = :status WHERE id = :id',
    ['status' => 'vip', 'id' => 10]
);

selectRaw(): array fetches rows from a single SELECT starting with SELECT, without semicolons. Use raw() for CTEs and other statements. execute(): int executes a statement on the writer and returns its affected row count; DDL counts are driver-dependent. Statements returning rows throw after execution; use raw() for INSERT/UPDATE RETURNING.

raw(): array|int returns associative rows when the statement has result columns (columnCount() > 0), otherwise an affected row count. It always uses the writer and is blocked in readonly mode, including SELECT, WITH and PRAGMA. Use selectRaw() for explicit SELECT fetching with read routing. SQL text cannot guarantee read-only behavior: use a database read-only account. Never pass write statements to selectRaw().

JSON queries and updates

MySQL/MariaDB, PostgreSQL and SQLite support JSON helpers. SQL Server and Oracle throw for these operations. Use valid JSON in the column; PostgreSQL updates operate on JSONB.

whereJson()

$users = $db->table('users')
    ->whereJson('profile->preferences->theme', '=', 'dark')
    ->get();

Read paths use column->key->nested_key. Keys contain letters, digits or underscores; arbitrary JSONPath expressions are not supported. MySQL and PostgreSQL extract text, while SQLite returns scalar values according to JSON type; numeric and boolean comparisons are not identical across drivers.

jsonSet()

$db->table('users')
    ->where('id', '=', 10)
    ->jsonSet('profile', [
        'preferences.theme' => 'dark',
        'preferences.notifications' => true,
        'tags' => ['php', 'sql'],
    ]);

Update paths use dot notation relative to the column. Values are JSON encoded, preserving strings, numbers, booleans, arrays and null. Multiple paths are applied in one UPDATE. PostgreSQL requires intermediate objects to exist for nested paths; this example assumes preferences is present.

jsonSet() executes immediately and returns the query builder, not an affected row count. Use a WHERE condition to target the intended rows.

Policies and scopes

policy() returns a cloned DBF instance. Keep that instance to apply the guard. Context uses type, not action.

$guarded = $db->policy(function (array $ctx) {
    if (($ctx['type'] ?? '') === 'delete'
        && str_starts_with($ctx['table'] ?? '', 'system_')) {
        throw new RuntimeException('Deleting system tables is not allowed.');
    }
});
$guarded->table('system_settings')->where('id', '=', 10)->delete();

Raw SQL context contains sql instead of a builder table. A table-name guard alone does not protect raw operations. Soft deletion executes an update, so policies must cover that operation when required.

Tenant scope

$tenantDb = $db->withScope(['tenant_id' => 42]);
$users = $tenantDb->table('users')
    ->where('status', '=', 'active')
    ->orWhere('status', '=', 'vip')
    ->get();

Scope equality conditions are ANDed with the grouped user predicates. Scopes constrain builder WHERE clauses; they do not inject tenant values into inserts, constrain upsert conflicts or rewrite raw SQL. Set those values and enforce authorization explicitly.

Middleware and logging

use() returns a clone. Middleware receives execution context and must return the result from $next($ctx).

$instrumented = $db->use(function (array $ctx, callable $next) {
    $start = microtime(true);
    $result = $next($ctx);
    error_log(sprintf(
        '[dbf] %s %s %.1fms',
        $ctx['type'] ?? 'query',
        $ctx['table'] ?? 'raw',
        (microtime(true) - $start) * 1000
    ));
    return $result;
});
$users = $instrumented->table('users')->get();

$db->setLogger(function (string $sql, array $params, float $ms) {
    error_log(sprintf('[dbf] %.1fms %s', $ms, $sql));
});
$db->setMetrics(function (array $metrics) {
    error_log(json_encode($metrics, JSON_THROW_ON_ERROR));
});

For streaming queries, execution is lazy and middleware results may be generators. Avoid logging sensitive parameter values. Inspect the last query with queryString() and queryParams().

Security

• Values are bound to prepared statements. Parameters cannot represent identifiers: allowlist user-selectable tables, columns and sort directions.
• Use trusted SQL with raw methods. Builder scopes and soft-delete filters are not added to raw queries.
• setReadonly(true) blocks builder writes, raw(), execute() and transactions. selectRaw() is an explicit read API; database privileges provide the final protection.
• Set WHERE conditions before updating or deleting. Unfiltered writes can affect every row.
• Keep database credentials and sensitive query parameters out of public logs.
• Apply authorization on every request. Encoded pagination cursors and tenant scopes are not complete access-control systems.

$db->setReadonly(true);
$rows = $db->table('users')->select(['id', 'email'])->get();

Configuration reference

Connection settings can be supplied as an array, URI, DSN or existing PDO. MariaDB uses the mysql driver; Oracle configuration uses oracle and the PDO OCI extension.

$db = new ndtan\DBF([
    'type' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'app',
    'username' => 'user',
    'password' => 'password',
    'charset' => 'utf8mb4',
    'prefix' => 'ndt_',
    'readonly' => false,
    'options' => [PDO::ATTR_TIMEOUT => 5],
    'features' => [
        'soft_delete' => [
            'enabled' => true,
            'column' => 'deleted_at',
            'mode' => 'timestamp',
        ],
        'max_in_params' => 1000,
    ],
]);

Register policies and middleware through policy() and use(). No configuration keys for migrations, models, pooling or automatic caching are provided.

Timeout and test mode

$rows = $db->table('users')->timeout(1000)->limit(20)->get();
$db->setTestMode(true);
$db->table('users')->where('id', '=', 10)->get();
$sql = $db->queryString();
$params = $db->queryParams();
$db->setTestMode(false);

timeout() is best-effort and driver-dependent, not a portable execution deadline. Test mode suppresses query execution, but construction still connects and schema inspection may query the database; it is not an offline SQL compiler.