Load Balancing

The API supports connection load balancing, connection pooling, and persistent connections.

The DatabaseRouter class, an implementation of the DbDriverInterface, provides routing capabilities. You can define routes, and the system will automatically select the appropriate DbDriver based on your route definitions.

Basic Usage

<?php
use ByJG\AnyDataset\Db\DatabaseRouter;
use ByJG\AnyDataset\Db\DatabaseExecutor;
use ByJG\AnyDataset\Db\Factory;

$route = new DatabaseRouter();

// Create database driver instances from connection strings
$driver1 = Factory::getDbInstance('sqlite:///tmp/a.db');
$driver2 = Factory::getDbInstance('sqlite:///tmp/b.db');
$driver3 = Factory::getDbInstance('sqlite:///tmp/c.db');

// Add drivers to routes
$route
    ->addDriver('route1', $driver1)
    ->addDriver('route2', $driver2)
    ->addDriver('route3', $driver3)
;

// Define the routing rules
$route
    ->addRouteForWrite('route1')
    ->addRouteForRead('route2', 'mytable')
    ->addRouteForRead('route3')
;

// Create a DatabaseExecutor wrapper around the route
$executor = DatabaseExecutor::using($route);

// Query the database through the executor
$iterator = $executor->getIterator('select * from mytable'); // Will select route2
$iterator = $executor->getIterator('select * from othertable'); // Will select route3
$executor->execute('insert into table (a) values (1)'); // Will select route1;

Available Route Types

Method	Description
`addRouteForWrite($routeName, $table = null)`	Routes any `INSERT`, `UPDATE`, or `DELETE` operation. An optional specific table can be specified.
`addRouteForRead($routeName, $table = null)`	Routes any `SELECT` operation. An optional specific table can be specified.
`addRouteForInsert($routeName, $table = null)`	Routes any `INSERT` operation. An optional specific table can be specified.
`addRouteForDelete($routeName, $table = null)`	Routes any `DELETE` operation. An optional specific table can be specified.
`addRouteForUpdate($routeName, $table = null)`	Routes any `UPDATE` operation. An optional specific table can be specified.
`addRouteForFilter($routeName, $field, $value)`	Routes based on `WHERE` clauses with specific `FIELD = VALUE` conditions.
`addRouteForSelect($routeName, $table = null)`	Same as `addRouteForRead()`.
`addRouteForTable($routeName, $table = null)`	Routes reads and writes (`addRouteForRead()` + `addRouteForWrite()`).
`addCustomRoute($routeName, $regEx)`	Routes based on a custom regular expression (see below).
`addDefaultRoute($routeName)`	Routes any statement not matched by a route added before it (routes are tried in the order added).

Routes are tried in the order they were added, and the first one that matches wins. Adding the same route twice (e.g. the same table for two route names) does not replace the first one: the first one added keeps matching.

The $table, $field and $value arguments are matched literally (a table a.b does not match axb). All routes are matched case-insensitively: addRouteForRead('x', 'Users') matches select * from users, and addRouteForFilter('x', 'email', 'A@B') matches email = 'a@b'.

Advanced Examples

Master-Slave Configuration

A common use case is to have a master database for write operations and multiple slave databases for read operations:

<?php
use ByJG\AnyDataset\Db\DatabaseRouter;
use ByJG\AnyDataset\Db\DatabaseExecutor;
use ByJG\AnyDataset\Db\Factory;

$route = new DatabaseRouter();

// Create driver instances
$masterDriver = Factory::getDbInstance('mysql://user:pass@master-host/database');
$slave1Driver = Factory::getDbInstance('mysql://user:pass@slave1-host/database');
$slave2Driver = Factory::getDbInstance('mysql://user:pass@slave2-host/database');

// Add drivers to routes
$route
    ->addDriver('master', $masterDriver)
    ->addDriver('slaves', [$slave1Driver, $slave2Driver])
;

// DatabaseRouter all write operations to master
$route->addRouteForWrite('master');

// DatabaseRouter read operations to slaves (random selection between slave1 and slave2)
$route->addRouteForRead('slaves');

// Create executor to perform queries
$executor = DatabaseExecutor::using($route);

// Example usage
$executor->execute('INSERT INTO users (name) VALUES (?)', ['John']); // Goes to master
$users = $executor->getIterator('SELECT * FROM users')->toArray(); // Goes to slaves

Table-Specific Routing

You can route queries for specific tables to different databases:

<?php
use ByJG\AnyDataset\Db\DatabaseRouter;
use ByJG\AnyDataset\Db\DatabaseExecutor;
use ByJG\AnyDataset\Db\Factory;

$route = new DatabaseRouter();

// Create driver instances
$db1Driver = Factory::getDbInstance('mysql://user:pass@host1/database');
$db2Driver = Factory::getDbInstance('mysql://user:pass@host2/database');
$db3Driver = Factory::getDbInstance('mysql://user:pass@host3/database');

// Add drivers to routes
$route
    ->addDriver('db1', $db1Driver)
    ->addDriver('db2', $db2Driver)
    ->addDriver('db3', $db3Driver)
