Room Database

Introduction

Room is Android's modern persistence library that provides an abstraction layer over SQLite, making it easier to work with local databases in Android applications. It's part of the Android Jetpack libraries and follows the Repository pattern, offering compile-time verification of SQL queries and seamless integration with other Jetpack components.

This comprehensive guide will take you from basic Room setup to advanced features like complex relationships, migrations, and testing. Whether you're building a simple note-taking app or a complex application with offline capabilities, Room provides the tools you need for robust local data storage.

Why Use Room?

Before diving into implementation, let's understand the benefits of using Room over raw SQLite:

• Compile-time Verification: SQL queries are validated at compile time
• Type Safety: Strongly typed data access with Kotlin support
• LiveData Integration: Reactive data updates with LiveData
• Coroutines Support: Asynchronous database operations
• Migration Support: Easy database schema updates
• Testing: Built-in support for unit testing
• Performance: Optimized for Android with minimal overhead

Setting Up Room

Let's start by setting up Room in your Android project.

Dependencies

// app/build.gradle.kts
dependencies {
    val roomVersion = "2.6.1"
    
    implementation("androidx.room:room-runtime:$roomVersion")
    implementation("androidx.room:room-ktx:$roomVersion")
    kapt("androidx.room:room-compiler:$roomVersion")
    
    // Optional: Testing
    testImplementation("androidx.room:room-testing:$roomVersion")
}

Basic Project Structure

app/src/main/java/com/example/app/
├── data/
│   ├── database/
│   │   ├── AppDatabase.kt
│   │   └── DatabaseMigrations.kt
│   ├── dao/
│   │   └── UserDao.kt
│   ├── entity/
│   │   └── User.kt
│   └── repository/
│       └── UserRepository.kt
└── ui/
    └── MainActivity.kt

Entities

Entities represent tables in your database. They are data classes annotated with Room-specific annotations.

Basic Entity

@Entity(tableName = "users")
data class User(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    
    @ColumnInfo(name = "user_name")
    val name: String,
    
    @ColumnInfo(name = "user_email")
    val email: String,
    
    @ColumnInfo(name = "created_at")
    val createdAt: Long = System.currentTimeMillis()
)

Entity with Relationships

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

@Entity(
    tableName = "posts",
    foreignKeys = [
        ForeignKey(
            entity = User::class,
            parentColumns = ["id"],
            childColumns = ["user_id"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class Post(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    
    val title: String,
    val content: String,
    
    @ColumnInfo(name = "user_id")
    val userId: Int,
    
    @ColumnInfo(name = "created_at")
    val createdAt: Long = System.currentTimeMillis()
)

Entity with Indices

@Entity(
    tableName = "users",
    indices = [
        Index(value = ["email"], unique = true),
        Index(value = ["name", "email"])
    ]
)
data class User(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    val name: String,
    val email: String
)

Data Access Objects (DAOs)

DAOs define the methods for accessing the database. They contain the SQL queries and database operations.

Basic DAO

@Dao
interface UserDao {
    @Query("SELECT * FROM users")
    fun getAllUsers(): Flow>
    
    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getUserById(userId: Int): User?
    
    @Insert
    suspend fun insertUser(user: User): Long
    
    @Insert
    suspend fun insertUsers(users: List)
    
    @Update
    suspend fun updateUser(user: User)
    
    @Delete
    suspend fun deleteUser(user: User)
    
    @Query("DELETE FROM users WHERE id = :userId")
    suspend fun deleteUserById(userId: Int)
    
    @Query("SELECT * FROM users WHERE name LIKE '%' || :searchQuery || '%'")
    fun searchUsers(searchQuery: String): Flow>
}

Advanced DAO with Relationships

@Dao
interface UserDao {
    @Transaction
    @Query("SELECT * FROM users")
    fun getUsersWithPosts(): Flow>
    
    @Transaction
    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getUserWithPosts(userId: Int): UserWithPosts?
    
    @Query("SELECT * FROM users WHERE id IN (SELECT DISTINCT user_id FROM posts)")
    fun getUsersWithPostsCount(): Flow>
}

// Data classes for relationships
data class UserWithPosts(
    @Embedded val user: User,
    @Relation(
        parentColumn = "id",
        entityColumn = "user_id"
    )
    val posts: List
)

data class UserWithPostCount(
    @Embedded val user: User,
    @Relation(
        parentColumn = "id",
        entityColumn = "user_id"
    )
    val posts: List
) {
    val postCount: Int
        get() = posts.size
}

Complex Queries

@Dao
interface UserDao {
    @Query("""
        SELECT * FROM users 
        WHERE name LIKE '%' || :searchQuery || '%' 
        OR email LIKE '%' || :searchQuery || '%'
        ORDER BY name ASC
        LIMIT :limit OFFSET :offset
    """)
    suspend fun searchUsersPaginated(
        searchQuery: String,
        limit: Int,
        offset: Int
    ): List
    
    @Query("""
        SELECT u.*, COUNT(p.id) as post_count 
        FROM users u 
        LEFT JOIN posts p ON u.id = p.user_id 
        GROUP BY u.id 
        HAVING post_count > :minPostCount
        ORDER BY post_count DESC
    """)
    fun getActiveUsers(minPostCount: Int): Flow>
    
    @Query("""
        SELECT * FROM users 
        WHERE created_at BETWEEN :startDate AND :endDate
    """)
    fun getUsersByDateRange(startDate: Long, endDate: Long): Flow>
}

Database Class

The database class serves as the main access point for the persistent data. It's an abstract class that extends RoomDatabase.

Basic Database

@Database(
    entities = [User::class, Post::class],
    version = 1,
    exportSchema = false
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun postDao(): PostDao
    
    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null
        
        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                ).build()
                INSTANCE = instance
                instance
            }
        }
    }
}

