Possible to simulate mysql server in memory?

Question

I wrote some functions that work with SQL. I test the functions using testthat and an in memory SQLite database. However, some functions cannot be tested using SQLite because SQLite does not support the ALTER TABLE command.

Is there some way to simulate a mySQL database in memory the same way that one can simulate a SQLite?

> DBI::dbConnect(RSQLite::SQLite(), ":memory:")
<SQLiteConnection>
  Path: :memory:
  Extensions: TRUE
> DBI::dbConnect(RMySQL::MySQL(), ":memory:")
Error in .local(drv, ...) : 
  Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

If not, how does one write automatic tests for mySQL functions?

Answer 1

You can't make a whole MySQL instance run in memory like the SQLite :memory: option. MySQL is designed to be a persistent database server, not an ephemeral embedded database like SQLite.

However, you can use MySQL's MEMORY Storage Engine for individual tables:

CREATE TABLE MyTable ( ...whatever... ) ENGINE=MEMORY;

The equivalent in RMySQL seems to be the dbWriteTable() method, but as far as I can tell from documentation, you can't specify the ENGINE when creating a table with this method. You'll have to create the table manually in the MySQL client if you want to use the MEMORY engine.

But you should be aware that every storage engine has some subtle different behavior. If any of your tests depend on features of InnoDB, you won't be able to simulate them with the MEMORY storage engine (e.g. row-level locking, foreign keys, fulltext search).

Read the manual on the MEMORY storage engine: https://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

P.S.: Ignore the suggestions on that manual page to use NDB Cluster. It may be faster, but it requires multiple servers and special database design to achieve that performance. It's much harder to set up.