Object Relationships in Room ORM Library for Android

Object Relational Mapping (ORM) libraries are common tools for back-end development for interacting with a relational database. As the name implies they help facilitate mapping your data that is stored in a relational database to objects used in your application code. Room is the standard ORM library that sits between your Android application and its SQLite database.

While not necessary, ORMs provide a lot of functionality out of the box to save the developer time. Using an ORM allows the developer to interact with the database with the same language used in the back end. Instead of having to concern themselves with creating complex SQL queries, interactions with the database can look as simple as a normal function call.

ORMs typically support object references between entities. Or more specifically, nested entity relationships. For example, consider the following Java code snippet for a Hibernate entity relationship (Hibernate is another popular ORM):

@Entity
public class StudentEntity {

    @Id
    @Column(name = "STUDENT_ID")
    public Long id;

    //....

    @JoinColumn(name = "ADDRESS_ID")
    public AddressEntity address;

    // ...
}

@Entity
public class AddressEntity {

    @Id
    @Column(name = "ADDRESS_ID")
    public Long id;

    @Column(name = "STREET_NAME")
    public String streetName;

    // ...
}

The StudentEntity object has an AddressEntity object as a member of its class. This means that when a StudentEntity is fetched from the database the AddressEntity object will also be fetched, either eagerly or lazily, and be readily available to access from the StudentEntity object.

someStudent.address.streetName

While Room provides a lot of features, it intentionally does not support this behavior. The focus of this article is to see how we can establish and manage object relationships in Room without relying on nested entities. As we'll see, there are a few different approaches, each valid in its own way depending on the use case.

💡
In case you are wondering why exactly Room doesn't allow this behavior, there is a section in the Android documentation explaining their reasoning. However, in short, nested models can either be loaded lazily, meaning they are loaded at the time of access, or eagerly, meaning all entities are loaded at once. On Android, lazy loading would occur on the UI thread, and even a fast database query could result in the system being unable to meet the 16 ms time limit to calculate and redraw a frame. Eager loading is the other option, but this could result in lots of data being loaded even if the UI doesn't require it, therefore wasting memory.

I will provide code snippets throughout the article, however, a full sample project can be found here on my GitHub. Since the focus of this article is on the database and not the UI, the UI is bare bones, but it provides enough functionality to demonstrate the different approaches.

@Embedded Annotation

The easiest approach is to embed the nested object into the entity. This is done by annotating the nested object with @Embedded which tells Room to create additional columns in the parent entity's table for all the fields in the nested object. Essentially, Room is destructuring the nested object and treating all its fields as if it were part of the parent entity.

The following shows an example model setup. Notice that the nested address object is a simple data class, not an entity. I did add @ColumnInfo annotations to the fields in the nested object just for naming clarity, but it does not affect functionality.

@Entity(tableName = "studentWithEmbedded")  
data class StudentWithEmbeddedEntity(  
    @PrimaryKey(autoGenerate = true)  
    val id: Long = 0,  

    @ColumnInfo(name = "first_name")  
    val firstName: String,  

    @ColumnInfo(name = "last_name")  
    val lastName: String,  

    @Embedded  
    val address: AddressForEmbedded,  
)

data class AddressForEmbedded(  
    @ColumnInfo(name = "house_number_embedded")  
    val houseNumber: String,  

    @ColumnInfo(name = "street_name_embedded")  
    val streetName: String,  

    @ColumnInfo(name = "street_type_embedded")  
    val streetType: String,  

    @ColumnInfo(name = "city_embedded")  
    val city: String,  

    @ColumnInfo(name = "state_embedded")  
    val state: String,  

    @ColumnInfo(name = "postal_code_embedded")  
    val postalCode: String,  
)

Now when Room creates the studentWithEmbedded table in the database, every field in the nested AddressForEmbedded object will be its own column. Therefore, a full list of columns in the studentWithEmbedded table will be:

• id

• first_name

• last_name

• house_number_embedded

• street_name_embedded

• street_type_embedded

• city_embedded

• state_embedded

• postal_code_embedded

Pros and Cons

Pros:

• Probably the simplest of all the approaches

• Can handle one-to-one relationships, one student has only one address

• The address data is queryable

Cons:

