Room Database: Offline-First Android Apps

Open your app in a metro tunnel with no signal. If it shows a blank screen or a spinner that never stops, you have built a demo, not an app. Real users lose connectivity — in lifts, on trains, in campus basements — and an app that cannot function offline feels broken even when the network is fine most of the time. Building an offline-first Android app means the local database is the source of truth, and the network is just a way to refresh it.

Room is Google's official persistence library over SQLite, and it is the standard answer for offline-first Android apps. This guide builds the full picture: entities, DAOs, migrations, conflict handling, reactive queries, and the sync strategy you run when the network returns — with the exact error messages you will hit and what to do about each.

The offline-first rule: every screen reads from the local database first. The network updates the database; the UI never waits for the network.

What offline-first actually means

An offline-first app follows three rules:

Reads come from local storage. The UI observes the Room database. Whether the network is up or down, the screen shows something immediately.
Writes go to local storage first. A new note, a new attendance record, a toggled reminder — saved locally the instant the user taps, marked for upload.
Sync reconciles later. When connectivity returns, a background worker pushes pending changes and pulls server updates, resolving conflicts by a defined policy.

This is the architecture behind apps like an offline note-taking app with a sync dashboard, where notes created in airplane mode appear in the server dashboard later without the user thinking about it.

The alternative — hitting the network on every screen and caching nothing — fails the moment connectivity dips. SharedPreferences can store settings but not structured data. Files can store blobs but not queries. SQLite directly works but leaves you writing hundreds of lines of Cursor boilerplate and string SQL with zero compile-time checking. Room sits in the middle: you write annotated Kotlin, and it generates the SQLite plumbing at compile time, verifying your SQL before the app ever runs.

The three pieces: Entity, DAO, Database

Room has exactly three building blocks. Learn them in this order and everything else is detail.

Entity — a Kotlin data class annotated with @Entity that becomes one SQLite table. Each property becomes a column.

DAO (Data Access Object) — an interface or abstract class annotated with @Dao where you declare how to read and write. Room generates the implementation.

Database — an abstract class extending RoomDatabase, annotated with @Database, listing your entities and the schema version.

Here is a minimal notes feature, the kind an offline note-taking app needs:

@Entity(tableName = "notes")
data class Note(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val title: String,
    val body: String,
    val updatedAt: Long,
    val isSynced: Boolean = false
)

@Dao
interface NoteDao {
    @Query("SELECT * FROM notes ORDER BY updatedAt DESC")
    fun getAllNotes(): Flow<List<Note>>

    @Query("SELECT * FROM notes WHERE id = :noteId")
    suspend fun getNoteById(noteId: Long): Note?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(note: Note): Long

    @Update
    suspend fun update(note: Note)

    @Delete
    suspend fun delete(note: Note)

    @Query("SELECT * FROM notes WHERE isSynced = 0")
    suspend fun getUnsynced(): List<Note>
}

@Database(entities = [Note::class], version = 1, exportSchema = true)
abstract class AppDatabase : RoomDatabase() {
    abstract fun noteDao(): NoteDao
}

And the singleton that builds it once for the whole app:

@Volatile
private var INSTANCE: AppDatabase? = null

fun getDatabase(context: Context): AppDatabase {
    return INSTANCE ?: synchronized(this) {
        val instance = Room.databaseBuilder(
            context.applicationContext,
            AppDatabase::class.java,
            "app_database"
        ).build()
        INSTANCE = instance
        instance
    }
}

Three decisions in this snippet deserve attention:

exportSchema = true writes each version's schema to a JSON file in your project. Keep it on from day one — migrations are written by comparing these schema files, and regenerating history later is painful.
The singleton exists because opening multiple database connections invites "database is locked" errors and wasted memory. One instance per process, built with the application context (never an Activity context, which leaks).
isSynced is the cheapest possible sync primitive: every write sets it false, the sync worker uploads rows where it is false and flips them true. Crude but honest, and adequate for a single-user final-year app.

Reactive UI with Flow: the screen updates itself

The signature fun getAllNotes(): Flow<List<Note>> is the most important line in the DAO. Room observes the tables your query touches and re-emits the result whenever they change. Insert a note, and every collector of that Flow receives the new list — no manual refresh, no callbacks.

In the ViewModel you expose it as state the UI collects:

