SQLite database setup in mobile app

SQLite Database Setup in Mobile Applications

SQLite built into iOS and Android at OS level. Question not whether to connect it — it's already there. Question is how to work with it so in six months you don't rewrite everything due to tangled raw queries or crashes from SQLiteDatabaseLockedException on Android.

ORM/Abstraction Choice

Working with SQLite directly via android.database.sqlite.SQLiteDatabase or sqlite3 on iOS is option for minimal scenarios. In real projects use ORM:

Room is Android standard, Google votes for it. GRDB.swift on iOS gives typesafe queries without extra magic. SQLDelight interesting for KMM projects: single .sq file with SQL, generates Kotlin and Swift code.

Room on Android: Right Architecture

@Entity(tableName = "products",
    indices = [Index(value = ["category_id"]), Index(value = ["sku"], unique = true)]
)
data class ProductEntity(
    @PrimaryKey val id: String,
    @ColumnInfo(name = "category_id") val categoryId: String,
    val sku: String,
    val title: String,
    @ColumnInfo(name = "price_cents") val priceCents: Int,
    @ColumnInfo(name = "updated_at") val updatedAt: Long,
    @ColumnInfo(name = "is_deleted") val isDeleted: Boolean = false
)

@Dao
interface ProductDao {
    @Query("SELECT * FROM products WHERE category_id = :categoryId AND is_deleted = 0 ORDER BY title ASC")
    fun observeByCategory(categoryId: String): Flow<List<ProductEntity>>

    @Upsert
    suspend fun upsert(products: List<ProductEntity>)

    @Query("UPDATE products SET is_deleted = 1, updated_at = :timestamp WHERE id = :id")
    suspend fun softDelete(id: String, timestamp: Long)
}

@Upsert appeared in Room 2.5 — before that needed @Insert(onConflict = OnConflictStrategy.REPLACE). Soft delete via is_deleted flag — standard practice for synced DBs to not lose record before server confirmation.

Migrations — Most Painful Place

Room checks exportedSchema on schema change. If fallbackToDestructiveMigration() — DB recreates on every schema change. Fine for debug, unacceptable for production.

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

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE products ADD COLUMN tags TEXT NOT NULL DEFAULT ''")
        db.execSQL("CREATE INDEX IF NOT EXISTS index_products_updated_at ON products(updated_at)")
    }
}

Export schema to JSON (room.schemaLocation in build.gradle) and commit to git. Code review instantly shows schema changes. Room can auto-generate migration for simple cases (add column) via AutoMigration, but table/column rename needs @RenameTable/@RenameColumn annotations.

GRDB.swift on iOS

// Open and configure
let dbQueue = try DatabaseQueue(path: dbPath)

try dbQueue.write { db in
    try db.create(table: "products", ifNotExists: true) { t in
        t.primaryKey("id", .text)
        t.column("category_id", .text).notNull().indexed()
        t.column("sku", .text).unique()
        t.column("title", .text).notNull()
        t.column("price_cents", .integer).notNull()
        t.column("updated_at", .integer).notNull()
    }
}

// Reactive observation via ValueObservation
let observation = ValueObservation.tracking { db in
    try Product.filter(Column("categoryId") == categoryId).fetchAll(db)
}
let cancellable = observation.start(in: dbQueue,
    onError: { error in print(error) },
    onChange: { products in self.updateUI(products) }
)

ValueObservation — like Room's Flow: auto-reruns query on affected table changes.

WAL Mode and Performance

SQLite runs in journal mode by default. For mobile apps, WAL (Write-Ahead Logging) better: readers don't block writers. Room enables WAL automatically. In GRDB: dbQueue.configuration.journalMode = .wal.

Typical problem — N+1 query in RecyclerView. SELECT * FROM orders returns 200 rows, then for each SELECT * FROM order_items WHERE order_id = ?. 200 queries on UI thread — ANR in 5 seconds on real device. Solution: JOIN or separate batch query WHERE order_id IN (...).

Setup SQLite with Room or GRDB, migration strategy, indexes: 1 week on one platform. Cost estimated individually.