Database Laravel Package

Getting Started

1. Install and confirm the PHP floor. composer require cycle/database — the README says PHP 8.0+, but 2.16 bumped the requirement to 8.1. Check the resolver output rather than the README, and pin an explicit minor ("cycle/database": "^2.23") since this package ships features in patch-sized increments. The latest release at time of writing is 2.23.3 (a Postgres-only bug fix), so ^2.23 gets you the current fix line.

2. Learn the four-object mental model. Everything hangs off DatabaseManager:

DatabaseManager  ->  Database (named)  ->  Table  ->  {Query builders, TableSchema}

• DatabaseManager holds all connections/databases from DatabaseConfig.
• Database is a logical DB ($dbm->database('default')), supports query(), execute(), transaction().
• Table ($db->table('users')) is your entry point for select(), insert(), update(), delete() and getSchema().
• TableSchema is both the DDL builder and the introspection result object.

3. Bootstrap the smallest possible config and run the README flow end to end (create table → insert → select). Use SQLite file or memory for the first loop:

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases'   => ['default' => ['connection' => 'sqlite']],
    'connections' => ['sqlite' => new Config\SQLiteDriverConfig(
        connection: new Config\SQLite\FileConnectionConfig(database: 'runtime/app.db'),
    )],
]));

Then swap the connection block for MySQLDriverConfig, PostgresDriverConfig or SQLServerDriverConfig — nothing above the config changes. That portability is the whole point of the package.

4. Read the docs in this order: Configuration → Access Database → Query Builders → Transactions → Schema Declaration → Schema Introspection. Skip Migrations on the first pass; it belongs to cycle/migrations, not this package. Keep the changelog open: onConflict(), wrapWhere(), server-side cursors and wrapOnWhere() are all recent and are not in most blog posts. Read the 2.20 → 2.23.3 entries as one block, since adoption guides are still written against 2.1x.

5. Translate your Laravel reflexes. There is no Eloquent, no Schema::create, no model events:

First real-world use case: a legacy database you cannot migrate — introspect it, generate TableSchema objects from the live structure, and use those as the source of truth for a query layer.

Implementation Patterns

Configuration as a container singleton. Build DatabaseManager once in a service provider and inject DatabaseInterface (not the manager) into repositories. Config-driven multi-database setups are the norm; use 'databases' => ['default' => ['connection' => 'mysql', 'prefix' => 'app_']] and let the prefix handle shared-schema deployments.

Query builder composition. Build small, reusable scopes on SelectQuery and clone/pass them around rather than concatenating SQL:

$select = $users->select()
    ->where(['status' => 'active'])        // array form
    ->andWhere('created_at', '>', $since)  // operator form
    ->where('role', 'IN', ['admin', 'staff'])
    ->orderBy('id', 'DESC')
    ->limit(50)->offset($page * 50);

For grouped boolean logic use wrapWhere() (2.18+) so a joined OR block doesn't leak into the outer WHERE. Joins get the same treatment with wrapOnWhere() (2.19+).

Subqueries instead of string interpolation. ->subQuery() accepts a SelectQuery plus bound parameters, so you can build WHERE id IN (SELECT ...) and FROM (SELECT ...) AS t without manual escaping. This is the single biggest upgrade over hand-written SQL.

Writes and upserts. Use insertOne() for single rows and insert()->columns(...)->values(...)->run() for batches. InsertQuery::onConflict() (2.18+) gives you dialect-correct ON CONFLICT / ON DUPLICATE KEY — do not hand-roll upserts per driver.

Transactions. Prefer the closure form; it commits on return and rolls back on any throwable, including nested calls (implemented with savepoints). Pass an IsolationLevel for the rare serializable batch job. Keep transactions short: cycle/database will happily hold a PDO transaction open while you call an HTTP API, and you'll blame the DB.

Declarative schema as the default workflow. Describe the desired end state, call save(), and let the schema comparator compute the diff:

$schema = $users->getSchema();
$schema->primary('id');
$schema->integer('id')->autoIncrement();   // note: after primary()
$schema->string('email', 190);
$schema->string('name')->nullable();
$schema->datetime('created_at');
$schema->index(['email'])->unique();
$schema->foreignKey(['team_id'])->references('teams', ['id'])->onDelete('CASCADE');
$schema->save();

Call save() idempotently at boot in tests, or feed the same objects to cycle/migrations to produce versioned DDL in production.

Streaming for big result sets. Generators over SelectQuery work, but for large tables use runChunks() or server-side cursors (2.17+) so PDO doesn't buffer the entire set. Pattern: one chunk callback that does a bounded amount of work, one counter, one resume key (id > $lastId) rather than OFFSET.

