php-mysql-engine runs your SQL against arrays instead of a database

The two usual answers, and why both are unsatisfactory

The README's motivation section names the two ways people test database code and then explains why each falls short. The first is mocking SQL query execution, which requires an explicit list of the queries expected to run and the results to return. The cost is significant manual work setting up expectations, and the tests become fragile, needing updates even for benign changes to the code or the queries. It also means the data access layer itself is never unit tested, since a mock stands in for it. The second option is using an actual database, which is what Vimeo did in the past. The problem the README identifies is a mismatch between how databases are designed and how tests use them: databases like MySQL are built to hold lots of long-lasting data, while unit tests write small amounts of very short-lived data, so extra care is needed to truncate test databases between tests, which creates a performance issue. The library takes a third route rather than improving either of the first two.

It parses the query and executes it against PHP arrays

The approach is to parse and execute SELECT, INSERT, UPDATE and DELETE queries against an in-memory database stored in PHP arrays. The engine is a subclass of PDO, so from the application's point of view it is a PDO connection, and the README says you can instantiate the subclass as you would PDO and use dependency injection or similar to hand that instance to your application code. The rest of the code operates normally, using the database the same way it is used in production. The important caveat the README attaches to this is that it only works as long as the amount of data used for testing is small, which is exactly the case the motivation section identifies as the mismatch between test data and real database data. The project is based on Slack's Hack SQL Fake by Scott Sandler, and there is a Vimeo engineering article about the tool, so the design lineage and the reasoning behind it are both documented rather than folklore.

The SQL surface is wide, and the parser is deliberately strict

The supported syntax list is long enough that the practical question is where it stops. The clauses FROM, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT are supported as appropriate for each query type. JOIN queries work with all join types, and multi-queries are covered including subqueries, UNION, UNION ALL, INTERSECT and EXCEPT. Complex expressions such as CASE, BETWEEN and row comparators like (1, 2, 3) < (4, 5, 6) are handled, along with all basic operators implemented with operator precedence, column aliases and cross-database queries. INSERT ... ON DUPLICATE KEY UPDATE is supported, as are functions such as COUNT, NULLIF, COALESCE and CONCAT_WS, plus temporary variables in the form of @previous_name := user.name. One feature deserves particular attention from a testing perspective: there is a validating parser, so the query parser throws exceptions on most invalid SQL queries, which the README frames as helping protect your production environment from accidental SQL syntax errors. A test suite that silently accepted malformed SQL would be a test suite that proves nothing.

Stored procedures, triggers and views are simply not there

The unsupported section is short and unambiguous. The engine does not support MySQL stored objects, which precludes the testing of stored procedures, triggers and views. For most application code that is a non-issue, because the convention in most PHP stacks is to put the logic in the application rather than in the database. For a codebase that has leaned on views for query reuse, or on triggers for audit trails, it is a hard boundary that no amount of configuration gets around. The stated philosophy behind the gaps is in the section on why features are missing: the library aims to support everything its users use in MySQL, rather than every possible feature MySQL offers, and pull requests are welcome for new syntax, SQL functions, data types and bug fixes. That is a defensible scope rule for an unofficial implementation, and it is also the reason you have to audit your own codebase's SQL before adopting.

Installing it and wiring the fake PDO into your tests

The package is a Composer dev dependency.

composer require --dev vimeo/php-mysql-engine

The usage pattern is a subclass instance, and the class name is version specific because the PDO APIs changed across major PHP versions, which the comment in the example flags explicitly.

// use a class specific to your current PHP version (APIs changed in major versions)
$pdo = new \Vimeo\MysqlEngine\Php8\FakePdo($dsn, $user, $password);
// currently supported attributes
$pdo->setAttribute(\PDO::ATTR_CASE, \PDO::CASE_LOWER);
$pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

So the constructor takes the same three arguments as PDO, and the supported attributes are `PDO::ATTR_CASE` and `PDO::ATTR_EMULATE_PREPARES`. The engine lives in the `Vimeo\MysqlEngine` namespace, with a `Php8` sub-namespace for the current PHP line, and the repository has a `src/` directory with the implementation, `tests/` for the suite, a `composer.json`, a `phpcs.xml` for coding standards, a `psalm.xml` with a `psalm-baseline.xml`, and a `phpunit.xml.dist`. The code is analysed with Psalm, the static analyser from the same organisation.

Result types differ from real MySQL when prepares are emulated

The known issues section is worth reading before you trust a green test run. By default the engine returns all data formatted as a string. If `PDO::ATTR_EMULATE_PREPARES` is set to false, the engine instead infers column types, so something like SUM(some_int_column) is given an int type. The README then notes that in some cases the engine may do a better job of inferring correct column types than actual MySQL, which defaults to string when it cannot work out a column type, and that strict type checks on the results may show small discrepancies. That is a subtle and important difference. A test asserting `assertSame(1, $row['total'])` will pass against the engine and fail against production MySQL, or the reverse, and the failure only appears after deployment. If your suite does strict comparisons on numeric columns, that is the first place to look when a test behaves differently in CI than in production, and it is an argument for comparing loosely, or for keeping the assertions on values rather than types.

No issue tracker, and that is a deliberate choice

Two README sections are unusual enough to deserve a mention. The first explains why the project does not have an issue tracker: maintaining open source projects is hard work, the maintainer does not want to make more work for themselves or their colleagues, and the instruction is to use the project at your own risk. The second is the Caveat Emptor section, which says plainly that unlike Psalm this package is not designed with a wide audience in mind, that a project will only really benefit from it if it already has a large number of tests that require a database connection, and that the maintainers must understand the tradeoffs of using an unofficial MySQL implementation in their test suite. Combined with the statement that the library is usable only while test data stays small, that is the adoption guidance in two sentences. If your project has a few database tests, a real MySQL instance in CI is likely simpler. If it has thousands, this library removes a serialisation bottleneck that no amount of container tuning will fix. Contributors are asked to make sure pull requests pass the unit tests and the Psalm checks.

Editorial conclusion

Adopt php-mysql-engine if your PHP test suite needs a database connection to run and you want those tests to be fast and free of a server dependency. Skip it if your code uses stored procedures, triggers or views, since the engine does not support MySQL stored objects, and skip it if the project has few database tests, because the maintenance cost of an unofficial MySQL implementation is not worth it there. Before you commit, check the type behaviour around PDO::ATTR_EMULATE_PREPARES, since the engine defaults to returning everything as strings and infers types when emulation is off, which can differ from real MySQL.

Frequently asked questions

Official sources

1. Issues
2. License: MIT
3. README
4. Releases
5. vimeo/php-mysql-engine on GitHub