Android Room Database - Complete Guide to Local Persistence

Published on: March 5, 2026

Introduction to Room

Room is Android’s abstraction layer over SQLite, providing compile-time verification of SQL queries and a more robust, maintainable approach to database operations. It’s part of Android Architecture Components and is the recommended way to handle persistent local data.

Why Room?

Compile-Time Verification: Catch SQL errors at compile time
Type Safety: Work with Kotlin objects instead of raw cursors
Boilerplate Reduction: Less verbose than raw SQLite
Reactive Support: LiveData and Flow integration
Migration Support: Version management and schema evolution

Setting Up Room

Dependencies

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"
}

Create the Database

@Database(
    entities = [User::class, Post::class, Comment::class],
    version = 1,
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun postDao(): PostDao
    abstract fun commentDao(): CommentDao
    
    companion object {
        @Volatile
        private var instance: AppDatabase? = null
        
        fun getInstance(context: Context): AppDatabase {
            return instance ?: synchronized(this) {
                Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                )
                    .addMigrations(MIGRATION_1_2)
                    .build()
                    .also { instance = it }
            }
        }
        
        private val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL(
                    "ALTER TABLE users ADD COLUMN last_updated INTEGER NOT NULL DEFAULT 0"
                )
            }
        }
    }
}

Defining Entities

Basic Entity

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

Composite Primary Key

@Entity(
    tableName = "user_posts",
    primaryKeys = ["user_id", "post_id"]
)
data class UserPost(
    @ColumnInfo(name = "user_id")
    val userId: Long,
    
    @ColumnInfo(name = "post_id")
    val postId: Long
)

Relationships

One-to-Many

@Entity(tableName = "users")
data class User(
    @PrimaryKey
    val id: Long,
    val name: String
)
 
