Entities, DAOs and the database
A library has shelves (the tables), a catalog card format for each book (the entity), and a librarian who knows how to find and put away books (the DAO). You never walk into the stacks yourself; you ask the librarian.
Room uses three pieces:
- an entity: a data class that describes one table, one property per column;
- a DAO (data access object): an interface with the queries you need, written in SQL;
- the database class that lists the entities and gives you the DAOs.
Room checks your SQL while compiling: a misspelled column is a build error, not a crash on a user’s phone.
Room generates the DAO implementations (via KSP) and validates every query against the schema at compile time. suspend DAO functions run on Room’s query executor; Flow-returning queries register an invalidation observer on the tables they read and re-run when those tables change. Multi-statement work goes in @Transaction (or withTransaction). Since Room 2.7 the same API also runs in Kotlin Multiplatform with the bundled SQLite driver.
@Entity(tableName = "notes")
data class NoteEntity(
@PrimaryKey val id: String, // a UUID, created on the device
val title: String,
val body: String,
@ColumnInfo(name = "updated_at") val updatedAt: Long,
val archived: Boolean = false,
)
@Dao
interface NoteDao {
@Query("SELECT * FROM notes WHERE archived = 0 ORDER BY updated_at DESC")
fun observeActive(): Flow<List<NoteEntity>> // re-emits whenever notes change
@Query("SELECT * FROM notes WHERE id = :id")
suspend fun byId(id: String): NoteEntity?
@Upsert
suspend fun save(note: NoteEntity) // insert or update
@Query("UPDATE notes SET archived = 1, updated_at = :now WHERE id = :id")
suspend fun archive(id: String, now: Long)
}
@Database(entities = [NoteEntity::class], version = 1, exportSchema = true)
abstract class AppDatabase : RoomDatabase() {
abstract fun notes(): NoteDao
}
val db = Room.databaseBuilder(context, AppDatabase::class.java, "app.db").build() // one per appFlow<List<NoteEntity>>: the screen collects it once, and Room sends a new list every time a note is saved or archived. You never “refresh” by hand.suspendfor writes and one-off reads: Room runs them off the main thread. Room refuses to run a blocking query on the main thread at all, because it would freeze the UI.@Upsertinserts a new row or updates the existing one with the same primary key.- One database instance for the whole app (create it in your DI setup), because opening it is expensive.
Tap the line with the bug.
@Dao
interface NoteDao {
@Query("SELECT * FROM notes WHERE archived = 0 ORDER BY updated_at DESC")
suspend fun active(): List<NoteEntity>
}
class NotesViewModel(private val dao: NoteDao) : ViewModel() {
val notes = MutableStateFlow<List<NoteEntity>>(emptyList())
init { viewModelScope.launch { notes.value = dao.active() } }
fun add(title: String) = viewModelScope.launch {
dao.save(NoteEntity(UUID.randomUUID().toString(), title, "", System.currentTimeMillis()))
}
}Hint: The list is read once, in init. What tells the ViewModel that a note was added?
The list is read once, when the ViewModel is created. Adding a note writes to the database, but nothing reads it again, so the new note only appears after the screen is reopened. The quick fix (calling dao.active() again after every write) breaks as soon as data changes from somewhere else: a background sync, another screen, a notification action.
Observe the query instead. The database becomes the single source of truth, and every change, from anywhere, reaches the screen:
@Query("SELECT * FROM notes WHERE archived = 0 ORDER BY updated_at DESC")
fun observeActive(): Flow<List<NoteEntity>>
class NotesViewModel(private val dao: NoteDao) : ViewModel() {
val notes: StateFlow<List<NoteEntity>> = dao.observeActive()
.stateIn(viewModelScope, SharingStarted.WhileSubscribed(5_000), emptyList())
fun add(title: String) = viewModelScope.launch {
dao.save(NoteEntity(UUID.randomUUID().toString(), title, "", System.currentTimeMillis()))
}
}Predict the output
A screen collects dao.observeActive(). The user archives one of 10 notes. How many lists does the Flow emit in total, from opening the screen?
A Room Flow emits the current result when collection starts (10 notes), then runs the query again whenever the notes table changes. Archiving updates the table, so it emits a second list with 9 notes, and the UI updates on its own. That's the whole point of observing: write anywhere, and every screen that shows the data is up to date.
Search notes
Add a DAO query that returns active notes whose title or body contains a search text, newest first, as a Flow. Then, in the ViewModel, connect it to a query: MutableStateFlow<String> so the results update as the user types.
›
SQL’s LIKE '%' || :text || '%' matches text anywhere in a column. In the ViewModel, flatMapLatest switches to a new database Flow every time the query changes.
SolutionTry it yourself first
@Query("""
SELECT * FROM notes
WHERE archived = 0 AND (title LIKE '%' || :text || '%' OR body LIKE '%' || :text || '%')
ORDER BY updated_at DESC
""")
fun search(text: String): Flow<List<NoteEntity>>
class NotesViewModel(private val dao: NoteDao) : ViewModel() {
val query = MutableStateFlow("")
@OptIn(ExperimentalCoroutinesApi::class)
val results: StateFlow<List<NoteEntity>> = query
.debounce(200) // wait until typing pauses
.flatMapLatest { text -> dao.search(text) } // cancel the previous query's Flow
.stateIn(viewModelScope, SharingStarted.WhileSubscribed(5_000), emptyList())
}:text is a bound parameter, so user input can never change the SQL itself (no SQL injection). For large amounts of text, Room’s full-text search (@Fts4 entities and MATCH) is much faster than LIKE.
Things the generated code won’t do for you
- Invalidation is per table: a Flow re-runs its query on any write to a table it reads, even when its own rows didn’t change. Add
distinctUntilChanged()before expensive downstream work. - Relations:
@Relationand multimap return types (Map<Order, List<Item>>) run several queries; mark such DAO methods@Transactionso they read a consistent snapshot. - Large lists: return
PagingSource<Int, NoteEntity>and use Paging 3 instead of loading thousands of rows into memory. - Testing:
Room.inMemoryDatabaseBuilderwith the real DAO in a Robolectric or instrumented test checks your SQL against real SQLite; mocking DAOs tests nothing about the queries.
@Entity,@Dao,@Database; queries validated at compile time (KSP). One database instance per process.suspendwrites/one-shot reads;Flowqueries re-emit on table invalidation (per table, sodistinctUntilChanged).@Transaction/withTransactionfor multi-statement work and relations; Paging 3PagingSourcefor large lists.- Test DAOs against an in-memory database, not mocks. Room 2.7+ runs in KMP.
Check this part
Why does a DAO query return Flow<List<NoteEntity>> instead of List<NoteEntity>?
When does Room report a misspelled column in a @Query?
A Flow query on orders WHERE id = 5 re-emits after order 7 changes. Why?