🗄️ Core Concept · ~35 min read · Intermediate

Room & SQLite

How Android apps store data that survives process death — Room’s three-layer architecture, WAL mode, schema migrations, TypeConverters, atomic transactions, and the Flow integration that makes your UI reactive without a single poll.

🏗️ @Entity

📋 @Dao

🔄 Migrations

✍️ WAL mode

🌊 Flow

Room architecture

SQLite has been on Android since API 1, but writing raw SQL against SQLiteOpenHelper is painful: no compile-time query verification, no object mapping, manual cursor parsing, and migrations that are trivial to get wrong. Room is Google’s persistence library that wraps SQLite in a clean, annotation-driven abstraction — while keeping you close enough to the metal that you can understand and control exactly what SQL runs.

Room has exactly three components. Every Room database is built from these three layers and nothing else. If you keep this model clear in your head, the rest of Room falls into place.

📋 Room’s three-layer architecture

The key architectural insight: your code only ever touches the @Dao interface. Room generates the concrete implementation at compile time — you can inspect the generated class in build/generated/ to see the exact SQL being run. This compile-time generation is also how Room catches bad queries before your app ships: a typo in a @Query annotation is a build error, not a runtime crash.

💡 The analogy

Think of Room as a very strict assistant managing a filing cabinet (SQLite). You hand Room a typed request form (@Dao method) — Room translates it into the right drawer and folder lookup (SQL), retrieves the document, and hands it back as a proper Kotlin object. You never touch the raw filing cabinet yourself.

@Entity — defining your schema

An @Entity class maps directly to a SQLite table. Each property maps to a column. Room infers column names from property names, but you can override with @ColumnInfo. The primary key is declared with @PrimaryKey — if you want SQLite to auto-assign integer IDs, set autoGenerate = true.

User.kt — @Entity with all common annotations

@Entity(
    tableName = "users",
    indices = [Index(value = ["email"], unique = true)]   // unique email index
)
data class User(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,

    @ColumnInfo(name = "full_name")          // column is "full_name", not "name"
    val name: String,

    val email: String,

    @ColumnInfo(defaultValue = "0")
    val isPremium: Boolean = false,

    // Stored as a Long (epoch ms); TypeConverter handles Date ↔ Long
    val createdAt: Date
)

// ── Foreign key example ─────────────────────────────────────────────────
@Entity(
    tableName = "orders",
    foreignKeys = [ForeignKey(
        entity = User::class,
        parentColumns = ["id"],
        childColumns = ["user_id"],
        onDelete = ForeignKey.CASCADE   // delete orders when user is deleted
    )],
    indices = [Index("user_id")]            // always index FK columns — Room warns if missing
)
data class Order(
    @PrimaryKey(autoGenerate = true) val orderId: Int = 0,
    val userId: Int,
    val total: Double
)

⚠️ Always index foreign key columns

Room emits a warning if you declare a foreign key without a matching index. Without an index, every delete on the parent table triggers a full scan of the child table to find cascading rows. On large datasets this causes visible jank. Always add indices = [Index("fk_column")] alongside every foreign key.

Embedded objects and relations

Sometimes a model has a nested object that should be stored flat in the same table — use @Embedded. For true one-to-many relations across tables, use @Relation with a data class that holds the parent and a list of children.

@Embedded and @Relation

// ── @Embedded: flatten Address into User table ─────────────────────────
data class Address(
    val street: String,
    val city: String,
    val postalCode: String
)

@Entity(tableName = "users")
data class User(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    val name: String,
    @Embedded val address: Address   // columns: street, city, postalCode in users table
)

// ── @Relation: one User has many Orders ───────────────────────────────
data class UserWithOrders(
    @Embedded val user: User,
    @Relation(
        parentColumn = "id",
        entityColumn = "user_id"
    )
    val orders: List<Order>
)

// In DAO — must be annotated @Transaction to load parent + children atomically
@Transaction
@Query("SELECT * FROM users WHERE id = :userId")
fun getUserWithOrders(userId: Int): Flow<UserWithOrders>

@Dao — queries, inserts, updates, deletes

The DAO (Data Access Object) is where you write all your database operations. It’s an interface — you never implement it. Room reads your annotations at compile time and generates a concrete class that handles the SQL, cursor parsing, and thread marshalling.

UserDao.kt — the full DAO pattern

@Dao
interface UserDao {

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

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertUsers(users: List<User>)

