Db Sqlite Laravel Package

Getting Started

First, a reality check that shapes everything below: yiisoft/db-sqlite is a Yii DB driver, not a Laravel package. There is no Eloquent integration, no DB::connection() hook, and it will not plug into Laravel's query builder. Laravel developers realistically reach for it in three situations: maintaining a Yii app, contributing to a framework-agnostic library that already depends on yiisoft/db, or (rarely) pulling a standalone DBAL into a Laravel service for a specific SQLite task.

Minimal steps:

1. composer require yiisoft/db-sqlite:^2.0 — 2.0.0 requires PHP 8.1–8.5 and yiisoft/db:^2.0 (it declares the yiisoft/db-implementation virtual package, so Composer will pull the right base).
2. Build a Dsn (driver=sqlite, path or :memory:), then construct Connection from yiisoft/db-sqlite\Connection. In Laravel, register it as a singleton in a service provider so you're not opening a handle per resolution.
3. First use case: verify the driver end-to-end by opening :memory:, running DDL, and reading a row back through Command/QueryBuilder. Then exercise Schema introspection to confirm column typing — 2.0.0 rewrote that layer heavily and it's where upgrade surprises live.

If you actually want Eloquent, stop here — this package will cost you more than it saves.

Implementation Patterns

Direct execution vs. builder. Use Command for raw SQL and QueryBuilder for programmatic composition. 2.0.0 added a FOR clause to the builder, so SELECT ... FOR UPDATE no longer requires splicing.

Upserts and returning. The DML layer gained DMLQueryBuilder::upsertReturning() and insertReturningPks(), and Command::insertWithReturningPks() was refactored. For SQLite work where you need generated PKs back from a bulk insert, this is the cleanest path — prefer it over lastInsertRowId() when inserting multiple rows.

JSON columns. New JSON overlaps condition builder lets you express OVERLAPS-style predicates in the builder rather than raw SQL. Pair this with the enum column type support and collation support added in 2.0.0 when modelling richer schemas.

LIKE behavior. The new caseSensitive option on like conditions matters a lot on SQLite, whose default LIKE is ASCII case-insensitive. If your app assumes MySQL-style casing, set it explicitly.

DDL composition. The new ColumnFactory, ColumnBuilder, and ColumnDefinitionBuilder split column definition into discrete responsibilities. If you generate migrations or schema SQL dynamically, target ColumnDefinitionBuilder rather than hand-rolling SQL strings — that's the extension point the maintainers designed for.

Introspection. Schema::loadResultColumn() and the new "source of column information" replace older ad-hoc paths. For a Laravel dev, this is the piece you'd actually leverage in a standalone service: reading SQLite schema into typed column objects without writing a parser.

Laravel integration tip. If you must use it inside Laravel, wrap Connection in a service provider, bind a PSR-3 logger into it for query visibility, and keep it isolated from Illuminate\Database — do not try to make it satisfy Laravel's ConnectionInterface.

Gotchas and Tips

2.0.0 is a large, breaking release. Treat it as a rewrite. The highlights you will hit:

• Quoting changed from backtick to double-quote. Any raw SQL or migration string containing ` breaks. This is the single most common upgrade failure.
• ColumnSchemaInterface was renamed to ColumnInterface, and the old ColumnInterface was removed. Type hints and implements clauses need updating on both sides.
• TableSchema was removed and Schema refactored. Code that resolved table schemas directly is gone.
• Unsigned columns now throw an exception. Porting a MySQL schema to SQLite? Strip unsigned first, or you get a hard failure at column-definition time.
• Exceptions: the driver now throws native \InvalidArgumentException instead of Yiisoft\Db\Exception\InvalidArgumentException. Update catch blocks and any instanceof checks.
• Command::insertWithReturningPks() returns array|false. Strict comparison against array alone will silently pass false.
• QueryBuilder constructor changed — if you subclass or bind it manually in a container, re-check the signature (this bit DI configs frequently).
• Schema::getCacheKey() / getCacheTag() removed. Custom caching around schema introspection must move to your own layer.
• yiisoft/json dependency dropped. If you relied on it transitively, declare it yourself.
• hasLimit() / hasOffset() usage removed from the DQL builder — relevant if you wrote custom pagination logic.

PHP floor. 8.1 minimum now. Locking a project to 8.0 blocks the upgrade entirely.

Debugging. The driver defers to yiisoft/db for logging; there's no Laravel Telescope story. Bind a PSR-3 logger to Connection and log at debug level for query traces. For schema-type mismatches, inspect the ColumnInterface instances coming out of Schema rather than guessing from raw PRAGMA output — 2.0.0 moved that normalization into ColumnFactory, and the mapping is not 1:1 with SQLite's declared types.

Extension points worth knowing. Connection::getColumnBuilderClass() lets you swap column-building behavior per connection; ColumnFactory is the intended override for default-value normalization (previously buried in Schema::normalizeDefaultValue()). If you need custom column types, implement ColumnInterface and register via the factory rather than patching the driver.

Version pin. Because so much of the public surface moved, pin ^2.0 and read the full changelog before any minor bump — the 1.x → 2.x gap is wide enough that copy-pasted snippets from blog posts and older Yii docs will fail in non-obvious ways.