28.15. Working with SQLite Databases: Using SQLite with Room Persistence Library

When it comes to Android app development, managing local data efficiently is crucial. SQLite databases are a popular choice for local data storage due to their lightweight nature and powerful query capabilities. However, working directly with SQLite can be cumbersome, involving tedious boilerplate code and potential errors. This is where the Room Persistence Library comes into play, offering an abstraction layer over SQLite to facilitate database interactions while maintaining robust compile-time checks.

Room is part of Android's Jetpack suite and provides an efficient way to handle database operations with minimal boilerplate. It leverages the full power of SQLite while providing a more intuitive and type-safe API. With Room, you can define your database schema using annotated classes, ensuring that your database operations are both efficient and easy to understand.

Setting Up Room in Your Project

To get started with Room, you need to add the necessary dependencies to your build.gradle file. Ensure that you have the following dependencies included:

implementation "androidx.room:room-runtime:2.5.0"
kapt "androidx.room:room-compiler:2.5.0"
// For Kotlin use kapt instead of annotationProcessor
implementation "androidx.room:room-ktx:2.5.0"

Once you have added these dependencies, make sure to enable Kotlin annotation processing by applying the Kotlin KAPT plugin in your build.gradle:

apply plugin: 'kotlin-kapt'

Defining the Database Schema

Room uses annotations to define the database schema. You start by creating an entity class, which represents a table in your database. Each entity is a simple data class annotated with @Entity. For example, consider a User entity:

@Entity(tableName = "user")
data class User(
    @PrimaryKey(autoGenerate = true) val uid: Int,
    @ColumnInfo(name = "first_name") val firstName: String?,
    @ColumnInfo(name = "last_name") val lastName: String?
)

In this example, the User class represents a table named user with columns for user ID, first name, and last name. The @PrimaryKey annotation specifies that uid is the primary key of the table, and autoGenerate = true means that Room will automatically generate unique IDs for new entries.

Creating a Data Access Object (DAO)

DAOs are interfaces that define methods for interacting with the data in your database. Each DAO method is responsible for a specific database operation, such as inserting, updating, or querying data. DAOs are annotated with @Dao. Here’s an example DAO for the User entity:

@Dao
interface UserDao {
    @Insert
    suspend fun insert(user: User)

    @Update
    suspend fun update(user: User)

    @Delete
    suspend fun delete(user: User)

    @Query("SELECT * FROM user WHERE uid = :userId")
    suspend fun getUserById(userId: Int): User?

    @Query("SELECT * FROM user")
    suspend fun getAllUsers(): List<User>
}

This DAO interface defines methods for inserting, updating, deleting, and querying users. Note the use of the @Query annotation to specify SQL queries. The suspend keyword indicates that these methods are designed to be called from a coroutine, ensuring non-blocking database operations.

Configuring the Room Database

The next step is to create an abstract class that extends RoomDatabase. This class serves as the main access point for the underlying SQLite database. You annotate it with @Database to define the list of entities and the database version:

@Database(entities = [User::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

In this example, the AppDatabase class includes the User entity and provides an abstract method to access the UserDao. The version parameter is used to manage database migrations.

Building the Database Instance

To create an instance of the Room database, use the Room.databaseBuilder method. It’s a good practice to create a singleton instance of the database to prevent multiple instances from opening simultaneously:

val db = Room.databaseBuilder(
    applicationContext,
    AppDatabase::class.java, "database-name"
).build()

In a real-world application, you might want to implement a singleton pattern to manage the database instance efficiently, ensuring that only one instance is created throughout the app's lifecycle.

Handling Database Operations

With the database and DAO set up, you can now perform database operations. Since Room supports coroutines, you can execute these operations in a coroutine scope, making them non-blocking:

GlobalScope.launch {
    val user = User(uid = 0, firstName = "John", lastName = "Doe")
    db.userDao().insert(user)

    val users = db.userDao().getAllUsers()
    users.forEach {
        Log.d("RoomExample", "User: ${it.firstName} ${it.lastName}")
    }
}

This example demonstrates inserting a new user into the database and retrieving all users. The use of coroutines ensures that these operations do not block the main thread, maintaining a smooth user experience.

Migrating the Database

As your application evolves, you may need to update the database schema. Room provides a straightforward way to handle database migrations. You define a migration strategy by creating a Migration object and specifying the changes required to transition from one version to another:

val migration_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE user ADD COLUMN age INTEGER DEFAULT 0")
    }
}

In this example, a new column age is added to the user table. You then pass this migration object to the Room.databaseBuilder method:

val db = Room.databaseBuilder(
    applicationContext,
    AppDatabase::class.java, "database-name"
).addMigrations(migration_1_2).build()

This setup ensures that when the database version changes, Room will execute the migration code to update the schema accordingly.

Testing Your Room Database

Testing is an essential part of development, and Room provides test utilities to facilitate database testing. You can use an in-memory database for unit tests, which doesn’t persist data on disk, ensuring tests are isolated and repeatable:

val db = Room.inMemoryDatabaseBuilder(
    context, AppDatabase::class.java).build()

With an in-memory database, you can write tests to verify your DAO methods, ensuring that they behave as expected:

@Test
fun testInsertAndRetrieveUser() {
    val user = User(uid = 0, firstName = "Jane", lastName = "Doe")
    db.userDao().insert(user)
    val retrievedUser = db.userDao().getUserById(user.uid)
    assertEquals(user.firstName, retrievedUser?.firstName)
}

This test checks that a user can be inserted and retrieved correctly, helping you maintain the integrity of your database operations.

Conclusion

The Room Persistence Library significantly simplifies working with SQLite databases in Android app development. By providing a robust abstraction layer, Room reduces boilerplate code, ensures type safety, and supports modern development practices such as coroutines and LiveData integration. By following the steps outlined in this guide, you can efficiently manage local data storage in your Android applications, leading to more maintainable and performant code.

Next page of the Free Ebook:

44Working with SQLite Databases: Backup and Restore SQLite Databases

7 minutes