Dibi Documentation

The Translator is the core component responsible for assembling final SQL strings. It constructs queries using a combination of literal fragments, %modifier placeholders, and values that undergo automatic type detection.

When no modifier is provided, Dibi detects the type of the value to determine how to format it:

• is_string → escapeText
• is_int or is_float → number_format
• is_bool → escapeBool
• null → 'NULL'
• Supports DateTimeInterface, DateInterval, Literal, and Expression objects.

The fetchAssoc() method can create complex, nested associative arrays based on a descriptor string. This is particularly powerful when joining multiple tables.

Descriptor Syntax

• key1|key2: Represents $all[$key1][$key2] = $row;
• key1->key2: Represents $all[$key1]->key2 = $row; (inserts the row as an intermediate object)
• key1[]key2: Represents $all[$key1][$index][$key2] = $row; (used to handle duplicate keys by creating a numbered index array)

// Nested array: $all[$customerId][$orderId] = $row;
$all = $result->fetchAssoc('customer_id|order_id');

// Nested array with intermediate row object: $all[$customerId]->order_id[$orderId] = $row;
$all = $result->fetchAssoc('customer_id->order_id');

// Handling duplicate keys: $all['Name'][$index][$orderId] = $row;
$all = $result->fetchAssoc('name[]order_id');

The Connection class automatically updates the \dibi static facade during execution. This coupling has specific behaviors regarding SQL logging and profiling statistics:

1. SQL Logging: Every native query writes its SQL to \dibi::$sql.
2. Profiling Statistics: Statistics like \dibi::$elapsedTime, \dibi::$totalTime, and \dibi::$numOfQueries are updated via the Event class.
3. The $numOfQueries Counter: This counter does not represent only SQL queries. It increments for every profiled event, including CONNECT, BEGIN, COMMIT, and ROLLBACK.
4. Listener Requirement: Profiling statistics (time and query counts) are only updated if an event listener is registered. If no listener is active, the statistics remain at 0, although \dibi::$sql will still be updated by nativeQuery.

Dibi automatically converts database rows into PHP types during fetching via Result::normalize(array &$row). This process is triggered by calls to fetch() or fetchSingle().

Column types are detected upfront using detectTypes() and are represented as string constants from the Type class (e.g., Type::Integer). If a column is detected as native or has a null type, no conversion is performed and the raw value is returned.

You can configure how specific types are handled using Connection::setFormats().

The Fluent query builder uses process-global configuration. Clause configuration is stored in four public static arrays: $masks, $modifiers, $separators, and $clauseSwitches, along with a shared static $normalizer HashMap.

Warning: Because these are public static, any changes made to them (e.g., adding a new clause like WITH or RETURNING) are shared across every Connection and Fluent instance in the entire PHP process. Modifying these arrays in one part of your application will affect all queries everywhere.

Dibi handles LIMIT and OFFSET clauses via the applyLimit(string &$sql, ?int $limit, ?int $offset) method. Because different database engines use different SQL dialects (e.g., LIMIT ... OFFSET vs SELECT TOP vs ROWNUM), this logic is implemented per-driver.

Key behaviors to note:

• Negative Values: If $limit or $offset are less than 0, Dibi throws a NotSupportedException.
• Implementation: In PdoDriver, this logic is handled via a switch statement on the $driverName. In native drivers, the logic is implemented directly within the driver class.
• Maintenance Note: Because the logic is duplicated across multiple driver files to support different dialects, changes to how limits are handled may not be uniform across all database engines.

Dibi allows you to include conditional logic directly within your SQL strings using %if, %else, and %end. The %if modifier must be placed at the end of the string and is followed by a boolean variable.

$user = ???

$result = $database->query(
	'SELECT * FROM table %if', isset($user), 'WHERE user=%s', $user, '%end ORDER BY name'
);

// With %else
$result = $database->query(
	'SELECT * FROM %if', $cond, 'one_table %else second_table'
);

For write operations, you can pass an associative array of data. Dibi will automatically map the keys to column names and values to the appropriate SQL format. Modifiers and ? are not required when passing an array for these operations.

Insert

// Single insert
$database->query('INSERT INTO users', [
	'name' => $name,
	'year' => $year,
]);

// Multiple inserts
$database->query('INSERT INTO users', [
	'name' => 'Jim',
	'year' => 1978,
], [
	'name' => 'Jack',
	'year' => 1987,
]);

// Get auto-increment ID
$id = $database->getInsertId();

Update

$database->query('UPDATE users SET', [
	'name' => $name,
	'year' => $year,
], 'WHERE id = ?', $id);

Delete

$database->query('DELETE FROM users WHERE id = ?', $id);
$affectedRows = $database->getAffectedRows();

Install Dibi using Composer. Dibi 6.0 requires PHP 8.2 and supports up to PHP 8.5.

composer require dibi/dibi

While the transaction(callable) helper does not support savepoints, the underlying driver layer does. If you need partial rollbacks, you must manage the transaction manually using begin(), commit(), and rollback() outside of a transaction() block.

To use a savepoint, pass a name to the begin() method:

// This is the only way to use savepoints in Dibi
dibi->begin('my_savepoint');
// ... perform some work
dibi->rollback('my_savepoint');

Note: Never call these manual methods inside a transaction() callback, as this will trigger a LogicException.