Room Database in Android: What I Wish I Knew Before My First Production App

I shipped my first QR scanner app without scan history. Users complained. Fair enough - scanning the same WiFi QR code every time is annoying. So I added history using raw SQLite.

That was a mistake.

Cursor management, forgetting to close database connections, SQL typos that only crashed in production. I spent more time debugging database code than building actual features. When I rewrote it with Room, the scan history feature took an afternoon instead of a week.

Raw SQLite vs Room #

Here's what fetching scan history looked like before Room:

fun getAllScans(): List<ScanRecord> {
    val scans = mutableListOf<ScanRecord>()
    val db = dbHelper.readableDatabase
    var cursor: Cursor? = null
    try {
        cursor = db.rawQuery(
            "SELECT * FROM scan_history ORDER BY timestamp DESC",
            null
        )
        while (cursor.moveToNext()) {
            scans.add(
                ScanRecord(
                    id = cursor.getLong(cursor.getColumnIndexOrThrow("id")),
                    content = cursor.getString(cursor.getColumnIndexOrThrow("content")),
                    format = cursor.getString(cursor.getColumnIndexOrThrow("format")),
                    timestamp = cursor.getLong(cursor.getColumnIndexOrThrow("timestamp")),
                    isFavorite = cursor.getInt(cursor.getColumnIndexOrThrow("is_favorite")) == 1
                )
            )
        }
    } finally {
        cursor?.close()
    }
    return scans
}

And here's the same thing with Room:

@Query("SELECT * FROM scan_history ORDER BY timestamp DESC")
fun getAllScans(): Flow<List<ScanRecord>>

One line. The SQL is checked at compile time. If I typo a column name, the build fails instead of crashing on a user's phone. And it returns a Flow, so my UI updates automatically when data changes.

Setup #

Add these to your module's build.gradle.kts:

plugins {
    id("com.google.devtools.ksp")
}

dependencies {
    val roomVersion = "2.6.1"
    implementation("androidx.room:room-runtime:$roomVersion")
    implementation("androidx.room:room-ktx:$roomVersion")
    ksp("androidx.room:room-compiler:$roomVersion")
}

Use KSP, not KAPT. It's faster and Google recommends it now.

The Entity #

An Entity is just a data class that maps to a database table. For scan history:

@Entity(tableName = "scan_history")
data class ScanRecord(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val content: String,
    val format: String,
    val timestamp: Long,
    @ColumnInfo(name = "is_favorite")
    val isFavorite: Boolean = false,
    val label: String? = null
)

Few things I learned:

• autoGenerate = true handles ID creation. Don't generate IDs yourself.
• @ColumnInfo lets you use snake_case in the database while keeping camelCase in Kotlin. I prefer this because SQL conventions use underscores.
• Nullable fields like label become nullable columns. Room handles this correctly.
• Default values work. New scans get isFavorite = false automatically.

The DAO #

This is where you define database operations. I use suspend functions for one-shot operations and Flow for data I want to observe.

@Dao
interface ScanDao {

    @Query("SELECT * FROM scan_history ORDER BY timestamp DESC")
    fun getAllScans(): Flow<List<ScanRecord>>

    @Query("SELECT * FROM scan_history WHERE is_favorite = 1 ORDER BY timestamp DESC")
    fun getFavorites(): Flow<List<ScanRecord>>

    @Query("SELECT * FROM scan_history WHERE content LIKE '%' || :query || '%'")
    fun searchScans(query: String): Flow<List<ScanRecord>>

    @Insert
    suspend fun insert(scan: ScanRecord)

    @Query("UPDATE scan_history SET is_favorite = NOT is_favorite WHERE id = :scanId")
    suspend fun toggleFavorite(scanId: Long)

    @Query("DELETE FROM scan_history WHERE id = :scanId")
    suspend fun delete(scanId: Long)

    @Query("DELETE FROM scan_history WHERE timestamp < :cutoffTime AND is_favorite = 0")
    suspend fun deleteOldScans(cutoffTime: Long)
}

The search query uses SQLite's LIKE with wildcards. Works fine for hundreds of records. If you're dealing with thousands, consider FTS (Full-Text Search), but I've never needed it for scan history.

That deleteOldScans query is useful - I run it on app launch to clear scans older than 30 days, but only if they're not favorited. Users don't lose their starred items.

Database Class #

@Database(
    entities = [ScanRecord::class],
    version = 1
)
abstract class ScanDatabase : RoomDatabase() {
    abstract fun scanDao(): ScanDao
}

For dependency injection with Hilt:

