Database relations with Room
An important part of designing a relational database is splitting the data into related tables and pulling the data together in meaningful ways. Starting with Room 2.2 (now stable) we have support for all possible relations between tables: one-to-one, one-to-many and many-to-many, with one annotation: @Relation.
One-to-one relations
Let’s say that we live in a (sad) world where a person can own only one dog and a dog can have only one owner. This is a one-to-one relation. To model this in a relational database, we create two tables: Dog and Owner, where the Dog table has a reference to the owner id, or the Owner has a reference to a dog id. In Room, we create two entities:
@Entity
data class Dog(
@PrimaryKey val dogId: Long,
val dogOwnerId: Long,
val name: String,
val cuteness: Int,
val barkVolume: Int,
val breed: String
)@Entity
data class Owner(@PrimaryKey val ownerId: Long, val name: String)
Let’s say that we want to display the list of all dogs and their owners on the screen. To do this, we would create a DogAndOwner data class:
data class DogAndOwner(
val owner: Owner,
val dog: Dog
)To query this using SQLite, we would need to 1) run two queries: one that gets all owners, and one that gets all dogs based on owner ids and then 2) handle the object mapping.
SELECT * FROM OwnerSELECT * FROM Dog
WHERE dogOwnerId IN (ownerId1, ownerId2, …)
To get a List<DogAndOwner> using Room, we don’t need to implement the two queries ourselves and handle the object mapping, but rather, use the @Relation annotation.
In our example, since Dog has the owner’s information, we add the @Relation annotation to the dog variable,: specifying that the ownerId column on the parent (i.e. the Owner entity) corresponds to the dogOwnerId:
data class DogAndOwner(
@Embedded val owner: Owner,
@Relation(
parentColumn = "ownerId",
entityColumn = "dogOwnerId"
)
val dog: Dog
)Our Dao is now simplified to:
@Transaction
@Query("SELECT * FROM Owner")
fun getDogsAndOwners(): List<DogAndOwner>Note: Because Room runs the two queries for us under the hood, add the @Transaction annotation, to ensure that this happens atomically.
One-to-many relations
Let’s say that an owner can have multiple dogs (yay!); we’d have a one-to-many relation between Dog and Owner. The database schema we previously defined doesn’t change — we still have the same tables, since the relating key is already in the “many” table of the relationship.
Now, to display the list of owners with their dogs, we need to create a new data class to model this:
data class OwnerWithDogs(
val owner: Owner,
val dogs: List<Dog>
)To avoid running two separate queries, we can define a one-to-many relation between Dog and Owner, by annotating the List<Dog> with @Relation as before:
data class OwnerWithDogs(
@Embedded val owner: Owner,
@Relation(
parentColumn = "ownerId",
entityColumn = "dogOwnerId"
)
val dogs: List<Dog>
)The Dao becomes:
@Transaction
@Query("SELECT * FROM Owner")
fun getDogsAndOwners(): List<OwnerWithDogs>Many-to-many relations
Now suppose we live in a perfect world where an owner can have multiple dogs, and that a dog can have multiple owners. To model this schema, our Dog and Owner tables are not enough. Since a dog can have multiple owners, we need to have multiple entries of the same dog id, matching to different owner ids. Because dogId is the primary key in Dog, we can’t insert multiple dogs with the same id. To overcome this, we need to create an associative table (also known as cross-reference table) that keeps (dogId,ownerId) pairs:
@Entity(primaryKeys = ["dogId", "ownerId"])
data class DogOwnerCrossRef(
val dogId: Long,
val ownerId: Long
)If we now want to get the list of all owners with dogs: List<OwnerWithDogs>, using just SQLite queries, we need to write two queries: one that gets all owners and one that joins the Dog and the DogOwnerCrossRef tables:
SELECT * FROM OwnerSELECT
Dog.dogId AS dogId,
Dog.dogOwnerId AS dogOwnerId,
Dog.name AS name,
_junction.ownerId
FROM
DogOwnerCrossRef AS _junction
INNER JOIN Dog ON (_junction.dogId = Dog.dogId)
WHERE _junction.ownerId IN (ownerId1, ownerId2, …)
To implement this in Room, we need to update our OwnerWithDogs data class and tell Room that in order to get the Dogs, it needs to use the DogOwnerCrossRef associate table. We reference the table by using a Junction:
data class OwnerWithDogs(
@Embedded val owner: Owner,
@Relation(
parentColumn = "ownerId",
entityColumn = "dogId",
associateBy = Junction(DogOwnerCrossRef::class)
)
val dogs: List<Dog>
)In our Dao, we need to select from Owners and return the right data class:
@Transaction
@Query("SELECT * FROM Owner")
fun getOwnersWithDogs(): List<OwnerWithDogs>Advanced relation use cases
When using the @Relation annotation, Room infers the entity to use from the type of the annotated property by default. For example, until now we annotated a Dog (or a List<Dog>) with @Relation, telling Room how to model the class and which columns to query
If we want to return a different object, for example a Pup, that is not an entity but contains some of the fields, we can specify the entity to use in the @Relation annotation:
data class Pup(
val name: String,
val cuteness: Int = 11
)data class OwnerWithPups(
@Embedded val owner: Owner,
@Relation(
parentColumn = "ownerId",
entity = Dog::class,
entityColumn = "dogOwnerId"
)
val dogs: List<Pup>
)
If we want to return only specific columns from an entity you need to tell Room which these are by defining them in the projection property of the @Relation. For example, let’s say that we just want to get the names of all the dogs in our OwnerWithDogs data class. Since we would need a List<String>, Room can’t deduce whether those strings correspond to the name or to the breed, so we need to specify the column in the projection:
data class OwnerWithDogs(
@Embedded val owner: Owner,
@Relation(
parentColumn = "ownerId",
entity = Dog::class,
entityColumn = "dogOwnerId",
projection = ["name"]
)
val dogNames: List<String>
)If you want to define a stricter relationship between the dogOwnerId and ownerId, independent of what kind of relation you’re creating, use a ForeignKey constraint between the fields. Keep in mind that SQLite foreign keys define indices and can have cascading triggers that update or delete entries in your tables. So decide whether you want to use foreign keys based on whether you do want this kind of functionality in your database.
Whether you need one-to-one, one-to-many or many-to-many support, Room has you (and your doggos) covered with one annotation: @Relation. Find out more about Room 2.2 features from our Android Dev Summit ’19 talk:
