A powerful abstraction layer for database. Fast & Save.
Supported platforms:
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 save-points for nested transactions;
true by default |
sqlProcessorFactory | factory implementing ISqlProcessorFactory interface; use for adding custom
modifiers; null by default; |
searchPath | PgSQL only; sets the connection search_path ; |
sqlMode | MySQL only; sets the sql_mode ,
TRADITIONAL by default; |
ssl* | MySQL only; use sslKey , sslCert ,
sslCa , sslCapath and sslCipher to set SSL
options for connection; |
$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.
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();
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