Database with Migrations

@Database(
    entities = [User::class, Post::class],
    version = 2,
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun postDao(): PostDao
    
    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null
        
        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                )
                .addMigrations(MIGRATION_1_2)
                .build()
                INSTANCE = instance
                instance
            }
        }
        
        private val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // Add new column to users table
                database.execSQL(
                    "ALTER TABLE users ADD COLUMN phone_number TEXT"
                )
            }
        }
    }
}

Repository Pattern

The Repository pattern provides a clean API for data operations and manages different data sources.

Basic Repository

class UserRepository(private val userDao: UserDao) {
    
    fun getAllUsers(): Flow> = userDao.getAllUsers()
    
    suspend fun getUserById(userId: Int): User? = userDao.getUserById(userId)
    
    suspend fun insertUser(user: User): Long = userDao.insertUser(user)
    
    suspend fun updateUser(user: User) = userDao.updateUser(user)
    
    suspend fun deleteUser(user: User) = userDao.deleteUser(user)
    
    fun searchUsers(query: String): Flow> = userDao.searchUsers(query)
}

Repository with Multiple Data Sources

class UserRepository(
    private val userDao: UserDao,
    private val userApi: UserApi,
    private val userPreferences: UserPreferences
) {
    
    fun getAllUsers(): Flow> = flow {
        // Emit cached data first
        emitAll(userDao.getAllUsers())
        
        try {
            // Fetch fresh data from API
            val users = userApi.getUsers()
            userDao.insertUsers(users)
        } catch (e: Exception) {
            // Handle error, maybe emit cached data
            Log.e("UserRepository", "Error fetching users", e)
        }
    }.flowOn(Dispatchers.IO)
    
    suspend fun refreshUsers() {
        try {
            val users = userApi.getUsers()
            userDao.insertUsers(users)
        } catch (e: Exception) {
            Log.e("UserRepository", "Error refreshing users", e)
            throw e
        }
    }
    
    suspend fun getUserById(userId: Int): User? {
        // Try cache first
        var user = userDao.getUserById(userId)
        
        if (user == null) {
            try {
                // Fetch from API if not in cache
                user = userApi.getUserById(userId)
                user?.let { userDao.insertUser(it) }
            } catch (e: Exception) {
                Log.e("UserRepository", "Error fetching user", e)
            }
        }
        
        return user
    }
}

