Room Database

Many apps must work without internet: a shop's stock list, a student's timetable, saved train searches, an offline cache of yesterday's news. Android phones include SQLite, a small SQL database that stores data in a file inside your app. Writing raw SQLite code is long and error-prone, so Google's Jetpack library Room sits on top of it. You describe tables with Kotlin classes and queries with annotations, and Room generates the rest.

Room checks your SQL when you compile, so a misspelt column name is a build error instead of a crash on a user's phone. It also speaks Kotlin fluently: queries can return a Flow that updates automatically, and writes are suspend functions. In this lesson you will build the database of a school library app in Pune, step by step. If SQL is new to you, read the SQL tutorial first. The code in this lesson was compiled with Room 2.8.

The three parts of Room

Room's code generator runs through KSP (Kotlin Symbol Processing). In the module's build file, add the com.google.devtools.ksp plugin, then implementation("androidx.room:room-runtime:2.8.5") and ksp("androidx.room:room-compiler:2.8.5"). Since Room 2.7, the old room-ktx artifact is no longer needed because its Kotlin features are part of room-runtime.

Building the library database, step by step

1. Define the entity. @PrimaryKey(autoGenerate = true) lets SQLite number the rows. An index on isbn with unique = true makes duplicate ISBNs impossible and makes lookups by ISBN fast.

@Entity(tableName = "books", indices = [Index(value = ["isbn"], unique = true)])
data class Book(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val isbn: String,
    val title: String,
    val author: String,
    val copies: Int,
    val issued: Int = 0,
    val shelf: String = "General",
)

2. Write the DAO. Functions that return a Flow are observable queries: Room runs them again whenever the books table changes and emits the new list. Functions marked suspend run once. Room runs both on its own background threads, so they are safe to call from the main thread.

data class AuthorCount(val author: String, val total: Int)

@Dao
interface BookDao {
    @Query("SELECT * FROM books WHERE copies > issued ORDER BY title")
    fun observeAvailable(): Flow<List<Book>>

    @Query("SELECT * FROM books WHERE title LIKE '%' || :text || '%' OR author LIKE '%' || :text || '%'")
    fun search(text: String): Flow<List<Book>>

    @Query("SELECT author, COUNT(*) AS total FROM books GROUP BY author ORDER BY total DESC")
    suspend fun countByAuthor(): List<AuthorCount>

    @Upsert
    suspend fun save(book: Book)

    @Query("UPDATE books SET issued = issued + 1 WHERE id = :bookId AND issued < copies")
    suspend fun issue(bookId: Long): Int

    @Delete
    suspend fun remove(book: Book)
}

:text and :bookId are bind parameters: Room passes the function arguments safely, so a title containing quotes can never break the query (this is what protects you from SQL injection). @Upsert inserts a new row or updates the existing one with the same key. An UPDATE query can return Int, the number of rows changed; here 0 means "no copy left to issue". AuthorCount is a plain data class: Room maps any query result whose column names match its properties.

3. Create the database class and make sure the app opens only one instance, because opening a database is slow and two instances can clash.

@Database(entities = [Book::class], version = 2, exportSchema = false)
abstract class LibraryDatabase : RoomDatabase() {
    abstract fun bookDao(): BookDao

    companion object {
        @Volatile
        private var instance: LibraryDatabase? = null

        fun get(context: Context): LibraryDatabase =
            instance ?: synchronized(this) {
                instance ?: Room.databaseBuilder(
                    context.applicationContext,
                    LibraryDatabase::class.java,
                    "library.db",
                )
                    .addMigrations(MIGRATION_1_2)
                    .build()
                    .also { instance = it }
            }
    }
}

@Volatile and synchronized make sure two threads asking at the same moment still get the same database. In a real app, a dependency injection library usually creates this single instance for you (see Dependency Injection with Hilt & Koin).

4. Wrap the DAO in a repository, so ViewModels depend on your own API and not on Room directly.

class LibraryRepository(private val dao: BookDao) {
    val available: Flow<List<Book>> = dao.observeAvailable()

    suspend fun issue(book: Book): Boolean = dao.issue(book.id) == 1

    suspend fun addCopies(book: Book, extra: Int) = dao.save(book.copy(copies = book.copies + extra))
}

A ViewModel can turn repository.available into screen state with stateIn (see SharedFlow, stateIn & Advanced Flow Operators). When the librarian issues the last copy of "Wings of Fire", the UPDATE changes the table, the Flow emits a new list without that book, and the Compose list updates by itself. You never write "refresh the list" code.

Migrations: changing the schema safely

Version 1 of the app had no shelf column. Users already have version 1 databases full of books, so you cannot just change the class: Room would find a table that does not match and stop with an error. Instead, raise version in @Database and give Room a migration that changes old databases to the new shape.

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE books ADD COLUMN shelf TEXT NOT NULL DEFAULT 'General'")
    }
}

When an old user updates the app, Room sees version 1 on disk, runs the migration, and the existing books get the shelf "General". Room can also generate simple migrations for you with autoMigrations, but that needs exportSchema = true and a schema folder configured in Gradle, so that Room can compare old and new schemas.

Testing the DAO

DAO tests are instrumented tests in src/androidTest, because SQLite comes from Android. Build a database in memory, so each test starts empty and nothing is written to disk:

@RunWith(AndroidJUnit4::class)
class BookDaoTest {
    private lateinit var db: LibraryDatabase
    private lateinit var dao: BookDao

    @Before
    fun createDb() {
        db = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            LibraryDatabase::class.java,
        ).build()
        dao = db.bookDao()
    }

    @After
    fun closeDb() = db.close()

    @Test
    fun issuingTheLastCopyHidesTheBook() = runTest {
        dao.save(Book(isbn = "978-01", title = "Wings of Fire", author = "A. P. J. Abdul Kalam", copies = 1))
        val book = dao.observeAvailable().first().single()

        assertEquals(1, dao.issue(book.id))
        assertEquals(0, dao.issue(book.id))
        assertEquals(emptyList<Book>(), dao.observeAvailable().first())
    }
}

first() takes the current value of an observable query. The second issue returns 0 because no copy is left, and the book disappears from the available list. ViewModels are tested with a fake repository instead, as in Testing Kotlin.

Common mistakes

• Building a new database for every screen. Create one instance for the whole app.
• Changing an entity without raising version and adding a migration. Users who update the app will crash on start.
• Joining strings into SQL. Always use :parameters.
• Calling .first() on an observable query to "refresh". Collect the Flow once; it updates by itself.
• Storing money as Double. Store paise in an Int or Long column.

Key points

• Room maps @Entity classes to tables, @Dao interfaces to queries and a @Database class to the SQLite file.
• SQL in @Query is checked at compile time; the code is generated by KSP.
• DAO functions returning Flow re-emit when the table changes; suspend functions are main-safe one-off operations.
• Keep a single database instance and put a repository between Room and your ViewModels.
• Changing the schema means raising version and adding a Migration.
• Test DAOs with Room.inMemoryDatabaseBuilder in src/androidTest.

// In Book, add a property:
//     val language: String = "English",

@Dao
interface LibraryReportDao {
    @Query("SELECT COALESCE(SUM(issued), 0) FROM books")
    fun observeIssuedCount(): Flow<Int>

    @Query("SELECT * FROM books WHERE shelf = :shelf ORDER BY title")
    fun onShelf(shelf: String): Flow<List<Book>>
}

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE books ADD COLUMN language TEXT NOT NULL DEFAULT 'English'")
    }
}