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.

Leave a Reply

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

Related Blog Posts
Up to Spec: JavaScript Numeric Separators
Let's take a look at the proposal to add Numeric Separators to the JavaScript specification.
Using Conftest to Validate Configuration Files
Conftest is a utility within the Open Policy Agent ecosystem that helps simplify writing validation tests against configuration files. In a previous blog post, I wrote about using the Open Policy Agent utility directly to […]
SwiftGen with Image & Color Asset Catalogs
You might remember back in 2015 when iOS 9 was introduced, and we were finally given a way to manage all of our assets in one place with Asset Catalogs. A few years later, support […]
Tracking Original URL Through Authentication
If you read my other post about refreshing AWS tokens, then you probably have a use case for keeping track of the original requested resource while the user goes through authentication so you can route […]