Skip to content
Tutorials Center
My learning

Topics

Lesson 67 of 104 ยท Local Data

Room Database Basics

Learn Room, Android's SQLite library: entities, DAOs, the database class, queries, Flow results that update the UI, suspend inserts, conflict strategies and KSP setup.

Most apps need to remember things: notes, orders, downloaded articles, a to-do list. They must survive restarts and work offline. Android devices already include a fast, reliable database engine, SQLite, but using it directly means writing SQL strings, cursors and conversion code by hand, with errors showing up only at run time. Room is the Jetpack library that sits on top of SQLite. You describe your tables as Kotlin classes and your queries as annotated functions, and at build time Room checks the SQL and generates all the boilerplate.

This lesson covers the three pieces every Room setup has: entities, DAOs and the database class, plus queries, Flow results that update your UI by themselves, suspend writes and conflict handling. It assumes you know basic SQL from Introduction to SQL. Room needs the Android build tools and a device, so the real Room code is read-only. The TryIts are plain Kotlin stand-ins: an in-memory table with a DAO-style API, and an observable version that shows how Flow results re-emit after a write.

The three pieces

flowchart LR
    E["Entity<br/>a data class = a table"] --> D["DAO<br/>an interface of queries"]
    D --> DB["Database class<br/>(abstract RoomDatabase)"]
    DB --> G["Room generates the code<br/>(KSP, at build time)"]
    G --> S[("SQLite file")]

You write the first three boxes. The build generates the implementation that talks to SQLite.

Entity: a table as a class

An entity is a data class annotated with @Entity. Each property becomes a column, and each instance is a row:

Kotlin
// read-only example: needs Room
@Entity(tableName = "notes")
data class NoteEntity(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val title: String,
    val body: String = "",
    val pinned: Boolean = false,
    @ColumnInfo(name = "updated_at") val updatedAt: Long,
)
  • Every entity needs a @PrimaryKey. With autoGenerate = true and a default of 0, SQLite assigns the next number when you insert.
  • Column names default to property names. Use @ColumnInfo(name = "...") for snake_case or to keep a name stable when you rename the property.
  • Room supports the basic types (numbers, String, Boolean, ByteArray). Other types, such as dates or lists, need a TypeConverter, covered in Room Relations and Migrations.
  • Keep entities for storage. The rest of the app should use domain models, mapped in the repository, as in The Repository Pattern.

DAO: the queries

A DAO (data access object) is an interface or abstract class with one function per operation. You never implement it; Room does.

Kotlin
// read-only example
@Dao
interface NoteDao {

    @Insert(onConflict = OnConflictStrategy.ABORT)
    suspend fun insert(note: NoteEntity): Long              // returns the new row id

    @Update
    suspend fun update(note: NoteEntity): Int               // returns rows changed

    @Delete
    suspend fun delete(note: NoteEntity): Int

    @Upsert
    suspend fun upsert(note: NoteEntity)                    // insert, or update if the key exists

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

    @Query("SELECT * FROM notes ORDER BY pinned DESC, updated_at DESC")
    fun observeAll(): Flow<List<NoteEntity>>

    @Query("SELECT * FROM notes WHERE title LIKE '%' || :text || '%'")
    suspend fun search(text: String): List<NoteEntity>

    @Query("SELECT COUNT(*) FROM notes WHERE pinned = 1")
    fun observePinnedCount(): Flow<Int>
}

@Insert, @Update, @Delete and @Upsert need no SQL; @Query takes any SQL statement, and :name binds the function parameter safely, which protects against SQL injection. Room checks every query at build time: a typo in a column name or a mismatch between the result and the return type is a compile error, not a crash after release.

Two return shapes matter:

  • suspend fun runs once, on a Room-managed background thread, and returns the result. Room refuses to run a query on the main thread, so these functions are main-safe, as explained in Coroutines on Android.
  • Flow<T> returns a stream. The function itself is not suspend. It emits the current result when collected, and emits again every time the table changes.

Database class

The database class lists the entities, sets a version and exposes the DAOs:

