Android Room Database: Knowledge You Need to Know

Room Database is a Google library that supports data management through a database for Android. Room Database is built on the foundation of SQLite, so all features of SQLite can be conveniently used on Room Database.

In Room Database, there are 3 most important and basic definitions: Entity, DAO, and Database.

In addition, we also have other definitions to enhance understanding and application of Room Db: TypeConverter, Migrate Database, Entity Relationship.

Question: Why do I only choose Entity, DAO, and Database as basic and not choose Migrate Database, Entity Relationship, or TypeConverter?

Answer: Because you cannot use Room Database without touching the 3 basic definitions. As for TypeConverter, Migrate Database, or Entity Relationship, you can skip or cheat in one way or another.

Entity

Room Entity are classes that represent a Table in the database.

For example, your TODO application designs a Table called Note with columns and data types respectively as

To represent the above structure in Room Entity, we need the following code

@Entity(tableName = "notes")
data class Note(
		@PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    var id: Int = 0,
    @ColumnInfo(name = "title")
    var title: String,
    @ColumnInfo(name = "content")
    var content: String,
    @ColumnInfo(name = "created_at")
    var created_at: Long = System.currentTimeMillis(),
    @ColumnInfo(name = "updated_at")
    var updated_at: Long = System.currentTimeMillis()
)

DAO

DAO is an interface where you declare the necessary queries to retrieve Entity data from the Database. In the same DAO, it can act as a data source for one or more different Entities.

// Demo DAO using RxJava to query data
@Dao
interface NotesDao {

    // insert notes
    @Insert(onConflict = OnConflictStrategy.REPLACE, entity = Note::class)
    fun insertNote(note: Note): Single

    // update notes
    @Update(onConflict = OnConflictStrategy.REPLACE, entity = Note::class)
    fun updateNotes(notes: Note): Single

    // get all notes from db
    @Query("SELECT * FROM notes ORDER BY date_updated DESC")
    fun getNotes(): Single>

    @Query("SELECT * FROM notes WHERE updated_at >

    // delete notes by id
    @Query("DELETE FROM notes where id=:id")
    fun deleteNote(id: Int): Single

    @Query("SELECT * FROM notes where id=:id")
    fun getNote(id: Int): Single
}

// Demo DAO using Kotlin suspend function
@Dao
interface NotesDao {

    // insert notes
    @Insert(onConflict = OnConflictStrategy.REPLACE, entity = Note::class)
    suspend fun insertNote(note: Note): Long

    // update notes
    @Update(onConflict = OnConflictStrategy.REPLACE, entity = Note::class)
    suspend fun updateNotes(notes: Note): Int

    // get all notes from db
    @Query("SELECT * FROM notes ORDER BY date_updated DESC")
    suspend fun getNotes(): List

