Multiple Connections

Multiple Connections

The static facade is intentionally a single connection. Most applications need only one. When you need more (replicas, sharded data, reporting warehouse, tenant DBs, …) there are three patterns.

Pattern 1 — Facade + secondary instances

Keep the facade for your primary database; build secondaries with DB::connect() or new Database(...). Neither touches the facade slot.

use InitORM\Database\Database;
use InitORM\Database\Facade\DB;

// Primary — used everywhere via DB::...
DB::createImmutable([
    'dsn'      => 'mysql:host=primary.internal;dbname=app;charset=utf8mb4',
    'username' => 'app',
    'password' => '…',
]);

// Secondary read-replica — pass around by reference
$replica = DB::connect([
    'dsn'      => 'mysql:host=replica.internal;dbname=app;charset=utf8mb4',
    'username' => 'app_ro',
    'password' => '…',
]);

DB::create('audit', ['event' => 'login']);                 // primary
$users = $replica->read('users')->asAssoc()->rows();       // replica

Pattern 2 — Pure instance API

Skip the facade entirely; pass DatabaseInterface around via DI. Recommended for libraries, microservices, and applications with a container.

use InitORM\Database\Database;
use InitORM\Database\Interfaces\DatabaseInterface;

final class UserRepository
{
    public function __construct(private DatabaseInterface $db) {}

    public function findActive(): array
    {
        return $this->db->read('users', ['*'], ['active' => 1])->asAssoc()->rows();
    }
}

$primary = new Database($primaryCfg);
$repo    = new UserRepository($primary);

In a container (PHP-DI shown):

return [
    'db.primary' => fn () => new Database($primaryCfg),
    'db.replica' => fn () => new Database($replicaCfg),
    'db.reports' => fn () => new Database($reportsCfg),

    UserRepository::class => fn (ContainerInterface $c) =>
        new UserRepository($c->get('db.primary')),

    ReportingService::class => fn (ContainerInterface $c) =>
        new ReportingService($c->get('db.reports')),
];

Pattern 3 — Sibling Database, shared connection

Sometimes you want two Databases that share a live connection (so they're in the same transaction context) but carry independent builder state. Use withFreshBuilder():

$base    = new Database($cfg);
$sibling = $base->withFreshBuilder();

assert($base->getConnection() === $sibling->getConnection()); // ✅ same connection
assert($base !== $sibling);                                   // ✅ different builders

$base->select('id')->where('active', '=', 1);  // base has builder state
$sibling->read('users');                       // sibling is clean: SELECT * FROM users

Useful for composing a sub-query against the same live transaction without polluting the parent's builder.

Read/write splitting

A common setup: writes go to the primary, reads go to the replica.

final class UserRepository
{
    public function __construct(
        private DatabaseInterface $primary,
        private DatabaseInterface $replica,
    ) {}

    public function create(array $data): string|false
    {
        $this->primary->create('users', $data);
        return $this->primary->insertId();
    }

    public function findActive(): array
    {
        return $this->replica->read('users', ['*'], ['active' => 1])->asAssoc()->rows();
    }
}

Be mindful of replica lag: a row written to the primary may not be visible on the replica yet. For read-your-write consistency, route the immediate follow-up read to the primary.

Tenant-per-database

Each tenant has its own connection:

final class TenantConnections
{
    /** @var array<string, DatabaseInterface> */
    private array $pool = [];

    public function for(string $tenant): DatabaseInterface
    {
        return $this->pool[$tenant] ??= new Database([
            'dsn'      => "mysql:host=db;dbname=tenant_{$tenant};charset=utf8mb4",
            'username' => 'tenant_app',
            'password' => $this->secret($tenant),
        ]);
    }
}

Database is lazy — the underlying PDO connection isn't opened until you actually run a query. Memoizing the Database instance gives you a thin handle that opens its connection on first use.

Sharding

For horizontal sharding, build a lookup that returns the right Database for a given key:

final class Shards
{
    public function __construct(private array $databases) {}  // [Database, Database, Database, …]

    public function for(int $userId): DatabaseInterface
    {
        return $this->databases[$userId % count($this->databases)];
    }
}

$shards = new Shards([
    new Database($shard0Cfg),
    new Database($shard1Cfg),
]);

$shards->for($userId)->read('users', ['*'], ['id' => $userId]);

Swapping the facade target

createImmutable() is single-shot. When you really need to swap (typically in tests), use replaceImmutable():

// In a test's setUp:
DB::replaceImmutable(SqliteHelper::makeConnection());

// In tearDown:
DB::replaceImmutable(null); // clear the slot

replaceImmutable() accepts a credentials array, a ConnectionInterface, a DatabaseInterface, or null.

Connection pooling

Neither this package nor initorm/dbal pool connections — PDO is created on demand and held for the lifetime of the Connection object. If you need pooling:

• Per-request PHP (PHP-FPM) keeps PDO connections alive within a request. Good enough for most.
• Across requests, set PDO::ATTR_PERSISTENT => true in options.
• Long-running workers (Swoole, RoadRunner, ReactPHP) should manage Database instances per worker; consider a small wrapper that drops and rebuilds the Database on connection loss.

See also

• Static Facade — when the facade is the right pick
• Configuration — every credential, every option
• Architecture — what's shared vs cloned across siblings