Pest Plugin MySQL Compat

Run a MySQL-flavored Laravel test suite against SQLite in memory without changing a single line of production SQL. The production query is the truth; the test database is what adapts.

A Laravel project with a legacy MySQL database usually runs its test suite on SQLite for speed and isolation. Every query that uses a MySQL-only function then dies in the test, and the usual fixes are all bad: rewriting the query to a SQLite-compatible subset (now the test covers SQL that isn't the production SQL), marking the test ->todo() (the business rule goes uncovered), or requiring a real MySQL for the suite (losing isolation and speed). This package installs the missing/divergent MySQL functions as SQLite user-defined functions (UDFs), with fidelity to NULL propagation, byte-length semantics, and legacy zero-dates — so the query itself never has to change.

Installation

You can install the package via composer:

composer require jeffersongoncalves/pest-plugin-mysql-compat --dev

Usage

Pest

// tests/Pest.php
use JeffersonGoncalves\PestPluginMysqlCompat\Pest\Plugin;

Plugin::boot();

This is shorthand for:

// tests/Pest.php
use JeffersonGoncalves\PestPluginMysqlCompat\MysqlCompat;

pest()->beforeEach(fn () => MysqlCompat::install());

Alternative, without the Pest-specific helper — bind your own base TestCase instead:

// tests/Pest.php
uses(Tests\TestCase::class)->in('Feature', 'Unit');

// tests/TestCase.php
use JeffersonGoncalves\PestPluginMysqlCompat\MysqlCompat;

abstract class TestCase extends BaseTestCase
{
    protected function setUp(): void
    {
        MysqlCompat::install();

        parent::setUp();
    }
}

PHPUnit / plain Laravel

abstract class TestCase extends BaseTestCase
{
    protected function setUp(): void
    {
        MysqlCompat::install();

        parent::setUp();
    }
}

MysqlCompat::install() must run before the app resolves its first database connection — it registers a connection resolver, and resolvers only affect connections created afterwards. That is why it is called before parent::setUp(), not after.

Granular configuration

MysqlCompat::install(
    functions: ['DATE_FORMAT', 'IF', 'CONCAT'],   // default: all
    rewriters: MysqlCompat::REWRITE_ALL,           // reserved for Phase 2, no-op today
    strict: true,                                  // reserved for Phase 3, no-op today
);

Function catalog

Missing from SQLite — installed as UDFs

Native in SQLite, but with divergent semantics — overridden

LENGTH is the most treacherous divergence: on a column of digits (CPF, CNPJ, CEP) both agree and the test passes; on accented text they silently diverge.

JSON_EXTRACT was measured to diverge too (JSON_EXTRACT returns a bare scalar in SQLite, a quoted JSON value in MySQL) but is not overridden — JSON_UNQUOTE is adapted instead to strip the surrounding quotes only when they are present, so it works over either shape.

Known limitations

• WHERE of UPDATE/DELETE with a float parameter is not rewritten — the (Phase 2) float-binding rewriter only targets select()/cursor(). No real case found yet; documented instead of covered speculatively.
• Rewriting will be textual (Phase 2). SQL assembled at runtime by string concatenation with a fragment coming from data is risky territory; the lexer will refuse when unsure, and refusing means the original error comes back, not corrupted SQL.
• GROUP_CONCAT exists in both engines with the same default separator (,), but ordering and DISTINCT diverge. It belongs in a future catalog addition with a parity test, not as "already works".
• Text collation/ordering is not covered. ORDER BY on accented text differs between the two engines — that is a collation problem, not a function problem.
• The LENGTH override changes behavior for anyone who was already writing SQL expecting SQLite's native character-count LENGTH. This is a breaking change for that consumer.

Roadmap

• Phase 1 — what pays for the package. FunctionRegistry + the 12 missing functions + LENGTH and ROUND overrides. Pest and PHPUnit integration. Unit tests.
• Phase 2 — the differentiator. Lexer + FloatBindingRewriter (highest measured payoff) + IsNullRewriter + IntervalRewriter.
• Phase 3 — confidence. scan diagnostic command, strict mode, a MySQL-parity suite that runs against a real MySQL service in CI.
• Phase 4 — reach. The demand-driven catalog (IFNULL, CONCAT_WS, RPAD, DATEDIFF, DAY/MONTH/YEAR, ADDDATE, PERIOD_DIFF, STR_TO_DATE, TIMESTAMPDIFF, GROUP_CONCAT, FIND_IN_SET, FIELD, GREATEST, LEAST, …) and an adapter for consumers not on Laravel (Doctrine, plain PDO).

Testing

composer test

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security

If you discover any security related issues, please email the author instead of using the issue tracker.

Credits

• Jefferson Gonçalves
• All Contributors

License

The MIT License (MIT). Please see License File for more information.