Testing Workflow Speedup

Context

I'm currently working on a few projects with large-ish test suites (1500 - 3500), and prefer to use the same database for my tests as I do in prod (I've been bitten a few times by tests that pass, but cause failures in prod due to differences between SQLite & Mysql/Mariadb)

Problem

As the projects grow over the years, even doing database squashes on a regular basis still incurs a pretty hefty test setup time (over 5 seconds on my M1 Macbook pro) any time we need to touch the database. (Side note: Without regular database squashes, this was over 30 seconds for one project, as running all the migrations was taking a looong time. Database squashing is awesome!)

Obviously having a 5 second delay to start a single test (or a test suite) when trying to use TDD causes some frustration

Current solution

Today, I started experimenting with a hybrid model... I'll still use mysql when I run my full test suite, but for individual feature development, I'll use an SQLite database to cut the DB instantiation time down from 5.6 seconds 468ms.

Implementation Issues (and solutions)

I'd previously done some migration squashing using the --prune command (will probably not use this in future projects, to be honest). This means that I don't have a full migration history for my project, which means I don't have a current sqlite schema that I can upgrade.

Instead, I did a schema-dump on my mysql database, then used this tool to convert it to sqlite. https://github.com/ww9/mysql2sqlite

There were some weird issues (it didn't add autoincrementing, and sqlite only autoincrements integers), so I had to do some minor fixes to the generated code (like replacing the first line of each table with the following ID schema

`id` INTEGER  NOT NULL PRIMARY KEY ,

but ultimately it was pretty easy & painless.

Future work

I'm using a lot of spatial features in one of the projects, so I'll have to decide whether it's worth beefing up the SQLite implementation to be able to handle that (probably by shimming features into SQLite as described here: https://youtu.be/f4QShF42c6E?t=32825)

For now, I'm happy to keep it simple, and just remember to change out my phpunit env name for "database" between mysql & sqlite depending on the type of work I'm doing (feature dev vs. running full test suite).

Request for help

I'm sure there are better solutions than this - I would love to hear how you lovely folks have solved this problem!!