• Can not handle a one-to-many, or many-to-many relationship. This means a row in the studentWithEmbedded table can only have one and only one address association.

• If multiple students are associated with the same address, then multiple rows in the studentWithEmbedded table will have identical values in the associated address columns, i.e., duplicate data.

Type Converters

Another approach is to use a Type Converter to serialize the child object to a JSON string, and then store the whole object as a string in one of the table's columns for the parent entity. When the parent entity is fetched from the database the Type Converter will deserialize the string into the correct object.

The model setup is almost the same as before. The only real differences are the lack of the @Embedded annotation, and the addition of an id field in the address object. While an ID is not technically necessary for this to work, this approach can handle one-to-many and many-to-many relationships, so an ID is helpful.

@Entity(tableName = "studentWithJson")  
data class StudentWithJsonEntity(  
    @PrimaryKey(autoGenerate = true)  
    val id: Long = 0,  

    @ColumnInfo(name = "first_name")  
    val firstName: String,  

    @ColumnInfo(name = "last_name")  
    val lastName: String,  

    @ColumnInfo(name = "address_json")
    val address: AddressForJson,  
)

data class AddressForJson(  
    val id: Long = 0,  
    val houseNumber: String,  
    val streetName: String,  
    val streetType: String,  
    val city: String,  
    val state: String,  
    val postalCode: String,  
)

With the models set, we can then create a Type Converter to go back and forth between AddressForJson and String. For this example, I'm using the Moshi library to handle the JSON conversions.

@ProvidedTypeConverter  
class MyRoomTypeConverters(private val moshi: Moshi) {  

    @TypeConverter  
    fun fromAddressJson(json: String): AddressForJson {  
        return moshi.adapter(AddressForJson::class.java).fromJson(json)!!  
    }  

    @TypeConverter  
    fun toAddressJson(address: AddressForJson): String {  
        return moshi.adapter(AddressForJson::class.java).toJson(address)  
    }  
}

Room will create the StudentWithJsonEntity database table with the following columns:

• id

• first_name

• last_name

• address_json

Pros and Cons

Pros:

• Still relatively simple

• Can handle a one-to-one relationship.

• Can handle a one-to-many relationship, one student can have multiple addresses. In this case, the entity would instead have a field for List<AddressForJson>.

Cons:

• The same duplicate data problem as before, if multiple students are associated with the same address, then multiple rows in the studentWithJson table will have identical values in the address_json data.

• From the duplicate data issue in the first point, this approach technically can't handle a many-to-many relationship. While students can have multiple addresses and an address can be used by multiple students, since all addresses are stored in their separate JSON strings, there are no relationships between two identical addresses from different student rows.

• The address data is not queryable.

Intermediate Models

The last approach is the most complex, but also the most powerful since it best leverages Room and SQLite. An important distinction between this approach and the previous two is that both objects we are trying to relate are entities with their own respective tables. We can define relationships between entities by using a few different components together:

1. Room's @Relation annotation

2. Room's @ForeignKey annotation

3. Room's @Embedded annotation

4. Intermediate model

Unlike the previous two approaches where we try and have a nested relationship, this approach avoids that by using a third model called the intermediate model. The intermediate model contains two fields, one for the parent entity and one for the child entity(s).

The @Relation annotation is used on the child entity in the intermediate model, and it is important because it defines how to relate both tables when retrieving data.

The @ForeignKey annotation is important because it helps enforce the relational structure when inserting/modifying data. If you try and insert a row into the child table with a value for the foreign key column that doesn't match any primary key in the parent table an exception will be thrown.

android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)

Ultimately, the power of this approach is that depending on how we use these components we can model a one-to-one, one-to-many, or many-to-many relationship.

One-to-One Relationship

Starting with the simplest relation type, consider again the student and address objects.

@Entity(tableName = "studentWithOneToOneRelation")  
data class StudentWithOneToOneRelationEntity(  
    @PrimaryKey(autoGenerate = true)  
    val studentId: Long = 0,  
    val firstName: String,  
    val lastName: String  
)

@Entity(tableName = "addressForOneToOneRelation")
data class AddressForOneToOneRelationEntity(  

    @PrimaryKey(autoGenerate = true)  
    val id: Long = 0,  
    val houseNumber: String,  
    val streetName: String,  
    val streetType: String,  
    val city: String,  
    val state: String,  
    @ColumnInfo(name = "postal_code")  
    val postalCode: String,  

    val studentOwnerId: Long  
)

