Recently, we wrote a lot of raw and complex SQL queries for data analysis in our new project, and these queries run on a MySQL database. Then, we notice that it becomes a nightmare to maintain these queries, and we decide to add unit tests to ensure their correctness.

Traditionally, we use a light-weight embeded DB like H2 or Derby in unit tests. However, they do not support some of the MySQL syntax. We could also setup a MySQL database for unit test, which will result in a harder project setup and change the CI pipeline configurations. Finally, we find MariaDB4j is a good fit: it provides functionality like an embedded MySQL. MariaDB4j packages MariaDB native binaries in the JAR files and they are extracted to a temporary directory on the fly. Then, the MariaDB binaries are started by Java, and we can connect it via standard JDBC. In the default MariaDB4j setup, the data are placed in a temporary directory as well and the directory will be cleaned up at every restart, so it's suitable to run unit test upon it.

The detailed usages of MariaDB4j can be found in its GitHub project home page. There are some tips and tricks we noticed when we use it in our project:

• If you choose 3306 (which is the default MySQL/MariaDB port) to start MariaDB4j, make sure that there's no other local MySQL/MariaDB instance running at the default port before starting MariaDB4j. MariaDB4j won't start its own instance if there is already one running at the configured port. Then, the JDBC connection will connect to the running local MySQL/MariaDB instance instead of the MariaDB4j instance. The queries in unit tests will be run upon the data in the local MySQL/MariaDB instance, and it may result in ridiculous results in consequence.
• If the queries contain time zone conversions, the time zone description table needs to be loaded into MariaDB4j, which can be downloaded from https://dev.mysql.com/downloads/timezones.html.
• Make sure the value of sql_mode is set as that of the production MySQL database.
• Make sure the path of the MariaDB4j socket file is short enough. Otherwise it could not start the MariaDB instance.