Day: March 11, 2022

When working with Room, there might come a situation during bulk CRUD operations that it would be useful to add a Foreign Key to one of our tables. This provides two main benefits: cascading delete (must be configured manually) and enforcing data integrity on linked entities.

In this tutorial, we will learn how to add Foreign Keys into Room entities.

At the end of the tutorial, you would have learned:

1. How to add Foreign Keys to Room entities.

1. Android Studio. The version used in this tutorial is Bumblebee 2021.1.1 Patch 2.

1. Intermediate Android.
2. SQL.
3. Basic Room database.
4. Kotlin coroutines.

To follow along with the tutorial, perform the steps below:

1. Create a new Android project with the default Empty Activity.

2. Add the dependencies below for Room into the Module build.gradle.

    def room_version = "2.4.2"
    implementation "androidx.room:room-runtime:$room_version"
    kapt "androidx.room:room-compiler:$room_version"
    implementation "androidx.room:room-ktx:$room_version"
    implementation 'androidx.lifecycle:lifecycle-runtime-ktx:2.4.1'

3. In the same file, add the kapt plugin under plugins

    id 'kotlin-kapt'

4. Create a new @Entity called Student with the code below.

    @Entity
    data class Student(
       @PrimaryKey(autoGenerate = true) val id: Long = 0,
       @ColumnInfo(name = "first_name") val firstName: String,
       @ColumnInfo(name = "last_name") val lastName: String
    )

5. Create another @Entity called ReportCard with the code below.

    @Entity(tableName = "report_card")
    data class ReportCard(
       @PrimaryKey(autoGenerate = true) val id: Long = 0,
       @ColumnInfo(name = "student_id") val studentId: Long
    )

6. Create a new empty DAO for the Student entity.

    @Dao
    interface StudentDao {
   
       @Insert(onConflict = OnConflictStrategy.IGNORE)
       suspend fun insertStudent(student: Student): Long
   
       @Query("SELECT * FROM student WHERE id=:id")
       suspend fun getStudentById(id: Long): Student?
   
    }

7. Create the abstract class MyRoomDB with the code below.

    @Database(entities = [Student::class, ReportCard::class], version = 1)
    abstract class MyRoomDB : RoomDatabase() {
       abstract fun studentDao(): StudentDao
    }

8. Append the code below to MainActivity onCreate(). This creates an instance of the database and then attempts to perform a query on the empty database.

    val db = Room.databaseBuilder(
       applicationContext,
       MyRoomDB::class.java, "my-room-db"
    ).build()
   
    lifecycleScope.launch {
       db.studentDao().getStudentById(1)
    }

9. For most of the tutorial, we will interact with the database via the Database Inspector, and not in code. The DAO query above performs a dummy connection so that the Database Inspector can keep the database connection open in debugging mode. Run the app in Debug mode and then switch to the App Inspection window. Verify that the database connection stays open. Later on, we can interact with the database directly using a Query tab.

For this tutorial, we are completely ignoring the frontend. We will only focus on the interaction with the database via Room.

We currently have two entities, Student and ReportCard. The Student entity is not aware of the ReportCard entity, but the ReportCard entity is dependent on the Student entity. Each ReportCard contains its own ID as well as the associated Student ID.

There is no foreign key constraint to Student in ReportCard, so it is possible that a ReportCard might be referencing a Student who does not exist in the student table. At the end of the tutorial, we should have created a Foreign Key for the ReportCard entity; this way we can ensure that each ReportCard can only reference a valid Student.

First, in order to have a better understanding of the problems that a Foreign Key can solve, let us walk through an example where data integrity is violated.

The current student table is empty, but we can INSERT new ReportCard into report_card just fine, referencing non-existent students.

INSERT INTO report_card (id, student_id) VALUES
(1, 30),
(2, 2)

If we query the join for the two tables, we would receive nothing because those student_id do not exist in the student table.

SELECT * FROM report_card INNER JOIN student ON report_card.student_id=student.id

Fortunately for us, we can create a Foreign Key to an entity so that the database can throw errors when we try to violate this constraint. One thing to keep in mind is that this does not prevent developers from writing code that violates the constraint; only via runtime testing that data inconsistencies will show up with a SQL exception.