There are two things to note:

1. The StudentWithOneToOneRelationEntity does not have a nested AddressForOneToOneRelationEntity field.

2. The AddressForOneToOneRelationEntity has a reference (studentOwnerId) that points back to the primary key for StudentWithOneToOneRelationEntity.

With our two entities defined, we now need to create the intermediate object:

data class StudentWithAddressOneToOneIntermediate(  
    @Embedded  
    val student: StudentWithOneToOneRelationEntity,  

    @Relation(  
        parentColumn = "studentId",  
        entityColumn = "studentOwnerId"  
    )  
    val address: AddressForOneToOneRelationEntity?  
)

As discussed before, there are only two fields, one for student and one for address. The student field is marked with @Embedded since it is the parent, and the address field has the @Relation annotation, since it is the child. The @Relation annotation needs to be configured with two properties: parentColumn, which is the name of the primary key column in the parent entity, and entityColumn, which is the name of the column in the child entity that references the parent entity's primary key.

With the relationship configured, we can define a query function in the DAO interface for the student.

@Dao  
interface StudentWithOneToOneRelationDao {  

    // ... 

    @Transaction  
    @Query("SELECT * from studentWithOneToOneRelation")  
    fun getAllStudentsWithAddresses(): List<StudentWithAddressOneToOneIntermediate>  
}

The function is annotated with @Query that selects from the student table as we would normally do. However, there are two important differences:

1. The list that is being returned contains intermediate objects (StudentWithAddressOneToOneIntermediate) instead of the table's entity (StudentWithOneToOneRelationEntity)

2. The query is marked as a transaction. This is because Room will query the fields in the intermediate object separately, therefore it needs to make sure that all separate query operations are complete before returning.

Foreign Key Constraint

What we've done so far is enforce a one-to-one relationship between StudentWithOneToOneRelationEntity and AddressForOneToOneRelationEntity, but only when retrieving data. We still need to complete the circle and enforce the one-to-one relationship during the time of insertion/modification, because right now there are two issues. First, it's still possible to insert a row into the address table where the studentOwnerId value does not match any studenId in the student table. Second, it's possible to have two rows in the address table refer to the same student, therefore breaking the one-to-one relationship.

To fix the first issue, we just need to update the child entity, AddressForOneToOneRelationEntity, by adding a foreign key.

@Entity(  
    tableName = "addressForOneToOneRelation",  

    foreignKeys = [  
        ForeignKey(  
            entity = StudentWithOneToOneRelationEntity::class,  
            parentColumns = arrayOf("studentId"),  
            childColumns = arrayOf("studentOwnerId"),
            onDelete = ForeignKey.CASCADE    
        )  
    ],  
)  
data class AddressForOneToOneRelationEntity(  
    // ...   
)

Where:

• entity - The name of the parent table.

• parentColumns - The name of the column in the parent table that the foreign key references.

• childColumns - The name of the column in the child table that contains the foreign key value.

• onDelete - Defines how to treat the child entity when the parent entity it references is deleted. In the case of cascade, when the parent entity is deleted the child entity is also deleted.

To fix the second issue we once again update the @Entity annotation for AddressForOneToOneRelationEntity. It has an indices property in which we can add an Index for the foreign key column, and require it to be unique in the table.

@Entity(  
    tableName = "addressForOneToOneRelation", 

    foreignKeys = [  
        ForeignKey(  
            entity = StudentWithOneToOneRelationEntity::class,  
            parentColumns = arrayOf("studentId"),  
            childColumns = arrayOf("studentOwnerId"),
            onDelete = ForeignKey.CASCADE       
        )  
    ],  

    indices = [Index(value = ["studentOwnerId"], unique = true)]  
)  
data class AddressForOneToOneRelationEntity(  
    // ...
)

Now if we try and insert rows with duplicate studentOwnerId values an exception will be thrown.

Process: com.nicholasfragiskatos.objectrelationshipsinroom, PID: 10239
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: addressForOneToOneRelation.studentOwnerId (code 2067 SQLITE_CONSTRAINT_UNIQUE)

