Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 38 ยท Data Access

Relationships and Other Data Stores

Map one-to-many and many-to-many relations in Micronaut Data, avoid the N+1 query trap, and see where JPA, R2DBC and MongoDB fit. Runnable H2 stand-ins included.

Real data has relationships: an author has many books, an order has many lines, a book has many tags and each tag belongs to many books. Mapping those links is where many data layers go wrong, usually by quietly running hundreds of extra queries. This lesson shows how Micronaut Data models relations, how to load them efficiently, and which other data modules exist for different kinds of storage.

The Micronaut framework is not installed on this site, so the framework code is read-only. The runnable programs use real SQL on H2: one proves the N+1 problem by counting queries, the other builds a many-to-many relation with a join table.

One-to-many and many-to-one

In SQL, the "many" side holds a foreign key to the "one" side. In Micronaut Data you can model that with @Relation:

Java
@MappedEntity
public class Author {
    @Id @GeneratedValue
    private Long id;
    private String name;

    @Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "author")
    private List<Book> books;
    // getters and setters
}

@MappedEntity
public class Book {
    @Id @GeneratedValue
    private Long id;
    private String title;

    @Relation(Relation.Kind.MANY_TO_ONE)
    private Author author;                 // stored in the column author_id
    // getters and setters
}

mappedBy names the property on the other side that owns the foreign key. Relation types are ONE_TO_ONE, ONE_TO_MANY, MANY_TO_ONE, MANY_TO_MANY and EMBEDDED. If you use the Hibernate module with jakarta.persistence, the equivalent annotations are @OneToMany, @ManyToOne and @ManyToMany.

Entities with relations are classes (not records) because relations may be loaded later and need to be set after construction. Micronaut Data deliberately does not lazy-load relations behind your back: it has no proxy objects and no open "session". A relation is either loaded by your query or it is not loaded, which makes behavior predictable and fast.

Loading relations: joins

You choose what to load with @Join on a repository method:

Java
@JdbcRepository(dialect = Dialect.POSTGRES)
public interface BookRepository extends CrudRepository<Book, Long> {

    @Join("author")
    List<Book> findByTitleContains(String text);          // books with their authors, one query

    @Join(value = "author", type = Join.Type.LEFT_FETCH)
    Optional<Book> findById(Long id);
}

Without @Join, book.getAuthor() has only the id filled in; the other fields are null. With @Join, the generator adds a SQL JOIN and maps the joined columns into the nested object, all in a single round trip. You can combine joins for deeper graphs (@Join("author") plus @Join("publisher")) and use @Join("tags") for collections.

The N+1 problem

Suppose you load 100 books, then call a method per book to fetch its author. That is one query for the list plus 100 for the authors: N+1 queries. Each query is fast, but the round trips add up, and the cost grows with your data. It is the most common reason a page that was fine in testing crawls in production.

The fix is to load related rows together (@Join, or an explicit JOIN in @Query) or in bulk (WHERE id IN (...)). The next stand-in makes the problem visible.

Stand-in 1: counting queries

The program wraps JDBC in a tiny helper that counts how many statements run. It loads ten books with their authors twice: the naive way (one author query per book) and the joined way. The counts are real; only the framework's annotations are missing.

Java N+1 queries versus one JOIN

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

With 10 books the difference is 11 queries versus 1. With 10,000 books it is 10,001 versus 1, and each of those would be a network round trip to a database server. Always check the number of queries a page runs.

Many-to-many

A book can have many tags and a tag many books. SQL expresses that with a join table holding two foreign keys. In Micronaut Data:

Java
@MappedEntity
public class Book {
    @Id @GeneratedValue
    private Long id;
    private String title;

    @Relation(value = Relation.Kind.MANY_TO_MANY)
    @JoinTable(name = "book_tag")
    private Set<Tag> tags;
}

When you save a book with its tags, Micronaut Data writes the rows of the join table. If the join table needs its own columns (such as the date a tag was added), model it as a separate entity with two many-to-one relations instead.

Stand-in 2: a join table and grouping

This program creates the three tables, links books and tags, and runs the two queries you would write behind a many-to-many mapping: "tags of a book" and "books of a tag". It also folds a flat join result into a map of lists, which is what an ORM does to build book.getTags().

Java A many-to-many join table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Other Micronaut data modules

The same repository style works for several storage technologies. Pick by the shape of your data and your team's skills:

Module Use it for
Micronaut Data JDBC Plain SQL databases with fast start-up and native-image friendliness
Micronaut Data JPA (Hibernate) Rich object graphs, existing JPA entities, JPQL
Micronaut Data R2DBC Reactive, non-blocking access to PostgreSQL, MySQL and others
Micronaut Data MongoDB Document storage with the same CrudRepository style
Micronaut Data Azure Cosmos / DynamoDB Cloud document and key-value stores
Micronaut Redis, Cache Caching and fast lookups; see the @Cacheable advice in AOP

A MongoDB entity looks very similar, which is the point:

Java
@MappedEntity
public record Note(@Id @GeneratedValue ObjectId id, String text, List<String> tags) {}

@MongoRepository
public interface NoteRepository extends CrudRepository<Note, ObjectId> {
    List<Note> findByTagsContains(String tag);
}

For R2DBC, return Mono and Flux from a ReactorCrudRepository, and combine it with the reactive return types from streaming and async. Choose R2DBC only when your whole stack is reactive; mixing blocking and reactive code is where most pain comes from.

Relations in Micronaut Data

ONE_TO_MANY One author, many books.
MANY_TO_ONE Each book points to its author.
MANY_TO_MANY A join table links both sides.

Relations are loaded only when you ask.

1 / 2

Practical advice

  • Load only the relations a use case needs; return DTO projections for lists (see queries, pagination and projections).
  • Keep foreign keys and indexes in your migrations, including an index on every foreign key column.
  • Avoid bidirectional relations unless you need both directions; they make serialization loop forever unless one side is ignored.
  • Decide cascade behavior (Relation.Cascade.PERSIST, UPDATE) consciously. Cascading deletes can remove far more than you expect.
  • Test with realistic volumes. N+1 problems never show up with three rows.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A page loads 200 books and then asks for each book's author separately. How many queries run?

    One query for the list plus one per book is the classic N+1 pattern: 1 + 200.
  2. 2. In Micronaut Data, what does @Join("author") on a repository method do?

    @Join makes the generated query load the related entity together with the main one.
  3. 3. How is a many-to-many relation stored in SQL?

    A join table holds one row per pairing of the two entities.
  4. 4. Which data module fits an all-reactive application that uses PostgreSQL?

    R2DBC gives non-blocking SQL access with Reactor types; blocking JDBC on the event loop would stall the server.

Summary

  • Model relations with @Relation (or JPA annotations in the Hibernate module); the many side holds the foreign key.
  • Micronaut Data does not lazy-load: relations are loaded by @Join or an explicit query, otherwise only ids are present.
  • N+1 queries are the main performance trap; count queries and use joins or bulk loading.
  • Many-to-many uses a join table; add an entity when the link has its own data.
  • The same repository style works for JDBC, JPA, R2DBC, MongoDB and cloud stores.
  • Index foreign keys, keep cascades deliberate and test with realistic data sizes.

Next: Configuration Properties and Files