Dibi: Smart Database Abstraction Library for PHP

To install the latest stable Dibi version, use the Composer command:

composer require dibi/dibi

You can find version overview on the Releases page.

Requires PHP 8.0 or newer.

Connecting to Database

The database connection is represented by the Dibi\Connection object:

$database = new Dibi\Connection([
	'driver'   => 'mysqli',
	'host'     => 'localhost',
	'username' => 'root',
	'password' => '***',
	'database' => 'table',
]);

$result = $database->query('SELECT * FROM users');

Alternatively, you can use the dibi static registry, which maintains a connection object in globally accessible storage and calls all functions on it:

dibi::connect([
	'driver'   => 'mysqli',
	'host'     => 'localhost',
	'username' => 'root',
	'password' => '***',
	'database' => 'test',
	'charset'  => 'utf8',
]);

$result = dibi::query('SELECT * FROM users');

In case of a connection error, it throws Dibi\Exception.

Queries

We query the database using the query() method, which returns Dibi\Result. Rows are returned as Dibi\Row objects.

You can try all the examples online at the playground.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

// array of all rows
$all = $result->fetchAll();

// array of all rows, keyed by 'id'
$all = $result->fetchAssoc('id');

// associative pairs id => name
$pairs = $result->fetchPairs('id', 'name');

// number of result rows, if known, or number of affected rows
$count = $result->getRowCount();

The fetchAssoc() method can return more complex associative arrays.

You can easily add parameters to the query – note the question mark:

$result = $database->query('SELECT * FROM users WHERE name = ? AND active = ?', $name, $active);

// or
$result = $database->query('SELECT * FROM users WHERE name = ?', $name, 'AND active = ?', $active);

$ids = [10, 20, 30];
$result = $database->query('SELECT * FROM users WHERE id IN (?)', $ids);

$database->query('SELECT * FROM users WHERE id = ' . $id); // BAD!!!

Instead of question marks, you can also use so-called modifiers.

$result = $database->query('SELECT * FROM users WHERE name = %s', $name);

In case of failure, query() throws either Dibi\Exception or one of its descendants:

• ConstraintViolationException – violation of some table constraint
• ForeignKeyConstraintViolationException – invalid foreign key
• NotNullConstraintViolationException – violation of the NOT NULL condition
• UniqueConstraintViolationException – collision with unique index

You can also use shortcut methods:

// returns associative pairs id => name, shortcut for query(...)->fetchPairs()
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// returns array of all rows, shortcut for query(...)->fetchAll()
$rows = $database->fetchAll('SELECT * FROM users');

// returns row, shortcut for query(...)->fetch()
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// returns cell, shortcut for query(...)->fetchSingle()
$name = $database->fetchSingle('SELECT name FROM users WHERE id = ?', $id);

Modifiers

In addition to the ? placeholder, we can also use modifiers:

Example:

$result = $database->query('SELECT * FROM users WHERE name = %s', $name);

If $name is null, NULL is inserted into the SQL statement.

If the variable is an array, the modifier is applied to all of its elements and they are inserted into SQL separated by commas:

$ids = [10, '20', 30];
$result = $database->query('SELECT * FROM users WHERE id IN (%i)', $ids);
// SELECT * FROM users WHERE id IN (10, 20, 30)

The %n modifier is used when the table or column name is a variable. (Beware: do not allow the user to manipulate the content of such a variable):

$table = 'blog.users';
$column = 'name';
$result = $database->query('SELECT * FROM %n WHERE %n = ?', $table, $column, $value);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'

Four special modifiers are available for the LIKE operator:

Search for names starting with a certain string:

$result = $database->query('SELECT * FROM table WHERE name LIKE %like~', $query);

Array Modifiers

The parameter inserted into an SQL query can also be an array. These modifiers determine how to construct the SQL statement from it:

Example:

$arr = [
	'a' => 'hello',
	'b'  => true,
];

$database->query('INSERT INTO table %v', $arr);
// INSERT INTO `table` (`a`, `b`) VALUES ('hello', 1)

$database->query('UPDATE `table` SET %a', $arr);
// UPDATE `table` SET `a`='hello', `b`=1

In the WHERE clause, you can use %and or %or modifiers:

