We spin up a temporary and local MySQL instance per test run with helpers to generate necessary data on-demand. All our tests therefore use real queries on a real db. Due to speed constraints, this db is shared between all the tests you run in that session, so it’s possible to influence tests after yours runs in CI. The reality is that that is pretty easy to detect and it’s caused us to write less brittle code.
We're using mssql, on bootstrap we're running all migrations, seeding database, then backing it up; each test file restores its own database from backup which is much faster than alternatives; e2e tests run against simulated environment that has single database; e2e are split into concurrent runs with distinct set of tests to speed things up.
Sqlite is a great simulation for SQL. You need to limit your SQL to the subset that us supported by both it and your target database though, which might be a problem.
In a world of Docker existing there’s rarely a reason not to just use your target database - I can have a non-production grade Postgres instance up and running in less time than it took me to write this message.
