Laravel Sql Dialect Laravel Package

Getting Started

plin-code/laravel-sql-dialect exists for one reason: your app has to run the same Eloquent/query-builder code against more than one database driver (typically MySQL/MariaDB in production and Postgres, or sqlite in tests), and you keep writing driver-specific raw SQL to bridge the gaps. The package gives you small expression objects that compile to the right SQL per driver, so the difference lives in one class instead of scattered whereRaw() strings.

Minimal setup:

composer require plin-code/laravel-sql-dialect

There is no config to publish and no migration; the service provider is auto-discovered. Pin the constraint (^1.2) so a compiler change can't land mid-sprint.

Where to look first: resources/boost/guidelines/core.blade.php. As of v1.2.0 this file is a deliberately short, drift-tested API cheat sheet — every class, method and argument count it names is verified against src/ in CI, and the supported-driver line is checked against the drivers actually handled in src/. Read it before reading the source; it is the fastest correct map of the package.

First use case: replacing a hand-written LIKE with LikeOperator. This is the highest-value swap because LIKE is case-insensitive on a default MySQL collation and case-sensitive on Postgres, so the naive query silently behaves differently per driver. The same reasoning applies to YearExpression (MySQL YEAR() vs Postgres EXTRACT(YEAR FROM ...)) and CsvValues (splitting/matching a comma-separated column, where FIND_IN_SET has no Postgres equivalent).

use PlinCode\SqlDialect\Expressions\LikeOperator;
use PlinCode\SqlDialect\Expressions\YearExpression;
use PlinCode\SqlDialect\Expressions\CsvValues;

// case-insensitive search, one code path on MySQL and Postgres
Post::where(LikeOperator::make('title', $term));

// year buckets in reporting, no YEAR()/EXTRACT() in your codebase
Post::selectRaw('?, count(*) as total', [YearExpression::make('published_at')])
    ->groupByRaw('1');

// match a single id inside a legacy CSV column
Post::where(CsvValues::make('tag_ids', $tagId));

Signatures above are indicative — confirm argument order/arity against the shipped guidelines file, which is the drift-tested source of truth.

If you use Laravel Boost: php artisan boost:install picks the guidelines up automatically; an app that already has Boost uses php artisan boost:update --discover. Two conditions: this package must be a direct requirement of the app, and laravel/boost is only a suggest, never a dependency — nothing breaks or warns if Boost is absent.

Implementation Patterns

Wrap expressions in query scopes, not in controllers. The whole point is that driver differences live in one place, so push each expression behind a named scope or a small query-builder macro. When you later drop a driver, you delete one scope rather than grepping for whereRaw.

// app/Models/Post.php
public function scopeSearchTitle(Builder $q, string $term): void
{
    $q->where(LikeOperator::make('title', "%{$term}%"));
}

Use them in selectRaw / whereRaw / orderByRaw / groupByRaw — that's where raw SQL normally creeps in. YearExpression in a selectRaw + groupByRaw is the canonical reporting pattern (revenue/registrations per year) that otherwise forces you to fork the query per driver.

Keep values as bindings. Pass the user-supplied value through the expression (or as a binding next to it) rather than interpolating it into a string. Raw SQL is exactly where injection bugs appear, and using this package is a good moment to audit existing whereRaw("... $var ...") calls.

Treat the guidelines file as a promptable contract. If your team uses an AI agent (Boost or otherwise), the guidelines exist so the agent writes LikeOperator instead of LIKE, YearExpression instead of YEAR()/EXTRACT, and CsvValues instead of explode(',')/FIND_IN_SET. Point the agent at the file, or add it to your own internal conventions doc — it stays accurate because CI fails when it drifts.

Test against a real second driver in CI. A matrix of MySQL + Postgres is where this package pays for itself. sqlite-only test suites will happily pass while the Postgres grammar path is never executed.

Legacy CSV columns: use CsvValues to buy time, not as a design goal. It keeps a denormalised tag_ids column queryable across drivers while you plan the pivot table; migrate off it rather than building new features on it.

Gotchas and Tips

• Supported drivers are not uniform across expressions. The guidelines list the drivers this package handles, but individual expressions may have narrower coverage. Check the driver line (and the corresponding src/ grammar) before assuming CsvValues works on sqlite, and add a test per driver you actually deploy.
• LIKE case sensitivity is not fully yours to control. LikeOperator normalises the operator (ILIKE vs LIKE), but on MySQL the result still depends on column collation. Don't assume a case-sensitive search behaves identically on both drivers; assert it in tests.
• Wrappers don't fix indexes. LIKE '%term%' is a full scan regardless of which class produced it. YearExpression in a WHERE/GROUP BY also defeats an index on the timestamp column — use a range predicate (whereBetween) when you actually need performance, and keep the expression for display/reporting.
• Boost discovery requires a direct dependency. If this package arrives transitively (via your own internal package), boost:install / boost:update --discover won't pick up the guidelines. Guidelines are inert without laravel/boost installed — no runtime effect.
• The drift test is a contract, not decoration. If you fork the package or add your own expression classes, either extend the guidelines file or keep your additions out of it — a rename that leaves the file stale fails CI, which is the intended behaviour, not a bug to route around.
• v1.2.0 is a no-runtime-change release. Upgrade is safe: no migration, no behaviour change, no deprecation. The only delta is Boost guidelines plus their CI guard. Don't schedule a regression pass beyond your normal suite.
• Debugging compiled SQL: log the output per driver (DB::listen, or dump the expression's compiled SQL) and diff MySQL vs Postgres side by side. If the generated SQL looks right on one driver and wrong on another, the bug is almost always in the grammar for a driver you aren't testing.
• Extension point: the expression classes are small value objects; adding your own for an in-house function (a custom JSON_EXTRACT wrapper, say) is straightforward. Follow the existing class shape so the pattern stays recognisable, and note that your class won't be drift-tested unless you document it in the guidelines file.