Room Relationship Recap
In this post, we’re going to explore some advanced concepts of the Room Persistence Library. Room is a great tool for storing complex data for your Android applications inside a SQLite database. As you begin to store more data in your applications though, it can be difficult to determine how to organize all of it.
We’re going to demistify database organization, and break down everything you need to know about database relationships in the Room library.
This blog post is adapted from a YouTube video I made for AsyncAndroid.
The Problem
To start, let’s look at a very common limitation we will encounter when working with a SQLite database. Here is a basic Room entity with three fields:
@Entity
data class Student(
@PrimaryKey(autoGenerate = true)
val studentId: Long = 0L,
val firstName: String = "",
val lastName: String = ""
)
Room will be able to take this entity, and successfully map it to a SQLite table with three columns:
This works because Room is able to take primitive data types (numbers, strings, booleans) and map them to valid SQLite data types. We aren’t able to store anything more complex than that in a single table. Let’s try to make Student
more complex and include an Address
field:
@Entity
data class Student(
@PrimaryKey(autoGenerate = true)
val studentId: Long = 0L,
val firstName: String = "",
val lastName: String = "",
val address: Address? = null
)
data class Address(
val streetName: String = "",
val streetNumber: String = ""
)
Room will not be able to convert this to a SQLite table, because Address is not a valid SQLite data type:
This is the baseline for the rest of this post. While SQLite can’t store complex data in a single table, our applications still need to store information about students such as addresses, and the classes they’re taking. The solution to storing this data can vary, so let’s explore each of the options we have.
Embedded Properties
The previous example of storing a Student with their Address actually has a quick, one line, solution, which is the @Embedded annotation. This will tell the Room library to take each field from the @Embedded
property, and map it to a column inside the entity we’re creating. We can demonstrate this clearly by also specifying a prefix
for the embedded columns.
This means we can take this code:
@Entity
data class Student(
@PrimaryKey(autoGenerate = true)
val studentId: Long = 0L,
val firstName: String = "",
val lastName: String = "",
@Embedded(prefix = "address_")
val address: Address? = null
)
data class Address(
val streetName: String = "",
val streetNumber: String = ""
)
To create a database table with these columns:
Embedded Property Limitations
It’s really great that we can get a one line solution to this problem with embedded properties, but it’s important to understand their own limitations, too. Since these are just additional columns on our student table, we don’t have a way to safely share this data across entities. Let’s consider two students, Adam and John, who live at the same address:
studentName | streetNumber | streetName |
---|---|---|
Adam | 111 | Wall Street |
John | 111 | Wall Street |
If we go into the database, it’s possible that we only update one of the rows, causing the students to have different addresses:
studentName | streetNumber | streetName |
---|---|---|
Adam | 111 | Wall Street |
John | 112 | Wall Street |
Looking at the database, we have no idea which one is right. This problem is something called an update anomaly. Now, if you aren’t concerned about sharing information between rows, and you are just looking for a way to better structure your Room entities, then embedded properties are the perfect solution.
If you need stronger data integrity, we need to move this information into a different entity, and create a link between the two. This connection between two entities is called an entity relationship. We’re going to look at three types of relationships in this post.
One-To-Many Relationships
The first relationship to discuss is a one-to-many relationship. This is when you have exactly one instance of a parent entity, and zero or more instances of a child entity that relates to it. An example could be a Student
, and any Vehicle
s that they have registered to park at their university. Using our knowledge of creating Room entities, we may arrive on a solution kind of like this one:
/**
* @property[ownerId] This property should map to a valid id in the [Student.kt] entity.
*/
@Entity
data class Vehicle(
@PrimaryKey(autoGenerate = true)
val vehicleId: Long = 0L,
val ownerId: Long = 0L,
val vehicleType: String = ""
)
This entity will compile, and we will be able to insert vehicles for valid students, query them, and all the functionality we want. There is a small problem here, though, which is that the two entities aren’t actually linked at a database level. This means:
- We can add vehicle entities with invalid student ids that don’t actually exist.
- If we delete a student, the vehicles associated with them still exist in the database and we end up with orphaned records.
Creating One-To-Many Relationships
To solve this problem, we need to define a foreign key relationship between the two tables. To create one of these, we need to specify three things:
- The entity we want to reference.
- The columns in the child entity that will be used to define this relationship.
- The columns in the parent entity that should match the child columns to ensure the relationship.
Altogether, we will end up with this code:
@Entity(
foreignKeys = [
ForeignKey(
entity = Student::class,
childColumns = ["ownerId"],
parentColumns = ["studentId"]
)
]
)
data class Vehicle(
@PrimaryKey(autoGenerate = true)
val vehicleId: Long = 0L,
val ownerId: Long = 0L,
val vehicleType: String = ""
)
This means that we will be unable to insert into the Vehicle
entity unless we use an ownerId that does exist inside the Student
table already.
Optionally, we can add a fourth property which is what to do when a row is deleted from the Student table if there are associated Vehicle records. You can learn more about that here.
Querying One-To-Many Relationships
A common query with this structure is to request a student and the vehicles associated with them. This may seem like an intimidating challenge in the Room library, like you might have to learn join queries and write some complex SQL, but the Room library takes care of a lot of that for us. Let’s try to build a query that returns the following response class:
data class StudentWithVehicles(
val student: Student,
val vehicles: List<Vehicle>
)
In order for Room to return this entity from a query, we need to supply two annotations.
- The
@Embedded
annotation on student, so that Room will be able to map each column from the response to the Student entity, much like we saw with embedded properties. - The
@Relation
annotaion on vehicles, so that Room will understand how the two entities are related.
The resulting code is like this:
data class StudentWithVehicles(
@Embedded
val student: Student,
@Relation(
parentColumn = "studentId",
entityColumn = "ownerId"
)
val vehicles: List<Vehicle>
)
Next, we can go into our DAO and write a query to fetch every student and their vehicles:
@Dao
interface UniversityDAO {
@Query("SELECT * FROM Student")
@Transaction
fun fetchStudentsWithVehicles(): LiveData<List<StudentWithVehicles>>
}
You’ll notice two annotations here as well:
- The
@Query()
annotation which defines every student we want returned for the query. - The
@Transaction
annotation which is required because Room is actually going to run two queries behind the scenes - so they must run inside a database transaction.
Room Behind The Scenes
It’s rare that you’ll ever dive into the generated code from a library like Room, but it may be helpful in scenarios like this one to understand what each of the annotations are actually doing. We can see all of the generated code for the UniversityDAO_Impl.java
file in this gist, but let’s just highlight what actually happens when we call fetchStudentsWithVehicles()
.
- First, we run the SQL query specified in our
@Query()
annotation, which in this example is to request all students. Seen on line 30. - Next, we get the index of all the relevant Student columns, thanks to the
@Embedded
annotation in our response object. Seen on lines 39 to 43. - Once we’ve requested all of the students, we request all vehicles that belong to one of those students. Room knows how to relate the two tables thanks to our
@Relation
annotation. This is the second database query that’s run, and also why we needed the@Transaction
annotation. Seen on line 132. - After running these queries, the Room library will then take the responses and map everything into the neat data class that we’ve defined and return a list of them.
One-To-One Relationships
Having looked at one type of database relationship, we’ll notice that the syntax to create, enforce, and query the next two types of relationships will be quite similar.
A one-to-one relationship is when you have exactly one instance of a parent and a child entity. An example would be a Student
, and their university Application
, if they can only apply once.
This may seem counter intuitive at first, and it’s reasonable to wonder why you wouldn’t just put all of the information in one table. There’s a couple reasons we might do that:
- Data size concerns. Let’s say we want to request information about all of the students, but we don’t need to care about their applications. Separating the data into two entities allows us to request only the data we actually need.
- Security. This may not be common in Android applications, but in other database systems you may want some information to be in a separate database that has different permissions, but still keep a one-to-one relationship with information in another database.
Creating One-To-One Relationships
This type of relationship is hard to enforce perfectly. We can’t create a link between two entities directly because we don’t have a way to insert into two tables at the exact same time. We can get close, though, by limiting the number of Application
that can relate to a student.
If we add a unique index on our Application
entity, we can enforce that any given studentId
only appears once inside the applicaiton table:
@Entity(
foreignKeys = [
ForeignKey(
entity = Student::class,
parentColumns = ["studentId"],
childColumns = ["studentId"]
)
],
indices = [Index(value = ["studentId"], unique = true)]
)
data class Application(
@PrimaryKey(autoGenerate = true)
val applicationId: Long = 0L,
val studentId: Long = 0L,
val applicationText: String = ""
)
There is still potential for this to be a one-to-zero relationship, if you insert a student but never a corresponding application, so keep that in mind as you develop your applications.
Querying One-To-One Relationships
The query code for this will look very similar to the last example! There is only one difference:
- The response object references a single
Application
record, and not a list like it did in the last example.
Here is the code:
// StudentWithApplication.kt
data class StudentWithApplication(
@Embedded
val student: Student,
@Relation(
parentColumn = "studentId",
entityColumn = "studentId"
)
val application: Application
)
// UniversityDAO.kt
@Query("SELECT * FROM Student")
@Transaction
fun fetchStudentsWithApplication(): LiveData<List<StudentWithApplication>>
We still need the @Transaction
annotation because a second query is run behind the scenes. If we dive into the generated code for this method, as well, we can understand potential corner cases that we’ll encounter here:
- If there is no application associated with a student, this query will return null and potentially crash. The solution is to make
application
property nullable insideStudentWithApplication
. - If there is only one application associated with a student, this will return as expected.
- If there happens to be more than one application per student (which won’t be possible in our example), then Room will return the last
application
that it finds.
Many-To-Many Relationships
The last relationship type to discuss is a many-to-many relationship. This is when you have many instances of a parent entity, and many instances of a child entity. An example would be students and classes. A student can take more than one class, and a class can have more than one student.
To create this entity, we need something called a joining table. We can’t link students and classes directly, we have to go through a third table that defines the relationships between them. We can do that with a third entity called ClassEnrollments
. Here is what that relationship will look like:
Creating Many-To-Many Relationships
Let’s look at the entity code required for ClassEnrollments
. We need to do the following things:
- Create a ForeignKey relationship between this and
Student
. - Create a ForeignKey relationship between this and
Class
. - Create a composite primary key between (studentId, classId) so that we can ensure a student isn’t taking a class twice.
Here is the resulting entity:
@Entity(
primaryKeys = ["studentId", "classId"],
foreignKeys = [
ForeignKey(
entity = Student::class,
parentColumns = ["studentId"],
childColumns = ["studentId"]
),
ForeignKey(
entity = Class::class,
parentColumns = ["classId"],
childColumns = ["classId"]
)
]
)
data class ClassEnrollments(
val studentId: Long,
val classId: Long
)
Querying Many-To-Many Relationships
You guessed it, the query code here is once again almost identical to the previous example. The only new addition for requesting a many-to-many response is to specify the joining table using the associateBy
property inside the @Relation
annotation:
data class StudentWithClasses(
@Embedded
val student: Student,
@Relation(
parentColumn = "studentId",
entityColumn = "classId",
associateBy = Junction(value = ClassEnrollments::class)
)
val classes: List<Class>
)
The query itself remains consistent with what we’ve already done:
@Query("SELECT * FROM Student")
@Transaction
fun fetchStudentsWithClasses(): LiveData<List<StudentWithClasses>>
Recap
Now you have everything in your toolbox to understand how to structure complex data within a SQLite database using room. We know the use cases for each time of relationship, how to enforce them at a database level, and how to query each one. If you have any questions, or want to learn more about the Room library, please let me know in the comments below! You can also reach out to me on Twitter.
If you’d like to learn more about database organization as a concept, you can read my series on Effective Database Design at dev.to.