Integrating Postgres Database Running on Docker into Spring Boot Application

Whether you’re a proficient developer or just getting started with Docker and Spring Boot, this step-by-step guide has everything you need to get up and running in no time.
Refer to the following GitHub repository for the full implementation.

By Noy Gvishi

Running Postgres Image on a Docker Container

Docker, in short, is a containerization platform that allows us to run applications in isolated and lightweight runtime environments called containers. Here is a concise and informative 5-minute video, and here you can download Docker to your computer.

The official Postgres image is a pre-built Docker image that provided and maintained by the PostgreSQL Global Development Group (PGDG).

This image can be obtained from the Docker Hub, which is a repository of pre-built Docker images for various software packages and applications. By using the official Postgres image, users can quickly and easily set up a Postgres database within a Docker container, without the need to install and configure the software manually.

Let’s begin by navigating to Postgres official image on Docker Hub and scrolling down to the indicated section:

Based on that example, we will execute docker run to both create and run a new container with the Postgres image.

docker run --name postgres-spring -e POSTGRES_PASSWORD=password -d -p 5432:5432 postgres:alpine

• postgres-spring is the name of the new container.
• -e sets environment variable inside the container, in this case, POSTGRES_PASSWORD=password.
• The -d option stands for "detached mode" which allows the Docker container to run in the background, detached from the terminal session.
• -p 5432:5432 maps the 5432 container port, which is the default for Postgres, to the 5432 machine port.
• The postgres:alpine image is a version of the official PostgreSQL image that is based on the Alpine Linux distribution, which is known for its small size, simplicity, and security.

Eventually, it should represent the following template:

docker run — name {container_name} -e {variable} -d -p {container_port}:{machine_port} {image}

You may now execute docker ps to display a list of currently running containers, which should include the newly created container.

by Progress

Database Connection Setup

In order to set up a database connection for your Spring Boot application, you will need to add several dependencies to your pom.xml file.

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<scope>runtime</scope>
</dependency>
<dependency>
	<groupId>org.flywaydb</groupId>
	<artifactId>flyway-core</artifactId>
</dependency>

• spring-boot-starter-jdbc: Spring Boot starter that provides the necessary libraries and configuration to use JDBC in a Spring Boot application
• postgresql: PostgreSQL JDBC driver that enables the application to connect to a PostgreSQL database
• flyway-core: database migration tool that provides version control for database schema changes.
  It works by running SQL scripts in a specified location (by default - db/migration, as you will see later) and keeping track of which scripts have been run and which ones haven't.

Once you have added these dependencies, you can create a PostgresDataSource.java file and instantiate a data source creator as a bean.

@Configuration
public class PostgresDataSource {
    @Bean
    @ConfigurationProperties("app.datasource")
    public HikariDataSource hikariDataSource() {
        return DataSourceBuilder
                .create()
                .type(HikariDataSource.class)
                .build();
    }
}

This is a Spring configuration class that creates a HikariDataSource bean by calling a factory method annotated with @Bean.
HikariDataSource is a high-performance JDBC connection pool that is widely used in Java-based applications.
The method is also annotated with @ConfigurationProperties, which binds the properties in the "app.datasource" namespace on the next file to the HikariDataSource bean.

You will also need to create an application.yml file inside the resources directory and add the necessary configurations.

app:
  datasource:
    jdbc-url: jdbc:postgresql://localhost:5432/demodb
    username: postgres
    password: password
    pool-size: 30

The jdbc-url property defines the URL for the PostgreSQL database instance hosted on the local machine, if we were to connect to a remote database, the jdbc-url would change to reflect the IP address or hostname of the remote server.
For example: jdbc:postgresql://myserver.example.com:5432/demodb.

The username and password properties represent the credentials required for authentication, where postgres is Postgres’ default user.

Thepool-size property specifies the maximum number of database connections that can be opened simultaneously in the connection pool.
A connection pool is a cache of database connections maintained by the application server to reuse them rather than opening a new connection for every request.

