Android Room: Entity Relationships

When using a database, the data we query may be associated with multiple tables. Android Room allows us to define relationships between objects. When querying data, Android Room will automatically read it together with the relevant data. This article describes how to define these relationships.

Room

In this article, we will not introduce how to use Room. If you are not familiar with Room, you can refer to the following article first.

• • Android

Accessing Database with Room on Android

• ByWayne
• 14/04/2022

Embedded Objects

Suppose we have a table books as follows. It contains information about books and authors.

Room provides a way for object embedding. In the following code, Book contains the data of a book, and puts the author’s data in Author. Then embed Author into Book. This makes the code more structured. The way to use it is to add @Embedded in front of Book::author.

It is worth noting that Author is not an entity, it is just an object that contains some data in books. So don’t put @Entity on Author.

@Entity("books")
data class Book(
    @PrimaryKey val bookId: String,
    val bookName: String,
    @Embedded val author: Author,
)

data class Author(
    val authorName: String,
    val authorEmail: String,
)

@Dao
interface BookDao {
    @Upsert
    suspend fun upsert(entity: Book)

    @Query("SELECT * FROM books")
    fun findAll(): Flow<List<Book>>
}

val dao = BookDatabase.getInstance(context).bookDao()

dao.upsert(
    Book(
        "b1",
        "How to read a book",
        Author("Charles", "charles@gmail.com"),
    )
)
dao.upsert(
    Book(
        "b2",
        "Atomic Habits",
        Author("James", "james@gmail.com"),
    )
)

dao.findAll()
    .collect {
        Log.d("MainViewModel", "books=$it")
    }
}

In addition, we can add a prefix in @Embedded to improve the naming of Author’s fields.

@Entity("books")
data class Book(
    @PrimaryKey @ColumnInfo("book_id") val bookId: String,
    val name: String,
    @Embedded(prefix = "author_") val author: Author,
)

data class Author(
    val name: String,
    val email: String,
)

One-to-One Relationships

Next, we want to separate the author’s data from books into another table authors, as shown below. Compared with putting the book and author data in the same table before, this is more common in practice.

Room can also read the relevant data in authors when query books. In the following code, we will add @Entity to Book and Author because they are both tables.

Declare BookAndAuthor object, and declare a Book and an Author in it. Because we mainly query books, and also want to read related data in authors together, we use @Embedded to embed Book into BookAndAuthor. Then, use @Relation to define the relationship between Book and Author. Use parentColumn to specify the field to be associated in Book, that is, Book::id. Then, use entityColumn to specify the field to be associated with parentColumn in Author, that is, Author::bookId.

@Entity("books")
data class Book(
    @PrimaryKey val id: String,
    val name: String,
)

@Entity("authors")
data class Author(
    @PrimaryKey val id: String,
    val name: String,
    val email: String,
    val bookId: String,
)

data class BookAndAuthor(
    @Embedded val book: Book,

    @Relation(
        parentColumn = "id",
        entityColumn = "bookId",
    )
    val author: Author,
)

Define the relationship between Book and Author in BookAndAuthor. Then, in findAll() in BookDao, we only need to query books, and Room will read the relevant data in authors together according to the defined relationships.

In addition, we also need to add @Transaction on BookDao::findAll(). Because Room actually executes two queries, in order to ensure that the entire query action is atomically, we must add @Transaction.

@Dao
interface BookDao {
    @Upsert
    suspend fun upsert(entity: Book)

    @Transaction
    @Query("SELECT * FROM books")
    fun findAll(): Flow<List<BookAndAuthor>>
}

@Dao
interface AuthorDao {
    @Upsert
    suspend fun upsert(entity: Author)
}

val db = BookDatabase.getInstance(context)

db.bookDao().apply {
    upsert(Book("b1", "How to read a book"))
    upsert(Book("b2", "Atomic Habits"))
}
db.authorDao().apply {
    upsert(Author("a1", "Charles", "charles@gmail.com", "b1"))
    upsert(Author("a2", "James", "james@gmail.com", "b2"))
}

