Postgres, Docker and Spring Boot

12.01.2019

Postgres, Docker and Spring Boot

Here I explain how to start a Docker container with Postgres and create a database with schema on startup. For this I use docker-compose and a SQL script. I'll also show you how to use Spring-Boot to access the database and store data in it.

Docker-Compose

The Docker Compose (docker-compose.yml) file is shown below:

postgres:
    image: postgres:9.6-alpine
    ports:
      - '5432:5432'
    container_name: postgres_items
    environment:
      POSTGRES_USER: 'hameiste'
      POSTGRES_PASSWORD: 'hameistePW'
    volumes:
      - ./init:/docker-entrypoint-initdb.d/

To start the container, execute the following command:

 docker-compose -f docker-compose.yml up
 

SQL

The SQL (./init/init.sql) file for creating the database and the database schema looks like this:

CREATE DATABASE itemdb
    WITH
    OWNER = hameiste
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;
GRANT ALL PRIVILEGES ON DATABASE itemdb TO hameiste;
\c itemdb

CREATE TABLE Item (
  id              BIGSERIAL  PRIMARY KEY,
  description VARCHAR(255)
);

insert into Item (description) values ('Description1');
insert into Item (description) values ('Description2');

A database itemdb is created for the user hameiste and the user gets all rights. Then connect to database with the following command \ c itemdb. Finally, a relation Item is created which has two columns. In addition, two test records are inserted.

PSQL

Psql can be used to test the database.

Start PSQL:

 docker run -it --rm --link postgres_items:postgres postgres:9.6-alpine psql -h postgres -U hameiste

The password is: hameistePW

Then you connect to the database with the command: \c itemdb

With the command: \d you can see all relations.

The following SQL command can be used to query the contents of the Item table:

 select * from Item;

Spring-Boot-Application

In the very simple Spring Boot application, the items are read from the database and a new item is created to show how to access the Postgres database with Spring Boot.

The Spring Boot Application class DemoDbApplication.java looks like this:

package org.hameister.demoDB;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoDbApplication implements CommandLineRunner {

	@Autowired
	private ItemRepository itemRepository;

	public static void main(String[] args) {
		SpringApplication.run(DemoDbApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		Item item = new Item();
		item.setDescription("New Item");
		Item save = itemRepository.save(item);

		Iterable<Item> all = itemRepository.findAll();
		for (Item item1:all) {
			System.out.println(item1.getId()+":"+item1.getDescription());
		}
	}
}

The class Item looks like this:

package org.hameister.demoDB;

import javax.persistence.*;

@Entity
@Table(name = "Item")
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long id;

    @Column(name = "description")
    private String description;

    public Item() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

In addition, a JPA repository is needed:

package org.hameister.demoDB;

import org.springframework.data.repository.CrudRepository;

interface ItemRepository extends CrudRepository<Item,String> {
}

And finally, in the file application.properties you have to define how the database is accessed:

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url= jdbc:postgresql://localhost:5432/itemdb
spring.datasource.username=hameiste
spring.datasource.password=hameistePW
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

That's it already. The following command can be used to stop and delete the Docker container:

 docker rm -f docker rm -f postgres_items