Edit

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 a main object that provides an API for accessing your database. Connection's constructor accepts a configuration array. The possible keys depend on the specific driver; some configuration keys are the shared 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; pass a timezone name, auto or auto-offset keyword, see DateTime TimeZones chapter for more info;
nestedTransactionsWithSavepoint	boolean which indicates whether use savepoints for nested transactions; true by default
sqlProcessorFactory	factory implementing ISqlProcessorFactory interface; use for adding custom modifiers; null by default;
sqlMode	MySQL only; sets the sql_mode, TRADITIONAL by default;
searchPath	PgSQL only; sets the connection search_path;

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

The connection implementation is lazy; it connects to database when needed. You can explicetely connect 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 a single SQL statement. Dbal supports parameter placeholders called modifiers – values are passed separately and its value will replace the placeholder properly escaped and sanitized. Read more in 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"

Our SQL processor supports [] (square brackets) for easily escaping of column/table names. However, if you pass a column name as an input 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 new Nextras\Dbal\Result\Result object. Result object allows to iterate over the fetched data and fetch each row into Nextras\Dbal\Result\Row instance. Row instance is simple value object with property access:

$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 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() helper 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 setting 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 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