ViewModel Integration

ViewModels provide a way to manage UI-related data and survive configuration changes.

Basic ViewModel

class UserViewModel(
    private val userRepository: UserRepository
) : ViewModel() {
    
    val allUsers: StateFlow> = userRepository.getAllUsers()
        .stateIn(
            scope = viewModelScope,
            started = SharingStarted.WhileSubscribed(5000),
            initialValue = emptyList()
        )
    
    private val _searchQuery = MutableStateFlow("")
    val searchQuery: StateFlow = _searchQuery.asStateFlow()
    
    val searchResults: StateFlow> = combine(
        userRepository.getAllUsers(),
        searchQuery
    ) { users, query ->
        if (query.isEmpty()) {
            users
        } else {
            users.filter { 
                it.name.contains(query, ignoreCase = true) ||
                it.email.contains(query, ignoreCase = true)
            }
        }
    }.stateIn(
        scope = viewModelScope,
        started = SharingStarted.WhileSubscribed(5000),
        initialValue = emptyList()
    )
    
    fun setSearchQuery(query: String) {
        _searchQuery.value = query
    }
    
    fun addUser(name: String, email: String) {
        viewModelScope.launch {
            val user = User(name = name, email = email)
            userRepository.insertUser(user)
        }
    }
    
    fun deleteUser(user: User) {
        viewModelScope.launch {
            userRepository.deleteUser(user)
        }
    }
}

ViewModel with Error Handling

class UserViewModel(
    private val userRepository: UserRepository
) : ViewModel() {
    
    private val _uiState = MutableStateFlow(UserUiState.Loading)
    val uiState: StateFlow = _uiState.asStateFlow()
    
    init {
        loadUsers()
    }
    
    private fun loadUsers() {
        viewModelScope.launch {
            _uiState.value = UserUiState.Loading
            
            try {
                userRepository.getAllUsers()
                    .catch { e ->
                        _uiState.value = UserUiState.Error(e.message ?: "Unknown error")
                    }
                    .collect { users ->
                        _uiState.value = UserUiState.Success(users)
                    }
            } catch (e: Exception) {
                _uiState.value = UserUiState.Error(e.message ?: "Unknown error")
            }
        }
    }
    
    fun refreshUsers() {
        viewModelScope.launch {
            try {
                userRepository.refreshUsers()
            } catch (e: Exception) {
                _uiState.value = UserUiState.Error(e.message ?: "Refresh failed")
            }
        }
    }
}

sealed class UserUiState {
    object Loading : UserUiState()
    data class Success(val users: List) : UserUiState()
    data class Error(val message: String) : UserUiState()
}

Advanced Features

Room provides several advanced features for complex use cases.

Type Converters

class Converters {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return value?.let { Date(it) }
    }
    
    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? {
        return date?.time
    }
    
    @TypeConverter
    fun fromString(value: String?): List? {
        return value?.split(",")?.filter { it.isNotEmpty() }
    }
    
    @TypeConverter
    fun listToString(list: List?): String? {
        return list?.joinToString(",")
    }
}

// Add converters to database
@Database(
    entities = [User::class],
    version = 1
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    // Database implementation
}

Complex Relationships

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

@Entity(tableName = "posts")
data class Post(
    @PrimaryKey val id: Int,
    val title: String,
    val userId: Int
)

@Entity(tableName = "comments")
data class Comment(
    @PrimaryKey val id: Int,
    val text: String,
    val postId: Int,
    val userId: Int
)

// Many-to-many relationship
@Entity(
    tableName = "user_post_cross_ref",
    primaryKeys = ["userId", "postId"]
)
data class UserPostCrossRef(
    val userId: Int,
    val postId: Int
)

// Data classes for complex queries
data class UserWithPostsAndComments(
    @Embedded val user: User,
    @Relation(
        entity = Post::class,
        parentColumn = "id",
        entityColumn = "userId",
        associateBy = Junction(
            value = UserPostCrossRef::class,
            parentColumn = "userId",
            entityColumn = "postId"
        )
    )
    val posts: List
)