class NotesViewModel(private val dao: NoteDao) : ViewModel() {
    val notes: StateFlow<List<Note>> = dao.getAllNotes()
        .stateIn(
            scope = viewModelScope,
            started = SharingStarted.WhileSubscribed(5000),
            initialValue = emptyList()
        )

    fun addNote(title: String, body: String) {
        viewModelScope.launch {
            val note = Note(
                title = title,
                body = body,
                updatedAt = System.currentTimeMillis(),
                isSynced = false
            )
            dao.insert(note)
        }
    }
}

Because the write goes to Room first, the screen updates instantly — even in airplane mode. The isSynced = false flag means the sync layer will pick it up later. This two-step pattern (write locally, sync later) is the entire offline-first trick; everything else is refinement.

A local train timetable app is a good mental model for reads: the timetable is bundled or downloaded once, stored in Room, and every search query runs against the local database in milliseconds — no network involved at all.

Migrations: the crash every student hits

Release version 1, then add a reminder-time column to your notes and raise the schema version to 2. Launch the app, and Room stops you cold:

java.lang.IllegalStateException: Room cannot verify the data integrity.
Looks like you've changed schema but forgot to update the version number.

Or its sibling:

A migration from 1 to 2 was required but not found.
Please provide the necessary Migration path via
RoomDatabase.Builder.addMigration(...)

Room refuses to silently destroy user data, which is the correct behaviour. When the schema changes, you supply a Migration describing the SQL delta:

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE notes ADD COLUMN reminderTime INTEGER NOT NULL DEFAULT 0")
    }
}

// in the builder:
.addMigrations(MIGRATION_1_2)

The migration decision table:

Situation	What to do
Development only, no users yet, schema changed	Uninstall and reinstall the app (or `.fallbackToDestructiveMigration()` temporarily — never in a release build)
Released app, added a column	Write a `Migration` with `ALTER TABLE ... ADD COLUMN` and a sensible default
Released app, renamed a column	SQLite has no rename-column on older versions: create new table, copy data, drop old, rename. Test this one twice.
Released app, removed a table	`Migration` with `DROP TABLE`, plus remove the entity from the `@Database` list
Multiple versions skipped (user jumps 1 → 4)	Provide the full chain 1→2, 2→3, 3→4 — Room walks it automatically

Two non-negotiable practices:

Test migrations. Room ships a room-testing artifact with MigrationTestHelper that runs your migration against a real database file and validates the schema. Write one test per migration; the five minutes it takes catches the errors that corrupt user data.
Never use fallbackToDestructiveMigration() in a shipped app. It drops every table and recreates them empty. During development it is convenient; in a release it deletes your users' data on update.

TypeConverters: storing what SQLite cannot

SQLite columns hold integers, reals, text, and blobs. Your Kotlin model will want Date, List<String>, enums. TypeConverter bridges the gap:

class Converters {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? = value?.let { Date(it) }

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? = date?.time

    @TypeConverter
    fun fromStringList(value: String?): List<String> =
        value?.split(",")?.filter { it.isNotEmpty() } ?: emptyList()

    @TypeConverter
    fun stringListToString(list: List<String>?): String =
        list?.joinToString(",") ?: ""
}

Register on the database class with @TypeConverters(Converters::class). A flashcard learning app with spaced repetition is a natural fit: each card stores a list of review timestamps and an interval enum, converted to columns Room can persist.

Warning: converters run on every read and write of the column. Keep them cheap — string splitting is fine, JSON serialisation of large objects on a hot query path is not.

Relations without foreign-key pain

A common student design: decks containing flashcards. In Room you model the tables separately and declare the relationship for reads:

@Entity(tableName = "decks")
data class Deck(
    @PrimaryKey(autoGenerate = true) val deckId: Long = 0,
    val name: String
)

@Entity(
    tableName = "cards",
    foreignKeys = [ForeignKey(
        entity = Deck::class,
        parentColumns = ["deckId"],
        childColumns = ["deckId"],
        onDelete = ForeignKey.CASCADE
    )],
    indices = [Index("deckId")]
)
data class Card(
    @PrimaryKey(autoGenerate = true) val cardId: Long = 0,
    val deckId: Long,
    val front: String,
    val back: String,
    val nextReviewAt: Long
)

data class DeckWithCards(
    @Embedded val deck: Deck,
    @Relation(parentColumn = "deckId", entityColumn = "deckId")
    val cards: List<Card>
)