One-to-Many Relationship

Luckily, the one-to-many relationship is almost identical to the one-to-one relationship except for a couple of minor tweaks.

1. The child entity no longer needs the unique index.

2. The intermediate model has a list of addresses instead of just one.
   

@Entity(tableName = "studentWithOneToManyRelation")  
data class StudentWithOneToManyRelationEntity(  
    @PrimaryKey(autoGenerate = true)  
    val studentId: Long = 0,  
    val firstName: String,  
    val lastName: String  
)

@Entity(  
    tableName = "addressForOneToManyRelation",  

    foreignKeys = [  
        ForeignKey(  
            entity = StudentWithOneToManyRelationEntity::class,  
            parentColumns = arrayOf("studentId"),  
            childColumns = arrayOf("studentOwnerId"),
            onDelete = ForeignKey.CASCADE   
        )  
    ]  
)  
data class AddressForOneToManyRelationEntity(  

    @PrimaryKey(autoGenerate = true)  
    val id: Long = 0,  
    val houseNumber: String,  
    val streetName: String,  
    val streetType: String,  
    val city: String,  
    val state: String,  
    @ColumnInfo(name = "postal_code")  
    val postalCode: String,  

    val studentOwnerId: Long  
)

data class StudentWithAddressOneToManyIntermediate(  
    @Embedded  
    val student: StudentWithOneToManyRelationEntity,  

    @Relation(  
    parentColumn = "studentId",  
    entityColumn = "studentOwnerId"  
    )  
    val address: List<AddressForOneToManyRelationEntity>?  
)

@Dao  
interface StudentWithOneToManyRelationDao {  

    // ... 

    @Transaction  
    @Query("SELECT * from studentWithOneToManyRelation")  
    fun getAllStudentsWithAddresses(): List<StudentWithAddressOneToManyIntermediate>  
}

Many-to-Many Relationship

For this approach, the parent entity stays the same again, but the child entity no longer uses a foreign key, which also means it no longer needs the studentOwnerId field.

@Entity(tableName = "studentWithManyToManyRelation")  
data class StudentWithManyToManyRelationEntity(  
    @PrimaryKey(autoGenerate = true)  
    val studentId: Long = 0,  
    val firstName: String,  
    val lastName: String  
)

@Entity(tableName = "addressForManyToManyRelation")  
data class AddressForManyToManyRelationEntity(  
    @PrimaryKey(autoGenerate = true)  
    val addressId: Long = 0,  
    val houseNumber: String,  
    val streetName: String,  
    val streetType: String,  
    val city: String,  
    val state: String,  
    @ColumnInfo(name = "postal_code")  
    val postalCode: String  
)

