Is there a way to run MySQL in-memory for JUnit test cases?

I'm just trying to add test cases for services accessing a MySQL DB, and I would like to recreate the whole schema (and for some scenarios also just use a MySQL dump file with the data needed for each test case). I was looking around and found some guys using SQLite / H2 and others to do this, but I'm just wandering if there is any way to run MySQL in-memory so I don't need to worry about anything specific to the the MySQL dialect I might be using on our services.

The easiest way for using an in memory database that is fully compatible to MySQL and can be used within JUnit test cases is imho MariaDB4j. you just need a Gradle (/Maven) dependency (http://search.maven.org/#search%7Cga%7C1%7Ca%3A%22mariaDB4j%22) and a few lines of code to start:

DB database = DB.newEmbeddedDB(3306);
database.start();
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");

a startup script can be included via

database.source("path/to/resource.sql");

More information on GitHub readme: https://github.com/vorburger/MariaDB4j

EDIT: I have a add some hints to this answer: The MariaDB4j seems to add files in the systems temporary folder. So it will work in an embedded way which means there is no need to install anything and you can just use the dependency via your desired build tool. But it's not a true in-memory-only solution and therefore we cannot speak of unit tests anymore because unit tests mustn't rely on files or databases

We use MySQL and flyway to handle the migration.

For unit testing and simple integration tests, we use the H2 in-memory database with the MODE=MySQL param. Mode=MySQL enables the H2 DB to handle most of the MySQL dialect.

Our test datasource in the Spring config is set up like this:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
    <property name="driverClassName" value="org.h2.Driver"/>
    <property name="url" value="jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE" />
</bean>

(If you don't know Spring - the XML translates into calling new BasicDataSource and then call setDriverClassName and setUrl on the instance created)

Then we use Flyway on the datasource to create the schema and read in like we would against a regular MySQL DB:

<bean id="flyway" class="com.googlecode.flyway.core.Flyway" init-method="migrate">
    <property name="dataSource" ref="dataSource" />
    <property name="cleanOnValidationError" value="false" />
    <property name="initOnMigrate" value="true" />
    <property name="sqlMigrationSuffix" value=".ddl" />
</bean>

You could also just use the dataSource bean in a jdbcTemplate and run some SQL scripts that way or run a number of MySQL scripts using the <jdbc:initialize-database...> tag.

This is one of the reasons why using proprietary SQL extensions is usually not a good idea.

What I would do is try to identify the places where you use non-standard SQL and refactor your code to move these parts to dedicated services. Then you can mock these when running unit tests.

I would suggest to use docker based mysql/postgres/DB testcontainer.

Pom.xml

    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>postgresql</artifactId>
        <version>1.15.1</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>1.15.1</version>
        <scope>test</scope>
    </dependency>

XyzIT.java

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ActiveProfiles("test")
@Testcontainers

Application-test.yml

  datasource:
    initialization-mode: always
    schema: classpath*:schema-anyDb.sql  #initial sql script to createDB
    url: jdbc:tc:postgresql:11.9:///
  jpa:
    hibernate.ddl-auto: none
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true
        default_schema: public
    show-sql: true

You can use a different schema for the JUnit tests. If you're using Spring, it's JUnit extensions allow each test to run in a read-only transaction, so no data will be persistent in the database after the tests. If you need initial data for the tests, you put the needed data in the @Before marked method that participates in the transaction.

You may mount a ramdrive (using ImDisk), copy your datas files on it, and start Mysql services after changing the appropriate configuration in my.cnf Unit test databases being usually small (and you should keep them small for fast testing), they can normally fit in a ramdrive.

You may also consider using transaction in your spring tests instead of rebuilding tables at each test.

We used that for our dev team and it worked like a charm, we gained a order of magnitude in speed.