$result = $database->query('SELECT * FROM users WHERE %and', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND `year` = 1978

See also Complex queries.

The %by modifier is used for sorting – keys specify the columns, and the boolean value determines whether to sort in ascending order:

$result = $database->query('SELECT id FROM author ORDER BY %by', [
	'id' => true, // ascending
	'name' => false, // descending
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Insert, Update & Delete

We insert data into SQL queries as associative arrays. Modifiers and the ? placeholder are not necessary in these cases.

$database->query('INSERT INTO users', [
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // returns the auto-increment of the inserted record

$id = $database->getInsertId($sequence); // or sequence value

Multiple INSERT:

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

Deleting:

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

// returns number of deleted rows
$affectedRows = $database->getAffectedRows();

Updating records:

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

// returns the number of updated rows
$affectedRows = $database->getAffectedRows();

Substitute any identifier:

$database->query('INSERT INTO users', [
	'id' => $id,
	'name' => $name,
	'year' => $year,
], 'ON DUPLICATE KEY UPDATE %a', [ // here the modifier %a must be used
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Transaction

There are four methods for dealing with transactions:

$database->beginTransaction();

$database->commit();

$database->rollback();

$database->transaction(function () {
	// some action
});

Testing

In order to play with Dibi a little, there is a test() method that you pass parameters like to query(), but instead of executing the SQL statement, it is echoed on the screen.

The query results can be echoed as a table using $result->dump().

These variables are also available:

dibi::$sql; // the latest SQL query
dibi::$elapsedTime; // its duration in sec
dibi::$numOfQueries;
dibi::$totalTime;

Complex Queries

The parameter may also be an object DateTime.

$result = $database->query('SELECT * FROM users WHERE created < ?', new DateTime);

$database->query('INSERT INTO users', [
	'created' => new DateTime,
]);

Or SQL literal:

$database->query('UPDATE table SET', [
	'date' => $database->literal('NOW()'),
]);
// UPDATE table SET `date` = NOW()

Or an expression in which you can use ? or modifiers:

$database->query('UPDATE `table` SET', [
	'title' => $database::expression('SHA1(?)', 'secret'),
]);
// UPDATE `table` SET `title` = SHA1('secret')

When updating, modifiers can be placed directly in the keys:

$database->query('UPDATE table SET', [
	'date%SQL' => 'NOW()', // %SQL means SQL ;)
]);
// UPDATE table SET `date` = NOW()

In conditions (ie, for %and and %or modifiers), it is not necessary to specify the keys:

$result = $database->query('SELECT * FROM `table` WHERE %and', [
	'number > 10',
	'number < 100',
]);
// SELECT * FROM `table` WHERE (number > 10) AND (number < 100)

Modifiers or placeholders can also be used in expressions:

$result = $database->query('SELECT * FROM `table` WHERE %and', [
	['number > ?', 10],  // or $database::expression('number > ?', 10)
	['number < ?', 100],
	['%or', [
		'left' => 1,
		'top' => 2,
	]],
]);
// SELECT * FROM `table` WHERE (number > 10) AND (number < 100) AND (`left` = 1 OR `top` = 2)

The %ex modifier inserts all items of the array into SQL:

$result = $database->query('SELECT * FROM `table` WHERE %ex', [
	$database::expression('left = ?', 1),
	'AND',
	'top IS NULL',
]);
// SELECT * FROM `table` WHERE left = 1 AND top IS NULL

Conditions in SQL Statements

Conditional SQL statements are controlled by three modifiers: %if, %else, and %end. The %if must be at the end of the string representing SQL and is followed by a variable:

$user = ???

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

The condition can be supplemented with an %else section:

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

Conditions can be nested within each other.

Identifiers and Strings in SQL

SQL itself goes through processing to meet the conventions of the given database. Identifiers (table and column names) can be enclosed in square brackets or backticks, and strings in single or double quotes, but the server always sends what the database requires. Example:

$database->query("UPDATE `table` SET [status]='I''m fine'");
// MySQL: UPDATE `table` SET `status`='I\'m fine'
// ODBC:  UPDATE [table] SET [status]='I''m fine'

Quotes inside strings in SQL are written by doubling them.

Result as Associative Array

Example: returns results as an associative array where the key will be the value of the id field:

$assoc = $result->fetchAssoc('id');

The greatest power of fetchAssoc() is demonstrated in SQL queries joining several tables with different types of relationships. The database creates a flat table, fetchAssoc restores the shape.

Example: Let's have a customer and order table (N:M relationship) and query:

$result = $database->query('
  SELECT customer_id, customers.name, order_id, orders.number, ...
  FROM customers
  INNER JOIN orders USING (customer_id)
  WHERE ...
');

And we'd like to get a nested associative array by Customer ID and then by Order ID:

$all = $result->fetchAssoc('customer_id|order_id');

// we will iterate like this:
foreach ($all as $customerId => $orders) {
   foreach ($orders as $orderId => $order) {
	   ...
   }
}

The associative descriptor has similar syntax to when you write arrays using assignment in PHP. Thus 'customer_id|order_id' represents the assignment series $all[$customerId][$orderId] = $row; sequentially for all rows.

Sometimes it would be useful to associate by the customer's name instead of their ID:

$all = $result->fetchAssoc('name|order_id');

// elements are then accessed like this:
$order = $all['Arnold Rimmer'][$orderId];

But what if there are multiple customers with the same name? The table should have the form:

$row = $all['Arnold Rimmer'][0][$orderId];
$row = $all['Arnold Rimmer'][1][$orderId];
...

So we distinguish multiple possible Rimmers using a regular array. The associative descriptor again has a format similar to assignment, with sequential arrays represented by []:

$all = $result->fetchAssoc('name[]order_id');

// we iterate all Arnolds in the results
foreach ($all['Arnold Rimmer'] as $arnoldOrders) {
   foreach ($arnoldOrders as $orderId => $order) {
	   ...
   }
}

Returning to the example with the customer_id|order_id descriptor, let's try to list orders for each customer:

$all = $result->fetchAssoc('customer_id|order_id');

foreach ($all as $customerId => $orders) {
   echo "Orders for customer $customerId":

   foreach ($orders as $orderId => $order) {
	   echo "Document number: $order->number";
	   // customer name is in $order->name
   }
}

It would be nice to display the customer name instead of ID. But we would have to look it up in the $orders array. So let's modify the results to have this shape:

$all[$customerId]->name = 'John Doe';
$all[$customerId]->order_id[$orderId] = $row;
$all[$customerId]->order_id[$orderId2] = $row2;

So, between $customerId and $orderId, we insert an intermediate element. This time not numbered indexes as we used to distinguish individual Rimmers, but directly a database record. The solution is very similar – just remember that a record is symbolized by an arrow:

$all = $result->fetchAssoc('customer_id->order_id');

foreach ($all as $customerId => $row) {
   echo "Orders for customer $row->name":

   foreach ($row->order_id as $orderId => $order) {
	   echo "Document number: $order->number";
   }
}

Prefixes & Substitutions

Table and column names can contain variable parts. You will first define them:

// create new substitution :blog:  ==>  wp_
$database->substitute('blog', 'wp_');

and then use them in SQL. Note that in SQL they are enclosed in colons:

$database->query("UPDATE [:blog:items] SET [text]='Hello World'");
// UPDATE `wp_items` SET `text`='Hello World'

Field Data Types

Dibi automatically detects the types of individual query columns and converts cells to native PHP types. We can also specify the type manually. Possible types can be found in the Dibi\Type class.

$result->setType('id', Dibi\Type::INTEGER); // id will be integer
$row = $result->fetch();

is_int($row->id) // true

Logging

Dibi has a built-in logger that lets you track all executed SQL statements and measure the duration of their execution. Activation:

$database->connect([
	'driver'   => 'sqlite',
	'database' => 'sample.sdb',
	'profiler' => [
		'file' => 'file.log',
	],
]);

A more versatile profiler is the Tracy panel, which is activated when connecting to Nette.

Connect to Nette

In the configuration file, we register the DI extension and add the dibi section – this creates the required objects and also the database panel in the Tracy debugger bar.

extensions:
	dibi: Dibi\Bridges\Nette\DibiExtension22

dibi:
	host: localhost
	username: root
	password: ***
	database: foo
	lazy: true

Then the connection object can be obtained as a service from the DI container, e.g.:

class Model
{
	private $database;

	public function __construct(Dibi\Connection $database)
	{
		$this->database = $database;
	}
}

Community Extensions

Various libraries, ORMs and extensions are built on top of Dibi. You can find a complete list of them on Packagist.