🔌 APIs, Databases, and Backend Integration

Room Database Tutorial with Kotlin

Room is Android's recommended SQLite library: compile-time-checked SQL, Kotlin coroutine and Flow support, and painless migrations. This tutorial builds a notes database from setup to migrations and tests.

✍️ Yunok Tech 📅 Updated ⏱ 3 min read 🎯 Beginner
On this page
  1. Room's three parts
  2. Setup
  3. Entities
  4. DAOs
  5. The database
  6. Relations
  7. Type converters
  8. Migrations
  9. Testing a DAO
  10. Real-world uses
  11. Common mistakes
  12. Frequently asked questions
  13. Conclusion

Raw SQLite on Android means string queries that fail at runtime, cursor juggling and manual threading. Room keeps the power of SQL but checks your queries at compile time, maps rows to Kotlin classes, and exposes results as Flows that update automatically when data changes.

Room's three parts

Room components The RoomDatabase class is the access point and holds DAOs. DAOs declare queries with SQL annotations and return Flow or suspend results. Entities are data classes mapped to tables in SQLite. AppDatabase@Database, version NoteDao@Query / @Upsert → Flow NoteEntity@Entity → SQLite table
The database provides DAOs, and DAOs read and write entities. Room generates the implementation at compile time.

Setup

app/build.gradle.ktsGradle KTS
plugins { id("com.google.devtools.ksp") }
dependencies {
    implementation("androidx.room:room-runtime:<room-version>")
    implementation("androidx.room:room-ktx:<room-version>")      // coroutines/Flow support
    ksp("androidx.room:room-compiler:<room-version>")
    testImplementation("androidx.room:room-testing:<room-version>")
}

Entities

NoteEntity.ktKotlin
@Entity(
    tableName = "notes",
    indices = [Index("folderId"), Index("updatedAt")],
)
data class NoteEntity(
    @PrimaryKey val id: String,
    val title: String,
    val body: String,
    val folderId: String?,
    val pinned: Boolean = false,
    val updatedAt: Long,                    // epoch millis: simple, sortable
)

@Entity(tableName = "folders")
data class FolderEntity(@PrimaryKey val id: String, val name: String)

DAOs

NoteDao.ktKotlin
@Dao
interface NoteDao {
    // Observed query: re-emits whenever the notes table changes
    @Query("SELECT * FROM notes ORDER BY pinned DESC, updatedAt DESC")
    fun observeAll(): Flow<List<NoteEntity>>

    @Query("SELECT * FROM notes WHERE title LIKE '%' || :query || '%' OR body LIKE '%' || :query || '%'")
    fun search(query: String): Flow<List<NoteEntity>>

    @Query("SELECT * FROM notes WHERE id = :id")
    suspend fun getById(id: String): NoteEntity?

    @Upsert
    suspend fun upsert(note: NoteEntity)

    @Query("DELETE FROM notes WHERE id = :id")
    suspend fun delete(id: String)

    @Query("UPDATE notes SET pinned = NOT pinned WHERE id = :id")
    suspend fun togglePinned(id: String)
}

Room checks every SQL query against your schema at compile time, so a typo in a column name is a build error, not a crash. Suspend functions and Flow queries run off the main thread automatically.

The database

AppDatabase.ktKotlin
@Database(entities = [NoteEntity::class, FolderEntity::class], version = 2, exportSchema = true)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun noteDao(): NoteDao
    abstract fun folderDao(): FolderDao
}

// Provide ONE instance for the whole app (e.g. Hilt @Singleton)
fun buildDatabase(context: Context): AppDatabase =
    Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
        .addMigrations(MIGRATION_1_2)
        .build()

Relations

FolderWithNotes.ktKotlin
data class FolderWithNotes(
    @Embedded val folder: FolderEntity,
    @Relation(parentColumn = "id", entityColumn = "folderId")
    val notes: List<NoteEntity>,
)

@Dao
interface FolderDao {
    @Transaction                                       // runs the two underlying queries atomically
    @Query("SELECT * FROM folders ORDER BY name")
    fun observeFoldersWithNotes(): Flow<List<FolderWithNotes>>
}

Type converters

Converters.ktKotlin
class Converters {
    @TypeConverter fun fromTags(tags: List<String>): String = tags.joinToString(",")
    @TypeConverter fun toTags(value: String): List<String> = if (value.isEmpty()) emptyList() else value.split(",")
}

For queryable lists, prefer a separate table over a converted string.

Migrations

Migrations.ktKotlin
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0")
        db.execSQL("CREATE INDEX IF NOT EXISTS index_notes_updatedAt ON notes(updatedAt)")
    }
}

With exportSchema = true and a configured schema directory, Room writes JSON schema files you commit to Git. They enable auto-migrations for simple changes and migration testing. Never use fallbackToDestructiveMigration() for user data you can't re-download.

Testing a DAO

NoteDaoTest.kt (androidTest)Kotlin
@RunWith(AndroidJUnit4::class)
class NoteDaoTest {
    private lateinit var db: AppDatabase
    private lateinit var dao: NoteDao

    @Before fun setUp() {
        db = Room.inMemoryDatabaseBuilder(ApplicationProvider.getApplicationContext(), AppDatabase::class.java).build()
        dao = db.noteDao()
    }
    @After fun tearDown() = db.close()

    @Test fun pinnedNotesComeFirst() = runTest {
        dao.upsert(NoteEntity("1", "Old", "", null, pinned = false, updatedAt = 2))
        dao.upsert(NoteEntity("2", "Pinned", "", null, pinned = true, updatedAt = 1))
        assertEquals(listOf("2", "1"), dao.observeAll().first().map { it.id })
    }
}

Real-world uses

Offline cache

Room as the source of truth for API data. See Offline-First.

User-created content

Notes, tasks and expenses: data that must never be lost, with careful migrations.

Paged lists

Return PagingSource from DAOs for huge tables. See Paging 3.

Common mistakes

  • Creating multiple database instances. Use a singleton.
  • Missing indices on columns used in WHERE and ORDER BY clauses.
  • Exposing entities to the UI. Map them to domain models.
  • allowMainThreadQueries() in production.
  • Destructive migration for important user data.

Frequently asked questions

Room or SQLDelight?

Room is Google's Android-first solution, and it now supports Kotlin Multiplatform too. SQLDelight generates Kotlin from SQL files and is also multiplatform. Both are solid choices.

How do I do full-text search?

Room supports SQLite FTS through @Fts4 entities, which are much faster than LIKE for large text.

Can I inspect the database while debugging?

Yes. Android Studio's App Inspection includes a Database Inspector for live queries.

Conclusion

Room gives you compile-time-checked SQL, Flow-based observation, suspend writes and managed migrations. Use a single instance, index your queries, export schemas, test DAOs and migrations, and map entities before they reach the UI.

Next step: decide what belongs in Room and what in DataStore with Room vs DataStore.

Official references

📱 Apps built by Yunok Tech

Real Android apps on Google Play, free to download.

See all our apps →

Ready to build real Android applications?

Networking, persistence and offline sync are where real apps get complicated. Practise building a data layer that handles slow networks, failures and caching, not just the happy path.

Move beyond theory and strengthen your Android development skills with Yunoktech's Android course. Learn core concepts, practical implementation, modern Android development techniques, and the reasoning behind building maintainable applications.

Explore the Android course and take your next step as an Android developer.

Explore Android Course →Browse all Android topics
Share: LinkedIn X / Twitter WhatsApp