db.bookDao().findAll()
    .collect {
        Log.d("WAYNESTALK", "books=$it")
    }

One-to-Many Relationships

So far, books and authors is a one-to-one relationship. That is, a book can only have one author. Now we want to adjust it so that a book can have multiple authors. That is one-to-many relationship, as shown in the figure below.

The code is almost the same as for the one-to-one relationship, the difference is that we renamed BookAuthor to BookAuthors. Then, change BookAuthor::product to BookAuthors::products and change the type to List<Author>.

@Entity("books")
data class Book(
    @PrimaryKey val id: String,
    val name: String,
)

@Entity("authors")
data class Author(
    @PrimaryKey val id: String,
    val name: String,
    val email: String,
    val bookId: String,
)

data class BookAndAuthors(
    @Embedded val book: Book,

    @Relation(
        parentColumn = "id",
        entityColumn = "bookId",
    )
    val authors: List<Author>,
)

@Dao
interface BookDao {
    @Upsert
    suspend fun upsert(entity: Book)

    @Transaction
    @Query("SELECT * FROM books")
    fun findAll(): Flow<List<BookAndAuthors>>
}

@Dao
interface AuthorDao {
    @Upsert
    suspend fun upsert(entity: Author)
}

val db = BookDatabase.getInstance(context)

db.bookDao().apply {
    upsert(Book("b1", "How to read a book"))
    upsert(Book("b2", "Atomic Habits"))
}
db.authorDao().apply {
    upsert(Author("a11", "Charles", "charles@gmail.com", "b1"))
    upsert(Author("a12", "Mortimer", "mortimer@gmail.com", "b1"))
    upsert(Author("a2", "James", "james@gmail.com", "b2"))
}

db.bookDao().findAll()
    .collect {
        Log.d("WAYNESTALK", "books=$it")
    }

Many-to-Many Relationships

The last type of relationship is many-to-many relationship. That is to say, a book can have multiple authors, and an author can also have multiple books, as shown in the figure below. When using a many-to-many relationship, we need an additional cross-reference table, as shown in the figure book_author_cross_ref. It records the correspondence between books and authors.

In the code below, we add BookAuthorCrossRef as a cross-reference table between books and authors. The bookId and authorId in BookAuthorCrossRef must be set as primary keys.

If you want to query book data and read it together with its associated author data, use BookAndAuthors. In BookAndAuthors, use @Relation to define the relationship between Book and Author. Use parentColumn to specify the field to be associated in Book, that is, Book::id. Then, use entityColumn to specify the field to be associated with parentColumn in Author, that is, Author::bookId. In addition, specify the cross-reference table with associateBy.

Conversely, if you want to query author data and read it together with its associated book data, use AuthorAndBooks.

@Entity("books")
data class Book(
    @PrimaryKey val bookId: String,
    val name: String,
)

@Entity("authors")
data class Author(
    @PrimaryKey val authorId: String,
    val name: String,
    val email: String,
)

@Entity(
    tableName = "book_author_cross_ref",
    primaryKeys = ["bookId", "authorId"],
)
data class BookAuthorCrossRef(
    val bookId: String,
    val authorId: String,
)

data class BookAndAuthors(
    @Embedded val book: Book,

    @Relation(
        parentColumn = "bookId",
        entityColumn = "authorId",
        associateBy = Junction(BookAuthorCrossRef::class),
    )
    val authors: List<Author>,
)

data class AuthorAndBooks(
    @Embedded val author: Author,

    @Relation(
        parentColumn = "authorId",
        entityColumn = "bookId",
        associateBy = Junction(BookAuthorCrossRef::class),
    )
    val books: List<Book>,
)

@Dao
interface BookDao {
    @Upsert
    suspend fun upsert(entity: Book)