@Module
@InstallIn(SingletonComponent::class)
object DatabaseModule {

    @Provides
    @Singleton
    fun provideDatabase(@ApplicationContext context: Context): ScanDatabase {
        return Room.databaseBuilder(
            context,
            ScanDatabase::class.java,
            "scan_database"
        ).build()
    }

    @Provides
    fun provideScanDao(database: ScanDatabase): ScanDao {
        return database.scanDao()
    }
}

The @Singleton annotation matters. You want one database instance for the entire app. Multiple instances cause locking issues and weird bugs.

ViewModel Integration #

Here's how I connect the DAO to the UI:

@HiltViewModel
class ScanHistoryViewModel @Inject constructor(
    private val scanDao: ScanDao
) : ViewModel() {

    val scans: StateFlow<List<ScanRecord>> = scanDao.getAllScans()
        .stateIn(
            scope = viewModelScope,
            started = SharingStarted.WhileSubscribed(5000),
            initialValue = emptyList()
        )

    fun onScanCompleted(content: String, format: String) {
        viewModelScope.launch {
            scanDao.insert(
                ScanRecord(
                    content = content,
                    format = format,
                    timestamp = System.currentTimeMillis()
                )
            )
        }
    }

    fun onToggleFavorite(scanId: Long) {
        viewModelScope.launch {
            scanDao.toggleFavorite(scanId)
        }
    }

    fun onDelete(scanId: Long) {
        viewModelScope.launch {
            scanDao.delete(scanId)
        }
    }
}

The WhileSubscribed(5000) keeps the Flow active for 5 seconds after the last subscriber disappears. This handles configuration changes - if the user rotates the screen, the Flow doesn't restart immediately.

In your Composable, just collect the state:

@Composable
fun ScanHistoryScreen(viewModel: ScanHistoryViewModel = hiltViewModel()) {
    val scans by viewModel.scans.collectAsStateWithLifecycle()

    LazyColumn {
        items(scans, key = { it.id }) { scan ->
            ScanItem(
                scan = scan,
                onFavoriteClick = { viewModel.onToggleFavorite(scan.id) },
                onDeleteClick = { viewModel.onDelete(scan.id) }
            )
        }
    }
}

When you insert, delete, or update a scan, the UI updates automatically. No manual refresh needed.

Migrations #

This is where I messed up badly once.

I wanted to add a label field so users could add notes to scans. Simple change, right? I added the field to the Entity, bumped the version to 2, and shipped. The app crashed for every existing user.

Room doesn't know how to transform version 1 to version 2 automatically. You have to tell it:

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

// In your database builder
Room.databaseBuilder(context, ScanDatabase::class.java, "scan_database")
    .addMigrations(MIGRATION_1_2)
    .build()

The migration SQL runs once when a user with version 1 opens the app after updating. Their data stays intact.

You might see fallbackToDestructiveMigration() in tutorials. This deletes all user data when the schema changes. Fine for development, but never use it in production. Users will uninstall your app if their data disappears.

Mistakes I Made #

Querying on the main thread. Room blocks this by default and crashes. If you see "Cannot access database on the main thread", you forgot to use a coroutine or background thread. All my DAO functions are either suspend or return Flow which handles threading automatically.

Forgetting @Transaction. If you're doing multiple database operations that should succeed or fail together, wrap them:

@Transaction
suspend fun replaceAllScans(scans: List<ScanRecord>) {
    deleteAll()
    insertAll(scans)
}

Without @Transaction, a crash between delete and insert leaves the database empty.

Not testing migrations. Room provides MigrationTestHelper for this. I didn't use it at first. Then I shipped a broken migration. Test your migrations.

Indexing. If you're querying by a specific column frequently (like searching by format), add an index:

@Entity(
    tableName = "scan_history",
    indices = [Index(value = ["format"])]
)

I didn't need this for scan history since the dataset is small, but it matters for larger tables.

When Room Isn't the Answer #

Room is great for structured data with relationships. But sometimes it's overkill:

• Simple key-value pairs: Use DataStore instead. User preferences, settings, feature flags.
• Files or images: Store them in the filesystem, keep only the path in Room.
• Huge datasets with complex queries: Consider SQLDelight or direct SQLite if you need more control.

For most apps though, Room handles everything you need.

Room turned database code from my least favorite part of Android development into something I don't think about much anymore. It just works. The compile-time SQL checking alone has saved me from countless production bugs.

Start simple - one Entity, one DAO, basic CRUD. Add complexity when you actually need it. You probably don't need FTS, triggers, or database views for your first feature. I certainly didn't for scan history.