Postgres SQL with Micronaut on Heroku

4 min readFeb 12, 2022

In the earliar post Micronaut application in heroku, we have created a simple micronaut application and deployed to heroku.

Now lets add a database to persist data using Postgres SQL, a powerful, open source object-relational database system.

If you don’t have the database installed, follow the steps mentioned in official site.

Now lets update our application .

Add required dependencies (build.gradle)

micronaut-data is a database access toolkit that uses Ahead of Time (AoT) compilation to pre-compute queries for repository interfaces that are then executed by a thin, lightweight runtime layer.
hikari is a fast, simple, reliable. HikariCP is a “zero-overhead” production ready JDBC connection pool.
flyway makes database migrations easy.
postgresql

annotationProcessor("io.micronaut.data:micronaut-data-processor")implementation("io.micronaut.data:micronaut-data-jdbc")
implementation("io.micronaut.flyway:micronaut-flyway")
implementation("io.micronaut.sql:micronaut-jdbc-hikari")runtimeOnly("org.postgresql:postgresql")

Update application configuration (application.yml)

datasources:
  default:
    url: jdbc:postgresql://${DATABSE_HOST:`localhost`}:${DATABSE_PORT:`5432`}/${DATABSE_NAME:`postgres`}
    driverClassName: org.postgresql.Driver
    username: ${DATABSE_USERNAME:`postgres`}
    password: ${DATABSE_PASSWORD:`password`}
    dialect: POSTGRES

flyway:
  datasources:
    default:
      enabled: true

Now that the application is ready to use the postgres database. Lets create some tables , add some test data and create an endpoint to expose the data.

Database migrations

Create folders db/migrations inside resources (default path flyway looks into)

V1__schema.sql : creates tables

CREATE TABLE students (
    id   SERIAL PRIMARY KEY,
   name  VARCHAR(255) NOT NULL
);

CREATE TABLE scores(
    id INT NOT NULL,
    subject VARCHAR(10) NOT NULL,
    score INT NOT NULL,
    CONSTRAINT fk_student_id
            FOREIGN KEY (id)
                REFERENCES students (id)
);

CREATE TABLE grades(
    id INT NOT NULL,
    grade VARCHAR(1) NOT NULL,
    total INT NOT NULL,
    CONSTRAINT fk_student_id
            FOREIGN KEY (id)
                REFERENCES students (id)
);

V2__test_data.sql : adds test data

INSERT INTO students(id,name) VALUES (1,'student one');
INSERT INTO students(id,name) VALUES (2,'student two');
INSERT INTO students(id,name) VALUES (3,'student three');

INSERT INTO scores(id,subject,score) VALUES (1,'java',65);
INSERT INTO scores(id,subject,score) VALUES (1,'python',60);
INSERT INTO scores(id,subject,score) VALUES (2,'java',67);
INSERT INTO scores(id,subject,score) VALUES (2,'python',71);
INSERT INTO scores(id,subject,score) VALUES (3,'java',79);
INSERT INTO scores(id,subject,score) VALUES (3,'python',71);

INSERT INTO grades(id,grade,total) VALUES (1,'C',125);
INSERT INTO grades(id,grade,total) VALUES (2,'B',138);
INSERT INTO grades(id,grade,total) VALUES (3,'A',150);

Lets write simple code to fetch data from database.

Create packages domain, model, repository, service and create classes

domain -> StudentGrades

package com.micronaut.demo.domain;

import io.micronaut.core.annotation.Introspected;
import lombok.AllArgsConstructor;
import lombok.Data;

@Introspected
@Data
@AllArgsConstructor
public class StudentGrades {

    int id;
    String name;
    String grade;
    int total;

}

domain -> Subject

package com.micronaut.demo.domain;

import io.micronaut.core.annotation.Introspected;
import lombok.AllArgsConstructor;
import lombok.Data;

@Introspected
@Data
@AllArgsConstructor
public class Subject {

    String subject;
    int score;
}

model -> Student

package com.micronaut.demo.model;

import com.micronaut.demo.domain.Subject;
import io.micronaut.core.annotation.Introspected;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;

import java.util.List;

@Introspected
@Data
@AllArgsConstructor
@Builder(toBuilder = true)
public class Student {

    int id;
    String name;
    String grade;
    int total;
    List<Subject> subjects;

}

repository -> StudentRepository

package com.micronaut.demo.repository;

import com.micronaut.demo.domain.StudentGrades;
import com.micronaut.demo.domain.Subject;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.jdbc.runtime.JdbcOperations;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.transaction.annotation.ReadOnly;
import lombok.extern.slf4j.Slf4j;

import javax.transaction.Transactional;
import java.util.List;
import java.util.stream.Collectors;

@JdbcRepository(dialect = Dialect.POSTGRES)
@Slf4j
public class StudentRepository {

    private final JdbcOperations jdbcOperations;

    public StudentRepository(JdbcOperations jdbcOperations) {
        this.jdbcOperations = jdbcOperations;
    }

