Writing Custom Queries in Room for Jetpack Compose

Jetpack Compose has revolutionized Android UI development by introducing a declarative approach to building user interfaces. When combined with Room, Google’s official persistence library, it creates a robust architecture for managing local databases in Android applications. While Room provides a rich set of predefined queries, there are scenarios where custom queries are essential for optimizing performance or handling complex data requirements. In this post, we’ll dive deep into writing custom queries in Room and integrating them effectively with Jetpack Compose.

Why Custom Queries Matter

Room’s abstraction simplifies database interactions with annotations like @Insert, @Update, and @Delete. However, predefined queries may not cover advanced use cases such as:

• Filtering and sorting data dynamically.

• Joining multiple tables for aggregated results.

• Fetching specific data subsets based on user input or UI state.

• Optimizing queries for performance in resource-intensive applications.

Custom queries empower developers to fine-tune database operations and achieve greater control over data retrieval and manipulation.

Setting Up Room in a Jetpack Compose Project

Before diving into custom queries, let’s ensure the foundational setup of Room in a Jetpack Compose project.

1. Add Room Dependencies

Update your build.gradle file to include Room dependencies:

implementation "androidx.room:room-runtime:2.5.0"
kapt "androidx.room:room-compiler:2.5.0"
implementation "androidx.room:room-ktx:2.5.0"

For Kotlin projects, enable annotation processing:

kapt {
    correctErrorTypes = true
}

2. Define a Room Entity

An entity represents a table in the database. Here’s an example:

@Entity(tableName = "users")
data class User(
    @PrimaryKey(autoGenerate = true) val id: Int,
    val name: String,
    val age: Int,
    val email: String
)

3. Create a DAO Interface

Data Access Objects (DAOs) define methods for interacting with the database:

@Dao
interface UserDao {
    @Query("SELECT * FROM users")
    fun getAllUsers(): Flow<List<User>>
}

4. Initialize the Database

Create a Room database class:

@Database(entities = [User::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

Initialize it in your Application class or using dependency injection (e.g., Hilt):

val db = Room.databaseBuilder(
    context,
    AppDatabase::class.java,
    "app_database"
).build()

Writing Custom Queries

Custom queries are annotated with @Query in the DAO. Here are advanced use cases and how to implement them.

1. Filtering Data Dynamically

Imagine you want to fetch users above a certain age. Use parameters in the query:

@Query("SELECT * FROM users WHERE age > :minAge")
fun getUsersAboveAge(minAge: Int): Flow<List<User>>

Usage:

val usersAbove30 = userDao.getUsersAboveAge(30).collectAsState(initial = emptyList())

2. Sorting Results Dynamically

Dynamic sorting requires constructing queries with runtime parameters. However, Room does not support dynamic SQL strings directly. A workaround is to prepare multiple DAO methods or use third-party libraries like SQLDelight.

@Query("SELECT * FROM users ORDER BY name ASC")
fun getUsersSortedByName(): Flow<List<User>>

@Query("SELECT * FROM users ORDER BY age DESC")
fun getUsersSortedByAge(): Flow<List<User>>

3. Aggregating Data with Joins

To fetch aggregated data across multiple tables, Room supports SQL joins. Suppose you have another entity, Orders:

@Entity(tableName = "orders")
data class Order(
    @PrimaryKey(autoGenerate = true) val orderId: Int,
    val userId: Int,
    val amount: Double
)

Define a query to join users and orders:

@Query("""
    SELECT users.name, SUM(orders.amount) as totalSpent
    FROM users
    INNER JOIN orders ON users.id = orders.userId
    GROUP BY users.id
""")
data class UserSpending(
    val name: String,
    val totalSpent: Double
)

@Query("SELECT * FROM userSpending")
fun getUserSpendings(): Flow<List<UserSpending>>

4. Paginating Large Datasets

For large datasets, use pagination. Room integrates seamlessly with Paging:

Add dependencies:

implementation "androidx.paging:paging-runtime:3.2.0"

Modify the DAO:

@Query("SELECT * FROM users ORDER BY name ASC")
fun getPagedUsers(): PagingSource<Int, User>

In your ViewModel:

val pager = Pager(PagingConfig(pageSize = 20)) {
    userDao.getPagedUsers()
}.flow.cachedIn(viewModelScope)

Best Practices for Custom Queries

1. Optimize Queries: Use indices and limit data retrieval to only necessary columns to enhance performance.

2. Error Handling: Wrap queries in try-catch blocks or use Kotlin’s Result for better error management.

3. Flow and Compose: Prefer Flow for real-time updates, seamlessly integrating with Compose’s reactive paradigm.

4. Test Queries: Write unit tests for DAO methods to ensure accuracy and prevent regressions.

Integrating Room with Jetpack Compose

Let’s create a Compose UI that displays a list of users fetched via a custom query.

Example: Display Users Above a Certain Age

ViewModel:

class UserViewModel(private val userDao: UserDao) : ViewModel() {
    fun getUsersAboveAge(minAge: Int): Flow<List<User>> {
        return userDao.getUsersAboveAge(minAge)
    }
}

UI:

@Composable
fun UserList(viewModel: UserViewModel, minAge: Int) {
    val users by viewModel.getUsersAboveAge(minAge).collectAsState(initial = emptyList())

    LazyColumn {
        items(users) { user ->
            Text(text = "${user.name} ( ${user.age} years old)")
        }
    }
}

Conclusion

Writing custom queries in Room unlocks a new level of flexibility and power in managing your app’s local database. When paired with Jetpack Compose, you can build dynamic, real-time applications with highly optimized data interactions. By following best practices and leveraging Room’s capabilities, you can ensure your app remains performant and maintainable.

Do you have other advanced Room use cases or Compose integrations? Share your thoughts and experiences in the comments below!