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 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;
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',
]);

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