Postgres SQL with Micronaut on Heroku

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")
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
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)
);
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);
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;

}
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;
}
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;

}
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());
});
}
}
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();

}
}
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);

}
}
./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}]}
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
heroku config

DATABASE_URL: postgres://{user}:{password}@{hostname}:{port}/{database-name}
$ heroku config:get KEY

$ heroku config:set KEY=VALUE

$ heroku config:unset KEY
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}
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
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}]}

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store