Android Data Persistence with Room

Part 5 of the Android Deep Dive series

Room provides an abstraction layer over SQLite with compile-time query verification. Combined with an offline-first architecture, apps remain fully functional without network connectivity. This is one of the most practical interview topics - every production app needs robust data persistence.

Room Setup

// Entity - Maps to database table
@Entity(tableName = "meals")
data class MealEntity(
    @PrimaryKey
    val id: String,

    @ColumnInfo(name = "user_id")
    val userId: String,

    val name: String,

    @ColumnInfo(name = "meal_type")
    val mealType: MealType,

    @ColumnInfo(name = "consumed_at")
    val consumedAt: Instant,

    @ColumnInfo(name = "total_calories")
    val totalCalories: Double = 0.0,

    @ColumnInfo(name = "total_protein")
    val totalProtein: Double = 0.0,

    @ColumnInfo(name = "total_carbs")
    val totalCarbs: Double = 0.0,

    @ColumnInfo(name = "total_fat")
    val totalFat: Double = 0.0,

    @ColumnInfo(name = "sync_status")
    val syncStatus: SyncStatus = SyncStatus.PENDING,

    @ColumnInfo(name = "is_deleted")
    val isDeleted: Boolean = false,

    @ColumnInfo(name = "created_at")
    val createdAt: Instant = Instant.now(),

    @ColumnInfo(name = "updated_at")
    val updatedAt: Instant = Instant.now()
)

enum class MealType {
    BREAKFAST, LUNCH, DINNER, SNACK
}

enum class SyncStatus {
    PENDING, SYNCING, SYNCED, FAILED
}

Type Converters

class Converters {
    @TypeConverter
    fun fromInstant(value: Instant?): Long? = value?.toEpochMilli()

    @TypeConverter
    fun toInstant(value: Long?): Instant? = value?.let { Instant.ofEpochMilli(it) }

    @TypeConverter
    fun fromLocalDate(value: LocalDate?): String? = value?.toString()

    @TypeConverter
    fun toLocalDate(value: String?): LocalDate? = value?.let { LocalDate.parse(it) }

    @TypeConverter
    fun fromMealType(value: MealType): String = value.name

    @TypeConverter
    fun toMealType(value: String): MealType = MealType.valueOf(value)

    @TypeConverter
    fun fromSyncStatus(value: SyncStatus): String = value.name

    @TypeConverter
    fun toSyncStatus(value: String): SyncStatus = SyncStatus.valueOf(value)
}

DAO (Data Access Object)

@Dao
interface MealDao {

    // Insert or update
    @Upsert
    suspend fun upsert(meal: MealEntity)

    @Upsert
    suspend fun upsertAll(meals: List<MealEntity>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(meal: MealEntity)

    @Update
    suspend fun update(meal: MealEntity)

    @Delete
    suspend fun delete(meal: MealEntity)

    // Queries returning Flow (reactive)
    @Query("""
        SELECT * FROM meals
        WHERE user_id = :userId AND is_deleted = 0
        ORDER BY consumed_at DESC
    """)
    fun observeAll(userId: String): Flow<List<MealEntity>>

    @Query("""
        SELECT * FROM meals
        WHERE user_id = :userId
        AND date(consumed_at / 1000, 'unixepoch') = :date
        AND is_deleted = 0
        ORDER BY consumed_at DESC
    """)
    fun observeMealsByDate(userId: String, date: String): Flow<List<MealEntity>>

    // One-shot queries
    @Query("SELECT * FROM meals WHERE id = :id")
    suspend fun getById(id: String): MealEntity?

    @Query("SELECT * FROM meals WHERE sync_status = :status")
    suspend fun getBySyncStatus(status: SyncStatus): List<MealEntity>

    // Aggregations
    @Query("""
        SELECT
            SUM(total_calories) as calories,
            SUM(total_carbs) as carbs,
            SUM(total_protein) as protein,
            SUM(total_fat) as fat
        FROM meals
        WHERE user_id = :userId
        AND date(consumed_at / 1000, 'unixepoch') = :date
        AND is_deleted = 0
    """)
    fun observeDailyTotals(userId: String, date: String): Flow<NutritionTotals?>

    // Soft delete
    @Query("""
        UPDATE meals
        SET is_deleted = 1, updated_at = :now, sync_status = 'PENDING'
        WHERE id = :id
    """)
    suspend fun softDelete(id: String, now: Long = System.currentTimeMillis())

    // Update sync status
    @Query("UPDATE meals SET sync_status = :status WHERE id = :id")
    suspend fun updateSyncStatus(id: String, status: SyncStatus)

    // Cleanup synced deletions
    @Query("DELETE FROM meals WHERE is_deleted = 1 AND sync_status = 'SYNCED'")
    suspend fun cleanupSyncedDeletions()
}

Database Definition

@Database(
    entities = [
        MealEntity::class,
        MealIngredientEntity::class,
        IngredientEntity::class,
        WaterIntakeEntity::class
    ],
    version = 5,
    exportSchema = true
)
@TypeConverters(Converters::class)
abstract class NutritionDatabase : RoomDatabase() {
    abstract fun mealDao(): MealDao
    abstract fun ingredientDao(): IngredientDao
    abstract fun waterDao(): WaterIntakeDao
}

Hilt Module

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