data class PostWithComments(
    @Embedded val post: Post,
    @Relation(
        parentColumn = "id",
        entityColumn = "postId"
    )
    val comments: List
)

Database Migrations

object DatabaseMigrations {
    
    val MIGRATION_1_2 = object : Migration(1, 2) {
        override fun migrate(database: SupportSQLiteDatabase) {
            // Add new column
            database.execSQL(
                "ALTER TABLE users ADD COLUMN phone_number TEXT"
            )
        }
    }
    
    val MIGRATION_2_3 = object : Migration(2, 3) {
        override fun migrate(database: SupportSQLiteDatabase) {
            // Create new table
            database.execSQL("""
                CREATE TABLE posts (
                    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                    title TEXT NOT NULL,
                    content TEXT NOT NULL,
                    user_id INTEGER NOT NULL,
                    created_at INTEGER NOT NULL,
                    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
                )
            """)
            
            // Create index
            database.execSQL(
                "CREATE INDEX index_posts_user_id ON posts (user_id)"
            )
        }
    }
    
    val MIGRATION_3_4 = object : Migration(3, 4) {
        override fun migrate(database: SupportSQLiteDatabase) {
            // Complex migration with data transformation
            database.execSQL("""
                CREATE TABLE users_new (
                    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                    name TEXT NOT NULL,
                    email TEXT NOT NULL,
                    phone_number TEXT,
                    created_at INTEGER NOT NULL
                )
            """)
            
            // Copy data with transformation
            database.execSQL("""
                INSERT INTO users_new (id, name, email, phone_number, created_at)
                SELECT id, name, email, phone_number, created_at FROM users
            """)
            
            // Drop old table and rename new one
            database.execSQL("DROP TABLE users")
            database.execSQL("ALTER TABLE users_new RENAME TO users")
        }
    }
}

Testing

Room provides excellent support for testing your database operations.

Unit Testing

@RunWith(AndroidJUnit4::class)
class UserDaoTest {
    
    private lateinit var database: AppDatabase
    private lateinit var userDao: UserDao
    
    @Before
    fun createDb() {
        val context = ApplicationProvider.getApplicationContext()
        database = Room.inMemoryDatabaseBuilder(
            context, AppDatabase::class.java
        ).build()
        userDao = database.userDao()
    }
    
    @After
    fun closeDb() {
        database.close()
    }
    
    @Test
    fun insertAndReadUser() = runTest {
        val user = User(name = "John Doe", email = "john@example.com")
        val userId = userDao.insertUser(user)
        
        val retrievedUser = userDao.getUserById(userId.toInt())
        assertThat(retrievedUser).isNotNull()
        assertThat(retrievedUser?.name).isEqualTo("John Doe")
        assertThat(retrievedUser?.email).isEqualTo("john@example.com")
    }
    
    @Test
    fun searchUsers() = runTest {
        val user1 = User(name = "John Doe", email = "john@example.com")
        val user2 = User(name = "Jane Smith", email = "jane@example.com")
        
        userDao.insertUser(user1)
        userDao.insertUser(user2)
        
        val searchResults = userDao.searchUsers("John").first()
        assertThat(searchResults).hasSize(1)
        assertThat(searchResults[0].name).isEqualTo("John Doe")
    }
}

Repository Testing

@RunWith(MockitoJUnitRunner::class)
class UserRepositoryTest {
    
    @Mock
    private lateinit var userDao: UserDao
    
    @Mock
    private lateinit var userApi: UserApi
    
    private lateinit var userRepository: UserRepository
    
    @Before
    fun setup() {
        userRepository = UserRepository(userDao, userApi)
    }
    
    @Test
    fun `getAllUsers returns cached data`() = runTest {
        val cachedUsers = listOf(
            User(id = 1, name = "John", email = "john@example.com"),
            User(id = 2, name = "Jane", email = "jane@example.com")
        )
        
        whenever(userDao.getAllUsers()).thenReturn(flowOf(cachedUsers))
        
        val result = userRepository.getAllUsers().first()
        
        assertThat(result).isEqualTo(cachedUsers)
        verify(userDao).getAllUsers()
    }
    
