Using MariaDB4j for a Spring Boot Embedded Database
MariaDB4j is an embedded Java wrapper for spinning up a MariaDB instance for local development or continuous integration testing purposes. Using an embedded (or in-memory) database is extremely beneficial when developing a Java application. Traditional embedded DB options are H2, HSQLDB, and Derby. MariaDB4j provides a local DB perfect for a project that deploys to MySQL or MariaDB in production.
Why use an Embedded DB?
A few reasons. To have a full application database for local development. To spin up a separate clean DB for integration testing. Easy setup — no need for each developer to maintain local database servers. It provides the convenience of being able to develop an app completely off-network; it eliminates the need to connect to a shared development database.
Why use MariaDB4j?
We started our Spring Boot project using the default H2 database. It is very fast, and usually what I prefer. However our project had a large amount of complex Flyway SQL Database Migration scripts that were written by our DBA for use on the dev/staging/prod MariaDB database. At first we started to manually modify the SQL to work in a DB-agnostic fashion, but this was very time consuming and tedious. So we toyed with the idea of running a local DB inside a Docker container. Neither was as easy as running an embedded database, so after some searching we found MariaDB4j.
MariaDB4j can be used in any JVM environment (java, groovy, kotlin, etc…), and it has a nice convenience wrapper for Spring. On startup of your application it will install a DB instance in a tmp folder and start it. Depending on your configuration — on shutdown it can leave the DB persistent on disk, or you can tell it to destroy the DB.
Just add the following to your Gradle file (or use the Maven equivalent):
Spring Boot Configuration
The config here can be used for Local and Integration Tests, and is switched on using Spring Profiles. You’ll need a separate PersistenceConfig running under a normal profile for dev/staging/prod that wires up your normal LocalContainerEntityManagerFactoryBean configuration.
And some sample properties files, one for local development running on the same port every time, and data persistent to the file system. And the other for integrationTests that picks a random port and is stored in the tmp directories.
– I recommend the persistent disk DB for local development, but using a separate test DB config that is destroyed after integration tests.
– Configure the local db to live in a /data folder in your project. Then if you need to recreate it you can easily rm -Rf ./data
– You can use a DB tool, like the built-in DB viewer in IntelliJ, to connect to the running DB. Your app will need to be running to access the server, unlike how the H2 driver can connect directly to the filesystem. One drawback compared to H2.
– The startup time is slower than H2. Around 35 seconds on my machine, but worth it to catch any errors we wouldn’t see by using H2.
– You may get an error (Github Issue) when first using MariaDB4j like
Library not loaded: /usr/local/opt/openssl/lib/libssl.1.0.0.dylib
That is caused by something in the Embedded MariaDB that expects the SSL lib in a non-standard place. Try running:
sudo mkdir -p /usr/local/opt/openssl
sudo ln -s /usr/lib /usr/local/opt/lib
And that might fix it. If it doesn’t, give this a shot. Install homebrew, then run the following command:
brew install openssl
Hopefully this helps you run MariaDB4j on your own project. Let me know if you have any issues. The maintainers of MariaDB4j are very receptive to comments and feedback, and have a very welcoming community, so you can also reach out to their github page for more info. All the code here is published on a Github Gist.