    // ── UPDATE / DELETE ────────────────────────────────────────────────────
    @Update
    suspend fun updateUser(user: User)         // matches by primary key

    @Delete
    suspend fun deleteUser(user: User)

    @Query("DELETE FROM users WHERE id = :id")
    suspend fun deleteUserById(id: Int)

    // ── QUERIES ────────────────────────────────────────────────────────────
    @Query("SELECT * FROM users ORDER BY full_name ASC")
    fun getAllUsers(): Flow<List<User>>              // reactive — emits on every change

    @Query("SELECT * FROM users WHERE id = :id")
    fun getUserById(id: Int): Flow<User?>

    @Query("SELECT * FROM users WHERE email = :email LIMIT 1")
    suspend fun getUserByEmail(email: String): User?  // one-shot suspend

    @Query("SELECT * FROM users WHERE isPremium = 1")
    fun getPremiumUsers(): Flow<List<User>>

    // ── PARTIAL UPDATE with @Query ─────────────────────────────────────────
    @Query("UPDATE users SET isPremium = :isPremium WHERE id = :id")
    suspend fun setUserPremium(id: Int, isPremium: Boolean)

    // ── COUNT / AGGREGATE ─────────────────────────────────────────────────
    @Query("SELECT COUNT(*) FROM users")
    fun getUserCount(): Flow<Int>
}

ℹ️ suspend vs Flow return types

Use suspend fun for one-shot operations: insert, update, delete, or a single read that doesn’t need to react to future changes. Use Flow<T> (no suspend) when you want the UI to automatically update whenever the underlying data changes — Room will re-emit the query result every time any row in the queried table is written. Never call Flow-returning DAO methods on the main thread; collect them in a coroutine scope (typically viewModelScope).

OnConflictStrategy	Behaviour on primary key collision	Use when
ABORT	Throws exception, rolls back statement	Default — you want crashes to surface data bugs
REPLACE	Deletes old row, inserts new one	Upsert pattern — syncing from server
IGNORE	Silently skips the conflicting insert	Inserting a batch where some may already exist
FAIL	Throws exception, does NOT roll back prior statements	Rare — partial batch inserts

TypeConverters

SQLite only knows about a handful of types: INTEGER, REAL, TEXT, BLOB, NULL. When your entity has a Date, a List<String>, an enum, or any custom type, you need to tell Room how to convert it to and from a SQLite-native type. That’s what @TypeConverter methods do.

Converters.kt — Date, List, and Enum converters

class Converters {

    // ── Date ↔ Long (epoch milliseconds) ──────────────────────────────────
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? = value?.let { Date(it) }

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? = date?.time

    // ── List<String> ↔ comma-separated TEXT ───────────────────────────────
    @TypeConverter
    fun fromStringList(value: String?): List<String> =
        value?.split(",")?.filter { it.isNotBlank() } ?: emptyList()

    @TypeConverter
    fun toStringList(list: List<String>?): String =
        list?.joinToString(",") ?: ""

    // ── Enum ↔ String (safer than ordinal — survives enum reordering) ──────
    @TypeConverter
    fun fromUserRole(role: UserRole?): String? = role?.name

    @TypeConverter
    fun toUserRole(value: String?): UserRole? =
        value?.let { enumValueOf<UserRole>(it) }
}

// Register on the @Database class — applies to ALL daos in the database
@Database(entities = [User::class], version = 1)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

⚠️ Never store enums by ordinal

Storing role.ordinal (an integer 0, 1, 2…) is fragile: reordering enum values in code silently corrupts old data in the database. Always store enums by name (the string "ADMIN", "USER"). It’s slightly more storage but immune to refactoring.

WAL mode

By default, SQLite uses a journalling mode called DELETE (also called rollback journal). Before any write, the old data is copied to a separate journal file. If something goes wrong, SQLite rolls back by copying from the journal. This works, but it means reads and writes block each other — while a write transaction is in progress, all readers wait.

Room 2.1+ enables WAL (Write-Ahead Logging) by default. WAL is a fundamentally different approach: instead of copying the old data out before writing, SQLite appends the new data to a separate WAL file. The original database file is untouched during the write.

✍️ DELETE journal vs WAL — how concurrent access differs

The benefit: readers never block writers, writers never block readers. Multiple readers can read the database simultaneously even while a write is in progress — they simply read the old committed snapshot in the main database file while the writer works in the WAL file. SQLite periodically checkpoints the WAL back into the main file (default: every 1000 pages).

