Room, SQLite without the boilerplate

Writing raw SQLite in Android is painful. You write the CREATE TABLE statement as a String. You build a ContentValues to insert data. You write a cursor loop to read it back. Typos in column names only surface at runtime.

Room fixes all of this.

Three annotations

Room is built around three concepts: Entity, DAO, and Database.

Entity is a table:

@Entity(tableName = "users")
data class User(
    @PrimaryKey val id: String,
    val name: String,
    val email: String,
    val createdAt: Long
)

DAO is where you write queries:

@Dao
interface UserDao {
    @Query("SELECT * FROM users ORDER BY createdAt DESC")
    fun getAllUsers(): LiveData<List<User>>

    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getUserById(userId: String): User?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: User)

    @Delete
    suspend fun deleteUser(user: User)
}

Database ties everything together:

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

Create it once:

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

Compile-time verification

This is the best part. If you write a wrong column name in a @Query, the build fails:

error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: naem)

With raw SQLite this would be a crash at runtime on the user device. With Room it is a compile error. You never ship that bug.

LiveData integration

Returning LiveData from a DAO query means the UI updates automatically when the database changes. Write a new user, the list refreshes. Delete one, it disappears. No manual refresh needed.

userDao.getAllUsers().observe(this) { users ->
    adapter.submitList(users)
}

The ViewModel holds the reference, the Activity observes. Data flows from database to UI without any manual coordination.

Migrations

When you change the schema, you have to provide a Migration:

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE users ADD COLUMN phoneNumber TEXT")
    }
}

Room.databaseBuilder(context, AppDatabase::class.java, "app-database")
    .addMigrations(MIGRATION_1_2)
    .build()

If you forget to provide a migration and bump the version, Room throws an exception on app launch. It is strict. This is good because database migration errors on production are hard to recover from.

Replacing old SQLite code

We have a helper class with 400 lines of raw SQLite. Cursor loops, column index constants, ContentValues. After migrating to Room it is about 60 lines of clean Kotlin. The DAO replaces most of the helper, the Entity replaces the model mapping code.

The migration takes a day. We should have done it earlier.