There are million of database adapters out there. But very few tick all (our very specific) boxes
- Very minimal dependencies, to be used in very diverse environments
- Make every attempt to shield connection and management logic from implementer
- Support master + many-slave replication patterns
- Semi-intelligent connection choices:
- Writes automatically choose master
- Read queries randomly choose a single replica connection per request, unless...
- Choosing a master connection at any point in the lifecycle will always use it going forward
- Loosely follows Doctrine's tenets @see http://www.doctrine-project.org/api/dbal/2.0/class-Doctrine.DBAL.Connections.MasterSlaveConnection.html
- [NEW] When connection retrievals include a table, master connection rules are segmented by table, with automatic fallback behavior when not specified.
- Out-of-the-box convenience support for CRUD operations, accessors, and fallback to raw SQL (works with other SQL generators as well).
- Automatic conversion to prepared statements for convenience parameters
- Automatic retries for "mysql gone away" in long-running crons, workers, scripts
- Provide deep introspection with events
use Behance\NBD\Dbal;
$config['master'] = [
'username' => 'admin',
'password' => 'password',
'host' => 'db',
'port' => 3306,
'database' => 'dbal_test',
'driver' => 'Pdo_Mysql'
];
$config['replicas'] = [
[
'username' => 'admin',
'password' => 'password',
'host' => 'replica',
'port' => 3306,
'database' => 'dbal_test',
'driver' => 'Pdo_Mysql'
],
//[
// ...add as many slaves as necessary
//]
];
$db = Dbal\Factory::create( $config );
Unit testing:
composer install
./vendor/bin/phpunit
Integration testing: leveraging Docker, using actual mysql container
docker-compose up -d
docker exec -it nbdphpdbal_web_1 /bin/bash
cd /app
./vendor/bin/phpunit
Operation | Example | Result | Notes |
---|---|---|---|
insert | $adapter->insert( 'table', [ 'key' => 'value' ] ); | last insert ID | |
insertIgnore | $adapter->insertIgnore( 'table', [ 'key' => 'value' ] ); | last insert ID, false if not inserted | |
insertOnDuplicateUpdate | $adapter->insertOnDuplicateUpdate( 'table', [ 'key' => 'value' ], [ 'created_on' => new Sql( 'NOW()' ) ] ); | last insert ID, otherwise, 2 if updated | *see WHERE usage |
update | $adapter->update( 'table', [ 'key' => 'new_value' ] ); | rows affected | *see WHERE usage, enforces a non-empty WHERE is required |
delete | $adapter->delete( 'table', [ 'id' => 12345 ] ); | rows affected | *see WHERE usage, enforces a non-empty WHERE is required |
beginTransaction | $adapter->beginTransaction(); | bool successful | Nested transactions are not supported |
commit | $adapter->commit(); | bool successful | |
rollBack | $adapter->rollBack(); | bool successful | |
queryTable | $adapter->queryTable( ‘table’, "SELECT * FROM `table` WHERE id=? AND enabled=?", [ 12345, 0 ] ); | PDOStatement | *PDOStatement is already executed |
queryTableMaster | $adapter->queryTableMaster( ‘table’, "SELECT * FROM `table` WHERE id=:id AND enabled=:enabled, [ ':id' => 12345, ':enabled' => 0 ] ); | PDOStatement | *PDOStatement is already executed, master connection used additional `table` queries |
query | $adapter->query( "SELECT * FROM `table` WHERE id=? AND enabled=?", [ 12345, 0 ] ); | PDOStatement | *PDOStatement is already executed |
queryMaster | $adapter->queryMaster( "SELECT * FROM `table` WHERE id=:id AND enabled=:enabled, [ ':id' => 12345, ':enabled' => 0 ] ); | PDOStatement | *PDOStatement is already executed |
queryMaster | $adapter->queryMaster( "SELECT * FROM `table` WHERE id=:id AND enabled=:enabled, [ ':id' => 12345, ':enabled' => 0 ] ); | PDOStatement | *PDOStatement is already executed, master connection is used for all future queries |
quote | $adapter->queryMaster( "SELECT * FROM `table` WHERE id=:id AND enabled=:enabled, [ ':id' => 12345, ':enabled' => 0 ] ); | string | Parameterized statements |