Micronaut Data is a simple ORM provided by the micronaut team, Micronaut Data decouples the database structure from the application code, providing a more flexible and maintainable solution. It maps objects to database tables and defines relationships between the objects. The Micronaut Documentation doesn’t cover this in detail on how to actually use this feature, in this blog we will see how to use them.

Association in Databases

Association in relational databases refers to the relationship between two or more tables. There are several types of associations in SQL, including:

1. One-to-One: In this association, each row in one table is related to only one row in another table. This type of association is often used to represent a unique relationship between two entities, such as a relationship between a person and their passport.
2. One-to-Many: In this association, one row in one table can be related to many rows in another table. This type of association is often used to represent a hierarchical relationship between two entities, such as a relationship between a department and its employees.
3. Many-to-Many: In this association, many rows in one table can be related to many rows in another table. This type of association is often used to represent a relationship between two entities that can have multiple occurrences, such as a relationship between a student and the courses they take.

These relation between databases can be represented by using Micronaut Data at application level by using few annotation.

Fetching details with association

Micronaut provides a provision to fetch primary table with its association by overriding CoroutineCrudRepository methods.

--> consider you have following tables
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE courses (
  id INT PRIMARY KEY AUTO_INCREMENT,
  course_name VARCHAR(50) NOT NULL,
  start_date DATE NOT NULL,
  student_id INT NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

To represent this tables in Micronaut we define following class

@MappedEntity(value = "students")
data class Student(
    @field:Id @GeneratedValue var id: Long?,
    var firstName: String,
    var lastName: String,
    var email: String
)

@MappedEntity(value = "students")
data class Course(
    @field:Id @GeneratedValue var id: Long?,
    var courseName: String,
    var startDate: Date,
    var studentId: Long
)

Now these entities will have their own Repositories

@R2dbcRepository(dialect = Dialect.POSTGRES)
interface StudentRepository : CoroutineCrudRepository<Student, Long> {}

This interface have few of the common methods already implemented, eg. findBy{column_name}({column_value}) we can override these function to include association relation to get fetched along with the primary table.

This can be achieved by following these steps.

Define the association relation

Include courses in student class

@MappedEntity(value = "students")
data class Student(
    @field:Id @GeneratedValue var id: Long?,
    var firstName: String,
    var lastName: String,
    var email: String,
    @Relation(Relation.Kind.ONE_TO_MANY, mappedBy = "studentId")
    var courses: List<Course>? = null,
)

Override the function on which you want to include association

@R2dbcRepository(dialect = Dialect.POSTGRES)
interface StudentRepository : CoroutineCrudRepository<Student, Long> {
    @Join("courses", type = Join.Type.LEFT_FETCH)
    override suspend fun findById(id: Long): Student?
}

Here @Join Annotation help us to tell Micronaut to have a left join while fetching the data from students table.