Room enables WAL by default since version 2.1. You can verify or configure it:

AppDatabase.kt — WAL config and singleton setup

@Database(entities = [User::class, Order::class], version = 2)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {

    abstract fun userDao(): UserDao
    abstract fun orderDao(): OrderDao

    companion object {
        @Volatile private var INSTANCE: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                )
                .setJournalMode(JournalMode.WRITE_AHEAD_LOGGING)  // explicit (already default)
                .fallbackToDestructiveMigration()  // dev only! see Migrations section
                .build()
                .also { INSTANCE = it }
            }
        }
    }
}

// ── Hilt module (preferred over singleton companion) ─────────────────
@Module
@InstallIn(SingletonComponent::class)
object DatabaseModule {

    @Provides @Singleton
    fun provideDatabase(@ApplicationContext context: Context): AppDatabase =
        Room.databaseBuilder(context, AppDatabase::class.java, "app_database")
            .build()

    @Provides @Singleton
    fun provideUserDao(db: AppDatabase): UserDao = db.userDao()
}

Migrations

Every time you change your schema — add a column, rename a table, add an index — you must increment the version number in @Database and provide a Migration object that tells Room how to get from the old schema to the new one. Room runs these migrations in sequence at database open time. If no migration path exists, Room throws an IllegalStateException — unless you’ve told it to fallbackToDestructiveMigration(), which wipes the database. Never use destructive migration in production.

Migrations.kt — versioned migrations

// Version 1 → 2: add "is_premium" column to users
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL(
            "ALTER TABLE users ADD COLUMN is_premium INTEGER NOT NULL DEFAULT 0"
        )
    }
}

