Room database

Why a local database?

Many apps must remember data between launches and work without internet: an expense tracker, a notes app, a cached product catalogue. SQLite is a small database engine built into every Android phone. Writing raw SQLite code is long and error-prone, so Google provides Room, a library on top of SQLite that checks your SQL queries at compile time and maps rows to Kotlin objects.

Entity

@Entity(tableName = "expenses")
data class Expense(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val title: String,
    val amountPaise: Long,
    val category: String,
    val createdAt: Long = System.currentTimeMillis(),
)

DAO

@Dao
interface ExpenseDao {
    @Query("SELECT * FROM expenses ORDER BY createdAt DESC")
    fun observeAll(): Flow<List<Expense>>                 // live updates

    @Query("SELECT SUM(amountPaise) FROM expenses WHERE category = :category")
    fun totalFor(category: String): Flow<Long?>

    @Insert
    suspend fun insert(expense: Expense)

    @Update
    suspend fun update(expense: Expense)

    @Delete
    suspend fun delete(expense: Expense)
}

Two styles of functions: suspend functions do one piece of work (insert, update, a single read) and Room runs them off the main thread. Functions returning Flow emit the current result and then a new result every time the table changes, which is perfect for a list screen that should update itself after an insert.

Database

@Database(entities = [Expense::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun expenseDao(): ExpenseDao
}

// Create ONE instance for the whole app (for example in a Hilt module or an app container):
val db = Room.databaseBuilder(applicationContext, AppDatabase::class.java, "app.db").build()

Room generates code at build time using KSP (Kotlin Symbol Processing). Add the Room runtime, room-ktx and the room-compiler with ksp(...) in your Gradle file; the official Room guide shows the current setup. Older tutorials use kapt, which is in maintenance mode; prefer KSP.

Migrations

When you change an entity (add a column), you must increase the database version and tell Room how to move existing users' data: a Migration with the SQL to run, or an AutoMigration for simple changes. fallbackToDestructiveMigration() simply deletes and recreates the database, which loses user data; never ship it for important data without thinking.

Common mistakes

• Running database calls on the main thread: Room refuses (IllegalStateException). Use suspend functions or Flow.
• Creating a new database instance on every screen. Use a single shared instance.
• Changing an entity without increasing the version and adding a migration: the app crashes on update for existing users.
• Storing money as Double. Store paise as Long.