DEV Community

myougaTheAxo
myougaTheAxo

Posted on

Local DB Design Patterns — Room + Repository + ViewModel Architecture

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>
Enter fullscreen mode Exit fullscreen mode

}

@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?
Enter fullscreen mode Exit fullscreen mode

}
`\

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() } }
Enter fullscreen mode Exit fullscreen mode

}
`\

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) }
}
Enter fullscreen mode Exit fullscreen mode

}

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)
Enter fullscreen mode Exit fullscreen mode

}
`\

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

Top comments (0)