Raw SQL and dynamic identifiers. $db->query('SELECT ... WHERE id = ?', $id)->fetchAll() for reads, $db->execute() for DDL/DML. Any identifier that comes from outside code — table names, column names, sort columns — must go through $db->identifier(); values go through parameters, never both through the same path.

Integration and testing. Wrap each test in a transaction and roll back, or spin up SQLite MemoryConnectionConfig and run your save() routines in setUp(). Inject a PSR-3 logger via the manager's logger factory in dev so you can see compiled SQL — this is effectively your DB::listen replacement. Keep Table, SelectQuery and DTO conversion inside a repository so domain code doesn't depend on the package.

Gotchas and Tips

The schema cache will lie to you. Drivers cache introspection. After an out-of-band migration (another process, a DBA, a cycle/migrations run in a different PHP process) a long-lived worker still sees the old columns. Use Driver::clearCache() (2.13+) after deploy, or set queryCache: false on the driver config in dev. This is the #1 source of "column not found" ghost bugs.

save() is destructive by design. It is a declarative diff, not an additive migration — it will drop columns and indexes it doesn't know about. Never call it against a production DB without dumping the generated SQL first; feed it through cycle/migrations instead. Also expect driver gaps: SQLite can't do most ALTER COLUMN operations, so restructuring a table there means create-new/copy/drop.

Order of column declaration matters. primary('id') creates the column, then integer('id')->autoIncrement() mutates it. Declare the modifiers before save(), and set nullable/defaults on the column object, not afterwards.

Types come back as strings. decimal, bigint and often json are returned as PHP strings depending on driver and PDO options — that's intentional (no float precision loss). Cast at the DTO boundary, not in the query. Booleans are the historical weak spot: MySQL unsigned tinyint(1) comparison was fixed in 2.14, SQLite integer→bool in 2.11.1 — if you're below those, pin up.

Postgres JSON path quoting (fixed in 2.23.3). If you build jsonb path expressions whose segments contain a literal apostrophe (e.g. a key like customer's_note), versions before 2.23.3 emitted an unescaped quote into the generated path and Postgres threw a syntax error. Prefer passing path segments as parameters where you can; if you must inline them via Fragment, upgrade to ^2.23.3 before you start debugging your own code. This is also a reminder that JSON/array operators are the least portable corner of the query builder — keep them behind a repository method per driver.

Identifiers vs literals. where('status', 'active') binds correctly, but where($column, $value) with $column from a request is an injection vector. The package's quoting layer only protects what it's given as an identifier; build those with $db->identifier() and never with sprintf.

Fragments and parameters don't mix casually. Fragment is raw SQL — anything interpolated into it must be bound separately. Named-parameter handling in the interpolator had a bug fixed in 2.15.1; if you use :name placeholders and see mismatched binding counts, upgrade before debugging your own code.

Version-sensitive APIs. CursorableInterface was renamed to CursorInterface in 2.17; selectSubQuery() became subQuery() in 2.14; runChunks() dropped the last partial chunk before 2.20. If you're writing streaming code, require ^2.20 explicitly — and ^2.23.3 if you target Postgres JSON paths. Anything you copy from a pre-2.20 tutorial should be re-checked against the changelog for 2.21–2.23, which this release note doesn't summarize.

Transactions don't span connections. Each Database/Driver owns its own PDO handle. Two databases in one config means two independent transactions — there is no distributed commit, and you cannot join across them. Design for it or use a single connection.

DDL inside a transaction is a trap on MySQL. Implicit commits will break your rollback expectations; keep save() calls outside transactional code paths.

Don't expect framework glue. No service provider, no facade, no Eloquent, no Laravel migration driver. Adoption is small (sub-100 stars), so Stack Overflow coverage is thin: your best debugging tools are the source (src/Query, src/Schema), the changelog, and the logger showing compiled SQL. Write one thin integration test per driver you actually deploy against — dialect differences (upsert syntax, RETURNING, identifier case folding, boolean representation, JSON operators) surface there, not in unit tests.

Extension points to know: custom DriverConfig subclasses for exotic PDO options, Fragment for vendor-specific expressions (windowing functions, JSON_EXTRACT, generated columns), and the logger factory for observability. Everything else in the package is meant to be composed, not subclassed.

Watch the database prefix with introspection. Introspection reports real table names including the prefix, while $db->table('users') expects the unprefixed name. Mixing the two in schema-diff tooling produces spurious "table doesn't exist" diffs.