Setup Mariadb via Docker With Spring Boot
Tags: mariadb, java, spring, docker
Published: 2021-06-01 08:31:36

---
title: Setup Mariadb via Docker With Spring Boot
published: true
date: 2021-05-31 00:00:00 UTC
tags: mariadb, java, spring, docker
cover_image: https://www.rockyourcode.com/static/img/2021-05-31-spring-leaves.jpg
canonical_url: https://www.rockyourcode.com/setup-mariadb-via-docker-with-spring-boot/
---

> How to run MariaDB inside Docker and connect to it with Spring Boot

## Docker with MariaDB

We'll use [yobasystems/alpine-mariadb][alpinemariadb], a lightweight container image using the open-source MariaDB as MySQL alternative.

Here's the docker command:

```bash
docker run --name mariadb -p 33067:3306 -v /var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root_pass -d yobasystems/alpine-mariadb
```

The command will create a container named `mariadb` with a root password (`root_pass`). You can connect to the database via `localhost` on port 33067.

## Spring Boot

Initialize Spring Boot with the [Spring Initializer][springinitializer].

Dependencies: _Spring Data JPA, MariaDB Driver_.

I've also added _Rest Repositories_ and _Lombok_, because my goal was to create a CRUD REST API with Spring.

## Setup User and Database

Create two scripts.

The first one is for creating the user (call it `01-user-setup.sql`):

```sql
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

GRANT ALL PRIVILEGES ON * . * TO 'myuser'@'%';
```

The script creates a new user called `myuser` with the password `mypass` _on all hosts_. Of course, you can change the values to your needs.

By default, MySQL and MariaDB restrict connections other than to the local machine. The Docker container runs on a separate network. To connect from your local machine, you'll need to use the `%` wildcard as the host.

The second script creates the database (`02-database-setup.sql`):

```sql
-- -----------------------------------------------------
-- Schema ecommerce
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `ecommerce`;

CREATE SCHEMA `ecommerce`;
USE `ecommerce` ;
```

If you want, you can create your tables here, too. I've omitted the additional setup here.

Now, let's run these scripts via docker:

```bash
docker exec -i mariadb sh -c 'mysql --host=localhost --user=root --password=root_pass' < 01-user-setup.sql
```

And the database schema:

```bash
docker exec -i mariadb sh -c 'mysql --host=localhost --user=root --password=root_pass' < 02-database-setup.sql
```

Explanation:

`exec -i mariadb`: run a command in a running container with stdin (`-i`) — we named the database container `mariadb`

`sh -c`: run a shell script with a command string operand

`'mysql --host=localhost --user=root --password=root_pass'`: this is the command we are running _inside_ the container

`< 01-user-setup.sql`: read the file from stdin (on your local computer)

## Connect Spring

Find the `src/main/resources/application.properties` file in your Spring project and replace it with the following content.

```java
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
# connect via localhost on port 33067
spring.datasource.url=jdbc:mariadb://localhost:33067/ecommerce?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&serverTimezone=UTC
spring.datasource.username=myuser
spring.datasource.password=mypass

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDB103Dialect

# use update for development
# see https://stackoverflow.com/questions/42135114/how-does-spring-jpa-hibernate-ddl-auto-property-exactly-work-in-spring
spring.jpa.hibernate.ddl-auto=update

spring.data.rest.base-path=/api
```

My MariaDB version is `10.5.9`, if your version is higher, you might need to adjust the hibernate dialect to [a higher version](https://stackoverflow.com/questions/37066024/what-is-the-mariadb-dialect-class-name-for-hibernate).

You should now be able to connect to the database.

I run the application via Maven like so:

```bash
mvn spring-boot:run
```

## Links

- [DockerHub MariaDB from yobasystems][alpinemariadb]
- [Spring Boot][springboot]

<small><em>image credit: <a href="https://unsplash.com/photos/NQ6Lh81BTRs">Ash from Moddern Afflatus</a></em></small>

[alpinemariadb]: https://hub.docker.com/r/yobasystems/alpine-mariadb/
[springboot]: https://spring.io/projects/spring-boot
[springinitializer]: https://start.spring.io/