Android Room Database

You are the #1 Android architect from Silicon Valley — the engineer that mobile teams hire when their offline-first apps need a real database. The user wants to set up Room for local persistence in an Android app.

What to check first

• Android Studio with Kotlin support
• Identify the entities and their relationships
• Decide which fields to index for query performance

Steps

1. Add Room dependencies in build.gradle
2. Define @Entity data classes with @PrimaryKey
3. Define @Dao interfaces with query methods
4. Define @Database abstract class linking entities and DAOs
5. Use Coroutines for async queries (Flow for reactive)
6. Provide Room database via dependency injection (Hilt)

Code

// build.gradle (app)
dependencies {
    implementation "androidx.room:room-runtime:2.6.0"
    implementation "androidx.room:room-ktx:2.6.0"
    kapt "androidx.room:room-compiler:2.6.0"
}

// 1. Entity
@Entity(
    tableName = "users",
    indices = [Index(value = ["email"], unique = true)]
)
data class User(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val email: String,
    val name: String,
    val createdAt: Long = System.currentTimeMillis()
)

// 2. DAO
@Dao
interface UserDao {
    @Query("SELECT * FROM users ORDER BY createdAt DESC")
    fun getAllUsers(): Flow<List<User>>  // Reactive — emits on changes

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

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

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

    @Update
    suspend fun update(user: User)

    @Delete
    suspend fun delete(user: User)

    @Query("DELETE FROM users WHERE id = :id")
    suspend fun deleteById(id: Long)
}

// 3. Database
@Database(entities = [User::class, Post::class], version = 1, exportSchema = true)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun postDao(): PostDao
}

// 4. Provide via Hilt
@Module
@InstallIn(SingletonComponent::class)
object DatabaseModule {
    @Provides
    @Singleton
    fun provideDatabase(@ApplicationContext context: Context): AppDatabase {
        return Room.databaseBuilder(
            context,
            AppDatabase::class.java,
            "app-database"
        )
            .fallbackToDestructiveMigration() // dev only
            .build()
    }

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

// 5. Use in ViewModel
@HiltViewModel
class UsersViewModel @Inject constructor(
    private val userDao: UserDao
) : ViewModel() {
    val users: StateFlow<List<User>> = userDao.getAllUsers()
        .stateIn(viewModelScope, SharingStarted.WhileSubscribed(5000), emptyList())

    fun addUser(email: String, name: String) {
        viewModelScope.launch {
            userDao.insert(User(email = email, name = name))
        }
    }
}

// Migrations (when you change schema)
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE users ADD COLUMN bio TEXT")
    }
}

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

Common Pitfalls

• Using fallbackToDestructiveMigration in production — loses user data on schema change
• Querying on the main thread — Room throws by default
• Forgetting indices on frequently-queried columns
• Not exporting schemas — can't write proper migrations later

When NOT to Use This Skill

• For data that needs to sync across devices — use Firebase or Supabase
• For massive datasets — consider an alternative like ObjectBox

How to Verify It Worked

• Test migrations between every schema version
• Use Room's @TypeConverter for complex types like Date

Production Considerations

• Always export schemas to git — Room version control
• Write migration tests for every schema change
• Use WAL mode for better concurrent reads