Modern Android Architecture: Room Database vs SQLite with Kotlin Coroutines & Flow

1. The Pitfalls of Raw SQLite in Legacy Android

Historically, Android developers stored relational data using SQLiteOpenHelper. While functional, raw SQLite suffered from glaring architectural vulnerabilities:

• Zero Compile-Time SQL Verification: If you made a typo in a table or column name (e.g., SELEC * FORM users), the error only manifested at runtime when a user opened the screen, causing immediate app crashes.
• Cumbersome Cursor Parsing: Developers had to manually parse rows column-by-column: cursor.getString(cursor.getColumnIndexOrThrow("name")). This produced hundreds of lines of fragile boilerplate.
• No Reactive Observation: When data in the database changed, there was no native mechanism to notify the UI without manually dispatching custom broadcast intents.

2. How Jetpack Room Revolutionized Persistence

Room is an abstraction layer over SQLite that provides compile-time verification of SQL queries, automatic object-relational mapping (ORM), and first-class integration with Kotlin Coroutines and Flow.

[Entity Data Class] ──(Defines Table Schema)──> [Room Database]
         │                                              ▲
         ▼                                              │
[DAO (Data Access Object)] ──(Compiles SQL Queries)──────┘

3. Building a Production Room Implementation

1. Defining the Entity

import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "app_projects")
data class ProjectEntity(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val title: String,
    val clientName: String,
    val status: String,
    val budgetAmount: Double,
    val updatedAt: Long = System.currentTimeMillis()
)

2. Crafting the Reactive DAO with Kotlin Flow

import androidx.room.*
import kotlinx.coroutines.flow.Flow

@Dao
interface ProjectDao {

    @Query("SELECT * FROM app_projects ORDER BY updatedAt DESC")
    fun getAllProjectsFlow(): Flow<List<ProjectEntity>>

    @Query("SELECT * FROM app_projects WHERE id = :projectId LIMIT 1")
    suspend fun getProjectById(projectId: Long): ProjectEntity?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertProject(project: ProjectEntity): Long

    @Delete
    suspend fun deleteProject(project: ProjectEntity)
}

3. Defining the Database Singleton

import android.content.Context
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase

@Database(entities = [ProjectEntity::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {

    abstract fun projectDao(): ProjectDao

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

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

4. Connecting Room with Jetpack Compose & ViewModel

Because the DAO returns a reactive Flow<List<ProjectEntity>>, your ViewModel can expose it directly as state to modern Jetpack Compose layouts:

import androidx.lifecycle.ViewModel
import androidx.lifecycle.viewModelScope
import kotlinx.coroutines.flow.SharingStarted
import kotlinx.coroutines.flow.stateIn
import kotlinx.coroutines.launch

class ProjectsViewModel(private val repository: ProjectDao) : ViewModel() {

    // Converts cold Flow into a hot StateFlow with a 5-second stop timeout
    val projectsList = repository.getAllProjectsFlow()
        .stateIn(
            scope = viewModelScope,
            started = SharingStarted.WhileSubscribed(5000),
            initialValue = emptyList()
        )

    fun addNewProject(title: String, client: String, budget: Double) {
        viewModelScope.launch {
            repository.insertProject(
                ProjectEntity(
                    title = title,
                    clientName = client,
                    status = "In Progress",
                    budgetAmount = budget
                )
            )
        }
    }
}

5. Automated Room Migrations Without Data Loss

When upgrading database schemas in production apps, never use fallbackToDestructiveMigration()—doing so destroys your users' existing data! Instead, write explicit Migration objects:

import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Add new column to existing table
        db.execSQL("ALTER TABLE app_projects ADD COLUMN isArchived INTEGER NOT NULL DEFAULT 0")
    }
}