Kotlin
// read-only example
@Database(entities = [NoteEntity::class], version = 1, exportSchema = true)
abstract class AppDatabase : RoomDatabase() {
    abstract fun noteDao(): NoteDao
}

// Create ONE instance for the whole app (it is expensive), usually with dependency injection:
val db = Room.databaseBuilder(context, AppDatabase::class.java, "app.db").build()

Building a database opens a connection pool and checks the schema, so create it once and share it. Hilt makes this easy (Hilt), and a manual container works too (Dependency Injection Concepts). Whenever you change an entity's columns, you must raise version and describe the change in a migration, otherwise the app crashes at startup with a schema mismatch. That is the subject of the next Room lesson.

Setting up Room with KSP

Room generates code with an annotation processor. Modern projects use KSP (Kotlin Symbol Processing), the faster Kotlin-native replacement for the older kapt. The setup is three small edits, using the version catalog from Gradle for Android:

TOML
# read-only example: gradle/libs.versions.toml
[versions]
room = "<current Room version>"
ksp = "<KSP version that matches your Kotlin version>"

[libraries]
androidx-room-runtime = { module = "androidx.room:room-runtime", version.ref = "room" }
androidx-room-ktx = { module = "androidx.room:room-ktx", version.ref = "room" }
androidx-room-compiler = { module = "androidx.room:room-compiler", version.ref = "room" }

[plugins]
ksp = { id = "com.google.devtools.ksp", version.ref = "ksp" }
Kotlin
// read-only example: app/build.gradle.kts
plugins {
    alias(libs.plugins.android.application)
    alias(libs.plugins.kotlin.android)
    alias(libs.plugins.ksp)
}

dependencies {
    implementation(libs.androidx.room.runtime)
    implementation(libs.androidx.room.ktx)       // coroutine and Flow support
    ksp(libs.androidx.room.compiler)             // the code generator
}

Look up the current stable Room version, and pick the KSP release that matches your Kotlin version, when you create the project. A mismatched KSP version is the most common setup failure, and its error message names the Kotlin version it expects.

A DAO you can run: the stand-in table

Room's SQL engine is SQLite, which the site cannot run next to Kotlin. The first TryIt keeps the same shape: an entity-like data class, a table with an auto-generated key, and a DAO whose functions mirror the SQL in the comments. It also shows what the three conflict strategies do when you insert a row whose key already exists.

Kotlin A DAO over an in-memory table (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with readln()), it gets the next line from this box.

Note the return values, which are real Room behavior. @Insert returns the new row ID (or -1 when IGNORE skipped it), and @Update and @Delete return the number of rows affected. The default conflict strategy is ABORT, which throws SQLiteConstraintException on a duplicate key. REPLACE deletes the old row and inserts the new one, which can have side effects once tables reference each other (it triggers cascading deletes), so prefer @Upsert for "insert or update".

Flow results: a UI that updates itself

The real power of Room is in the queries that return Flow. Room tracks which tables each query reads. When any insert, update or delete touches one of those tables, Room runs the query again and emits the new result. Your ViewModel exposes that flow, Compose collects it, and the screen redraws after a database write without any refresh code:

Kotlin
// read-only example
class NotesViewModel(private val dao: NoteDao) : ViewModel() {

    val notes: StateFlow<List<NoteEntity>> = dao.observeAll()
        .stateIn(viewModelScope, SharingStarted.WhileSubscribed(5_000), emptyList())

    fun addNote(title: String) {
        viewModelScope.launch {
            dao.insert(NoteEntity(title = title, updatedAt = System.currentTimeMillis()))
            // no refresh call: the Flow emits the new list by itself
        }
    }
}

There is one detail that surprises people: Room invalidates per table, not per row. If you observe the count of pinned notes and someone edits the body of an unpinned note, the table changed, so the query reruns and emits 1 again even though the number did not change. Add .distinctUntilChanged() when downstream work is expensive.

sequenceDiagram
    participant UI as Compose screen
    participant VM as ViewModel
    participant R as Room
    UI->>VM: collect notes
    VM->>R: observeAll() subscribes
    R-->>UI: current rows
    UI->>VM: addNote("Milk")
    VM->>R: insert (suspend)
    R-->>UI: table changed, query re-run, new rows

