Edit

Nextras Dbal

Dbal is concise and secure API to construct queries and fetch data from storage independently on the database engine.

Supported platforms:

• MySQL via mysqli or pdo_mysql extension,
• Postgres via pgsql or pdo_pgsql extension,
• MS SQL Server via sqlsrv or pdo_sqlsrv extension.

Connection#

The Connection instance is the main access point to the database. Connection’s constructor accepts a configuration array. The possible keys depend on the specific driver; some configuration keys are shared for all drivers. To actual list of supported keys are enumerated in PhpDoc comment in driver’s source code.

$connection = new Nextras\Dbal\Connection([
    'driver'   => 'mysqli',
    'host'     => 'localhost',
    'port'     => '3306',
    'username' => 'root',
    'password' => '****',
    'database' => 'test',
]);

The Connection’s implementation is lazy; it connects to database only when needed. You can explicitly connect by calling connect() method; you can also disconnect() or reconnect() the connection. Use ping() method to avoid connection timeouts.

In real world application, you are expected to you use a Dependency Injection Container. Dbal comes with integration for Nette framework and Symfony framework. Utilizing those extensions helps you to set up the Connection.

Querying#

Use query() method to run SQL queries. The query method accepts a single SQL statement. Dbal supports parameter placeholders called modifiers - values are passed separately and its value will replace the placeholder with properly escaped and sanitized value. Read more in [Parameter Modifiers| param-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"

Our SQL processor supports [] (square brackets) for easily escaping of column/table names. However, if you want to pass an input retrieved from a user as a column name, use the save %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, it accepts a sequence name for PostgreSQL. The number of affected rows is available through getAffectedRows() method.

Each query() returns new Nextras\Dbal\Result\Result instance. Result’s instance allows iterating over the fetched rows and fetch each of them into a Nextras\Dbal\Result\Row instance. The Row instance is a simple value object with property access:

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

The Result object implements SeekableIterator, so you can iterate over the result. Also, 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 interface provides a convenient API for working with transactions. You can easily beginTransaction(), commitTransaction() and rollbackTransaction(). Usually, you need to react to an exception by calling the rollback method. For such use case there is a transactional() helper method that makes 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 committing or rollbacking), connection will use savepoints for nested transaction simulation. It is possible to disable such behavior by setting nestedTransactionsWithSavepoint configuration option to false.

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 the default setting of your database.

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