Laravel Mariadb Laravel Package

Getting Started

This package swaps Laravel's MySQL query grammar for a MariaDB-aware grammar so the -> and ->> JSON operators (which Laravel emits for MySQL 5.7+) get rewritten into JSON_EXTRACT() / JSON_UNQUOTE(JSON_EXTRACT(...)) calls that MariaDB actually understands.

Minimal steps:

1. Install:

   composer require ybr-nx/laravel-mariadb
   

   The service provider is auto-discovered; there is typically no config/app.php entry and no published config file.

2. Point a connection at the new driver in config/database.php. The important change is 'driver' => 'mariadb' (the package registers a connection resolver for that name). Keep everything else the same as your existing MySQL connection:

   'mariadb' => [
       'driver'   => 'mariadb',
       'host'     => env('DB_HOST'),
       'database' => env('DB_DATABASE'),
       'username' => env('DB_USERNAME'),
       'password' => env('DB_PASSWORD'),
       // charset, collation, options, etc.
   ],
   

   Then set DB_CONNECTION=mariadb (or route specific models/connections to it).

3. Clear caches so the resolver registers and the new config is read:

   php artisan config:clear
   

4. First use case — the whole point is that ordinary Laravel JSON syntax now works against MariaDB without hand-writing JSON_EXTRACT:

   $users = User::where('settings->theme', 'dark')
       ->where('settings->notifications->email', true)
       ->get();
   

   Confirm the translation before trusting it:

   User::where('settings->theme', 'dark')->toSql();
   // select * from `users` where json_unquote(json_extract(`settings`, '$.theme')) = ?
   

If you only have one database, you can rename the mysql connection to use 'driver' => 'mariadb' in place. A separate connection name is safer because it isolates the grammar override and makes it obvious which queries rely on it.

Implementation Patterns

Query builder JSON access is the sweet spot. Anywhere Laravel wraps a column and a JSON path — where, whereIn, orderBy, groupBy, pluck, first, update on JSON columns — the grammar rewrites the selector. Nested paths (meta->a->b->c) are translated into $.a.b.c internally, so you never write the $ syntax yourself.

->> vs ->. Use ->> when you want a scalar string back for comparison or selection (it wraps in JSON_UNQUOTE); use -> when you compare against JSON-typed literals or want to feed the result into another JSON function. In practice, for where('meta->name', 'John') most people want ->>, but Laravel's grammar chooses based on the operator you use, not on the value type — test the generated SQL.

Filtering, sorting, and aggregating:

Order::where('payload->status', 'paid')
     ->where('payload->total', '>', 100)
     ->orderBy('payload->created_at', 'desc')
     ->pluck('payload->reference');

// Nested
Product::where('attributes->dimensions->width', '<=', 30)->get();

Existence and containment. If you use whereJsonContains / whereJsonLength, verify they work on your MariaDB version. MariaDB has supported JSON_CONTAINS and JSON_LENGTH since 10.2, but the grammar's path rendering inside those helpers is exactly the kind of place a third-party grammar can diverge from core. Test each helper you depend on, not just where.

Raw selects are not rewritten. selectRaw('meta->>"$.name" as name') will pass straight through — the grammar only touches wrapped identifiers it constructs. Write those by hand:

->selectRaw("JSON_UNQUOTE(JSON_EXTRACT(`meta`, '$.name')) as name")

Consider a small query-scope or macro so you don't scatter raw SQL across the codebase.

Indexing pattern. MariaDB's JSON type is an alias for LONGTEXT with a JSON_VALID check, so it has no native JSON index. Create generated (virtual/persistent) columns and index those:

ALTER TABLE users
  ADD COLUMN theme VARCHAR(64)
  AS (JSON_UNQUOTE(JSON_EXTRACT(settings, '$.theme'))) PERSISTENT;
CREATE INDEX users_theme_idx ON users (theme);

Then either query where('theme', 'dark') directly, or keep the JSON syntax and accept a functional scan. Either way, add the index — a JSON_EXTRACT in a WHERE without a generated column is a full table scan.

Multi-connection apps. Keep the MariaDB connection separate from any MySQL connection. The grammar override belongs to that connection only; other connections keep stock MySQL behavior. Models can opt in via protected $connection = 'mariadb';.

Casting still matters. The package changes SQL, not Eloquent. Continue to declare protected $casts = ['settings' => 'array'] and json_encode on write. Reading a JSON column returns a string from the DB; only the cast makes it an array.

Testing. Run CI against a real MariaDB container. SQLite has no JSON_EXTRACT, and stock MySQL will happily accept -> — so testing on either hides exactly the failures this package exists to prevent. A ->toSql() assertion is a cheap regression test for the critical queries.

Gotchas and Tips

• MariaDB JSON is not binary JSON. It's LONGTEXT plus a validity check. Storage, collation, and comparison semantics differ from MySQL 5.7+ — most visibly, string comparisons may be collation-sensitive (utf8mb4_general_ci vs _bin) where MySQL's binary JSON would be exact.
• ->> returns a string. where('meta->age', '>', 18) can behave lexicographically ("9" > "18"). Cast explicitly (CAST(... AS UNSIGNED)) or use a generated numeric column if ranges/ordering matter.
• Boolean and null literals. JSON_EXTRACT returns JSON true/false and null differently from PHP's true/false/null. where('meta->active', true) may not match what you expect; compare against 1/0 or cast, and be aware that a missing key returns SQL NULL (not JSON null) after JSON_UNQUOTE.
• Version floor. JSON functions need MariaDB ≥ 10.2.7 (10.2.3 for JSON_CONTAINS). Check SELECT VERSION() before debugging your query — an unsupported function looks like a syntax error.
• Only builder-wrapped JSON works. selectRaw, havingRaw, orderByRaw, DB::statement, and anything going through raw PDO bypass the grammar. If a query "works in tinker but not in the app," check whether it's raw.
• update() on JSON paths. Some Laravel versions compile JSON update through the grammar; others don't. Verify with ->toSql() before relying on it in production — you may need DB::raw or read-modify-write with the array cast.
• Grammar conflicts. Any other package that also replaces or extends MySqlGrammar (or the connection resolver) can silently win depending on provider registration order. If -> suddenly stops being translated, composer show and the provider list are the first places to look.
• Debugging loop. ->toSql() and ->getBindings() on the builder, or DB::connection('mariadb')->listen(...), or DB::enableQueryLog(). Compare the generated SQL against a hand-written JSON_EXTRACT in a SQL client to isolate whether the problem is the grammar or the query.
• Config cache. After changing the driver name, php artisan config:clear (and config:cache in deploy scripts) — a stale bootstrap/cache/config.php will keep pointing at mysql and the grammar will never engage.
• Indexing is on you. The package solves syntax, not performance. Plan generated columns alongside the feature that reads them; retrofitting indexes after a slow query is common.
• Maintenance risk. This is a small, slow-moving package (sub-100 stars). Pin the version in composer.json, and be prepared to fork or inline the grammar if Laravel's MySqlGrammar internals change. The override surface is small, so a local subclass is a realistic fallback.
• Extension point. If you fork, the natural seam is a MariadbGrammar extends MySqlGrammar overriding wrapJsonSelector, wrapJsonFieldAndPath, and the compile methods for JSON helpers, registered via Connection::resolverFor('mariadb', ...) in a service provider — the same shape this package uses. Keep that provider bootable under config:cache (no env() calls, no runtime container lookups).