ingluisjimenez/nbd.php-dbal

Connects, protects access to database and queries

behance/nbd.php-dbal

There are million of database adapters out there. But very few tick all (our very specific) boxes

Goals

---

1. Very minimal dependencies, to be used in very diverse environments
2. Make every attempt to shield connection and management logic from implementer
3. Support master + many-slave replication patterns
4. 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.
5. 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
6. Automatic retries for "mysql gone away" in long-running crons, workers, scripts
7. Provide deep introspection with events

Usage

---

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 );

Testing

---

Unit testing:

1. composer install
2. ./vendor/bin/phpunit

Integration testing: leveraging Docker, using actual mysql container

1. docker-compose up -d
2. docker exec -it nbdphpdbal_web_1 /bin/bash
3. cd /app
4. ./vendor/bin/phpunit

Operations

---

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