Local DB Design Patterns — Room + Repository + ViewModel Architecture
Master the Room database architecture with Repository pattern and reactive ViewModel. This guide covers entity design, DAO patterns, and state management for robust offline-first Android apps.
Entity Design with Room
Define your database schema using @entity annotation:
\`kotlin
// User entity with primary key and indices
@entity(
tableName = "users",
indices = [Index(value = ["email"], unique = true)]
)
data class UserEntity(
@PrimaryKey(autoGenerate = true)
val id: Long = 0,
val name: String,
val email: String,
val createdAt: Long = System.currentTimeMillis()
)
// Post entity with foreign key constraint
@entity(
tableName = "posts",
foreignKeys = [
ForeignKey(
entity = UserEntity::class,
parentColumns = ["id"],
childColumns = ["userId"],
onDelete = ForeignKey.CASCADE
)
],
indices = [
Index(value = ["userId"]),
Index(value = ["createdAt"])
]
)
data class PostEntity(
@PrimaryKey(autoGenerate = true)
val id: Long = 0,
val userId: Long,
val title: String,
val content: String,
val createdAt: Long = System.currentTimeMillis()
)
`\
DAO Layer with Reactive Streams
Use Flow for observable queries and suspend functions for writes:
\`kotlin
@dao
interface UserDao {
// Insert with suspend function
@Insert
suspend fun insert(user: UserEntity): Long
// Update with suspend function
@Update
suspend fun update(user: UserEntity)
// Delete with suspend function
@Delete
suspend fun delete(user: UserEntity)
// Observable query returning Flow
@Query("SELECT * FROM users WHERE id = :userId")
fun observeUser(userId: Long): Flow<UserEntity?>
// Observable list query
@Query("SELECT * FROM users ORDER BY createdAt DESC")
fun observeAllUsers(): Flow<List<UserEntity>>
// Custom query with parameters
@Query("SELECT * FROM users WHERE email LIKE '%' || :searchTerm || '%'")
fun searchUsers(searchTerm: String): Flow<List<UserEntity>>
// Batch operations
@Query("SELECT * FROM users WHERE id IN (:userIds)")
suspend fun getUsersByIds(userIds: List<Long>): List<UserEntity>
}
@dao
interface PostDao {
@Insert
suspend fun insert(post: PostEntity): Long
@Query("SELECT * FROM posts WHERE userId = :userId ORDER BY createdAt DESC")
fun observeUserPosts(userId: Long): Flow<List<PostEntity>>
@Query("SELECT * FROM posts WHERE id = :postId")
suspend fun getPost(postId: Long): PostEntity?
}
`\
Repository Layer with Entity-Domain Conversion
Abstract database operations and handle data conversion:
\`kotlin
// Domain models (UI layer)
data class User(
val id: Long,
val name: String,
val email: String,
val createdAtMillis: Long
)
data class Post(
val id: Long,
val userId: Long,
val title: String,
val content: String,
val createdAtMillis: Long
)
// Extension functions for conversion
fun UserEntity.toDomain() = User(
id = id,
name = name,
email = email,
createdAtMillis = createdAt
)
fun User.toEntity() = UserEntity(
id = id,
name = name,
email = email,
createdAt = createdAtMillis
)
// Repository implementation
class UserRepository(private val userDao: UserDao) {
fun observeUser(userId: Long): Flow<User?> =
userDao.observeUser(userId)
.map { it?.toDomain() }
.distinctUntilChanged()
fun observeAllUsers(): Flow<List<User>> =
userDao.observeAllUsers()
.map { entities -> entities.map { it.toDomain() } }
.distinctUntilChanged()
suspend fun createUser(user: User): Long =
userDao.insert(user.toEntity())
suspend fun updateUser(user: User) =
userDao.update(user.toEntity())
suspend fun deleteUser(user: User) =
userDao.delete(user.toEntity())
fun searchUsers(query: String): Flow<List<User>> =
userDao.searchUsers(query)
.map { entities -> entities.map { it.toDomain() } }
}
`\
Filter State Management
Define a data class for filtering logic:
\kotlin
data class FilterState(
val searchQuery: String = "",
val sortBy: SortOrder = SortOrder.NEWEST,
val filterByUserId: Long? = null
) {
enum class SortOrder {
NEWEST, OLDEST, NAME_ASC, NAME_DESC
}
}
\\
ViewModel with State Flow and Reactive Updates
Use flatMapLatest to react to filter changes:
\`kotlin
class UserListViewModel(private val repository: UserRepository) : ViewModel() {
private val filterState = MutableStateFlow(FilterState())
val uiState: StateFlow<UserListUiState> = filterState
.flatMapLatest { filter ->
when {
filter.searchQuery.isNotEmpty() ->
repository.searchUsers(filter.searchQuery)
filter.filterByUserId != null ->
repository.observeUser(filter.filterByUserId)
.map { user -> listOfNotNull(user) }
else ->
repository.observeAllUsers()
}
}
.map { users ->
val sortedUsers = when (filterState.value.sortBy) {
FilterState.SortOrder.NEWEST ->
users.sortedByDescending { it.createdAtMillis }
FilterState.SortOrder.OLDEST ->
users.sortedBy { it.createdAtMillis }
FilterState.SortOrder.NAME_ASC ->
users.sortedBy { it.name }
FilterState.SortOrder.NAME_DESC ->
users.sortedByDescending { it.name }
}
UserListUiState.Success(sortedUsers)
}
.catch { error ->
emit(UserListUiState.Error(error.message ?: "Unknown error"))
}
.stateIn(
scope = viewModelScope,
started = SharingStarted.WhileSubscribed(5000),
initialValue = UserListUiState.Loading
)
fun setSearchQuery(query: String) {
filterState.update { it.copy(searchQuery = query) }
}
fun setSortOrder(order: FilterState.SortOrder) {
filterState.update { it.copy(sortBy = order) }
}
fun filterByUserId(userId: Long?) {
filterState.update { it.copy(filterByUserId = userId) }
}
}
sealed class UserListUiState {
data object Loading : UserListUiState()
data class Success(val users: List) : UserListUiState()
data class Error(val message: String) : UserListUiState()
}
`\
Database Setup with Dependency Injection
Initialize Room database in your app:
\`kotlin
// Abstract database class
@database(
entities = [UserEntity::class, PostEntity::class],
version = 1,
exportSchema = false
)
abstract class AppDatabase : RoomDatabase() {
abstract fun userDao(): UserDao
abstract fun postDao(): PostDao
}
// Dependency injection (Hilt example)
@Module
@InstallIn(SingletonComponent::class)
object DatabaseModule {
@Singleton
@Provides
fun provideDatabase(@ApplicationContext context: Context): AppDatabase =
Room.databaseBuilder(
context,
AppDatabase::class.java,
"app_database"
).build()
@Singleton
@Provides
fun provideUserDao(database: AppDatabase): UserDao =
database.userDao()
@Singleton
@Provides
fun provideUserRepository(userDao: UserDao): UserRepository =
UserRepository(userDao)
}
`\
Best Practices Summary
Entity Design: Use indices for frequently queried columns, set foreign key constraints
DAO Layer: Return Flow for observables, use suspend for writes
Repository Pattern: Convert between Entity and Domain models, provide clean API
State Management: Use StateFlow with stateIn() for lifecycle-aware subscriptions
Filtering: Create FilterState data class for complex query logic
Error Handling: Use catch() to emit error states from Flow operators
Lifecycle: Use SharingStarted.WhileSubscribed(5000) for proper collection management
This architecture ensures testability, scalability, and reactive updates across your app.
8 Android app templates: Gumroad