// Version 2 → 3: create orders table
val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS orders (
                orderId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                userId  INTEGER NOT NULL,
                total   REAL NOT NULL,
                FOREIGN KEY(userId) REFERENCES users(id) ON DELETE CASCADE
            )
        """.trimIndent())
        db.execSQL("CREATE INDEX IF NOT EXISTS index_orders_userId ON orders(userId)")
    }
}

// Register migrations on the builder — Room applies them in version order
Room.databaseBuilder(context, AppDatabase::class.java, "app_database")
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
    .build()

Auto migrations (Room 2.4+)

For simple schema changes — adding a column, adding a table — Room 2.4 can generate the migration SQL automatically. You declare the intent in @AutoMigration and Room handles the rest. For complex changes like renames and deletions, you provide a spec class.

Auto migrations — simple and complex

// Simple auto migration: Room figures out the SQL from schema diff
@Database(
    entities = [User::class],
    version = 3,
    autoMigrations = [
        AutoMigration(from = 1, to = 2),              // added a column → auto handled
        AutoMigration(from = 2, to = 3, spec = MyMigrationSpec::class)  // rename
    ]
)
abstract class AppDatabase : RoomDatabase() { ... }

// Complex spec: tell Room what you renamed
@RenameColumn(tableName = "users", fromColumnName = "name", toColumnName = "full_name")
class MyMigrationSpec : AutoMigrationSpec
// Other specs: @DeleteColumn, @RenameTable, @DeleteTable

✅ Migration testing tip

Room exports your schema as JSON files to app/schemas/ when you set room.schemaLocation in your build.gradle. Commit these files. Then use MigrationTestHelper in your tests to verify each migration runs without data loss. This is the only reliable way to catch migration bugs before users do.

Scenario	Use	Gotcha
Add column with default	AutoMigration or ALTER TABLE ADD COLUMN	SQLite only allows adding columns, not removing them directly
Remove a column	Manual migration — copy table, drop old, rename new	SQLite has no DROP COLUMN before 3.35; Room handles the copy pattern
Rename a column	AutoMigration + @RenameColumn spec	Not possible with plain ALTER TABLE in older SQLite versions
Add an index	CREATE INDEX in migration	Adding to @Entity is not enough — you must also write the migration SQL

Transactions

A transaction makes multiple database operations atomic: either all succeed and are committed, or the entire group is rolled back. This is critical for operations that must stay consistent — for example, transferring funds between two accounts, or writing an order and its line items together.

In Room there are two ways to run transactions. The simple case: annotate a DAO method with @Transaction. The complex case: call withTransaction on the database directly (available as a suspend extension function).

Transactions — @Transaction and withTransaction

// ── @Transaction on a DAO method ───────────────────────────────────────
// Room wraps this entire function in BEGIN/COMMIT
@Dao
interface OrderDao {

    @Insert
    suspend fun insertOrder(order: Order): Long

    @Insert
    suspend fun insertLineItems(items: List<LineItem>)

    @Transaction
    suspend fun insertOrderWithItems(order: Order, items: List<LineItem>) {
        val orderId = insertOrder(order)
        insertLineItems(items.map { it.copy(orderId = orderId.toInt()) })
    }   // if insertLineItems throws, insertOrder is rolled back automatically
}

// ── withTransaction — for repository-level transactions ─────────────
// Useful when you need to coordinate across multiple DAOs
suspend fun transferBalance(fromId: Int, toId: Int, amount: Double) {
    database.withTransaction {
        val from = accountDao.getAccount(fromId) ?: throw IllegalStateException("No account")
        if (from.balance < amount) throw InsufficientFundsException()

        accountDao.debit(fromId, amount)   // UPDATE accounts SET balance = balance - ? WHERE id = ?
        accountDao.credit(toId, amount)    // UPDATE accounts SET balance = balance + ? WHERE id = ?
        auditDao.logTransfer(fromId, toId, amount)
    }   // any exception → full rollback of all three operations
}

// ── @Transaction required for @Relation queries ──────────────────────
// Room issues two separate queries for parent + children.
// @Transaction ensures the data is consistent between the two reads.
@Transaction
@Query("SELECT * FROM users")
fun getAllUsersWithOrders(): Flow<List<UserWithOrders>>

⚠️ Never do heavy work inside a transaction

A transaction holds a write lock on the database for its entire duration. Network calls, image processing, or complex computation inside a withTransaction block will block all other writers for that entire time. Do your heavy work first, then enter the transaction with only the prepared data you need to write.

Room + Flow — reactive persistence

This is the feature that makes Room genuinely powerful for modern Android development. When a DAO method returns Flow<T>, Room doesn’t just run the query once. It runs it immediately and emits the result, then watches the underlying table. Every time any row in that table is inserted, updated, or deleted, Room automatically re-runs the query and emits the new result downstream.

The effect: your UI is always showing the current state of the database, with zero polling code. The data flows from SQLite through Room’s Flow, through your repository, through your ViewModel, and into Compose or your View adapter — automatically.

🌊 Reactive data flow — database write to UI update

Repository + ViewModel + Compose — end-to-end reactive pattern

// ── Repository: wraps DAO, exposes Flow ──────────────────────────────
class UserRepository @Inject constructor(
    private val userDao: UserDao
) {
    val allUsers: Flow<List<User>> = userDao.getAllUsers()

    suspend fun addUser(user: User) = userDao.insertUser(user)
    suspend fun deleteUser(user: User) = userDao.deleteUser(user)
}

// ── ViewModel: converts Flow to StateFlow for the UI ────────────────
@HiltViewModel
class UserViewModel @Inject constructor(
    private val repo: UserRepository
) : ViewModel() {

    val users: StateFlow<List<User>> = repo.allUsers
        .stateIn(
            scope = viewModelScope,
            started = SharingStarted.WhileSubscribed(5_000),
            initialValue = emptyList()
        )

    fun addUser(name: String, email: String) {
        viewModelScope.launch {
            repo.addUser(User(name = name, email = email, createdAt = Date()))
        }
    }
}

// ── Compose UI: collects StateFlow ──────────────────────────────────
@Composable
fun UserListScreen(viewModel: UserViewModel = hiltViewModel()) {
    val users by viewModel.users.collectAsStateWithLifecycle()

    LazyColumn {
        items(users, key = { it.id }) { user ->
            UserListItem(user)
        }
    }
}

🧠 The interview answer

When asked “how do you make the UI update automatically when the database changes?” — the full answer is: DAO returns Flow<T> (not suspend), Room internally registers an InvalidationTracker observer on the queried tables, which triggers a re-query on any write. The Flow is collected in viewModelScope and converted to a StateFlow via stateIn with WhileSubscribed(5000) (which cancels upstream when the UI is gone but keeps it alive for 5s to survive config changes). Compose collects the StateFlow with collectAsStateWithLifecycle(), which respects the lifecycle and stops collecting in the background. Each layer has a specific reason for existing.