A write never returns data to the UI. The observed Flow does.

The second TryIt rebuilds this mechanism. A tiny database runs queries as functions of its rows and re-runs every observed query whenever the table changes. Three collectors watch it: the list of titles, the raw pinned count and the pinned count with distinctUntilChanged().

Kotlin Flow results that re-emit on every table change (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with readln()), it gets the next line from this box.

Compare the last two collectors. The raw count prints a line after every write, including the rename that did not change the number, while the distinct version prints only when the value really changes. The list collector always reflects the latest rows.

Behind Room: SQLiteOpenHelper and pre-populated databases

Room generates code on top of the framework's own SQLite classes. You will still meet those classes in older apps, so it helps to know what Room is saving you from.

SQLiteOpenHelper owns the database file. You give it a name and a version number, and it calls you back at the two moments that matter:

Kotlin
// read-only example: needs Android
class NotesHelper(context: Context) : SQLiteOpenHelper(context, "notes.db", null, 2) {

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(
            """CREATE TABLE notes (
                 id INTEGER PRIMARY KEY AUTOINCREMENT,
                 title TEXT NOT NULL,
                 body TEXT NOT NULL DEFAULT '',
                 pinned INTEGER NOT NULL DEFAULT 0
               )"""
        )
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        if (oldVersion < 2) {
            db.execSQL("ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0")
        }
    }
}

onCreate runs once, the first time the file is created. onUpgrade runs when the version you pass is higher than the one stored in the file; you write the migration SQL yourself, step by step. The helper opens the database lazily on the first readableDatabase or writableDatabase call, which does disk work, so never call it on the main thread.

ContentValues and Cursor

Writes use ContentValues, a key-to-value bag, and reads return a Cursor that you walk row by row:

Kotlin
// read-only example: needs Android
fun insert(db: SQLiteDatabase, title: String): Long {
    val values = ContentValues().apply {
        put("title", title)
        put("pinned", 0)
    }
    return db.insert("notes", null, values)          // new row id, or -1 on error
}

fun readAll(db: SQLiteDatabase): List<Note> {
    val result = mutableListOf<Note>()
    db.query("notes", null, null, null, null, null, "pinned DESC, id DESC").use { c ->
        val idCol = c.getColumnIndexOrThrow("id")
        val titleCol = c.getColumnIndexOrThrow("title")
        while (c.moveToNext()) {
            result += Note(c.getLong(idCol), c.getString(titleCol))
        }
    }
    return result
}

fun rename(db: SQLiteDatabase, id: Long, title: String): Int =
    db.update("notes", ContentValues().apply { put("title", title) }, "id = ?", arrayOf(id.toString()))

fun delete(db: SQLiteDatabase, id: Long): Int =
    db.delete("notes", "id = ?", arrayOf(id.toString()))

Three habits matter here. Always pass user input through the ? placeholders and the argument array, never by gluing strings into the SQL, or you open the door to SQL injection. Always close the cursor (use does it). And look up column indexes by name, not by position.

Compare that to the Room DAO earlier in this lesson: the same four operations are four annotated lines, the compiler checks your SQL, and Flow replaces polling. That gap is the reason new code uses Room.

A CRUD screen outline

Whether the storage is Room or a helper, a "list with add, edit and delete" screen has the same shape:

  1. A repository wraps the data source and exposes observeAll(), add, rename and remove, all main-safe.
  2. A ViewModel turns the repository's stream into a StateFlow of UI state and offers functions for the screen's events, see ViewModel.
  3. The screen shows a LazyColumn of rows, a text field and an add button, and calls the ViewModel on taps. Swipe or a trailing icon deletes; tapping a row opens an edit dialog.

The next TryIt models the first layer with a raw-helper style: versions, onCreate, onUpgrade, ContentValues-like maps and a cursor you walk with moveToNext.

Kotlin A SQLiteOpenHelper-style store (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with readln()), it gets the next line from this box.

Note the order in the output: the pinned row comes first because the cursor was sorted by the pinned column, like ORDER BY pinned DESC in the real query.

