Postgres SQL with Micronaut on Heroku

Irfan Ali Shaik
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)

  1. 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.
  2. hikari is a fast, simple, reliable. HikariCP is a “zero-overhead” production ready JDBC connection pool.
  3. flyway makes database migrations easy.
  4. 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 run
curl -- 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.

--

--