Local Databases with Room (SQLite)

# CHAPTER 21

Local Databases with Room (SQLite) #

1. Introduction #

2. Learning Objectives #

• Explain the architectural importance of offline-first local data caching.

• Configure Room Database dependencies within Gradle.

• Define a database table utilizing the @Entity annotation.

• Execute SQL queries (CRUD operations) safely utilizing a Data Access Object (@Dao).

• Construct the central @Database class to manage connections.

• Execute database transactions securely utilizing Coroutines.

3. Setting Up Room in Gradle #

Add these to your build.gradle.kts (Module :app):

val room_version = "2.6.1"
implementation("androidx.room:room-runtime:$room_version")
implementation("androidx.room:room-ktx:$room_version") // For Coroutines support!
ksp("androidx.room:room-compiler:$room_version") // The code generator

4. Step 1: The @Entity (The Table) #

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

// 1. Tell Room this class represents a SQLite Table
@Entity(tableName = "users_table")
data class UserEntity(
    
    // 2. Every table needs a unique Primary Key! 'autoGenerate = true' counts up 1, 2, 3...
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    
    // 3. The columns of the table
    val name: String,
    val email: String,
    val age: Int
)

5. Step 2: The @Dao (The Queries) #

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

@Dao
interface UserDao {

    // 1. INSERT DATA
    // If we try to insert a User that already exists, REPLACE it!
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: UserEntity)

    // 2. READ DATA
    // Room verifies your SQL syntax at compile time!
    @Query("SELECT * FROM users_table ORDER BY id ASC")
    fun getAllUsers(): LiveData<List<UserEntity>> // Returns LiveData! Automatically updates UI when DB changes!

    // 3. DELETE DATA
    @Query("DELETE FROM users_table")
    suspend fun deleteAllUsers()
}

*Magic Feature:* By returning LiveData from a DAO query, Room automatically observes the database. If you insert a new user, Room instantly pushes the updated list to the Activity without you writing a second query!

6. Step 3: The @Database (The Core Engine) #

import android.content.Context
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase

// Tell Room which entities belong in this database, and set a version number!
@Database(entities = [UserEntity::class], version = 1, exportSchema = false)
abstract class UserDatabase : RoomDatabase() {

    abstract fun userDao(): UserDao

    companion object {
        @Volatile // Ensures the instance is immediately visible to all threads
        private var INSTANCE: UserDatabase? = null

        // The Singleton Pattern
        fun getDatabase(context: Context): UserDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    UserDatabase::class.java,
                    "user_database"
                ).build()
                INSTANCE = instance
                instance
            }
        }
    }
}

7. Step 4: Using the Database in the ViewModel #

import android.app.Application
import androidx.lifecycle.AndroidViewModel
import androidx.lifecycle.LiveData
import androidx.lifecycle.viewModelScope
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.launch

// We use AndroidViewModel because Room needs a Context to build the database!
class UserViewModel(application: Application) : AndroidViewModel(application) {

    val allUsers: LiveData<List<UserEntity>>
    private val dao: UserDao

    init {
        val database = UserDatabase.getDatabase(application)
        dao = database.userDao()
        // Room automatically runs LiveData queries on a background thread!
        allUsers = dao.getAllUsers()
    }

    fun insert(user: UserEntity) {
        // We MUST run inserts/deletes on the IO thread!
        viewModelScope.launch(Dispatchers.IO) {
            dao.insertUser(user)
        }
    }
}

8. Database Migrations #

9. Common Mistakes #

• Main Thread Crashes: Attempting to call dao.insertUser() directly from a Button click on the Main Thread. Room is designed to be bulletproof; if it detects you executing a synchronous query on the UI thread, it will purposefully crash the application with an IllegalStateException to prevent ANRs.

• Forgetting suspend: If you forget to add the suspend keyword to your @Insert or @Delete functions in the DAO, Kotlin will allow you to call them on the Main Thread, triggering the crash mentioned above.

10. Best Practices #

• Single Source of Truth: A robust architecture utilizes Room as the Single Source of Truth. When the app opens, it fetches the API (Retrofit). It does NOT display the API data directly. Instead, it saves the API data into Room. The UI *only* observes the Room Database. This guarantees seamless offline functionality.

11. Exercises #

1. 1. Create a NoteEntity data class with an auto-generating id (Int), title (String), and content (String).

1. 2. Create a NoteDao interface. Write an @Insert function, and an @Query function that selects all notes ordered by title alphabetically.

12. Coding Challenges #

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the structural mechanics of the Room DAO returning LiveData. How does this specific implementation facilitate reactive UI synchronization without manual database polling?

• Q: Describe the catastrophic consequences of incrementing a Room Database version without providing a corresponding Migration object. How does the concept of Schema integrity apply here?

• Q: Contrast the persistence capabilities of SharedPreferences/DataStore versus a Room Database. When is it architecturally inappropriate to utilize Room?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #