In my simple database application (see docker example post]) Java application is connecting to postgresql database. When the container starts, it creates a database named stoic.

What is the best practice when it comes to distributing my application as docker image and using it as a docker container?

Usecase

Here is my usecase.

I want to create the tables and initialize them with my data (i.e. stoic quotes). However, if the same container is started again (with the same volume), I expect the same data to stay there, together with all changes made by user.

How to achieve this? First, I need to learn about docker volumes.

Then, I need to think about schema creation. I cannot create tables on application start, so hibernate.hbm2ddl.auto certainly should not have the value create-only/create/create-drop (see explanation of ddl-auto values on SO and Vlad Michalcea blog post on schema generation)

What are my options? I could either create init.sql script (which is not recommended) or use a soltion like Liquibase library where I would write a script for both DDL changes and database initialization. Liquibase seems like a good idea and I plan to follow Liquibase tutorial on Spring-boot and maven and Liquibase.

DDL from JPA model

In order to peek on the DDL statements, I decided to run mvn spring-boot:start -Dspring-boot.run.jvmArguments="-Dspring.jpa.hibernate.ddl-auto=create -Ddebug=true" so that all DDL commands (tables that are created from scratch, constraints, indexes if any) are written to spring boot logs.

Liquibase for schema creation and initialization

Then, I removed spring.jpa.hibernate.ddl-auto=create from application.properties and added Liquibase property spring.liquibase.change-log=classpath:db/changelog/changelog.sql instead, added Liquibase dependency and actual changelog.sql where I put all the DDL statements I found in previous step.

Changes

My application.properties look like this:

1
2
3
4
5
6

spring.datasource.url=jdbc:postgresql://localhost:5432/stoic
spring.datasource.username=stoic_user
spring.datasource.password=stoic
spring.application.name=StoicNotebook
spring.liquibase.change-log=classpath:db/changelog/changelog.sql
spring.liquibase.enabled=true

My pom.xml gained one dependency:

1
2
3
4

<dependency>
  <groupId>org.liquibase</groupId>
  <artifactId>liquibase-core</artifactId>
</dependency>

And changelog.sql is very simple:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- liquibase formatted sql

-- changeset liquibase:1 splitStatements:true endDelimiter:;
alter table if exists thought drop constraint if exists constraint_quote_id_foreign_key_to_quote;
drop table if exists quote cascade;
drop table if exists thought cascade;
drop sequence if exists hibernate_sequence;
create sequence hibernate_sequence start 1 increment 1;
create table quote (id int8 not null, author varchar(255) not null, text varchar(2048) not null, primary key (id));
create table thought (id int8 not null, date_time timestamp, text varchar(255), quote_id int8, primary key (id));
alter table if exists quote add constraint uc_quote_text unique (text);
alter table if exists thought add constraint uc_thought_text unique (text);
alter table if exists thought add constraint constraint_quote_id_foreign_key_to_quote foreign key (quote_id) references quote;

-- changeset liquibase:2 splitStatements:true endDelimiter:;
insert into quote (id, author, text) values (nextval('hibernate_sequence'), 'kamila', 'w przypadku trudności pozostaję spokojna');
insert into quote (id, author, text) values (nextval('hibernate_sequence'), 'kamila', 'każdy krok w dobrą stronę jest dobry');

Add volumes to postgresql docker-compose service

In order for postgresql docker container to use external volume, I also modified docker-compose.yaml and added volumes key inside db service specification. This way I can rebuild my application image and be sure that Liquibase correctly applies all the changeset I need (with DDL) in case it is run on virgin new database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

version: '2'

services:
  app:
    image: 'docker-stoic:latest'

    build:
      context: .
    container_name: boot-stoic-docker-app
    depends_on:
      - db
    environment:
      - SPRING_DATASOURCE_URL=jdbc:postgresql://db:5432/stoic_db
      - SPRING_DATASOURCE_USERNAME=stoic
      - SPRING_DATASOURCE_PASSWORD=stoic
    ports:
      - "8081:8080"

  db:
    image: 'postgres:13.1-alpine'
    container_name: db
    environment:
      - POSTGRES_USER=stoic
      - POSTGRES_PASSWORD=stoic
      - POSTGRES_DB=stoic_db
    volumes:
      - ./postgres-data:/var/lib/postgresql/data

