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