I'm doing unit/integration tests. SQLite doesn't support RIGHT JOIN and FULL OUTER JOIN. Is there a way to work with MySQL (or MariaDB) completely stored in memory? MySQL has the MEMORY table engine. However, this may generate inconsistency in my tests.
I need some alternative to :memory: from SQLite, but with the same features as MySQL. My problem is performance. SQLite database in-memory speeds up my testing process. However, some queries aren't compatible with SQLite. I also do not find it good practice to do the tests in SQLite if the production database is MariaDB.
After a few years of research and testing of different approaches to this question, the best way I found to deal with the problem was using Docker. More precisely using tmpfs volumes. This way, I guarantee that the databases will not persist on the disk and will not suffer from I/O blocking.
For those interested, Docker has its own documentation on this type of volume: tmpfs mounts