Try it out

Let’s build the app:

1
2
3
4

mvn clean package
docker compose build
docker container  rm boot-stoic-docker-app
docker compose up

And I have exactly what I wanted:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

[karma@tpd|~] http localhost:8081/quote/
HTTP/1.1 200 
Connection: keep-alive
Content-Type: application/json
Date: Thu, 15 Sep 2022 11:21:47 GMT
Keep-Alive: timeout=60
Transfer-Encoding: chunked

[
    {
        "author": "kamila",
        "id": 1,
        "text": "w przypadku trudności pozostaję spokojna",
        "thoughtsCount": 0
    },
    {
        "author": "kamila",
        "id": 2,
        "text": "każdy krok w dobrą stronę jest dobry",
        "thoughtsCount": 0
    },
    {
        "author": "kamila",
        "id": 3,
        "text": "Dzisiaj pracuję nad wyobrażeniami - badam, czym są w istocie: żądzą, wspomnieniem, ucieczką z niewygodnego stanu?",
        "thoughtsCount": 0
    }
]

Now, let’s add a thought:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

[karma@tpd|~] http POST localhost:8081/thought/ quoteId=3 text="Jutro też trzeba nad tym pracować."
HTTP/1.1 201 
Connection: keep-alive
Content-Type: application/json
Date: Thu, 15 Sep 2022 11:25:11 GMT
Keep-Alive: timeout=60
Location: http://localhost:8081/thought/4
Transfer-Encoding: chunked

{
    "dateTime": "2022-09-15T11:25:10.985808722",
    "id": 4,
    "quote": {
        "author": "kamila",
        "id": 3,
        "text": "Dzisiaj pracuję nad wyobrażeniami - badam, czym są w istocie: żądzą, wspomnieniem, ucieczką z niewygodnego stanu?",
        "thoughtsCount": 1
    },
    "text": "Jutro też trzeba nad tym pracować."
}

Things I have learned

Pass spring boot parameters to maven

If I want to overwrite one of properties declared in application.properties, I can do it when I start spring boot application. For example, if I want to activate dev profile, I could do:

mvn spring-boot:run -Dspring-boot.run.jvmArguments="-Dspring.profiles.active=dev"

or, in my case, if I want to check what DDL statements

mvn spring-boot:start -Dspring-boot.run.jvmArguments="-Dspring.jpa.hibernate.ddl-auto=none -Ddebug=true"

(found here, on SO)

Use Liquibase with spring-boot

This is as simple as following the Liquibase tutorial. This is a list of steps I followed:

• removing ddl-auto properties from application.properties file
• adding spring.liquibase.change-log=classpath:db/changelog/changelog.sql property to application.properties
• creating subdirectories structure db/changelog inside src/main/resources where I just put single changelog.sql
• add maven dependency to Liquibase

Define a volume in docker-compose.yml

I found an example in this blog post in which a database is created and initialized with .sql init script which is placed in /docker-entrypoint-initb.d (as explained in postgres docker image documentation.

Add volumes definition to compose file

I didn’t want to add it this way but rather have more control over data and schema changes locally. Therefore I decided to use Liquibase. Neverheless, I needed to define a volume for postgresql so that data is kept safe between containers’ restarts. It was enough to just add:

1
2

    volumes:
      - ./postgres-data:/var/lib/postgresql/data

and the database is stored in postgres-data directory located at the current directory from where docker-compose starts.

Summary

I’ve set up liquibase with spring-boot app that takes care of schema creation and possibly future migrations. I added volume definition to docker-compose file so that postgresql always has access to the same database. I’ve also learned how to enable debug logs (with hibernate SQL statements) and overwrite spring boot properties when application is run with maven from commandline.

Project on github

I called it stoic_cafe: Let’s push it: https://github.com/kamchy/stoic_cafe

Resources

• SO answer on spring boot + Liquibase with xml
• Liquibase change types - reference - I might use it later, right now I just use pure SQL
• creating and filling postgresql with docker-compose

Ten wpis jest częścią serii docker.

• 2022-21-09 - Dockerize Spring Boot App with Frontend in React
• 2022-15-09 - Docker Volumes
• 2022-28-04 - Dockerize Spring Boot REST Service with Postgresql on Docker