DEV Community

loading...
Cover image for Setup Mariadb via Docker With Spring Boot

Setup Mariadb via Docker With Spring Boot

Sophia Brandt
former tax officer turned programmer
Originally published at rockyourcode.com on ・3 min read

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
Enter fullscreen mode Exit fullscreen mode

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'@'%';
Enter fullscreen mode Exit fullscreen mode

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` ;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

And the database schema:

docker exec -i mariadb sh -c 'mysql --host=localhost --user=root --password=root_pass' < 02-database-setup.sql
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Links

image credit: Ash from Moddern Afflatus

Discussion (0)