    @Transactional
    @ReadOnly
    public StudentGrades getStudentGrades(int id) {

        var selectSql = """
                SELECT
                student.id AS id,
                student.name AS name,
                grade.grade AS grade,
                grade.total AS total

                FROM
                students student
                INNER JOIN grades AS grade ON student.id = grade.id

                WHERE
                student.id=?;
                """;

        return jdbcOperations.prepareStatement(selectSql, statement -> {
            statement.setInt(1, id);
            var resultSet = statement.executeQuery();

            if (resultSet.next()) {
                return jdbcOperations.readEntity(resultSet, StudentGrades.class);
            } else {
                throw new RuntimeException("Not found");
            }
        });
    }

    @Transactional
    @ReadOnly
    public List<Subject> getSubjectScores(int id) {

        var selectSql = """
                SELECT
                student.id AS id,
                score.subject AS subject,
                score.score AS score

                FROM
                students student
                INNER JOIN scores AS score ON student.id = score.id
                                
                WHERE
                student.id=?;
                """;

        return jdbcOperations.prepareStatement(selectSql, statement -> {
            statement.setInt(1, id);
            var resultSet = statement.executeQuery();
            return jdbcOperations.entityStream(resultSet, Subject.class).collect(Collectors.toList());
        });
    }
}

service -> StudentService

package com.micronaut.demo.service;

import com.micronaut.demo.model.Student;
import com.micronaut.demo.repository.StudentRepository;
import jakarta.inject.Singleton;
import lombok.extern.slf4j.Slf4j;

@Singleton
@Slf4j
public class StudentService {

    private final StudentRepository studentRepository;

    public StudentService(StudentRepository studentRepository) {
        this.studentRepository = studentRepository;
    }

    public Student getStudentScoresById(int id){

        var grades = studentRepository.getStudentGrades(id);

        var subjects = studentRepository.getSubjectScores(id);

        return Student.builder()
                .id(grades.getId())
                .name(grades.getName())
                .grade(grades.getGrade())
                .total(grades.getTotal())
                .subjects(subjects)
                .build();

    }
}

resource -> StudentResource

package com.micronaut.demo.resource;

import com.micronaut.demo.model.Student;
import com.micronaut.demo.service.StudentService;
import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;
import io.micronaut.http.annotation.PathVariable;
import lombok.extern.slf4j.Slf4j;

import javax.validation.constraints.NotNull;

@Controller(value = "/student")
@Slf4j
public class StudentResource {

    private final StudentService studentService;

    public StudentResource(StudentService studentService) {
        this.studentService = studentService;
    }

    @Get("/{id}")
    public Student getStudentScoresById(@PathVariable @NotNull Integer id){

        return studentService.getStudentScoresById(id);

    }
}

Now our application is ready . Lets build , run and test

./gradlew clean build

./gradlew runcurl -- location -- request GET 'http://localhost:8080/student/1'

{"id":1,"name":"student one","grade":"C","total":125,"subjects":[{"subject":"java","score":65},{"subject":"python","score":60}]}

Lets us now prepare to deploy and run in heroku.

Heroku provides postgres as an add-on

Lets create a database from heroku-cli

heroku addons:create heroku-postgresql --region euCreating heroku-postgresql on ⬢ micronaut-heroku-demo... free
Database has been created and is available
...
Created postgresql-shallow-62392 as DATABASE_URL

This creates an environment variabe DATABASE_URL. We can check environement variables using heroku-cli

heroku config

DATABASE_URL:               postgres://{user}:{password}@{hostname}:{port}/{database-name}

We cannot use AS-IS in micronaut, so lets create new env variables via heroku-cli. Commands to get , set , delete variables

$ heroku config:get KEY

$ heroku config:set KEY=VALUE

$ heroku config:unset KEY

Now lets set the variables defined in application.yml , from DATABASE_URL value.

heroku config:set DATABSE_HOST={hostname}
heroku config:set DATABSE_PORT={port}
heroku config:set DATABSE_NAME={database-name}
heroku config:set DATABSE_USERNAME={user}
heroku config:set DATABSE_PASSWORD={password}

Lets check our values are present and have right values

micronaut-heroku-demo % heroku config
=== micronaut-heroku-demo Config Vars
DATABASE_URL:     postgres://{user}:{password}@{hostname}:{port}/{database-name}
DATABSE_HOST:     hostname
DATABSE_NAME:     database-name
DATABSE_PASSWORD: password
DATABSE_PORT:     port
DATABSE_USERNAME: user

Now our environment in heroku is ready to run our new application.

Commit , push and deploy the application.

curl -- location -- request GET 'https://micronaut-heroku-demo.herokuapp.com/student/1'

{"id":1,"name":"student one","grade":"C","total":125,"subjects":[{"subject":"java","score":65},{"subject":"python","score":60}]}

Now we have a micronaut application with postgres database running in heroku .

Happy Learning.