;

// DatabaseRouter queries for specific tables
$route
    ->addRouteForRead('db1', 'users')
    ->addRouteForRead('db2', 'products')
    ->addRouteForRead('db3', 'orders')
    ->addRouteForWrite('db1', 'users')
    ->addRouteForWrite('db2', 'products')
    ->addRouteForWrite('db3', 'orders')
;

// Create executor wrapper
$executor = DatabaseExecutor::using($route);

// Example usage
$executor->getIterator('SELECT * FROM users'); // Uses db1
$executor->getIterator('SELECT * FROM products'); // Uses db2
$executor->execute('INSERT INTO orders VALUES (...)'); // Uses db3

Filtering Based on WHERE Clause

You can route queries based on values in the WHERE clause:

<?php
use ByJG\AnyDataset\Db\DatabaseRouter;
use ByJG\AnyDataset\Db\DatabaseExecutor;
use ByJG\AnyDataset\Db\Factory;

$route = new DatabaseRouter();

// Create driver instances for different regions
$usDriver = Factory::getDbInstance('mysql://user:pass@us-host/database');
$euDriver = Factory::getDbInstance('mysql://user:pass@eu-host/database');
$asiaDriver = Factory::getDbInstance('mysql://user:pass@asia-host/database');

// Add drivers to routes
$route
    ->addDriver('us_db', $usDriver)
    ->addDriver('eu_db', $euDriver)
    ->addDriver('asia_db', $asiaDriver)
;

// DatabaseRouter based on region in WHERE clause
$route
    ->addRouteForFilter('us_db', 'region', 'US')
    ->addRouteForFilter('eu_db', 'region', 'EU')
    ->addRouteForFilter('asia_db', 'region', 'ASIA')
;

// Create executor wrapper
$executor = DatabaseExecutor::using($route);

// Queries will be routed based on the region value
$usData = $executor->getIterator("SELECT * FROM customers WHERE region = 'US'");  // Uses us_db
$euData = $executor->getIterator("SELECT * FROM customers WHERE region = 'EU'");  // Uses eu_db

Custom Routing with Regular Expressions

For more complex routing needs, you can use custom regular expressions:

<?php
use ByJG\AnyDataset\Db\DatabaseRouter;
use ByJG\AnyDataset\Db\DatabaseExecutor;
use ByJG\AnyDataset\Db\Factory;

$route = new DatabaseRouter();

// Create driver instances
$analyticsDriver = Factory::getDbInstance('mysql://user:pass@analytics-host/database');
$transactionalDriver = Factory::getDbInstance('mysql://user:pass@transactional-host/database');

// Add drivers to routes
$route
    ->addDriver('analytics', $analyticsDriver)
    ->addDriver('transactional', $transactionalDriver)
;

// DatabaseRouter complex queries to analytics database
$route->addCustomRoute('analytics', 'GROUP\s+BY.*HAVING');

// DatabaseRouter other queries to transactional database
$route->addRouteForRead('transactional');
$route->addRouteForWrite('transactional');

// Create executor wrapper
$executor = DatabaseExecutor::using($route);

// Complex queries go to analytics
$executor->getIterator('SELECT category, COUNT(*) FROM sales GROUP BY category HAVING COUNT(*) > 100');
// Simple queries go to transactional
$executor->getIterator('SELECT * FROM users WHERE id = 1');

The regular expression is matched case-insensitively against the SQL, with line breaks replaced by spaces and a trailing space added. It is used between / delimiters: escape a / inside it as \/.

Implementation Details

The DatabaseRouter class is an implementation of DbDriverInterface that works by analyzing SQL queries and routing them to the appropriate database driver. When a match is found, the corresponding driver is selected to execute the query.

The routing process follows these steps:

You create DbDriverInterface instances (drivers) from connection strings using Factory::getDbInstance()
You add these drivers to named routes using the addDriver() method
You define routing rules (for reads, writes, specific tables, filters, etc.) that map SQL patterns to route names
You wrap the DatabaseRouter in a DatabaseExecutor using DatabaseExecutor::using($route)
When a query is executed through the executor, the SQL is passed to the DatabaseRouter's matchRoute() method
The method iterates through the defined routes in the order they were added
The first route that matches wins: its driver is returned and used to execute the query
If no match is found, a RouteNotMatchedException is thrown

All DbDriverInterface methods are implemented in the DatabaseRouter class and delegate to the matched driver, making the routing process transparent to the application code.

Load Balancing

When you add multiple drivers to a single route (by passing an array), the DatabaseRouter class will randomly select one of them for each query, providing simple load balancing across multiple database servers.

Basic Usage​

Available Route Types​

Advanced Examples​

Master-Slave Configuration​

Table-Specific Routing​

Filtering Based on WHERE Clause​

Custom Routing with Regular Expressions​

Implementation Details​

Load Balancing​

Basic Usage

Available Route Types

Advanced Examples

Master-Slave Configuration

Table-Specific Routing

Filtering Based on WHERE Clause

Custom Routing with Regular Expressions

Implementation Details

Load Balancing