Db Laravel Package

Getting Started

yiisoft/db is the database layer extracted from Yii 3: a connection wrapper, a command layer, a query builder, schema introspection, and transactions. It is not an ORM and it is deliberately framework-agnostic — it depends on PSR-3, PSR-16, and a few small Yii utility packages, not on the Yii application. That is what makes it usable from Laravel, a CLI script, or a queue worker.

What to install. The base package is mostly interfaces and abstractions. You must add at least one driver:

composer require yiisoft/db yiisoft/db-sqlite
# alternatives: yiisoft/db-mysql, yiisoft/db-pgsql, yiisoft/db-mssql, yiisoft/db-oracle

First use case. The realistic first win for a Laravel developer is a standalone script or service that needs portable SQL generation and schema introspection without booting Laravel's container or Eloquent — a data-migration CLI, a reporting job against a read replica, or a small internal admin tool that lists tables and columns dynamically.

use Yiisoft\Db\Sqlite\Connection;
use Yiisoft\Db\Sqlite\Driver;
use Yiisoft\Db\Query\Query;

$db = new Connection(new Driver('sqlite:' . __DIR__ . '/runtime/app.sqlite'));

$rows = (new Query($db))
    ->select(['id', 'email'])
    ->from('users')
    ->where(['status' => 'active'])
    ->orderBy(['id' => SORT_DESC])
    ->limit(25)
    ->all();

Where to look first. In order: the driver package README (DSN and constructor shape, which changed across the 1.x line), the docs/ folder in the repo (per-topic guides rather than one big manual), then ConnectionInterface — it is the clearest map of what the package actually offers.

If you are wiring it into Laravel, the first step is a service provider binding a configured Connection as a singleton, fed from config/database.php. Do that before writing any query code; everything else hangs off that one object.

Implementation Patterns

Singleton connection, config-driven. Register one binding per logical database and inject it. Yii's Connection has no facade and no static state, so multiple connections in one process are just multiple objects — genuinely nicer than reaching for DB::connection() inside model code.

// app/Providers/YiiDbServiceProvider.php
public function register(): void
{
    $this->app->singleton(Connection::class, function ($app) {
        $cfg = $app['config']->get('database.connections.reporting');

        return new Connection(new Driver(
            sprintf('pgsql:host=%s;port=%s;dbname=%s', $cfg['host'], $cfg['port'], $cfg['database']),
            $cfg['username'],
            $cfg['password'],
        ));
    });
}

Use it beside Eloquent, not instead of it. Keep Eloquent for models, relationships, and anything touching request lifecycle. Reach for yiisoft/db for:

• bulk loads (batchInsert, upsert) where Eloquent's model hydration is the bottleneck;
• cross-vendor SQL: write a query once and let the driver-specific QueryBuilder quote and parameterize it for MySQL, Postgres, and SQLite;
• schema introspection for admin scaffolding, data-dictionary screens, or CI checks that diff a live schema against migrations;
• portable DDL in a standalone migration runner (yiisoft/db-migration) for services that don't ship Artisan.

Lean on the query builder's structured API rather than string SQL. Array conditions, andWhere/orWhere, Expression for functions, and hash-condition arrays all get parameterized and quoted correctly:

$query = (new Query($db))
    ->from(['u' => 'users'])
    ->select(['u.id', 'total' => new Expression('SUM(o.amount)')])
    ->leftJoin(['o' => 'orders'], ['o.user_id' => new Expression('[[u.id]]')])
    ->where(['u.tenant_id' => $tenantId])
    ->andWhere(['>=', 'o.created_at', $since])
    ->groupBy(['u.id']);

$rows = $query->all();

Transactions with a retry wrapper. Deadlock/lock-timeout retries are your responsibility. Wrap $db->transaction(callable) in a small helper that retries on the driver's transient error codes; the nested-transaction support (savepoints) means inner calls compose safely.

$db->transaction(function (ConnectionInterface $db) use ($rows) {
    $db->createCommand()->batchInsert('events', ['id', 'payload'], $rows)->execute();
});

Cache the schema. Bind a PSR-16 cache (a thin adapter over Laravel's Repository) to the schema cache. Without it, every getTableSchema() call hits the database for information_schema-style queries — painless locally, measurable in production on hot table lookups.

Test with SQLite in memory. new Connection(new Driver('sqlite::memory:')) gives you a full query builder and schema manager for unit tests with no Docker, as long as your SQL stays inside the portable subset.

Gotchas and Tips

• The base package alone won't connect to anything. If composer require yiisoft/db leaves you with abstract classes and no Connection, you forgot the driver package.
• Constructor shape varies by version. Older releases took ($dsn, $username, $password) directly; current lines take a Driver object with the DSN. When copy-pasting examples from a blog post, check which one the installed version expects — this is the single most common source of "why doesn't this instantiate."
• Fetched integers may arrive as strings. PDO attribute defaults (notably PDO::ATTR_STRINGIFY_FETCHES and emulated prepares) can make integer columns come back as numeric strings. If your JSON responses suddenly show "id": "42", inspect the driver's PDO attributes before blaming the package.
• The schema cache does not self-invalidate. After DDL in a long-running process, call the schema refresh methods or drop the cache key explicitly — otherwise a worker keeps serving stale column lists.
• Query is mutable and stateful. It returns $this from builder methods and accumulates parameters. Reusing the same instance across a loop leaks where clauses and bindings into later queries; clone before reuse, or build fresh each iteration.
• Identifier quoting is opt-in. Array keys and [[column]] markers get quoted and escaped; a raw string condition passed to where() is injected verbatim. Never interpolate user input, even in "just a date range" clauses.
• Catch Yii's exception hierarchy, not PDOException. Yiisoft\Db\Exception\Exception carries errorInfo and normalizes driver error codes, which is what makes portable retry logic possible. Catching PDOException directly re-couples you to the driver.
• No query log, no model events. There is no DB::listen() equivalent. Wire a PSR-3 logger into the connection, or decorate Command in your own binding, if you want per-query timing. Budget an hour for this the first time you need to profile.
• DDL inside transactions. Postgres DDL is transactional; MySQL implicitly commits. Nested transaction/savepoint behavior therefore differs by driver — don't assume a rolled-back migration leaves MySQL untouched.
• Column types are driver strings. Introspected types are not portable across engines. If you need normalized types, use the schema column classes rather than comparing raw type names.
• Long-running workers (Octane, queue daemons) need reconnection handling. Check isActive() and reopen on failure; PHP has no connection pool to fall back on.
• Small ecosystem, low bus factor. ~215 stars and maintenance concentrated in the Yii core team. The code is readable and BSD-3 licensed, so the risk is not legal — it's that edge cases have thin Stack Overflow coverage and you will occasionally be reading the driver's QueryBuilder source to understand a generated query. That's fine for a utility dependency behind your own repository/binding layer; it's a poor fit if you need a large hiring pool or a vendor with an SLA.
• Extension points worth knowing: subclass QueryBuilder, Command, Schema, or the column schema classes per driver; implement ExpressionInterface for custom SQL fragments. These are the seams to use instead of forking when a driver does something you dislike.