Entity Relationship in Room

Entity Relationship in Room
Note: This article is part of the advanced Room series which covers all the details about the Room persistence library. You can read all the articles here:

So, let's get started.

This article covers how we can define the relationship between entities in Room persistence library.

Since SQLite is a relational database, entities can have relationships between them. In Room, entities cannot directly reference other entities because it might cause loading unnecessary data every time.

Still, sometimes, you would want to reference other entities from your entity. You can achieve this in multiple ways.

Embedded Objects

You can use the @Embedded annotation to represent an object that you'd like to decompose into its subfields within a table(entity). You can then query the embedded fields just as you would for other individual columns.

Embedded fields can also include other embedded fields. 
data class Address(
  val street: String?,
  val state: String?,
  val city: String?,
  val postCode: Int
)

@Entity
data class User(
  @PrimaryKey val id: Int,
  val firstName: String?,
  @Embedded val address: Address?
)

The table representing a User object then contains columns with the following names: id , firstName , street , state , city , and postCode .

If an entity has multiple embedded fields of the same type, you can keep each column unique by setting the prefix property. Room then adds the provided value to the beginning of each column name in the embedded object.

In the above example, fields of an object are decomposed into an entity. In case you want to represent relationship between multiple entities, you cannot use the @Embedded annotation.

You can use either @Relation annotation or foreignkeys parameter of @Entity annotation for defining the relationship between two entities.
Both of them are different from each other in such a way that @Relation annotation can only be applied on a non-entity class whereas ForeignKey is used on an entity class. Also, ForeignKey affects the schema of an entity that requires that the child column(s) exist in the parent column(s). @Relation is used to join the tables without affecting the schema of tables.

You can define the relationship between entities in 3 ways:

  • One-to-one relationship
  • One-to-many relationship or Many-to-one relationship
  • Many-to-many relationship

One-to-one relationships

A one-to-one relationship between two entities is a relationship where each instance of the parent entity corresponds to exactly one instance of the child entity, and vice-versa.

For example, consider a music streaming app where the user has a library of songs that they own. Each user has only one library, and each library corresponds to exactly one user. 

@Entity
data class User(
  @PrimaryKey val userId: Long,
  val name: String,
  val age: Int
)

@Entity(foreignKeys = @ForeignKey(entity = User.class,
    parentColumns = "userId",
    childColumns = "userOwnerId",
    onDelete = CASCADE))
data class Library(
  @PrimaryKey val libraryId: Long,
  val title: String,
  val userOwnerId: Long
)

data class UserAndLibrary(
  @Embedded val user: User,
  @Relation(
      parentColumn = "userId",
      entityColumn = "userOwnerId"
  )
  val library: Library
)

In the above example, User and Library are the entities which have a one-to-one relationship. One of the entities must include a variable that is a reference to the primary key of the other entity( userOwnerId in Library entity).

In order to query the list of users and corresponding libraries, we must first model the one-to-one relationship between the two entities, which is done using UserAndLibrary class. The UserAndLibrary class contains an instance of the parent entity( User ) and the corresponding instance of the child entity( Library ). Then, add the @Relation annotation to the instance of the child entity, with parentColumn set to the name of the primary key column of the parent entity and entityColumn set to the name of the column of the child entity that references the parent entity's primary key.

Now we can query our database in the following way: 

@Transaction
@Query("SELECT * FROM User")
fun getUsersAndLibraries(): List<UserAndLibrary>
This method requires Room to run two queries, so add the @Transaction annotation to this method to ensure that the whole operation is performed atomically.

One-to-many relationships

A one-to-many relationship between two entities is a relationship where each instance of the parent entity corresponds to zero or more instances of the child entity, but each instance of the child entity can only correspond to exactly one instance of the parent entity.

In the previous music streaming app example, a User can have multiple playlists. Each user can create as many playlists as they want, but each playlist is created by exactly one user. 

@Entity
data class User(
  @PrimaryKey val userId: Long,
  val name: String,
  val age: Int
)

@Entity(foreignKeys = @ForeignKey(entity = User.class,
    parentColumns = "userId",
    childColumns = "userCreatorId",
    onDelete = CASCADE))
  data class Playlist(
  @PrimaryKey val playlistId: Long,
  val userCreatorId: Long,
  val playlistName: String
)

data class UserWithPlaylists(
  @Embedded val user: User,
  @Relation(
      parentColumn = "userId",
      entityColumn = "userCreatorId"
  )
  val playlists: List<Playlist>
)

As we can see, the approach is very similar to one-to-one relationship, the only difference here is in the relationship model( UserWithPlaylists ). Instead of containing a single child entity, it now contains a list of child entity. Querying the database is also very similar. 

@Transaction
@Query("SELECT * FROM User")
fun getUsersWithPlaylists(): List<UserWithPlaylists>

Many-to-many relationships

A many-to-many relationship between two entities is a relationship where each instance of the parent entity corresponds to zero or more instances of the child entity, and vice-versa.

In the music streaming app example, each playlist can include many songs, and each song can be a part of many different playlists. Therefore, there should be a many-to-many relationship between the Playlist entity and the Song entity.

Many-to-many relationships are distinct from other relationship types because there is generally no reference to the parent entity in the child entity. Instead, a third class is used to represent an associative entity (or cross-reference table) between the two entities. The cross-reference table must have columns for the primary key from each entity in the many-to-many relationship represented in the table. 

@Entity
data class Playlist(
  @PrimaryKey val id: Long,
  val playlistName: String
)

@Entity
data class Song(
  @PrimaryKey val id: Long,
  val songName: String,
  val artist: String
)

@Entity(primaryKeys = ["playlistId", "songId"],
    foreignKeys = {
      @ForeignKey(entity = Playlist.class,
      parentColumns = "id",
      childColumns = "playlistId"),
      @ForeignKey(entity = Song.class,
      parentColumns = "id",
      childColumns = "songId")
}))

data class PlaylistSongCrossRef(
  val playlistId: Long,
  val songId: Long
)

Now, the next step depends on how you want to query these related entities.

  • If you want to query playlists and a list of the corresponding songs for each playlist, create a new data class that contains a single Playlist object and a list of all of the Song objects that the playlist includes.
  • If you want to query songs and a list of the corresponding playlists for each, create a new data class that contains a single Song object and a list of all of the Playlist objects in which the song is included.

In either case, model the relationship between the entities by using the associateBy property in the @ Relation annotation in each of these classes to identify the cross-reference entity providing the relationship between the Playlist entity and the Song entity. 

data class PlaylistWithSongs(
  @Embedded val playlist: Playlist,
  @Relation(
      parentColumn = "playlistId",
      entityColumn = "songId",
      associateBy = @Junction(PlaylistSongCrossRef::class)
  )
  val songs: List<Song>
)

data class SongWithPlaylists(
  @Embedded val song: Song,
  @Relation(
      parentColumn = "songId",
      entityColumn = "playlistId",
      associateBy = @Junction(PlaylistSongCrossRef::class)
  )
  val playlists: List<Playlist>
)

Querying the database is similar to the previous approaches. 

@Transaction
@Query("SELECT * FROM Playlist")
fun getPlaylistsWithSongs(): List<PlaylistWithSongs>

@Transaction
@Query("SELECT * FROM Song")
fun getSongsWithPlaylists(): List<SongWithPlaylists>

This is all about Entity Relationship in Room. Hope you enjoyed this blog. In the next blog, we are going to learn How does Room work internally?

You can also connect with me on LinkedIn , Twitter , Facebook and Github .

Thank You!!!