Fork me on GitHub

Dbal

composer require nextras/dbal

Nextras Dbal

A powerful abstraction layer for database. Fast & Save.

Supported platforms:

  • MySQL via mysqli extension,
  • Postgres via pgsql extension,
  • MS SQL Server via sqlsrv extension.

Connection

The connection instance is the main object which provides API for working with your database. Connection's constructor accepts a configuration array. The possible keys depend on the current driver, although some configuration keys are the same for all drivers:

driver driver name, use mysqli, pgsql, or sqlsrv
host database server name
username username for authentication
password password for authentication
database name of the database
charset charset encoding of the connection
connectionTz timezone for the connection
nestedTransactionsWithSavepoint boolean which indicates whether use savepoints for nested transactions; enabled by default
sqlProcessorFactory factory implementing ISqlProcessorFactory interface; use for adding custom modifiers; null by default;
$connection = new Nextras\Dbal\Connection([
    'driver'   => 'mysqli',
    'host'     => 'localhost',
    'username' => 'root',
    'password' => '****',
    'database' => 'test',
]);

By default, the connection is lazy; it connects to database when needed. You can force the connection by calling connect() method; you can also disconnect() or reconnect() your connection. Use ping() method to stay in touch.


Querying

Use query() method to run SQL queries. Query method accepts “pseudo SQL” statement. Dbal supports parameter modifiers that works as parameter placeholder – values are passed separately and its modifier will be replaced by properly escaped and sanitized value. Take a look on Parameter Modifiers chapter.

$connection->query('SELECT * FROM foo WHERE id = %i', 1);
// SELECT * FROM foo WHERE id = 1

$connection->query('SELECT * FROM foo WHERE title = %s', 'foo" OR 1=1');
// SELECT * FROM foo WHERE title = "foo\" OR 1=1"

“Pseudo SQL” supports [] square brackets for easily escaping of column/table names. However, if you pass a column name as parameter retrieved from an user, use the proper %column modifier.

$connection->query('SELECT * FROM [foo] WHERE %column = %i', 'id', 1);
// SELECT * FROM `foo` WHERE `id` = 1

To retrieve the last inserted id use getLastInsertedId() method. For PostgreSQL, the method accepts a sequence name. The number of affected rows is available through getAffectedRows() method.

Each query() returns a new Nextras\Dbal\Result\Result object. Result object allows you to iterate over the fetched data and fetch each row into Nextras\Dbal\Result\Row object:

$users = $connection->query('SELECT * FROM [users]');
foreach ($users as $row) {
    $row->name;
}

Result object implements SeekableIterator. You can use fetch() method to fetch a row, fetchField() to fetch the first field form the first row, or fetchAll() to return array of rows' objects.

$maximum = $connection->query('SELECT MAX([age]) FROM [users]')->fetchField();

Transactions & savepoints

The connection object provides convenient API for working with transactions. You can easily beginTransaction(), commitTransaction() and rollbackTransaction(). Usually, you need to react to an exception by calling rollback method. For such use case there is a transactional() method that make its callback atomic.

$connection->transactional(function(Connection $connection) {
    $connection->query('INSERT INTO users %values', [
        'name' => 'new user'
    ]);
    $connection->query('INSERT INTO urls %values', [
        'url' => 'new-user',
        'user_id' => $connection->getLastInsertedId();
    ]);
});

If you call beginTransaction() repeatedly (without commiting or rollbacking), connection will use savepoints for nested transaction simulation. It is possible to disable such behavior by nestedTransactionsWithSavepoint configuration option.

You may create, release and rollback savepoints directly through appropriate methods.

$connection->createSavepoint('beforeUpdate');
$isOk = ...;
if ($isOk) {
    $connection->releaseSavepoint('beforeUpdate');
} else {
    $connection->rollbackSavepoint('beforeUpdate');
}

Connection also supports setting a transaction isolation level. The default isolation level depends on your database.

$connection->setTransactionIsolationLevel(IConnection::TRANSACTION_SERIALIZABLE);
// other available constants:
// IConnection::TRANSACTION_READ_UNCOMMITTED
// IConnection::TRANSACTION_READ_COMMITTED
// IConnection::TRANSACTION_REPEATABLE_READ