Room Database Explained
DATA › Storage
Room is Google's abstraction over SQLite, the standard persistence layer on Android. It's built from three pieces that work together:
@Entity
data class User(@PrimaryKey val id: Long, val name: String)
@Dao
interface UserDao {
@Query("SELECT * FROM User")
fun getAll(): Flow<List<User>>
}
@Database(entities = [User::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
abstract fun userDao(): UserDao
}
An @Entity maps a class to a table, a @Dao interface declares the queries, and the @Database class ties them together. Unlike raw SQLite, Room does this work at **compile time**: a KSP annotation processor reads your annotations and generates the real implementations of AppDatabase and UserDao, checking your SQL against the schema as it goes. That's the headline interviewers probe: which class of bugs moves from a runtime crash to a build failure.
Every DAO needs ways to write data. @Insert, @Update, and @Delete are Room's built-in mutation annotations, and each behaves differently:
@Dao
interface UserDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insert(user: User): Long
@Update
suspend fun update(user: User): Int
@Delete
suspend fun delete(user: User)
}
@Insert returns the new row's rowId as a Long (or a List<Long> for a batch). @Update and @Delete match rows **by primary key**, so if the object you pass doesn't correspond to an existing row, they quietly do nothing; @Update can optionally return an Int count of rows actually touched. The onConflict parameter on @Insert decides what happens when the primary key already exists: REPLACE deletes and reinserts, IGNORE keeps the existing row, and the default ABORT throws.
**Room can assign primary keys for you, and the mechanism is easy to get backwards.** Add autoGenerate = true to @PrimaryKey on a Long (or Int) property, and instead of choosing a value yourself, leave it at 0, SQLite's rowid machinery treats 0 as "assign me the next id", and Room's generated @Insert returns whatever it actually picked:
@Entity
data class User(
@PrimaryKey(autoGenerate = true) val id: Long = 0, // 0 -> SQLite assigns it
val name: String
)
val newId: Long = userDao.insert(User(name = "Alice"))
Passing a nonzero id yourself still works, Room won't overwrite an explicit value, autoGenerate only kicks in for the placeholder 0. This only applies to numeric primary keys; there's no auto-generation for a String key like a UUID, you generate those yourself before inserting.
@Query is where you write SQL directly, and it's the other half of Room's compile-time promise. Because Room knows your schema from the @Entity classes, it parses every @Query string during the KSP build step and checks it against that schema:
@Dao
interface UserDao {
@Query("SELECT * FROM User WHERE name = :name")
fun findByName(name: String): List<User>
}
The :name token binds to the function parameter of the same name. If you misspell a column, reference a table that doesn't exist, or write invalid SQL, the build fails right there with a clear compiler error, you never get to ship it. This is the single biggest reason to reach for Room over hand-rolled SQLite: correctness gets checked before the app ever runs.
DAO query methods can return data in three different shapes, and picking the right one matters for both correctness and compilation.
For a one-shot read or a write, mark the function suspend:
@Query("SELECT * FROM User WHERE id = :id")
suspend fun getUser(id: Long): User
For an observable read that should update the UI whenever the underlying table changes, return Flow (or LiveData) instead, and **do not** mark it suspend:
@Query("SELECT * FROM User")
fun getAllUsers(): Flow<List<User>>
Flow and LiveData are already asynchronous streams; Room re-emits a fresh list every time any row in User changes, so wrapping the function in suspend would be redundant, and Room's annotation processor rejects it outright. The mental model: suspend for a single answer, Flow for a subscription.
**Room refuses to run a blocking query on the main thread, and that's deliberate, not a bug you work around.** Call a non-suspend, non-Flow DAO method, one that returns List<User> directly, from the main thread, and Room throws IllegalStateException rather than silently jank the UI while SQLite does disk I/O:
// BAD: synchronous query called from the main thread
val users = db.userDao().getAllSync() // fun getAllSync(): List<User>
// -> IllegalStateException
The fix is almost always to use suspend or Flow as covered already, both push the work off the main thread for you. The actual escape hatch, allowMainThreadQueries() on the database builder, exists for tests and quick prototypes only, shipping it in production reintroduces the exact jank risk Room was protecting you from:
Room.databaseBuilder(context, AppDatabase::class.java, "db")
.allowMainThreadQueries() // tests/prototyping only
.build()
Bump the version on @Database and Room needs an explicit path from the old schema to the new one, it never infers one on its own. You supply a Migration object describing exactly what SQL to run:
val MIGRATION_1_2 = object : Migration(1, 2) {
override fun migrate(db: SupportSQLiteDatabase) {
db.execSQL("ALTER TABLE User ADD COLUMN age INTEGER NOT NULL DEFAULT 0")
}
}
Room.databaseBuilder(context, AppDatabase::class.java, "app-db")
.addMigrations(MIGRATION_1_2)
.build()
If you bump the version and provide neither a matching Migration nor fallbackToDestructiveMigration(), Room throws an IllegalStateException the moment it tries to open the database, it refuses to guess. fallbackToDestructiveMigration() is the escape hatch: it drops and recreates every table, which fixes the crash but **deletes all existing user data**, fine for a dev build, dangerous to ship.
SQLite's column types are limited to primitives, String, and byte[], so anything else, Date, an enum, a List, needs a @TypeConverter pair that translates to and from a storable type:
class Converters {
@TypeConverter
fun fromTimestamp(value: Long?): Date? = value?.let { Date(it) }
@TypeConverter
fun dateToTimestamp(date: Date?): Long? = date?.time
}
@TypeConverters(Converters::class)
@Database(entities = [Event::class], version = 1)
abstract class AppDatabase : RoomDatabase()
Room calls these automatically whenever it reads or writes an Event.date field, so your entity keeps using the real Date type while the table underneath stores a plain Long. You can register @TypeConverters at the database, entity, or even individual DAO level, the annotation just controls how widely that converter is visible.
**Room doesn't enforce relationships between tables unless you declare them, and declaring one changes what a delete actually does.** Add a foreignKeys array to the child @Entity, pointing childColumns at the columns that reference the parent's parentColumns. onDelete then decides what happens to child rows when their parent row is deleted, CASCADE deletes them along with it, the default leaves them orphaned pointing at nothing:
@Entity(
foreignKeys = [ForeignKey(
entity = Author::class,
parentColumns = ["id"],
childColumns = ["authorId"],
onDelete = ForeignKey.CASCADE // deleting an Author deletes their Books too
)]
)
data class Book(
@PrimaryKey val id: Int,
val authorId: Int,
val title: String
)
This is a real SQLite-level constraint enforced at runtime on every delete, not a compile-time-only annotation, so a delete you didn't expect to cascade can quietly remove far more rows than it looks like at the call site.
To fetch a parent row together with its children in one call, build a plain POJO (not an @Entity) that embeds the parent and marks the children with @Relation:
data class UserWithPets(
@Embedded val user: User,
@Relation(parentColumn = "userId", entityColumn = "ownerUserId")
val pets: List<Pet>
)
@Dao
interface UserDao {
@Transaction
@Query("SELECT * FROM User")
fun getUsersWithPets(): Flow<List<UserWithPets>>
}
Under the hood Room runs this as **two separate queries**, one for users, one for pets, then stitches them together by matching parentColumn to entityColumn. Because that's two queries rather than one atomic read, add @Transaction: it wraps both SELECTs so a write can't sneak in between them and hand you a User paired with pets from an inconsistent snapshot.
**One-to-many @Relation doesn't stretch to many-to-many, Students and Courses each have many of the other, and a plain foreign key can only point one way.** The fix is a junction (cross-reference) entity holding both keys, then a @Relation that points through it via associateBy = Junction(...):
@Entity(primaryKeys = ["studentId", "courseId"])
data class StudentCourseCrossRef(val studentId: Int, val courseId: Int)
data class StudentWithCourses(
@Embedded val student: Student,
@Relation(
parentColumn = "studentId",
entityColumn = "courseId",
associateBy = Junction(StudentCourseCrossRef::class)
)
val courses: List<Course>
)
Room resolves this as a join through the cross-reference table under the hood: match parentColumn against the junction's studentId, then the junction's courseId against entityColumn on Course. Nothing here is generated for you, you declare the junction entity yourself and Room validates the whole chain at compile time, same as any other @Query.
Pulling it together: @Entity classes define your tables, @Dao interfaces declare typed, compile-time-checked access to them, and the @Database abstract class wires the two together, built once as a singleton via Room.databaseBuilder(...).build(). @Insert, @Update, or @Delete cover simple mutations with primary-key matching and conflict strategies, @Query covers everything else with validated raw SQL. suspend gets you a one-shot answer, Flow gets you a live subscription, and Room enforces that split by refusing to run either kind synchronously on the main thread. Migration objects are mandatory the moment your schema changes shape. @ForeignKey and onDelete behavior make relationships between tables a real, enforced constraint, and @Relation extends from simple one-to-many all the way to many-to-many once a junction entity is in the mix. Interviewers are listening for you to connect each annotation back to what Room actually generates and validates at build time, not just recite the annotation names.