Jun 19, 2017

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.

MariaDB

First a quick aside, if you are unfamiliar with MariaDB — it was built by the original developers of MySQL as a completely open source successor. MariaDB is billed as a Drop In replacement for MySQL.

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.

About 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.

Install MariaDB4j

Just add the following to your Gradle file (or use the Maven equivalent):
compile “ch.vorburger.mariaDB4j:mariaDB4j:2.2.3”

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.


MariaDB4j tips

– 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

Conclusion

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.

About the Author

Jeff Sheets profile.

Jeff Sheets

VP - Technology

Jeff has developed Java, Groovy, Grails, and Javascript web apps for industries as varied as Defense, Energy, Weather, Insurance, and Telecom. He is a co-organizer of the Omaha Java Users Group. Jeff has worked on Grails projects since the Grails 1.3.x days, and has experience with production Groovy code as well as Spock tests and Gradle builds. His latest focus has been on AngularJS and Spring Boot applications using JHipster. Jeff also enjoys volunteering at local CoderDojo events to teach programming to our next generation.

One thought on “Using MariaDB4j for a Spring Boot Embedded Database

  1. dev says:

    Thanks for this – worked for me in Spring Boot with gradle and Java 8. In case it helps anyone, I also had to have:
    `compile(‘org.mariadb.jdbc:mariadb-java-client:2.3.0’)`
    in my build.gradle to be able to use the drivers.

  2. Patrick says:

    Thanks, I managed to get it working. But on the downside, this is not working code, missing returns, semi colons and such, also passing in unused parameters.

  3. Lance says:

    Hi Jeff,

    Nice article! I would like to explore the possibilities of using Mariadb4j instead of H2 for integration testing. Can you speak more about how Mariadb4j is working with Flyway during integration testing?

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Blog Posts
Performance Test Liquibase Update
When doing a liquibase update to a database if you’re having performance issues, it can be hard to find out which updates are causing problems. If you need to measure the time to apply each […]
TICK Stack Monitoring for the Non-Technical
TICK – Telegraf, Influx, Chronograf, and Kapacitor – is a method of monitoring your systems and applications. In this article, I discuss in non-technical terms what the difference is between TICK and Prometheus Grafana A […]
Design Systems, Part 1 • Introduction
Business leaders need a practical guide to plan and execute Design System Initiatives. The aim of this series is to be that guide. This installment introduces terms and definitions as a primer on Design Systems.
ML for Translating Dysarthria Speech (Pre-Part 1)
What is Dysarthria? Per the Mayo Clinic, Dysarthria occurs when the muscles you use for speech are weak or you have difficulty controlling them. Dysarthria often causes slurred or slow speech that can be difficult […]