@Transaction
@Query("SELECT * FROM decks")
fun getDecksWithCards(): Flow<List<DeckWithCards>>

Notes on this pattern:

@Transaction is required on relation queries because Room runs one query per relationship level; without the transaction annotation, another thread could modify data between the two queries and return an inconsistent join.
onDelete = CASCADE means deleting a deck deletes its cards — decide this deliberately, because the alternative is orphaned rows.
Always index foreign-key columns (indices = [Index("deckId")]). Unindexed foreign keys make every parent delete/update scan the child table. Room does not do this for you.

Conflict handling: last write wins is a policy, not an accident

@Insert(onConflict = ...) takes four strategies, and the choice is a product decision:

Strategy	Behaviour	When to use it
`ABORT` (default)	The insert fails on primary-key conflict	You want duplicates to surface as errors during development
`REPLACE`	Deletes the conflicting row and inserts the new one	Server data overwrites local on sync — "server wins"
`IGNORE`	Skips conflicting rows silently	Bulk imports where duplicates are expected and harmless
`FAIL`	Aborts the current statement	Rarely what you want; prefer ABORT

For offline-first sync, think in terms of a resolution policy rather than a single annotation:

Server wins: sync writes server rows with REPLACE. Simple, predictable, and right for reference data like timetables.
Client wins: pending local edits upload first, and server rows only fill gaps. Right for user-created content like notes — the user edited it most recently, so their version is newest.
Merge: field-level comparison using updatedAt timestamps. Right when two devices edit the same record. The updatedAt column in our Note entity exists for exactly this.

A robust student implementation: keep updatedAt on every synced entity, upload rows where isSynced = false, download server changes since the last sync timestamp, and resolve each conflict by comparing updatedAt. Document the policy in your report — evaluators ask about conflict handling precisely because most student apps have none.

The sync layer: WorkManager, not a button

Syncing on a "Sync now" button works in a demo and fails in real life. Use WorkManager with a network constraint so the OS runs sync when connectivity exists and the device can afford it:

val syncWork = OneTimeWorkRequestBuilder<SyncWorker>()
    .setConstraints(
        Constraints.Builder()
            .setRequiredNetworkType(NetworkType.CONNECTED)
            .build()
    )
    .setBackoffCriteria(
        BackoffPolicy.EXPONENTIAL,
        30, TimeUnit.SECONDS
    )
    .build()

WorkManager.getInstance(context)
    .enqueueUniqueWork("sync", ExistingWorkPolicy.APPEND_OR_REPLACE, syncWork)

Inside SyncWorker.doWork():

Read rows where isSynced = false from Room.
POST them to the server using Retrofit — an HTTP client that turns your REST API into a Kotlin interface.
On success, mark them synced in a @Transaction — the flag flip and any server-returned IDs update atomically, or not at all.
GET server changes since the stored lastSyncAt, upsert with your conflict policy, update lastSyncAt.
Return Result.retry() on network failure so WorkManager backs off and tries again — never crash the worker on a dropped connection.

Do not sync on the main thread, do not sync in onResume, and do not poll on a timer. The OS will throttle you, the battery will suffer, and a process death mid-sync leaves half-written state. WorkManager exists for exactly this job.

Pre-populating the database: ship data with the app

A train timetable app should not download 50,000 timetable rows on first launch over a user's mobile data. Room can pre-populate from a database file bundled in your APK:

// 1. Create the SQLite file once (a small desktop script or a one-off
//    Android run), place it at app/src/main/assets/database/timetable.db
// 2. Tell Room to copy it on first launch:
Room.databaseBuilder(context, AppDatabase::class.java, "app_database")
    .createFromAsset("database/timetable.db")
    .build()

Rules for this to work: the bundled file's schema must match version 1 of your entities exactly (same table and column names — generate it from the same entity classes to be safe), and migrations still apply on top of it when you bump the version. For data that changes occasionally (timetables update twice a year), combine createFromAsset with a versioned download: the asset covers first launch, a WorkManager job refreshes rows whose sourceVersion is older than the server's.

Size check: a 50,000-row timetable with a few text columns is typically 3–8 MB as SQLite — acceptable inside an APK. A 500 MB dataset is not; that belongs on the server with paged downloads.

Debugging with the Database Inspector

Android Studio's Database Inspector (View → Tool Windows → App Inspection, run the app on API 26+) lets you browse Room tables live, run ad-hoc SQL, and — critically — verify your migrations actually produced the schema you intended. The workflow when a query misbehaves:

