Kotlin Room Database — Android Local Persistence Guide
In this tutorial, you will learn about Kotlin Room Database. We cover key concepts, practical examples, and best practices to help you master this topic.
Room is an Android persistence library that wraps SQLite with Kotlin-friendly annotations, compile-time query verification, Flow-based reactive queries, and seamless coroutine integration for local data storage.
What You'll Learn
- Define entities with @Entity annotation
- Create DAOs with @Dao for data access
- Build the Room database class
- Use Flow for reactive queries
- Handle database migrations
- Use type converters for custom data types
- Write complex queries with Room
- Test Room databases
Why It Matters
Nearly every Android app needs local storage: caching API responses, saving user preferences, or storing offline data. Room is Google's recommended persistence library. It eliminates raw SQLite boilerplate, verifies SQL queries at compile time, and integrates with Kotlin coroutines and Flow for reactive data access.
Real-World Use
DodaTech's Android app uses Room to cache malware signature databases locally. The reactive Flow queries automatically update the UI when the database changes. Type converters store complex threat metadata. Migrations handle schema changes between app versions.
Learning Path
flowchart LR A[ViewModel] --> B[Room Database\nYou are here] B --> C[Retrofit] style B fill:#f90,color:#fff
Adding Room Dependencies
// build.gradle.kts (app level)
plugins {
id("com.google.devtools.ksp") version "2.0.21-1.0.25"
}
dependencies {
val roomVersion = "2.6.1"
implementation("androidx.room:room-runtime:$roomVersion")
implementation("androidx.room:room-ktx:$roomVersion")
ksp("androidx.room:room-compiler:$roomVersion")
// For testing
testImplementation("androidx.room:room-testing:$roomVersion")
}
KSP processes Room annotations at compile time, generating the DAO and database implementations.
Defining an Entity
An entity represents a table in the database.
import androidx.room.Entity
import androidx.room.PrimaryKey
import androidx.room.ColumnInfo
@Entity(tableName = "users")
data class User(
@PrimaryKey(autoGenerate = true)
val id: Long = 0,
@ColumnInfo(name = "full_name")
val name: String,
val email: String,
val age: Int,
val isActive: Boolean = true,
val createdAt: Long = System.currentTimeMillis()
)
@Entity defines the table name. @PrimaryKey marks the primary key. @ColumnInfo customizes the column name.
Creating a DAO
The Data Access Object defines database operations.
import androidx.room.*
import kotlinx.coroutines.flow.Flow
@Dao
interface UserDao {
// Insert
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insert(user: User): Long
@Insert
suspend fun insertAll(users: List<User>)
// Query
@Query("SELECT * FROM users ORDER BY createdAt DESC")
fun getAllUsers(): Flow<List<User>>
@Query("SELECT * FROM users WHERE id = :id")
suspend fun getUserById(id: Long): User?
@Query("SELECT * FROM users WHERE isActive = 1")
fun getActiveUsers(): Flow<List<User>>
@Query("SELECT * FROM users WHERE name LIKE '%' || :query || '%' OR email LIKE '%' || :query || '%'")
fun searchUsers(query: String): Flow<List<User>>
// Update
@Update
suspend fun update(user: User)
@Query("UPDATE users SET isActive = :isActive WHERE id = :userId")
suspend fun updateActiveStatus(userId: Long, isActive: Boolean)
// Delete
@Delete
suspend fun delete(user: User)
@Query("DELETE FROM users WHERE isActive = 0")
suspend fun deleteInactiveUsers()
// Count
@Query("SELECT COUNT(*) FROM users")
fun getUserCount(): Flow<Int>
}
Room verifies SQL queries at compile time. DAO functions can be suspend for one-shot queries or return Flow for reactive queries.
Building the Database
The database class extends RoomDatabase and provides DAO instances.
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.sqlite.db.SupportSQLiteDatabase
import kotlinx.coroutines.CoroutineScope
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.launch
@Database(
entities = [User::class, Post::class, Comment::class],
version = 2,
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 getDatabase(context: Context): AppDatabase {
return INSTANCE ?: synchronized(this) {
val instance = Room.databaseBuilder(
context.applicationContext,
AppDatabase::class.java,
"app_database"
)
.addCallback(DatabaseCallback())
.addMigrations(MIGRATION_1_2)
.build()
INSTANCE = instance
instance
}
}
}
private class DatabaseCallback : Callback() {
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
INSTANCE?.let { database ->
CoroutineScope(Dispatchers.IO).launch {
// Pre-populate data
}
}
}
}
}
Use the singleton pattern for the database instance. RoomDatabase.Builder configures the database with callbacks and migrations.
Using Room with Repository Pattern
Combine Room DAOs with a repository for Clean Architecture.
class UserRepository(private val userDao: UserDao) {
val allUsers: Flow<List<User>> = userDao.getAllUsers()
suspend fun createUser(name: String, email: String, age: Int): Long {
val user = User(
name = name,
email = email,
age = age
)
return userDao.insert(user)
}
suspend fun updateUser(user: User) {
userDao.update(user)
}
suspend fun deleteUser(user: User) {
userDao.delete(user)
}
fun searchUsers(query: String): Flow<List<User>> {
return userDao.searchUsers(query)
}
}
// In ViewModel
class UserViewModel(
private val repository: UserRepository
) : ViewModel() {
val users: StateFlow<List<User>> = repository.allUsers
.stateIn(
scope = viewModelScope,
started = SharingStarted.WhileSubscribed(5000),
initialValue = emptyList()
)
fun addUser(name: String, email: String, age: Int) {
viewModelScope.launch {
repository.createUser(name, email, age)
}
}
}
Output: The UI subscribes to the StateFlow. When a user is added via the repository, the Flow emits the new list and the UI recomposes.
Type Converters
Room stores primitive types. Use TypeConverters for complex types like Date, List, or custom objects.
import androidx.room.TypeConverter
import java.util.Date
class Converters {
@TypeConverter
fun fromTimestamp(value: Long?): Date? {
return value?.let { Date(it) }
}
@TypeConverter
fun dateToTimestamp(date: Date?): Long? {
return date?.time
}
@TypeConverter
fun fromStringList(value: String): List<String> {
return value.split(",").map { it.trim() }
}
@TypeConverter
fun toStringList(list: List<String>): String {
return list.joinToString(",")
}
}
// Register in Database class
@Database(
entities = [User::class],
version = 2,
exportSchema = true
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
// ...
}
Now entity fields of type Date or List
Migrations
When the database schema changes, migrations update existing databases without data loss.
val MIGRATION_1_2 = object : Migration(1, 2) {
override fun migrate(db: SupportSQLiteDatabase) {
// Add a new column
db.execSQL("ALTER TABLE users ADD COLUMN phone TEXT DEFAULT ''")
// Create a new table
db.execSQL("""
CREATE TABLE IF NOT EXISTS `posts` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`userId` INTEGER NOT NULL,
`title` TEXT NOT NULL,
`body` TEXT NOT NULL,
FOREIGN KEY(`userId`) REFERENCES `users`(`id`)
)
""")
}
}
val MIGRATION_2_3 = object : Migration(2, 3) {
override fun migrate(db: SupportSQLiteDatabase) {
// Destructive migration with data preservation
db.execSQL("CREATE TABLE IF NOT EXISTS `users_new` (" +
"`id` INTEGER PRIMARY KEY AUTOINCREMENT, " +
"`full_name` TEXT NOT NULL DEFAULT '', " +
"`email` TEXT NOT NULL DEFAULT '', " +
"`age` INTEGER NOT NULL DEFAULT 0, " +
"`isActive` INTEGER NOT NULL DEFAULT 1, " +
"`phone` TEXT DEFAULT '', " +
"`createdAt` INTEGER NOT NULL DEFAULT 0)")
db.execSQL("INSERT INTO users_new SELECT * FROM users")
db.execSQL("DROP TABLE users")
db.execSQL("ALTER TABLE users_new RENAME TO users")
}
}
Add migrations to the database builder:
Room.databaseBuilder(context, AppDatabase::class.java, "app_database")
.addMigrations(MIGRATION_1_2, MIGRATION_2_3)
.build()
If no Migration is provided for a version change, use fallbackToDestructiveMigration() which recreates all tables.
Complex Queries with Room
Room supports joins, subqueries, and aggregation.
data class UserWithPosts(
@Embedded val user: User,
@Relation(
parentColumn = "id",
entityColumn = "userId"
)
val posts: List<Post>
}
@Dao
interface PostDao {
// Join query
@Query("""
SELECT users.name, COUNT(posts.id) as postCount
FROM users
LEFT JOIN posts ON users.id = posts.userId
GROUP BY users.id
ORDER BY postCount DESC
""")
fun getUserPostCounts(): Flow<List<UserPostCount>>
// Transaction with multiple operations
@Transaction
suspend fun insertUserWithPosts(user: User, posts: List<Post>) {
insert(user)
posts.forEach { post ->
insert(post.copy(userId = user.id))
}
}
// Aggregation
@Query("SELECT AVG(age) FROM users WHERE isActive = 1")
fun getAverageActiveUserAge(): Flow<Double>
@Query("SELECT MAX(createdAt) FROM users")
suspend fun getLatestUserTimestamp(): Long?
// Subquery
@Query("""
SELECT * FROM users
WHERE id IN (
SELECT userId FROM posts GROUP BY userId HAVING COUNT(*) > :minPosts
)
""")
fun getUsersWithMinPosts(minPosts: Int): Flow<List<User>>
}
data class UserPostCount(
val name: String,
val postCount: Int
)
Output: Complex queries with joins, aggregations, and subqueries are type-safe and verified at compile time.
Testing Room
Room databases can be tested with an in-memory database.
class UserDaoTest {
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 testInsertAndRetrieve() = runTest {
val user = User(name = "Alice", email = "alice@test.com", age = 25)
val userId = userDao.insert(user)
val retrieved = userDao.getUserById(userId)
assertNotNull(retrieved)
assertEquals("Alice", retrieved?.name)
}
@Test
fun testReactiveQuery() = runTest {
val user1 = User(name = "Alice", email = "alice@test.com", age = 25)
val user2 = User(name = "Bob", email = "bob@test.com", age = 30)
userDao.insertAll(listOf(user1, user2))
userDao.getAllUsers().first { users ->
users.size == 2
}.also { users ->
assertEquals(2, users.size)
}
}
}
Output: Tests run against an in-memory database that is created and destroyed for each test.
Common Mistakes
Not using suspend functions in DAO: Room operations on the main thread cause crashes. Always use suspend functions or return Flow for automatic background execution.
Forgetting type converters for custom types: Room cannot store Date, List, or custom objects without TypeConverters. The compiler error clearly indicates the missing converter.
Making database instance non-singleton: Creating multiple database instances causes excessive memory and file descriptor usage. Always use the singleton pattern.
Not using migrations for schema changes: Changing an entity without a migration causes a crash on existing installations. Always add a migration or use fallbackToDestructiveMigration().
Blocking the main thread with synchronous queries: Room warns when you call suspend functions from a non-coroutine context. Use viewModelScope.launch { dao.method() }.
Using LiveData instead of Flow in coroutine-based code: Flow integrates naturally with Kotlin coroutines and Compose. Prefer Flow over LiveData in new code.
Practice Questions
- What is the purpose of the @Entity annotation?
Answer: It marks a data class as a database table. The table name defaults to the class name or can be customized with tableName.
- How does Room verify SQL queries?
Answer: At compile time, Room processes @Query annotations and validates SQL syntax against the entity schema. Invalid queries cause compilation errors.
- What is the difference between @Insert and @Upsert?
Answer: @Insert inserts a new row or fails on conflict (without REPLACE Strategy). @Upsert (Room 2.5+) inserts or updates if a conflict occurs.
- Why should you return Flow from DAO queries?
Answer: Flow returns emit new results whenever the underlying table changes. The UI observes the Flow and recomposes automatically.
- Challenge: Build a database with two related entities: Category and Note. A category has many notes. Implement a DAO that returns categories with their note count using a LEFT JOIN query. Write a migration that adds a priority column to notes.
Answer:
@Entity(tableName = "categories")
data class Category(
@PrimaryKey val id: Int,
val name: String,
val color: Int
)
@Entity(tableName = "notes", foreignKeys = [
ForeignKey(
entity = Category::class,
parentColumns = ["id"],
childColumns = ["categoryId"],
onDelete = ForeignKey.CASCADE
)
])
data class Note(
@PrimaryKey(autoGenerate = true) val id: Long = 0,
val title: String,
val content: String,
val categoryId: Int,
val createdAt: Long = System.currentTimeMillis()
)
data class CategoryWithCount(
val name: String,
val noteCount: Int
)
@Dao
interface CategoryDao {
@Query("""
SELECT categories.name, COUNT(notes.id) as noteCount
FROM categories
LEFT JOIN notes ON categories.id = notes.categoryId
GROUP BY categories.id
ORDER BY noteCount DESC
""")
fun getCategoryCounts(): Flow<List<CategoryWithCount>>
}
val MIGRATION_1_2 = object : Migration(1, 2) {
override fun migrate(db: SupportSQLiteDatabase) {
db.execSQL("ALTER TABLE notes ADD COLUMN priority INTEGER NOT NULL DEFAULT 3")
}
}
Mini Project
Build a journal app with Room database. Requirements:
- Entry entity with id, title, content, mood, tags, createdAt
- EntryDao with CRUD operations, search by title/content, filter by mood, sort by date
- TypeConverter for List
tags and Date - Repository pattern
- ViewModel with StateFlow
- Migration from version 1 to 2 (add mood column)
- Tests for the DAO
This project consolidates all Room concepts in a practical, feature-rich application.
FAQ
What's Next
After mastering Room, learn Retrofit for network communication. You can also explore Navigation for moving between screens.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro