Mastodon hachyterm.io

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

Docker with MariaDB

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

Here’s the docker command:

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.

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):

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):

-- -----------------------------------------------------
-- 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:

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

And the database schema:

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.

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.

You should now be able to connect to the database.

I run the application via Maven like so:

mvn spring-boot:run