Run the query in the inspector first, against real data. If it returns wrong rows there, the bug is in your SQL, not your Kotlin.
Prefix with EXPLAIN QUERY PLAN and confirm indexed access (USING INDEX) rather than full scans (SCAN TABLE).
Check the schema tab after a migration: the column you added should be there, with the default you specified. If it is missing, your Migration object never ran — usually because it was not registered with .addMigrations().

Students who learn the inspector stop guessing about database bugs entirely. It is the single highest-leverage debugging tool for this whole guide.

The error-message field guide

These are the Room errors you will actually see, in the order students usually meet them:

Error	Cause	Fix
`Cannot access database on the main thread`	DAO called from the UI thread without `suspend`/background dispatcher	Make the DAO method `suspend` or return `Flow`/`LiveData`; call from `viewModelScope`
`Room cannot verify the data integrity...`	Schema changed without version bump or migration	Add the version bump and a `Migration`, or reinstall during development
`A migration from X to Y was required but not found`	Version bumped, no migration supplied	Write the `Migration` object and register it with `.addMigrations()`
`Cannot find implementation for NoteDao`	DAO is not abstract/interface properly, or the `@Database` class does not list the entity	Check annotations; ensure the entity is in `entities = [...]`
`Not sure how to convert a Cursor to this method's return type`	Query return type Room cannot map (e.g. raw `Cursor` in Kotlin, or a POJO missing `@Embedded`)	Return entity types, primitives, or properly annotated POJOs
`Schema export directory is not provided`	`exportSchema = true` without the annotation-processor argument	Add the `room.schemaLocation` compiler argument in Gradle, or set `exportSchema = false` during early development
`UNIQUE constraint failed`	Insert with `ABORT` on a duplicate primary key	Decide the conflict policy deliberately — `REPLACE` for server-wins, `IGNORE` for idempotent imports

Performance: Room is fast until your queries are not

SQLite on a phone handles tens of thousands of rows without effort, but students still manage to make it slow:

Index what you query by. Any column in a WHERE, ORDER BY, or JOIN clause deserves an index. Unindexed, a 50,000-row timetable scan on every keystroke is visibly laggy; indexed, it is instant.
Page large lists. Returning 10,000 rows into a RecyclerView allocates 10,000 objects. Room integrates with Paging 3 (PagingSource) so the list loads in chunks. Any screen that can grow unbounded needs paging.
Watch the query plan. Prefix a slow query with EXPLAIN QUERY PLAN in the Database Inspector (Android Studio → App Inspection) and check it says USING INDEX rather than SCAN TABLE.
Batch writes in transactions. One hundred individual inserts take roughly a hundred times longer than one hundred inserts in a single @Transaction, because each commits separately.
Keep entities lean. A SELECT * on a table with a large blob column (say, stored images) drags the blob through every query. Split heavy columns into a separate table, or store images as files and keep only paths in Room.

Pre-submission checklist

Every screen works with WiFi and mobile data turned off — the airplane-mode test passes on all primary flows.
Writes save locally first and are flagged for sync (isSynced-style flag or equivalent queue).
A Migration exists for every schema version bump since the first installable build; migrations are covered by MigrationTestHelper tests.
exportSchema = true with schema JSON files committed, so the migration history is reviewable.
DAO methods are suspend or return Flow/LiveData — no main-thread database access anywhere.
Conflict policy is chosen and documented (server wins / client wins / timestamp merge), not left as the default ABORT.
Sync runs via WorkManager with a network constraint and retry backoff — no manual sync buttons as the only path, no polling timers.
Foreign-key columns are indexed; large lists use Paging 3; writes are batched in transactions.
The Database Inspector shows no SCAN TABLE on hot query paths.

Where this fits your project

If your final-year app stores anything the user creates or anything downloaded from a server, Room is not optional polish — it is the difference between an app and a network client wearing an app costume. An offline note-taking app with sync demonstrates the full loop (local write → flag → WorkManager sync → conflict resolution); a train timetable app shows read-heavy offline design with indexed queries; a flashcard app with spaced repetition exercises relations, converters, and date arithmetic. Structure the whole thing in an MVVM layout so your Room layer sits behind a repository, and pair it with Retrofit on the network side. More Android project ideas live in the Android branch hub.

Room Database: Build Offline-First Android Apps That Work Without Internet