To apply a Foreign Key, we can simply pass in ForeignKey objects to Entitys foreignKey parameter. entity, childColumns, and parentColumns are required by ForeignKey.

@Entity(tableName = "report_card",
   foreignKeys = [ForeignKey(
       entity = Student::class,
       childColumns = ["student_id"],
       parentColumns = ["id"]
)])
data class ReportCard(
   @PrimaryKey(autoGenerate = true) val id: Long = 0,
   @ColumnInfo(name = "student_id") val studentId: Long
)

After adding the Foreign Key, you can re-install the App so Room can make use of the new constraint.

If we attempt to run the same INSERT into report_card again, Room will not allow us and throw an exception instead.

E/SQLiteQuery: exception: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY); query: INSERT INTO report_card (id, student_id) VALUES
    (1, 30),
     (2, 2)

In order for the INSERT into report_card to work, valid students must exist. INSERT new Students with the statement below.

INSERT INTO student VALUES
(2, "Mary", "Anne"),
(30, "John", "Doe")

After this, you can INSERT into report_card for the valid student IDs.

INSERT INTO report_card (id, student_id) VALUES
(1, 30),
(2, 2)

If we run the JOIN query again, we can see that it returns all of the data correctly.

MainActivity.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import androidx.lifecycle.lifecycleScope
import androidx.room.Room
import kotlinx.coroutines.launch

class MainActivity : AppCompatActivity() {
   override fun onCreate(savedInstanceState: Bundle?) {
       super.onCreate(savedInstanceState)
       setContentView(R.layout.activity_main)

       val db = Room.databaseBuilder(
           applicationContext,
           MyRoomDB::class.java, "my-room-db"
       ).build()

       lifecycleScope.launch {
           db.studentDao().getStudentById(1)
       }

   }
}

MyRoomDB.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.room.Database
import androidx.room.RoomDatabase

@Database(entities = [Student::class, ReportCard::class], version = 1)
abstract class MyRoomDB : RoomDatabase() {
   abstract fun studentDao(): StudentDao
   abstract fun reportCardDao(): ReportCardDao
}

ReportCard.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.ForeignKey
import androidx.room.PrimaryKey

@Entity(tableName = "report_card",
   foreignKeys = [ForeignKey(
       entity = Student::class,
       childColumns = ["student_id"],
       parentColumns = ["id"]
)])
data class ReportCard(
   @PrimaryKey(autoGenerate = true) val id: Long = 0,
   @ColumnInfo(name = "student_id") val studentId: Long
)

Student.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "student")
data class Student(
   @PrimaryKey(autoGenerate = true) val id: Long = 0,
   @ColumnInfo(name = "first_name") val firstName: String,
   @ColumnInfo(name = "last_name") val lastName: String
)

StudentDao.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query

@Dao
interface StudentDao {

   @Insert(onConflict = OnConflictStrategy.IGNORE)
   suspend fun insertStudent(student: Student): Long

   @Query("SELECT * FROM student WHERE id=:id")
   suspend fun getStudentById(id: Long): Student?

}

Module build.gradle

plugins {
   id 'com.android.application'
   id 'org.jetbrains.kotlin.android'
   id 'kotlin-kapt'
}

android {
   compileSdk 32

   defaultConfig {
       applicationId "com.codelab.daniwebandroidroomforeignkey"
       minSdk 21
       targetSdk 32
       versionCode 1
       versionName "1.0"

       testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
   }

   buildTypes {
       release {
           minifyEnabled false
           proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
       }
   }
   compileOptions {
       sourceCompatibility JavaVersion.VERSION_1_8
       targetCompatibility JavaVersion.VERSION_1_8
   }
   kotlinOptions {
       jvmTarget = '1.8'
   }
}

dependencies {
   //Room deps
   def room_version = "2.4.2"
   implementation "androidx.room:room-runtime:$room_version"
   kapt "androidx.room:room-compiler:$room_version"
   implementation "androidx.room:room-ktx:$room_version"
   implementation 'androidx.lifecycle:lifecycle-runtime-ktx:2.4.1'

   implementation 'androidx.core:core-ktx:1.7.0'
   implementation 'androidx.appcompat:appcompat:1.4.1'
   implementation 'com.google.android.material:material:1.5.0'
   implementation 'androidx.constraintlayout:constraintlayout:2.1.3'
   testImplementation 'junit:junit:4.13.2'
   androidTestImplementation 'androidx.test.ext:junit:1.1.3'
   androidTestImplementation 'androidx.test.espresso:espresso-core:3.4.0'
}

