Room's @Relation: querying one-to-many data on Android without N+1 queries

A budgeting app has categories, and each category has entries. A pantry app has products, and each product has a history of scans. Almost every local-first app has this shape somewhere: one row that owns many other rows. The naive way to load that in Room — fetch the parents, then loop and fetch each parent’s children — is an N+1 query bug waiting to happen. Room has an annotation that fixes this properly, and it’s smaller than people expect: @Relation.

The query you shouldn’t write

Say you’re building Granyn’s spending-by-category screen. You have a Category table and an Entry table, where every entry points back to its category via categoryId. The instinct is to fetch categories, then ask the DAO for each category’s entries in a loop:

val categories = categoryDao.getAll()
val result = categories.map { category ->
    category to entryDao.getByCategory(category.id) // one query per category
}

That’s N+1: one query for the category list, then one more query per category. With five categories it’s invisible. With a year of history split across a dozen categories, it’s a dozen round trips to SQLite on every screen load, each one paying its own query-planning overhead for no reason.

What @Relation actually generates

@Relation doesn’t magically turn this into a SQL JOIN. What it does is smarter for this shape of data: it generates code that runs two queries total, no matter how many parents you have. The first fetches the parents. The second fetches all the children in one shot, filtered with a WHERE categoryId IN (...) built from every parent id at once.

The Kotlin side is a wrapper class that holds a parent and its children:

data class CategoryWithEntries(
    @Embedded val category: Category,
    @Relation(
        parentColumn = "id",
        entityColumn = "categoryId",
    )
    val entries: List<Entry>,
)

@Embedded flattens Category’s own columns into the result. @Relation tells Room which column on the parent (id) matches which column on the child (categoryId) — the same foreign-key relationship Granyn’s schema already expresses, just declared for Room’s query builder instead of hand-written SQL.

The DAO method is close to a plain query, with one addition:

@Transaction
@Query("SELECT * FROM categories")
fun getCategoriesWithEntries(): Flow<List<CategoryWithEntries>>

@Transaction matters here and it’s easy to skip by accident. Without it, the parent query and the batched child query run as two independent reads — if a write lands on the entries table between them, you can get a category list and an entries list that briefly disagree with each other. Wrapping both in a transaction guarantees the pair is read from one consistent snapshot.

The part that still surprises people: batching has a limit

SQLite caps the number of variables allowed in a single statement — historically 999, higher on recent versions but still finite. If you have more parent rows than that, Room doesn’t fail; it silently splits the IN (...) clause into multiple queries and stitches the results back together. For a category list that’s never going to happen, but if you apply this same pattern somewhere with thousands of parents (a product catalog, say), the query count quietly stops being exactly 2. Worth knowing before you assume “two queries” is a hard guarantee at every scale.

@Relation is read-only

The generated method only builds the combined object for reads. There’s no @Insert or @Update equivalent that understands CategoryWithEntries as a unit — you still insert a Category through CategoryDao and an Entry through EntryDao, exactly as you would without the relation. @Relation is a query-time convenience, not a new persistence model. Trying to reuse the wrapper class for writes is the most common way people get confused by it.

When to skip it entirely

Not every one-to-many read belongs behind @Relation. If the screen genuinely needs every entry — a category’s transaction list, say — it’s the right tool: two queries, correctly batched, no N+1. But if all you need is a number — this month’s total per category for a pie chart — loading every Entry into memory just to sum them in Kotlin is wasted work. A raw aggregate query does the same job without ever materializing the rows:

@Query("""
    SELECT categoryId, SUM(amountMinor) AS total
    FROM entries
    WHERE at BETWEEN :start AND :end
    GROUP BY categoryId
""")
fun monthlyTotals(start: Long, end: Long): Flow<List<CategoryTotal>>

The rule of thumb: use @Relation when the UI needs the actual child rows, and drop to a GROUP BY query the moment all you need is a number derived from them. Loading a full object graph to compute a sum is the local-database version of over-fetching, and SQLite will happily do the summing for you if you ask it directly instead of asking Kotlin to.

@Relation earns its place for the same reason the rest of Room does: it removes an entire category of correctness bug — the N+1 loop — without asking you to write the join by hand. Two queries, batched correctly, wrapped in a transaction. That’s the whole trick.