    @Provides
    @Singleton
    fun provideDatabase(@ApplicationContext context: Context): NutritionDatabase {
        return Room.databaseBuilder(
            context,
            NutritionDatabase::class.java,
            "nutrition.db"
        )
        .addMigrations(
            MIGRATION_1_2,
            MIGRATION_2_3,
            MIGRATION_3_4,
            MIGRATION_4_5
        )
        // Only for development!
        // .fallbackToDestructiveMigration()
        .build()
    }

    @Provides
    fun provideMealDao(db: NutritionDatabase): MealDao = db.mealDao()
}

Migrations

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE meals ADD COLUMN is_ai_analyzed INTEGER NOT NULL DEFAULT 0")
    }
}

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Add new table
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS water_intake (
                id TEXT NOT NULL PRIMARY KEY,
                user_id TEXT NOT NULL,
                amount_ml REAL NOT NULL,
                consumed_at INTEGER NOT NULL,
                sync_status TEXT NOT NULL DEFAULT 'PENDING',
                created_at INTEGER NOT NULL
            )
        """)

        // Create index
        db.execSQL("CREATE INDEX index_water_intake_user_id ON water_intake(user_id)")
    }
}

// Complex migration: recreate table with new schema
val MIGRATION_3_4 = object : Migration(3, 4) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("""
            CREATE TABLE meals_new (
                id TEXT NOT NULL PRIMARY KEY,
                user_id TEXT NOT NULL,
                name TEXT NOT NULL,
                meal_type TEXT NOT NULL,
                consumed_at INTEGER NOT NULL,
                total_calories REAL NOT NULL DEFAULT 0,
                total_carbs REAL NOT NULL DEFAULT 0,
                total_protein REAL NOT NULL DEFAULT 0,
                total_fat REAL NOT NULL DEFAULT 0,
                sync_status TEXT NOT NULL DEFAULT 'PENDING',
                is_deleted INTEGER NOT NULL DEFAULT 0,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL
            )
        """)

        db.execSQL("""
            INSERT INTO meals_new SELECT
                id, user_id, name, meal_type, consumed_at,
                total_calories, total_carbs, total_protein, total_fat,
                sync_status, 0, created_at, created_at
            FROM meals
        """)

        db.execSQL("DROP TABLE meals")
        db.execSQL("ALTER TABLE meals_new RENAME TO meals")
        db.execSQL("CREATE INDEX index_meals_user_id ON meals(user_id)")
    }
}

Entity Relationships

// One-to-Many: Meal has many MealIngredients
@Entity(
    tableName = "meal_ingredients",
    foreignKeys = [
        ForeignKey(
            entity = MealEntity::class,
            parentColumns = ["id"],
            childColumns = ["meal_id"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [Index("meal_id")]
)
data class MealIngredientEntity(
    @PrimaryKey
    val id: String,

    @ColumnInfo(name = "meal_id")
    val mealId: String,

    @ColumnInfo(name = "ingredient_id")
    val ingredientId: String?,

    val name: String,
    val quantity: Double,
    val unit: String?,

    @ColumnInfo(name = "estimated_calories")
    val estimatedCalories: Double = 0.0
)

// Query with relationship
data class MealWithIngredients(
    @Embedded val meal: MealEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "meal_id"
    )
    val ingredients: List<MealIngredientEntity>
)

@Dao
interface MealDao {
    @Transaction
    @Query("SELECT * FROM meals WHERE id = :id")
    suspend fun getMealWithIngredients(id: String): MealWithIngredients?

    @Transaction
    @Query("SELECT * FROM meals WHERE user_id = :userId AND is_deleted = 0")
    fun observeMealsWithIngredients(userId: String): Flow<List<MealWithIngredients>>
}

Offline-First Repository

class MealRepository @Inject constructor(
    private val mealDao: MealDao,
    private val api: NutritionApi,
    @IoDispatcher private val dispatcher: CoroutineDispatcher
) {
    // Always read from local database
    fun observeMeals(userId: String, date: LocalDate): Flow<List<Meal>> {
        return mealDao.observeMealsByDate(userId, date.toString())
            .map { entities -> entities.map { it.toDomain() } }
            .flowOn(dispatcher)
    }

    // Create: Save locally first, sync later
    suspend fun createMeal(meal: Meal): Result<Meal> = withContext(dispatcher) {
        val entity = meal.toEntity().copy(
            syncStatus = SyncStatus.PENDING,
            createdAt = Instant.now(),
            updatedAt = Instant.now()
        )

        // Save locally (offline-safe)
        mealDao.insert(entity)

        // Try to sync (best-effort)
        trySyncMeal(entity)

        Result.Success(entity.toDomain())
    }

    // Delete: Soft delete locally, sync later
    suspend fun deleteMeal(id: String): Result<Unit> = withContext(dispatcher) {
        mealDao.softDelete(id)
        trySyncDeletion(id)
        Result.Success(Unit)
    }

    // Sync pending changes (called when network available)
    suspend fun syncPendingChanges() = withContext(dispatcher) {
        val pending = mealDao.getBySyncStatus(SyncStatus.PENDING)

        pending.forEach { entity ->
            try {
                mealDao.updateSyncStatus(entity.id, SyncStatus.SYNCING)

                if (entity.isDeleted) {
                    api.deleteMeal(entity.id)
                } else {
                    api.createMeal(entity.toCreateRequest())
                }

                mealDao.updateSyncStatus(entity.id, SyncStatus.SYNCED)
            } catch (e: Exception) {
                mealDao.updateSyncStatus(entity.id, SyncStatus.FAILED)
            }
        }

        // Cleanup synced deletions
        mealDao.cleanupSyncedDeletions()
    }

    // Pull latest from server
    suspend fun refreshFromServer(userId: String, date: LocalDate) = withContext(dispatcher) {
        try {
            val serverMeals = api.getMeals(date.toString(), userId)
            val entities = serverMeals.map {
                it.toEntity().copy(syncStatus = SyncStatus.SYNCED)
            }
            mealDao.upsertAll(entities)
        } catch (e: Exception) {
            // Offline - use cached data
        }
    }

    private suspend fun trySyncMeal(entity: MealEntity) {
        try {
            api.createMeal(entity.toCreateRequest())
            mealDao.updateSyncStatus(entity.id, SyncStatus.SYNCED)
        } catch (e: Exception) {
            // Will sync later
        }
    }
}

Interview Questions

Q: What is Room and why use it over raw SQLite?

Room is a persistence library that provides:

1. Compile-time verification of SQL queries
2. Type-safe DAOs with Kotlin/Java methods
3. Flow/LiveData integration for reactive updates
4. Migration support for schema changes
5. Less boilerplate than raw SQLite

// Raw SQLite - Error-prone, no compile-time checks
db.execSQL("SLECT * FROM users")  // Typo not caught until runtime!

// Room - Compile-time verification
@Query("SLECT * FROM users")  // Won't compile - caught at build time
fun getUsers(): List<User>

Q: How do you handle database migrations?

Define Migration objects for each version change:

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

Room.databaseBuilder(...)
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
    .build()

For complex changes (changing column types):

1. Create new table with correct schema
2. Copy data from old table
3. Drop old table
4. Rename new table

Q: How do you implement offline-first architecture?

Key principles:

1. Local database is source of truth - UI always reads from Room
2. Optimistic updates - Save locally immediately, sync later
3. Sync status tracking - Know what needs to sync
4. Conflict resolution - Handle server/local conflicts

suspend fun createMeal(meal: Meal) {
    // 1. Save locally (works offline)
    mealDao.insert(meal.copy(syncStatus = PENDING))

    // 2. Try to sync (best effort)
    try {
        api.createMeal(meal)
        mealDao.updateSyncStatus(meal.id, SYNCED)
    } catch (e: IOException) {
        // Will sync when online
    }
}

// UI reads from local DB - always works
val meals = mealDao.observeAll().collect { ... }

Q: When should you use @Transaction?

Use @Transaction when:

1. Multiple queries need atomic execution
2. Reading related entities (@Relation)
3. Write operations that must succeed together

@Transaction
@Query("SELECT * FROM orders WHERE id = :id")
suspend fun getOrderWithItems(id: String): OrderWithItems

@Transaction
suspend fun transferFunds(from: Account, to: Account, amount: Double) {
    updateBalance(from.id, from.balance - amount)
    updateBalance(to.id, to.balance + amount)
}

Common Mistakes

1. Querying on Main Thread

// BAD - Crashes with "Cannot access database on main thread"
val meal = mealDao.getById("123")

// GOOD - Use suspend function or Flow
viewModelScope.launch {
    val meal = mealDao.getById("123")  // Runs on IO dispatcher
}

// OR observe with Flow (automatically off main thread)
mealDao.observeAll().collect { meals -> ... }

2. N+1 Query Problem

// BAD - Separate query for each meal's ingredients
meals.forEach { meal ->
    val ingredients = ingredientDao.getByMealId(meal.id)  // N queries!
}

// GOOD - Use @Relation
data class MealWithIngredients(
    @Embedded val meal: Meal,
    @Relation(parentColumn = "id", entityColumn = "meal_id")
    val ingredients: List<Ingredient>
)

@Transaction
@Query("SELECT * FROM meals")
fun getAllWithIngredients(): List<MealWithIngredients>  // 2 queries total

3. Not Testing Migrations

@RunWith(AndroidJUnit4::class)
class MigrationTest {
    @get:Rule
    val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        NutritionDatabase::class.java
    )

    @Test
    fun migrate1To2() {
        helper.createDatabase(TEST_DB, 1).apply {
            execSQL("INSERT INTO meals VALUES (...)")
            close()
        }

        helper.runMigrationsAndValidate(TEST_DB, 2, true, MIGRATION_1_2)

        val db = helper.openDatabase(TEST_DB)
        val cursor = db.query("SELECT is_ai_analyzed FROM meals")
        assertTrue(cursor.moveToFirst())
    }
}

4. Forgetting Indices

// BAD - Slow queries on large tables
@Query("SELECT * FROM meals WHERE user_id = :userId")
fun getByUserId(userId: String): List<Meal>

// GOOD - Add index for frequently queried columns
@Entity(
    tableName = "meals",
    indices = [
        Index("user_id"),
        Index("consumed_at"),
        Index(value = ["user_id", "consumed_at"])  // Compound index
    ]
)
data class MealEntity(...)

Summary Table

Concept	Purpose	Key Points
@Entity	Define table schema	Primary key required
@Dao	Database operations	Interface with queries
@TypeConverter	Convert custom types	Instant, enums, lists
@Upsert	Insert or update	Cleaner than checking first
@Transaction	Atomic operations	Required for @Relation
@Relation	One-to-many queries	Avoids N+1 problem
Migration	Schema changes	Never use destructive in prod
exportSchema = true	Version control schemas	Helps catch migration issues

---

Next in series: Part 6 - Flow & Reactive Patterns covers StateFlow, SharedFlow, and advanced coroutine patterns.