We have learned how to create Foreign Keys on Room Entities. The full project code can be found at https://github.com/dmitrilc/DaniwebAndroidRoomForeignKey

If your Android Application uses Room, then you might have wondered how to prepopulate a Room database. In this tutorial, we will learn how to prepopulate a Room database.

At the end of the tutorial, you would have learned:

1. How to prepopulate a Room database.

1. Android Studio. The version used in this tutorial is Bumblebee 2021.1.1 Patch 2.

1. Basic Android.
2. Basic Room.

To follow along with the tutorial, perform the steps below:

1. Create a new Android project with the default Empty Activity.

2. Add the dependencies below for Room into the Module build.gradle.

    def room_version = "2.4.2"
    implementation "androidx.room:room-runtime:$room_version"
    kapt "androidx.room:room-compiler:$room_version"
    implementation "androidx.room:room-ktx:$room_version"
    implementation 'androidx.lifecycle:lifecycle-runtime-ktx:2.4.1'

3. In the same file, add the kapt plugin under plugins.

    id 'kotlin-kapt'

4. Add a new Person Entity with the code below.

    @Entity(tableName = "person")
    data class Person(
       @PrimaryKey(autoGenerate = true) val id: Long,
       @ColumnInfo(name = "first_name") val firstName: String,
       @ColumnInfo(name = "last_name") val lastName: String
    )

5. Add a new Person DAO with the code below.

    @Dao
    interface PersonDao {
       @Query("SELECT * FROM person WHERE id=:id")
       suspend fun getPersonById(id: Long): Person?
    }

6. Create a new Room database with the code below.

    @Database(entities = [Person::class], version = 1)
    abstract class MyDatabase : RoomDatabase(){
       abstract fun personDao(): PersonDao
    }

7. In MainActivity#onCreate(), append the code below. This creates a dummy connection to the database at launch so that we can have access to it in debug mode.

    val db = Room.databaseBuilder(
       applicationContext,
       MyDatabase::class.java, "my-database"
    ).build()
   
    lifecycleScope.launch {
       db.personDao().getPersonById(1)
    }

For this tutorial, we are not concerned about the UI at all and only focus on the Room database itself.

There is only one simple Student Entity in our App. At the end of the tutorial, we should be able to prepopulate our database with data on boot.

There are three different ways to prepopulate a database.

1. The first option involves using prepackaged database files located under a special directory called assets/.
2. The second option allows you to load the prepackaged files from the file system itself.
3. The third option includes loading the database files from an InputStream.

Because loading pre-packaged databases are all the same conceptually for all three options, we will only need to know how to do it with the assets/ resource directory. Loading a pre-packaged database from File or InputStream is only different depending on the location of the file.

All options require you to export your pre-built database, so we will learn how to do that first.

To be able to export a database, we will need to build it first on a test device. Follow the steps below to create some data and export the database.

1. Select the top-most app directory of in the Android Project View.

2. File > New > Folder > Assets Folder.

3. Start the App in Debug mode.

4. Open the App Inspection tab to start the Database Inspector.

5. Run the statement below to add some Person into the database.

    INSERT INTO person VALUES
    (1, "Terence", "Landon"),
    (2, "Danielle", "Alger"),
    (3, "Arnold", "Chandler"),
    (4, "Mariah", "Blake"),
    (5, "Randal", " Maria ")

6. Now, highlight the my-database and select Export as File. Save it as a .db file.
   

7. You can export directly into the assets directory created previously.