    @Query("SELECT * FROM notes WHERE updated_at

    // delete notes by id
    @Query("DELETE FROM notes where id=:id")
    suspend fun deleteNote(id: Int): Int

    @Query("SELECT * FROM notes where id=:id")
    suspend fun getNote(id: Int): Note
}

We can also use Flow to retrieve data from the Database. This method will be instructed in more advanced technical articles!

DAO not only supports writing queries on 1 Entity, in complex projects you can completely use statements like JOIN, GROUP BY on many different Tables.

For details on designing Entity as well as DAO for complex projects that need JOIN, GROUP BY multiple Entities in 1 query, you can refer to Danh's Android Programming course.

Database

Once you have the design of Entity and DAO. Database is the class you need to create to be the origin of storing and accessing data on your Android app.

Please note that Database represents a database connection. So usually we will only have 1 Database Instance in 1 application.

const val VERSION = 1
@Database(
    entities = [Note::class],
    version = VERSION,
    exportSchema = false
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun getNoteDao(): NoteDAO
    companion object {
        @Volatile
        private var instance: AppDatabase? = null
        private val LOCK = Any()

        // Check for DB instance if not null then get or insert or else create new DB Instance
        operator fun invoke(context: Context) = instance ?: synchronized(LOCK) {
            instance ?: createDatabase(context).also { instance = it }
        }

        // create db instance
        private fun createDatabase(context: Context) = Room.databaseBuilder(
            context.applicationContext,
            AppDatabase::class.java,
            "app_db.db"
        ).build()
    }
}

TypeConverter

In Entity Note we have 2 Time Columns

@ColumnInfo(name = "created_at")
var created_at: Long = System.currentTimeMillis(),
@ColumnInfo(name = "updated_at")
var updated_at: Long = System.currentTimeMillis()

Basically, representing time in the database using a Long number is reasonable. However, when putting it on the application for use, we need a Convert step to the Date type for convenience. Room Database understands this need, so it created TypeConverter

How to use TypeConverter: Create a class named DateConverter

class DateConverter {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return value?.let { Date(it) }
    }
    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? {
        return date?.time
    }
}

Update the AppDatabase class

@TypeConverters(value = [DateConverter::class])
abstract class AppDatabase : RoomDatabase()

Update the Column in Entity Note

@ColumnInfo(name = "created_at")
var created_at: Date = Calendar.getInstance().time,
@ColumnInfo(name = "updated_at")
var updated_at: Date = Calendar.getInstance().time

Similarly, if we have many Converters, we will create and add them to the annotation of AppDatabase

Relationships

Room Database strongly supports querying on tables that have Relationships with each other. Let's take a look at the following example.

Problem: Your TODO application has a function to read and write User's notes (Note). The application has Labels to represent the topic that the Note is heading towards.

In reality, each Note can be related to many Labels, and vice versa, each Label can also contain many Notes inside it.

We have the following design.

@Entity(tableName = "notes")
data class Note(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Int = 0,
    @ColumnInfo(name = "title")
    val title: String,
    @ColumnInfo(name = "content")
    val content: String,
		@ColumnInfo(name = "created_at")
		var created_at: Date = Calendar.getInstance().time,
		@ColumnInfo(name = "updated_at")
		var updated_at: Date = Calendar.getInstance().time
)

@Entity(
    tableName = "labels",
    indices = [androidx.room.Index(value = ["name"], unique = true)]
)
data class Label(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Int = 0,
    @ColumnInfo(name = "name")
    val name: String
)

@Entity(tableName = "note_labels",
    indices = [androidx.room.Index(value = ["note_id", "label_id"], unique = true)])
data class NoteLabel(
    @PrimaryKey(autoGenerate = true)
    val id: Int  = 0,
    @ColumnInfo(name = "note_id")
    val noteId: Int,
    @ColumnInfo(name = "label_id")
    val labelId: Int
)

Question:

Why do we have to separate it into 3 tables? Are we complicating the problem?
What is @PrimaryKey?
What is indicies?

Next, after having the Entity classes representing the data. We proceed to write the query in DAO

@Query("SELECT * FROM labels " +
        "JOIN note_labels ON labels.id = note_labels.label_id " +
        "JOIN notes ON note_labels.note_id = notes.id")
suspend fun getAllLabelsWithNotes(): Map>

@Query("SELECT * FROM notes " +
        "JOIN note_labels ON notes.id = note_labels.note_id " +
        "JOIN labels ON note_labels.label_id = labels.id")
suspend fun getAllNotesWithLabels(): Map>

Question:

What is JOIN?
Why return a Map type
If you don't use JOIN, what is another way?

Migrate Database

During software development, the database will have to change continuously to synchronize with system requirements and features.

To support your Android users when upgrading to a new version and still be able to keep the data in their current database, and at the same time that database is compatible with the logic of the app in the new version, we need a step called Migrate Database

In Migrate Database, you will write SQL scripts with the purpose of updating and changing the database from the old version to the new version.

Some case studies of Migrate Database

Add / Delete one or more Columns to a Table (Add variable for Entity)

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
            "ALTER TABLE entity ADD COLUMN new_column TEXT NOT NULL DEFAULT ''")
    }
}

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
            "ALTER TABLE entity DROP COLUMN old_column")
    }
}

Change the name of a Column (Rename the variable in Entity but still keep the old value)

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
            "ALTER TABLE entity RENAME COLUMN old_column to new_column")
    }
}

Add / Delete/ Rename 1 Table in Database

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
              "CREATE TABLE new_entity (id INTEGER PRIMARY KEY NOT NULL, title TEXT, description TEXT, date INTEGER)"
          )
    }
}

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Drop the table
        database.execSQL("DROP TABLE old_table")
    }
}

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Rename the table
        database.execSQL("ALTER TABLE old_table RENAME TO new_table")
    }
}