    @Transaction
    @Query("SELECT * FROM books")
    fun findAll(): List<BookAndAuthors>
}

@Dao
interface AuthorDao {
    @Upsert
    suspend fun upsert(entity: Author)

    @Transaction
    @Query("SELECT * FROM authors")
    fun findAll(): List<AuthorAndBooks>
}

@Dao
interface BookAuthorCrossRefDao {
    @Upsert
    suspend fun upsert(entity: BookAuthorCrossRef)
}

val db = BookDatabase.getInstance(context)

db.bookDao().apply {
    upsert(Book("b1", "How to read a book"))
    upsert(Book("b2", "Atomic Habits"))
}
db.authorDao().apply {
    upsert(Author("a11", "Charles", "charles@gmail.com"))
    upsert(Author("a12", "Mortimer", "mortimer@gmail.com"))
    upsert(Author("a2", "James", "james@gmail.com"))
}
db.bookAuthorCrossRefDao().apply {
    upsert(BookAuthorCrossRef("b1", "a11"))
    upsert(BookAuthorCrossRef("b1", "a12"))
    upsert(BookAuthorCrossRef("b2", "a2"))
}

val books = db.bookDao().findAll()
Log.d("WAYNESTALK", "books=$books")

val authors = db.authorDao().findAll()
Log.d("WAYNESTALK", "authors=$authors")

Foreign Keys

Room also allows us to define foreign keys. In the figure below, books and authors is a one-to-many relationship, where bookId of authors is the foreign key.

In the following code, we use @ForeignKey to define foreign key. Use entity and parentColumns to specify Book::bookId corresponding to foreign key. Specify the foreign key field in childColumns, which is Author::bookId.

onDelete refers to the action SQLite will perform when the Book corresponding to Author::bookId is deleted. Here we specify ForiegnKey.CASCADE. When a book is deleted, all Author with the same bookId will also be deleted.

@Entity("books")
data class Book(
    @PrimaryKey val id: String,
    val name: String,
)

@Entity(
    tableName = "authors",
    foreignKeys = [
        ForeignKey(
            entity = Book::class,
            parentColumns = ["id"],
            childColumns = ["bookId"],
            onDelete = ForeignKey.CASCADE,
        )
    ]
)
data class Author(
    @PrimaryKey val authorId: String,
    val name: String,
    val email: String,
    val bookId: String,
)

data class BookAndAuthors(
    @Embedded val book: Book,

    @Relation(
        parentColumn = "bookId",
        entityColumn = "bookId",
    )
    val authors: List<Author>,
)

@Dao
interface BookDao {
    @Upsert
    suspend fun upsert(entity: Book)

    @Transaction
    @Query("SELECT * FROM books")
    fun findAll(): List<BookAndAuthors>

    @Delete
    suspend fun delete(entity: Book)
}

@Dao
interface AuthorDao {
    @Upsert
    suspend fun upsert(entity: Author)
}

val db = BookDatabase.getInstance(context)

db.bookDao().apply {
    upsert(Book("b1", "How to read a book"))
    upsert(Book("b2", "Atomic Habits"))
}
db.authorDao().apply {
    upsert(Author("a11", "Charles", "charles@gmail.com", "b1"))
    upsert(Author("a12", "Mortimer", "mortimer@gmail.com", "b1"))
    upsert(Author("a2", "James", "james@gmail.com", "b2"))
}

var books = db.bookDao().findAll()
Log.d("WAYNESTALK", "books=$books")

db.bookDao().delete(Book("b1", "How to read a book"))

books = db.bookDao().findAll()
Log.d("WAYNESTALK", "books=$books")

Conclusion

Android Room allows us to define the relationship between objects, especially when the relationship between data tables is complex, it is a very useful tool. It will be automatically read together with the associated data, without the need for us to manually write code to read between multiple tables one by one. This increases the speed of development and reduces bugs.