Download the PHP package vuthaihoc/laravel-db-portable without Composer

Laravel DB Portable

Query builder helpers that compile for PostgreSQL / CockroachDB, MySQL / MariaDB / MatrixOne and SQLite, plus three Artisan commands for switching an application from one database to another:

• db-portable:scan finds database-specific SQL in your code;
• db-portable:audit checks that the data of one connection fits the schema of another;
• db-portable:copy copies the rows.

It grew out of moving a Laravel application from CockroachDB to MatrixOne. Most of the changes that move needed were raw SQL written for one database (plan_data->>'amount', ::bigint, NULLS LAST, jsonb_set), and integer columns that were wider on the old database than on the new one.

Installation

Requires PHP 8.2+ and Laravel 12 or 13. The service provider is discovered automatically. The package works with Laravel's own drivers (PostgreSQL, MySQL, MariaDB, SQLite) and with these third-party drivers:

The API may still change before 1.0: pin a minor version (^0.3).

Laravel already covers a lot

Prefer Laravel's own methods where they exist. They compile for every database:

Query builder macros

Laravel reads JSON keys as text, so numbers compare and sort as strings on PostgreSQL ("10" < "2", sum(text) fails). These macros cast per database:

The macros are registered on the query builder and on Eloquent builders. Through Eloquent, incrementJson() also updates updated_at, like increment().

What they compile to:

Dashboards: conditional aggregates and subtotals

On CockroachDB and SQLite, rollup() cannot be combined with having(), limit() or offset(), and the groupBy() columns must be selected by name.

Stale and historical reads

Named by intent; the drivers compile them:

They need the drivers' historical reads: vuthaihoc/cockroachdb-laravel 2.2.2+ and vuthaihoc/laravel-matrixone. CockroachDB does not accept them in subqueries or inside a transaction (the driver then reads current data). The time read must be after the table was created, and within the database's history retention (MatrixOne: PITR or garbage-collection window).

For raw query parts, Portable returns the same expressions:

Search boxes and full-text relevance

suggest() returns values starting with the search and, from 3 characters, values containing it (or similar to it, with trigrams): prefix matches first, then the most similar, then the shortest.

The drivers (cockroachdb-laravel 2.3+, laravel-matrixone 1.1+) implement these methods themselves; the macros cover the other databases. MatrixOne has no typo-tolerant search: its ngram parser splits only CJK text into n-grams. The trigram threshold of % is the session's pg_trgm.similarity_threshold (0.3): set it with the connection's variables option on CockroachDB.

Migrations

Blueprint macros for schema features that differ between databases. When a database has no equivalent, the macro skips the feature and logs a warning. Set config(['db-portable.strict' => true]) to throw instead.

Skipped features and dropped conditions log a warning (or throw with db-portable.strict). forDriver() runs the callback of the connection's driver or family and emits nothing by itself.

The where condition of partialIndex() is raw SQL: keep it portable (deleted_at is null, status = 'active').

Search indexes for Scout models

db-portable:search-indexes reads the Scout attributes of your models and checks that their tables have the indexes the database engines need (SCOUT_DRIVER=database, crdb or matrixone), or writes a migration creating them:

• Without --migration the command lists every index as ok, missing, outdated or skipped (with the reason) and fails when one is missing: usable as a CI check.
• Existing indexes are recognized by their definition, whatever their name (e.g. a hand-written using gin (word gin_trgm_ops)).
• outdated: a CockroachDB full-text index made before cockroachdb-laravel 2.3 (no coalesce()) or with another language; the migration drops it first.
• MatrixOne: a table with its own foreign keys or a column already in another FULLTEXT index is reported instead of migrated (4.2.4 crashes on inserts into a table with both a FULLTEXT index and a foreign key; one FULLTEXT index per column).
• The migration is a regular file in database/migrations (or --path), with down(): review and commit it.

Switching databases

A suggested workflow, e.g. from CockroachDB (crdb) to MatrixOne (matrixone):

1. Scan the code for SQL the new database will reject, and rewrite it with the methods above.
2. Migrate the new database: php artisan migrate --database=matrixone.
3. Audit the data against the new schema, and widen the columns it reports.
4. Copy the data.
5. Point DB_CONNECTION at the new database and run your test suite.

Scan

The scanner reads the string literals of your PHP files (not comments or code) and reports each construct with the families it breaks on and a replacement:

Targets: mysql (MySQL, MariaDB), matrixone, pgsql (PostgreSQL, CockroachDB), sqlite. Code that already branches per driver is still reported, because the scanner cannot tell which branch runs.

Audit

It reports tables and columns missing from the target, integers outside the target column's range, and strings longer than the target varchar(n). It runs one min/max query per table on the source.

Copy

• Copies the columns present on both sides and skips migrations (change it with --except).
• Reads in primary-key order (keyset pagination, --chunk=500). --resume starts after the highest key already in the target.
• Converts values for the target: timestamps with a time zone offset become UTC for MySQL-family datetime columns, booleans match the target type, and arrays are encoded as JSON.
• Disables foreign key checks on MySQL-family and SQLite targets while copying. Rows are inserted with insertOrIgnore(), so a rerun does not duplicate them.

Testing

The Unit suite needs no server. The Conformance suite runs the same assertions on SQLite (in memory), MatrixOne and CockroachDB, and skips a server that is not reachable:

Override the servers with MATRIXONE_HOST, MATRIXONE_PORT, CRDB_HOST, CRDB_PORT (see phpunit.xml.dist). To test against local checkouts of the drivers, add path repositories to a local copy of composer.json ("repositories": [{"type": "path", "url": "../laravel-matrixone"}]) and require them as @dev.

License

MIT. See LICENSE.