Shipping a pre-populated database

Sometimes an app needs data on first launch: a phrase book, a list of countries, a recipe collection. Build the .db file on your computer, put it in app/src/main/assets/, and tell Room to copy it the first time the database is created:

Kotlin
// read-only example: needs Android
val db = Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .createFromAsset("databases/prepackaged.db")
    .build()

There are two variants: createFromAsset(path) for a file in assets, and createFromFile(file) for a file you downloaded or generated. Room copies the file only when no database exists yet, and then validates it against your entities. Two rules prevent most failures. The schema of the file must match your @Entity classes exactly (column names, types, nullability, indices), and the file must carry the right user_version. Export Room's schema (exportSchema = true) and create the asset by running that schema in a SQLite tool. If you later bump the version, give Room a migration (or a createFromAsset fallback with fallbackToDestructiveMigration only when losing user data is acceptable). See Room Relations and Migrations.

Practical habits

  • One database instance, one DAO per entity (or per feature). Share the database through DI.
  • Return Flow from DAOs for anything shown on screen, and suspend for one-shot reads and writes.
  • Never block the main thread with allowMainThreadQueries(). It exists for tests and demos.
  • Wrap related writes in @Transaction (or db.withTransaction { }) so they succeed or fail together.
  • Pass IDs, not whole entities, between screens, and load the entity in the destination.
  • Use SELECT lists carefully on big tables. Reading a long body column for a list that only shows titles wastes memory; define a small result class with just those columns.
  • Test with an in-memory database. Room can build one with Room.inMemoryDatabaseBuilder, see Instrumented, Room and Navigation Tests.

Common mistakes

  • Forgetting to bump the database version after changing an entity. The app crashes at startup with an "Expected identity hash" or schema error.
  • Creating the database in every ViewModel or screen. Each one opens its own connections and cannot see the others' cache.
  • Using @Insert(onConflict = REPLACE) with foreign keys and being surprised when child rows vanish.
  • Collecting a Room Flow with plain collectAsState, which keeps observing in the background. Use collectAsStateWithLifecycle, see Collecting Flows Safely.
  • Putting UI logic in entities (formatting, strings). Map them to domain or UI models instead.
  • Assuming a Flow query emits only when your row changes. It emits on any change to the table.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What are the three main pieces of a Room setup?

    Entities describe tables, DAOs hold queries, and the abstract database class ties them together. Room generates the implementation.
  2. 2. A DAO function declared as fun observeAll(): Flow<List<NoteEntity>> without suspend. What does it do?

    Room reruns the query after a change to any table it reads and emits the new result, which is how screens update after a write.
  3. 3. What does @Insert return for an entity with an auto-generated key?

    An insert function may return Long (the new row id), Unit, or a list of ids for several rows. With the IGNORE strategy a skipped insert returns -1.
  4. 4. Why is the Flow of COUNT(*) FROM notes WHERE pinned = 1 re-emitted when an unpinned note is edited?

    Room only knows that the notes table changed. Add distinctUntilChanged() when repeated equal values would trigger needless work.
  5. 5. What happens if you change an entity's columns but keep version = 1 on a device that already has the old database?

    Room validates the schema on open. Raise the version and provide a migration so existing data is upgraded.

Summary

  • Room wraps SQLite with compile-time checked SQL: entities (tables), DAOs (queries) and an abstract database class.
  • Add Room with the version catalog, implementation for runtime and ktx, and ksp for the compiler.
  • @Insert returns the row id, @Update and @Delete return affected rows, and the conflict strategy decides what a duplicate key does; prefer @Upsert for insert-or-update.
  • suspend DAO functions are main-safe one-shots; Flow DAO functions emit again whenever the observed table changes, which drives the UI.
  • Room invalidates per table, so add distinctUntilChanged() when repeated equal results matter.
  • Create one database instance, bump the version with a migration whenever the schema changes, and keep entities out of the UI layer.
  • Room code is read-only here; the TryIts are in-memory stand-ins with the same API shape and Flow behavior.

Next: Room Relations and Migrations