    @Test
    fun `refreshUsers updates cache`() = runTest {
        val apiUsers = listOf(
            User(id = 1, name = "John", email = "john@example.com")
        )
        
        whenever(userApi.getUsers()).thenReturn(apiUsers)
        
        userRepository.refreshUsers()
        
        verify(userApi).getUsers()
        verify(userDao).insertUsers(apiUsers)
    }
}

Performance Optimization

Optimizing Room performance is crucial for smooth user experience.

Query Optimization

• Use indices: Create indices for frequently queried columns
• Limit result sets: Use LIMIT and OFFSET for pagination
• Avoid N+1 queries: Use @Relation for efficient joins
• Use appropriate data types: Choose efficient column types

Batch Operations

@Dao
interface UserDao {
    @Insert
    suspend fun insertUsers(users: List)
    
    @Update
    suspend fun updateUsers(users: List)
    
    @Delete
    suspend fun deleteUsers(users: List)
    
    @Transaction
    suspend fun insertUsersWithPosts(users: List) {
        users.forEach { userWithPosts ->
            val userId = insertUser(userWithPosts.user)
            userWithPosts.posts.forEach { post ->
                insertPost(post.copy(userId = userId.toInt()))
            }
        }
    }
}

Async Operations

class UserRepository(private val userDao: UserDao) {
    
    fun getAllUsers(): Flow> = userDao.getAllUsers()
        .flowOn(Dispatchers.IO)
    
    suspend fun insertUser(user: User): Long = withContext(Dispatchers.IO) {
        userDao.insertUser(user)
    }
    
    suspend fun refreshUsers() = withContext(Dispatchers.IO) {
        // Perform database operations
        userDao.deleteAllUsers()
        userDao.insertUsers(fetchUsersFromApi())
    }
}

Best Practices

Database Design

• Plan your schema: Design tables and relationships carefully
• Use appropriate indices: Create indices for search columns
• Normalize data: Avoid data duplication
• Use foreign keys: Maintain referential integrity

Code Organization

• Separate concerns: Keep DAOs, entities, and repositories separate
• Use meaningful names: Clear naming for tables and columns
• Document complex queries: Add comments for complex SQL
• Version your schema: Always increment version numbers

Error Handling

• Handle database errors: Catch and handle SQL exceptions
• Validate data: Check data before inserting
• Use transactions: For complex operations
• Log errors: For debugging and monitoring

Common Pitfalls

Avoiding Common Mistakes

• Don't forget migrations: Always handle schema changes
• Don't block the main thread: Use coroutines for database operations
• Don't ignore indices: Create indices for performance
• Don't use raw SQL unnecessarily: Use Room's type-safe queries

Debugging Tips

• Enable SQL logging: Use setQueryCallback for debugging
• Use Database Inspector: Android Studio's database tool
• Test migrations: Always test migration paths
• Monitor performance: Use Android Studio profiler

Practice Exercises

Try these exercises to reinforce your Room knowledge:

Exercise 1: Note-Taking App

// Create a note-taking app with:
// - Notes with title, content, and categories
// - Search functionality
// - Category management
// - Offline-first architecture
// - Sync with cloud when online

Exercise 2: Task Manager

// Build a task manager with:
// - Tasks with priority, due date, and status
// - Project organization
// - Task dependencies
// - Progress tracking
// - Data export/import

Exercise 3: E-commerce App

// Create an e-commerce app with:
// - Product catalog with categories
// - Shopping cart functionality
// - Order history
// - User preferences
// - Offline browsing

Next Steps

Now that you have a solid foundation in Room, explore these advanced topics:

• Paging 3: Load large datasets efficiently
• DataStore: Modern preferences storage
• WorkManager: Background data synchronization
• Hilt Integration: Dependency injection with Room
• Multi-module Architecture: Organize Room in large projects
• Encryption: Secure database storage

Resources

• Room Documentation
• Room API Reference
• Paging Library
• WorkManager
• DataStore