Db Laravel Package

Getting Started

Install via Composer and make sure the pdo extension for your target driver (pdo_mysql, pdo_pgsql, pdo_sqlite) is enabled:

composer require delight-im/db

The entry point is Delight\Db\PdoDataSource, which takes the driver name, host, database (or SQLite file path), user, password, and an optional charset:

use Delight\Db\PdoDataSource;

$dataSource = new PdoDataSource('mysql', '127.0.0.1', 'app', 'app', 'secret', 'utf8mb4');
$db = $dataSource->makeConnection(); // Delight\Db\PdoDatabase

Your first use case is almost always a parameterized read. Pick the right helper instead of reaching for exec:

$user  = $db->selectOne('SELECT * FROM users WHERE id = :id', ['id' => 42]);
$email = $db->selectValue('SELECT email FROM users WHERE id = :id', ['id' => 42]);
$ids   = $db->selectColumn('SELECT id FROM users WHERE active = 1');
$rows  = $db->select('SELECT * FROM users WHERE role = ? LIMIT 20', ['admin']);

Read the PdoDataSource and PdoDatabase classes first — they are small and define the entire surface area you will use (four select* helpers, insert, update, delete, exec, transaction, getLastInsertId, getAffectedRows). There is no ORM, migration runner, or schema introspection to learn.

Implementation Patterns

Wrap the connection, don't scatter it. Bind PdoDatabase once (container, service, singleton) and inject it into repositories. The class has no global state, so this is trivial:

final class UserRepository
{
    public function __construct(private \Delight\Db\PdoDatabase $db) {}

    public function find(int $id): ?array
    {
        return $this->db->selectOne('SELECT * FROM users WHERE id = :id', ['id' => $id]);
    }
}

Use the insert/update/delete helpers for simple writes. They take associative arrays and infer bind types from PHP types, which keeps CRUD terse:

$id = $db->insert('users', ['email' => $email, 'name' => $name]); // returns last insert id

$db->update('users', ['name' => $newName], ['id' => $id]);

$db->delete('users', ['id' => $id, 'deleted_at IS NULL' => null]); // see gotchas

For anything non-trivial (joins, ON CONFLICT, UPSERT, bulk updates), drop to exec with explicit SQL. The helpers deliberately do not build complex conditions.

Prefer named parameters for anything with more than two placeholders. Named params survive reordering, and the library maps them the same way regardless of driver, which matters when you develop against SQLite and deploy on Postgres.

Transactions — use the closure form and let it roll back on exceptions:

try {
    $db->transaction(function (PdoDatabase $db) use ($order, $items) {
        $orderId = $db->insert('orders', $order);
        foreach ($items as $item) {
            $db->insert('order_items', $item + ['order_id' => $orderId]);
        }
    });
} catch (\Throwable $e) {
    // already rolled back by transaction(); log and surface
}

If you need finer control (savepoints, partial commits), use beginTransaction() / commit() / rollBack() directly — but you then own the try/catch discipline.

Add a logger in development. PdoDatabase::setLogger(callable $logger) receives each executed SQL string and its parameters — wire it to your PSR-3 logger to see exactly what runs, including transaction boundaries:

$db->setLogger(function (string $sql, array $params) use ($log) {
    $log->debug($sql, $params);
});

That is the single most valuable integration point for debugging an app built on this package, because there is no query builder or debug bar to inspect.

Handle schema changes out of band. This library has no migration concept — pair it with Phinx, Doctrine Migrations, or plain .sql files invoked by a script. Keep one canonical source of truth so repositories don't drift from the schema.

Batch inserts should be composed as a single statement with placeholders per row via exec, not a loop of insert() calls inside a transaction. For typical row counts the loop is fine, but the single-statement form is faster across all supported drivers.

Gotchas and Tips

• No query builder, no ORM. Every dynamic filter, ordering, or pagination fragment is hand-written SQL. Sanitize sort columns against a whitelist (['created_at','name','email']) rather than interpolating user input.
• The update/delete condition array is not a full WHERE DSL. Keys are column names, values are equality matches. For ranges, IN, or IS NULL use exec with explicit SQL. Passing an empty array to update/delete will wipe the whole table — guard against it in your repository methods.
• Mixing named and positional parameters in the same query is not supported. Pick one style per statement: ['id' => 1] or [1].
• Type inference is PHP-driven. Booleans map to integers on some drivers; large integers may be bound as strings unless you cast. For BIGINT columns used as IDs, bind as int or string explicitly rather than relying on inference.
• Charset is only meaningful for MySQL/MariaDB. Passing utf8mb4 to SQLite or Postgres is silently irrelevant (or rejected depending on version) — leave it null for those drivers.
• selectValue() returns null when no row matches, which is indistinguishable from a NULL column value. Use selectOne() when you need to know whether a row exists.
• transaction() with a closure swallows nothing — exceptions propagate after rollback. Don't wrap it in another try/catch that hides failures.
• No connection retry or lazy connect management beyond what PDO does. In long-running workers (queue consumers, Swoole), hold one PdoDatabase per worker and re-create it after fatal connection errors.
• exec still runs through the same prepare/bind pipeline, so parameterization is preserved — prefer it over string concatenation even for one-off statements.
• SQLite in-memory databases (:memory:) require the connection to live for the whole test; don't dispose the PdoDatabase between test cases or you lose the schema.
• Error mode. The library sets PDO::ERRMODE_EXCEPTION on the connection; exceptions carry the driver's SQLSTATE and message, which is usually enough to debug without a logger. Enable the logger only when you need the bound parameters.
• Extension points are minimal by design. If you need custom PDO attributes (persistent connections, ATTR_EMULATE_PREPARES, statement caching), construct your own PDO and wrap it — PdoDatabase accepts a pre-built PDO in its constructor — rather than patching PdoDataSource.
• Postgres RETURNING works via selectOne/exec but not via the insert() helper, which uses lastInsertId() (a MySQL/SQLite concept). For Postgres ID retrieval, write INSERT ... RETURNING id and call selectValue.