@Entity(
    tableName = "posts",
    foreignKeys = [
        ForeignKey(
            entity = User::class,
            parentColumns = ["id"],
            childColumns = ["user_id"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class Post(
    @PrimaryKey
    val id: Long,
    
    @ColumnInfo(name = "user_id")
    val userId: Long,
    
    val title: String,
    val content: String
)
 
// Query with relationship
data class UserWithPosts(
    @Embedded
    val user: User,
    
    @Relation(
        parentColumn = "id",
        entityColumn = "user_id"
    )
    val posts: List<Post>
)

Data Access Objects (DAOs)

CRUD Operations

@Dao
interface UserDao {
    
    @Insert
    suspend fun insert(user: User): Long
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAll(users: List<User>)
    
    @Update
    suspend fun update(user: User)
    
    @Delete
    suspend fun delete(user: User)
    
    @Query("SELECT * FROM users WHERE id = :id")
    suspend fun getUserById(id: Long): User?
    
    @Query("SELECT * FROM users")
    fun getAllUsers(): Flow<List<User>>
    
    @Query("SELECT * FROM users WHERE email LIKE :email LIMIT 1")
    suspend fun getUserByEmail(email: String): User?
    
    @Query("DELETE FROM users WHERE created_at < :timestamp")
    suspend fun deleteOldUsers(timestamp: Long)
}

Complex Queries

@Dao
interface PostDao {
    
    // Query with JOIN
    @Query("""
        SELECT p.*, u.name as author_name 
        FROM posts p
        INNER JOIN users u ON p.user_id = u.id
        WHERE p.id = :postId
    """)
    suspend fun getPostWithAuthor(postId: Long): PostWithAuthor
    
    // Pagination
    @Query("""
        SELECT * FROM posts 
        ORDER BY created_at DESC 
        LIMIT :limit OFFSET :offset
    """)
    suspend fun getPostsPaginated(limit: Int, offset: Int): List<Post>
    
    // Aggregation
    @Query("""
        SELECT user_id, COUNT(*) as post_count 
        FROM posts 
        GROUP BY user_id
    """)
    fun getUserPostCounts(): Flow<List<UserPostCount>>
}
 
data class PostWithAuthor(
    @Embedded
    val post: Post,
    @ColumnInfo(name = "author_name")
    val authorName: String
)
 
data class UserPostCount(
    val user_id: Long,
    val post_count: Int
)

Type Converters

class Converters {
    
    @TypeConverter
    fun fromDate(date: Date?): Long? {
        return date?.time
    }
    
    @TypeConverter
    fun toDate(timestamp: Long?): Date? {
        return timestamp?.let { Date(it) }
    }
    
    @TypeConverter
    fun fromJsonList(json: String?): List<String>? {
        return json?.let { Json.decodeFromString(it) }
    }
    
    @TypeConverter
    fun toJsonList(list: List<String>?): String? {
        return list?.let { Json.encodeToString(it) }
    }
}
 
@Database(entities = [User::class], version = 1)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase()

Using Room with Coroutines and Flow

class UserRepository(private val userDao: UserDao) {
    
    // Reactive queries
    val allUsers: Flow<List<User>> = userDao.getAllUsers()
    
    // Suspend function for one-shot queries
    suspend fun getUserById(id: Long): User? {
        return userDao.getUserById(id)
    }
    
    // Insert with coroutines
    suspend fun insertUser(user: User) {
        withContext(Dispatchers.IO) {
            userDao.insert(user)
        }
    }
    
    // Batch operations with transaction
    suspend fun syncUsers(users: List<User>) {
        withContext(Dispatchers.IO) {
            userDao.deleteAll()
            userDao.insertAll(users)
        }
    }
}
 
// Usage in ViewModel
class UserViewModel(private val repository: UserRepository) : ViewModel() {
    
    val users: Flow<List<User>> = repository.allUsers
        .stateIn(viewModelScope, SharingStarted.Lazily, emptyList())
    
    fun addUser(user: User) {
        viewModelScope.launch {
            repository.insertUser(user)
        }
    }
}

Database Migrations

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Add new table
        database.execSQL("""
            CREATE TABLE comments (
                id INTEGER PRIMARY KEY NOT NULL,
                post_id INTEGER NOT NULL,
                content TEXT NOT NULL,
                FOREIGN KEY(post_id) REFERENCES posts(id)
            )
        """)
        
        // Add column
        database.execSQL("ALTER TABLE users ADD COLUMN bio TEXT")
        
        // Update data
        database.execSQL("UPDATE users SET bio = 'No bio' WHERE bio IS NULL")
    }
}
 
Room.databaseBuilder(context, AppDatabase::class.java, "database")
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
    .build()

Testing Room Database

@RunWith(AndroidJUnit4::class)
class UserDaoTest {
    
    @get:Rule
    val instantExecutorRule = InstantTaskExecutorRule()
    
    private lateinit var database: AppDatabase
    private lateinit var userDao: UserDao
    
    @Before
    fun setup() {
        database = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            AppDatabase::class.java
        ).build()
        userDao = database.userDao()
    }
    
    @After
    fun teardown() {
        database.close()
    }
    
    @Test
    fun insertAndRetrieveUser() = runTest {
        val user = User(name = "John Doe", email = "john@example.com")
        userDao.insert(user)
        
        val retrieved = userDao.getUserByEmail("john@example.com")
        assertThat(retrieved?.name).isEqualTo("John Doe")
    }
}

Best Practices

Use Coroutines/Flow: Avoid blocking the main thread
Singleton Database: Keep one instance per app
Proper Migrations: Plan schema changes carefully
Transactions: Group related operations
Indexes: Add indexes on frequently queried columns
Export Schemas: Enable exportSchema = true for version control
Type Safety: Leverage compile-time checking

Conclusion

Room abstracts away SQLite complexity while maintaining performance and type safety. By following these patterns and best practices, you’ll build robust, efficient local data persistence for your Android apps. The combination of Room with Kotlin coroutines and Flow provides a modern, reactive approach to database operations.