8. Optionally, save another copy of the same database as the SQL file extension, and then open the file with a text editor. With the human-readable SQL file, we now understand exactly which statements are run against the underlying SQLite database.

    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    CREATE TABLE android_metadata (locale TEXT);
    INSERT INTO android_metadata VALUES('en_US');
    CREATE TABLE `person` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `first_name` TEXT NOT NULL, `last_name` TEXT NOT NULL);
    INSERT INTO person VALUES(1,'Terence','Landon');
    INSERT INTO person VALUES(2,'Danielle','Alger');
    INSERT INTO person VALUES(3,'Arnold','Chandler');
    INSERT INTO person VALUES(4,'Mariah','Blake');
    INSERT INTO person VALUES(5,'Randal',' Maria ');
    CREATE TABLE room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT);
    INSERT INTO room_master_table VALUES(42,'cd4ee16aca420ae15eab14b1baa5cdcf');
    DELETE FROM sqlite_sequence;
    INSERT INTO sqlite_sequence VALUES('person',5);
    COMMIT;

Now that we have the exported database, it is quite simple to load it into our database on the first run. All we have to do is to call the function createFromAsset() from the Room.Builder class on our database creation call. The builder step to add is:

.createFromAsset("my-database.db")

so that the previous database builder becomes this:

val db = Room.databaseBuilder(
   applicationContext,
   MyDatabase::class.java,
   "my-database"
)
   .createFromAsset("my-database.db")
   .build()

If you reinstall the app, then you can see that the database now has data upon first boot.

MainActivity.kt

package com.codelab.daniwebandroidprepopulateroomdatabase

import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import androidx.lifecycle.lifecycleScope
import androidx.room.Room
import kotlinx.coroutines.launch

class MainActivity : AppCompatActivity() {
   override fun onCreate(savedInstanceState: Bundle?) {
       super.onCreate(savedInstanceState)
       setContentView(R.layout.activity_main)

       val db = Room.databaseBuilder(
           applicationContext,
           MyDatabase::class.java,
           "my-database"
       )
           .createFromAsset("my-database.db")
           .build()

       lifecycleScope.launch {
           db.personDao().getPersonById(1)
       }
   }
}

MyDatabase.kt

package com.codelab.daniwebandroidprepopulateroomdatabase

import androidx.room.Database
import androidx.room.RoomDatabase

@Database(entities = [Person::class], version = 1)
abstract class MyDatabase : RoomDatabase(){
   abstract fun personDao(): PersonDao
}

Person.kt

package com.codelab.daniwebandroidprepopulateroomdatabase

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "person")
data class Person(
   @PrimaryKey(autoGenerate = true) val id: Long,
   @ColumnInfo(name = "first_name") val firstName: String,
   @ColumnInfo(name = "last_name") val lastName: String
)

PersonDao

package com.codelab.daniwebandroidprepopulateroomdatabase

import androidx.room.Dao
import androidx.room.Query

@Dao
interface PersonDao {
   @Query("SELECT * FROM person WHERE id=:id")
   suspend fun getPersonById(id: Long): Person?
}

Module build.gradle

plugins {
   id 'com.android.application'
   id 'org.jetbrains.kotlin.android'
   id 'kotlin-kapt'
}

android {
   compileSdk 32

   defaultConfig {
       applicationId "com.codelab.daniwebandroidprepopulateroomdatabase"
       minSdk 21
       targetSdk 32
       versionCode 1
       versionName "1.0"

       testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
   }

   buildTypes {
       release {
           minifyEnabled false
           proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
       }
   }
   compileOptions {
       sourceCompatibility JavaVersion.VERSION_1_8
       targetCompatibility JavaVersion.VERSION_1_8
   }
   kotlinOptions {
       jvmTarget = '1.8'
   }
}

dependencies {
   def room_version = "2.4.2"
   implementation "androidx.room:room-runtime:$room_version"
   kapt "androidx.room:room-compiler:$room_version"
   implementation "androidx.room:room-ktx:$room_version"
   implementation 'androidx.lifecycle:lifecycle-runtime-ktx:2.4.1'

   implementation 'androidx.core:core-ktx:1.7.0'
   implementation 'androidx.appcompat:appcompat:1.4.1'
   implementation 'com.google.android.material:material:1.5.0'
   implementation 'androidx.constraintlayout:constraintlayout:2.1.3'
   testImplementation 'junit:junit:4.13.2'
   androidTestImplementation 'androidx.test.ext:junit:1.1.3'
   androidTestImplementation 'androidx.test.espresso:espresso-core:3.4.0'
}

We have learned how to prepopulate a Room database in this tutorial. The full project code can be found at https://github.com/dmitrilc/DaniwebAndroidPrepopulateRoomDatabase