Room Database Explained

DATA › Storage

Room is Google's abstraction over SQLite, and it's the standard persistence layer on Android. It's built from three pieces that work together: an @Entity class maps to a table, a @Dao interface declares the queries against that table, and a @Database class ties the entities and DAOs together into one access point. Unlike hand-rolled SQLite, Room does most of its work at compile time. A KSP annotation processor reads your annotations and generates the real implementations of your database and DAO classes, checking your SQL against the schema as it goes. That compile-time move, from runtime crash to build failure, is the property interviewers are actually probing when they ask you to describe Room's architecture. You typically build the database once, as a singleton, via Room.databaseBuilder so the whole app shares one connection.

@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
}

val db = Room.databaseBuilder(context, AppDatabase::class.java, "app-db").build()

@Query is where you write SQL directly, and it's the other half of Room's compile-time promise. Because Room already knows your schema from the @Entity classes, it parses every @Query string during the KSP build step and checks it against that schema. A colon-prefixed token in the SQL 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 compiler error. You never get the chance to ship it. This is the single biggest argument for reaching for Room instead of hand-rolled SQLite: correctness gets checked before the app ever runs, not the first time a user happens to hit that code path.

@Dao
interface UserDao {
    // 'naem' is a typo for 'name'
    @Query("SELECT * FROM User WHERE naem = :name")
    fun findByName(name: String): List<User>
}

Every DAO needs ways to write data, and Room gives you three built-in mutation annotations that each behave differently. @Insert returns the new row's rowId as a Long, or a List of Longs 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. The onConflict parameter on @Insert decides what happens when the primary key you're inserting already exists. REPLACE deletes the conflicting row and inserts the new one in its place. IGNORE keeps the existing row and silently drops the new one. The default, ABORT, throws instead of picking a winner.

@Dao
interface UserDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(user: User): Long

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertIfAbsent(user: User): Long

    @Insert(onConflict = OnConflictStrategy.ABORT)
    suspend fun insertStrict(user: User): Long
}

@Update and @Delete both operate by matching the primary key of the object you pass in against what's already in the table. If nothing matches, they do nothing, no exception, no signal, the call just quietly has no effect. That makes their return values worth knowing precisely. @Delete returns nothing useful. @Insert returns the generated rowId as a Long. @Update is the one that can optionally return an Int, the count of rows it actually matched and updated, which is genuinely useful for confirming a write landed rather than silently missing its target.

@Dao
interface UserDao {
    @Update
    suspend fun updateUser(user: User): Int

    @Insert
    suspend fun insertUser(user: User): Long
}

Room can assign primary keys for you, and the mechanism is easy to get backwards if you haven't seen it before. Add autoGenerate = true to @PrimaryKey on a Long or Int property. Instead of choosing a value yourself, you leave that field at a placeholder value, and SQLite's rowid machinery treats that placeholder as an instruction to assign the next available id. Room's generated @Insert then returns whatever value SQLite actually picked. Pass a nonzero id yourself and Room won't overwrite it, autoGenerate only kicks in for the placeholder. This mechanism is specific to numeric primary keys too, there's no equivalent auto-generation for a String key like a UUID, you generate those yourself before inserting.

@Entity
data class User(
    @PrimaryKey(autoGenerate = true) val id: Long = ____,
    val name: String
)

val newId: Long = userDao.insert(User(name = "Alice"))

DAO query methods can return data in different shapes, and picking the right one matters for both correctness and whether the project even compiles. For a one-shot read or a write, mark the function suspend, you get a single answer and move on. For an observable read that should keep the UI in sync with the table, return Flow instead, and do not mark it suspend. Flow is already an asynchronous stream, and Room re-emits a fresh list every time any row in the queried table changes. Marking a Flow-returning function suspend is redundant on top of being wrong, Room's annotation processor rejects it outright. The mental model worth keeping is that suspend gets you a single answer, and Flow gets you a subscription.

@Dao
interface UserDao {
    // No 'suspend' here, Flow already handles async delivery
    @Query("SELECT * FROM User")
    fun getAllUsers(): Flow<List<User>>

    // suspend fun getAllUsers(): Flow<List<User>>  // won't compile
}

Room refuses to run a blocking query on the main thread, and that's a deliberate design choice, not a bug you should work around. Call a non-suspend, non-Flow DAO method, one that returns a plain List directly, from the main thread, and Room throws rather than silently letting the UI jank while SQLite does disk I/O. The fix is almost always what the previous chunk already covered: use suspend or Flow, both push the work off the main thread automatically. There is a genuine escape hatch on the database builder, but it exists for tests and quick prototypes only. Shipping it in production reintroduces the exact freeze Room was built to prevent.

// BAD: synchronous query called from the main thread
val users = db.userDao().getAllSync() // fun getAllSync(): List<User>

// GOOD: push the work off the main thread
lifecycleScope.launch {
    val users = db.userDao().getAll() // suspend fun
}