Finally, inside the resources directory, we create an SQL script that contains the necessary DDL (Data Definition Language) statements to create the database schema, tables, and indexes required by your application.

db/migrations/V1__TenantTable.sql:

CREATE TABLE tenant(
    id UUID NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

It is an SQL script that contains the necessary DDL (Data Definition Language) statements to create the database schema, tables, and indexes required by an application.
By convention, the filename includes a version number (in this case, V1) and a descriptive name for the migration, which are separated by two underscores (__).
The location of the resources/db/migrations directory is also a convention used by Flyway, to manage database migrations in a standardized way.

Interacting with PostgreSQL

In this section, we demonstrated how to interact with the PostgreSQL database using the psql command-line interface inside a Docker container.

After running a Postgres container in port 5432, run docker ps to browse the currently running containers and copy our container id.

Next, run the following to interact with the container through bash:

docker exec -it ba8492872ade bin/bash

The command will launch a Bash shell inside the running Docker container, this will allow you to run commands and interact with the container’s shell as if you were working on a local terminal.

Now when we’re inside the container — run psql to start interacting with the database:

psql

Then specify the user to connect to the PostgreSQL database:

psql -U postgres

This will open a command-line interface to the PostgreSQL database, allowing us to run queries and manage the database.

Create a new database:

CREATE DATABASE demodb;

Browse the list of databases:
(you should see your newly created database on the list)

\l

Connect to demodb database:

\c demodb

Now build and run the Spring Boot application.

After running the app, there should be a newly created tenant table.

Execute \dt to browse the list of tables and \d tenant to view the tenant table.

demodb=# \dt
                 List of relations
 Schema |         Name          | Type  |  Owner   
--------+-----------------------+-------+----------
 public | flyway_schema_history | table | postgres
 public | tenant                | table | postgres
(2 rows)

demodb=# \d tenant
                      Table "public.tenant"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | uuid                   |           | not null | 
 name   | character varying(100) |           | not null | 
Indexes:
    "tenant_pkey" PRIMARY KEY, btree (id)

If we select from the new tenant table, it should be empty at this point.

demodb=# SELECT * FROM tenant;
 id | name 
----+------
(0 rows)

Optionally, add the PostgreSQL uuid-ossp extension to the current database, so we could generate UUIDs during the INSERT queries.
This eliminates the need to generate UUID before each insertion.

CREATE EXTENSION "uuid-ossp";

UUIDs are commonly used in databases as unique identifiers for records, and the uuid-ossp extension provides the uuid_generate_v4() function, which can be used to generate UUIDs.

Now we can insert new records to the table using uuid_generate_v4():

INSERT INTO tenant (id, name) VALUES (uuid_generate_v4(), 'Company 1');
INSERT INTO tenant (id, name) VALUES (uuid_generate_v4(), 'Company 2');

Using Postgres in The Application

Finally, inside the Spring application data access layer, we use a JdbcTemplate to perform queries and provide a row mapper - Lambda function that maps each row to a Java object.

@Repository("postgres")
public class TenantDataAccessService implements TenantDao {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public TenantDataAccessService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<Tenant> selectAllTenants() {
        final String query = "SELECT name, id FROM tenant";

        List<Tenant> tenants = jdbcTemplate.query(query, (resultSet, i) -> {
            UUID id = UUID.fromString(resultSet.getString("id"));
            String name = resultSet.getString("name");
            return new Tenant(id, name);
        });

        return tenants;
    }
}

And there they are, the records we inserted to demodb, after using the GET request that calls selectAllTenants() data access method.

This is a straightforward process that can be achieved with just a few simple steps.
You can expand the database interaction abilities by consulting the JdbcTemplate documentation available here.

I hope this article has provided you with a good starting point for integrating a database to your Spring Boot application, as well as a basic understanding of how to use Docker.

Bonus

Docker cheatsheet-> https://quickref.me/docker
Postgres cheatsheet -> https://quickref.me/postgres