The Many-to-Many relationship is slightly more involved, and requires the use of a junction table (I've also seen this referred to as an Association Table, or Join Table), which like any other table, is modeled with an entity class. It's a two-column table where one column holds a reference to the primary key of the parent table and the other column holds a reference to the primary key of the child table. This helps resolve the many-to-many relationship. The primary key for the junction table itself is the composite of both columns.

For this example, the junction table entity contains two fields, one for the student ID and one for the address ID. Then we make sure to specify both fields as primary keys in the @Entity annotation. Since this is a many-to-many relationship there can be multiple rows with duplicate student IDs, and multiple rows with duplicate address IDs, but there can not be multiple rows with a duplicate combination of IDs. It's also probably a good idea to define two foreign keys with cascade deletes, one pointing to the student entity, and one pointing to the address entity. This will avoid having orphaned rows in the table when either a student or address is deleted.

@Entity(  
    tableName = "studentAddressCrossRef",  

    primaryKeys = ["studentId", "addressId"],  

    foreignKeys = [  
        ForeignKey(  
            entity = StudentWithManyToManyRelationEntity::class,  
            parentColumns = ["studentId"],  
            childColumns = ["studentId"],  
            onDelete = ForeignKey.CASCADE  
        ),  
        ForeignKey(  
            entity = AddressForManyToManyRelationEntity::class,  
            parentColumns = ["addressId"],  
            childColumns = ["addressId"],  
            onDelete = ForeignKey.CASCADE  
        )  
    ]  
)  
data class StudentAddressCrossRef(  
    val studentId: Long,  
    val addressId: Long  
)

In the intermediate object the @Relation annotation now refers to the junction table. The parentColumn property points to the studentId column in the junction table, and the entityColumn property points to the addressId column in the junction table. Lastly, there is the addition of the associateBy property that specifies the junction table entity class.

data class StudentWithAddressManyToManyIntermediate(  
    @Embedded  
    val student: StudentWithManyToManyRelationEntity,  

    @Relation(  
        parentColumn = "studentId",  
        entityColumn = "addressId",  
        associateBy = Junction(StudentAddressCrossRef::class)  
    )  
    val address: List<AddressForManyToManyRelationEntity>?  
)

Finally, we define the DAO interface as before.

@Dao  
interface StudentWithManyToManyRelationDao {  

    @Insert(onConflict = OnConflictStrategy.REPLACE)  
    fun saveStudent(student: StudentWithManyToManyRelationEntity): Long  

    @Insert(onConflict = OnConflictStrategy.REPLACE)  
    fun saveAddress(address: AddressForManyToManyRelationEntity): Long  

    @Insert(onConflict = OnConflictStrategy.REPLACE)  
    fun saveStudentAddressCrossRef(ref: StudentAddressCrossRef): Long  

    @Insert(onConflict = OnConflictStrategy.REPLACE)  
    @Transaction  
    fun saveAddress(address: AddressForManyToManyRelationEntity, studentId: Long) {  
        saveAddress(address)  
        saveStudentAddressCrossRef(StudentAddressCrossRef(studentId, address.addressId))  
    }  

    @Query("DELETE FROM studentWithManyToManyRelation where studentId = :studentId")  
    fun deleteStudent(studentId: Long): Int  

    @Query("DELETE FROM addressForManyToManyRelation where addressId = :addressId")  
    fun deleteAddress(addressId: Long): Int  

    @Transaction  
    @Query("SELECT * from studentWithManyToManyRelation")  
    fun getAllStudentsWithAddresses(): List<StudentWithAddressManyToManyIntermediate>  
}

It's important to note that when it comes to inserting new student or address data, if there is a relationship then the junction table needs to be manually updated. The business logic of when and how a relationship is formed is entirely up to your use case. However, the important point is that just saving a new student or address using the standard @Insert functions do not automatically create a new entry in the junction table.

As a simple example, I added the following utility functions to the DAO to facilitate inserting a new address and junction table row within one transaction.

@Insert(onConflict = OnConflictStrategy.REPLACE)  
fun saveStudentAddressCrossRef(ref: StudentAddressCrossRef): Long  

@Insert(onConflict = OnConflictStrategy.REPLACE)  
@Transaction  
fun saveAddress(address: AddressForManyToManyRelationEntity, studentId: Long) {  
    saveAddress(address)  
    saveStudentAddressCrossRef(StudentAddressCrossRef(studentId, address.addressId))  
}

Conclusion

Room is a powerful and convenient ORM for Android app development, but it does not support nested entity relationships like other ORMs that are used on more traditional back ends. This limitation stems from the need to preserve the responsiveness of the UI and avoid consuming unnecessary memory resources. Despite this limitation, there are still ways to create object relationships.

First, we looked at embedding the child object into the parent object using Room's @Embedded annotation. This approach is simple, but it can only support one-to-one relationships, and it does not efficiently handle identical child data being referenced by multiple parent entities.

Next, we looked at using a Type Converter to serialize the child object to a JSON string to store the whole object as a string in one of the columns for the parent entity. When the parent entity is fetched from the database the Type Converter will deserialize the string into the correct object. This approach can handle one-to-one and one-to-many relationships, but it also requires duplicating data. However, what might be even worse is that the child data is not easily queryable.

Finally, we learned about the most complex, but powerful approach which best leverages Room and SQLite by treating both parent and child objects as entities, and using a combination of the@Relation annotation, embedded objects, foreign keys, and intermediate models. This approach allows us to define one-to-one, one-to-many, and many-to-many relationships between objects all while keeping the data queryable without data duplication.

---

Thank you for taking the time to read my article. I hope it was helpful.

If you noticed anything in the article that is incorrect or isn't clear, please let me know. I always appreciate the feedback.

---

Comments

[Aayush Gupta]

Great detailed article! Thanks, learned something new today.