// Escape hatch, tests and prototyping only
Room.databaseBuilder(context, AppDatabase::class.java, "db")
    .allowMainThreadQueries()
    .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 that path as a Migration object describing exactly what SQL to run to get from one version to the next, then register it with addMigrations on the database builder. If you bump the version and provide neither a matching Migration nor a destructive fallback, Room doesn't guess and it doesn't quietly skip the check.

// version bumped to 2, no migration supplied, no destructive fallback
@Database(entities = [User::class], version = 2)
abstract class AppDatabase : RoomDatabase()

// what you actually need to supply:
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()

Two more pieces round out the migration picture. fallbackToDestructiveMigration is a real escape hatch, if no migration path exists, it drops and recreates every table instead of crashing. That fixes the crash but deletes all existing user data, which makes it fine for a dev build and dangerous to ship. AutoMigration is the other option, it generates simple migrations for you from the exported schema files across versions, no Migration object required. But it only handles unambiguous changes like adding a column. Renaming a column or dropping one is ambiguous from Room's point of view, it can't tell a rename apart from a drop followed by an add, so those cases require you to supply an AutoMigrationSpec that disambiguates the intent.

// Unambiguous change (added column), no spec needed
@Database(entities = [User::class], version = 2,
    autoMigrations = [AutoMigration(from = 1, to = 2)])
abstract class AppDatabase : RoomDatabase()

// Ambiguous change (renamed column), spec required
@RenameColumn(tableName = "User", fromColumnName = "old_name", toColumnName = "full_name")
class RenameSpec : AutoMigrationSpec

@Database(entities = [User::class], version = 3,
    autoMigrations = [AutoMigration(from = 2, to = 3, spec = RenameSpec::class)])
abstract class AppDatabase2 : RoomDatabase()

SQLite's column types are limited to primitives, String, and a byte array. Anything else, a Date, an enum, a List, needs a pair of @TypeConverter methods that translate to and from a storable type. Room calls these automatically whenever it reads or writes the field they're registered for, so your entity keeps using the real type in your Kotlin code while the table underneath stores something SQLite actually understands, typically a Long for a timestamp. You register @TypeConverters at the database level, the entity level, or even an individual DAO, the annotation just controls how widely that converter is visible.

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()

@Entity
data class Event(
    @PrimaryKey val id: Int,
    val date: Date // stored as Long via the converter
)

To fetch a parent row together with its children in one call, you don't add a list field to the entity itself, entities map directly to a single table and can't hold a reference to another entity. Instead you build a plain data class, not an @Entity, that embeds the parent and marks the children with @Relation, pointing a parentColumn at the matching entityColumn on the child table. Under the hood Room runs this as two separate queries, one for the parent rows and one for the children, then stitches the results together in memory by matching those two columns.

data class UserWithPets(
    @Embedded val user: User,
    @Relation(parentColumn = "userId", entityColumn = "ownerUserId")
    val pets: List<Pet>
)

@Dao
interface UserDao {
    @Query("SELECT * FROM User")
    fun getUsersWithPets(): Flow<List<UserWithPets>>
}

Fetching a parent plus its @Relation children is, as just covered, actually two separate SELECT statements under the hood, not one atomic read. That gap matters because a write can land in between them. Imagine the parent query runs, then someone deletes a child row, then the child query runs, you'd get back a parent paired with a children list that never existed together at any single point in time. Annotating that DAO method with @Transaction closes the gap, it wraps both underlying SELECTs so they run against one consistent snapshot, with no write able to sneak in between them.

@Dao
interface UserDao {
    @Transaction
    @Query("SELECT * FROM User")
    fun getUsersWithPets(): Flow<List<UserWithPets>>
}

Room doesn't enforce any relationship between two tables unless you explicitly declare one, and declaring one changes what a delete actually does at the database level. You add a foreignKeys array to the child @Entity, pointing childColumns at the columns that reference the parent's parentColumns. The onDelete parameter then decides what happens to child rows when their parent row is deleted. CASCADE deletes the children along with the parent, the default instead leaves them orphaned, pointing at a parent that no longer exists. This is a real SQLite-level constraint enforced at runtime on every delete, not something Room only checks at compile time, so a delete you didn't expect to cascade can quietly remove far more rows than it looks like from the call site.

@Entity(
    foreignKeys = [ForeignKey(
        entity = Author::class,
        parentColumns = ["id"],
        childColumns = ["authorId"],
        onDelete = ForeignKey.CASCADE
    )]
)
data class Book(
    @PrimaryKey val id: Int,
    val authorId: Int,
    val title: String
)

A one-to-many @Relation, one parent to a list of children, doesn't stretch to many-to-many. Students each take many courses, and courses each have many students, so a plain foreign key on either side can only point one way. The fix is a junction entity, sometimes called a cross-reference table, that holds nothing but the two foreign keys, one row per pairing. You then write a @Relation that points through that junction using associateBy = Junction, naming the junction entity. Nothing here is generated for you automatically, you declare the junction entity yourself, and Room validates the whole chain at compile time exactly like any other query.

@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>
)

Back to Room Database