We often encounter projects where SQLite is used as a simple key-value heap. After six months, such code turns into a nightmare of raw queries, SQLiteDatabaseLockedException on Android, and crashes during migrations. Without proper setup, the database becomes a bottleneck: 9 out of 10 reviews about slow app performance are linked to unoptimized queries. On one project, we reduced list loading time from 8 seconds to 0.3 seconds — simply by adding an index and enabling WAL. Our team with 10+ years of experience in mobile development configures SQLite so the database runs stably and scales without pain.
SQLite is embedded in iOS and Android at the OS level. The question is not whether to integrate it — it's already there. The question is how to work with it so that six months later you don't have to rewrite everything from scratch due to tangled raw queries or crashes with SQLiteDatabaseLockedException. Proper architecture with ORM and WAL mode eliminates 80% of typical problems.
How to Choose an ORM for Your Project?
Working with SQLite directly via android.database.sqlite.SQLiteDatabase or sqlite3 on iOS is an option for minimal scenarios. In real projects, ORMs are used:
| Platform | Library | Approach | Performance |
|---|---|---|---|
| Android | Room (Jetpack) | annotations + DAO | High, with WAL up to 5000 writes/s |
| iOS | GRDB.swift | typesafe Swift queries | Comparable to raw SQL |
| Flutter | sqflite + drift | codegen + reactive | Medium, but convenient |
| React Native | react-native-sqlite-storage / op-sqlite | raw SQL or TypeORM | Depends on wrapper |
| Multiplatform | SQLDelight | shared SQL schema | High, generates native code |
Room is the standard for Android, backed by Google. GRDB.swift on iOS provides type-safe queries without unnecessary magic. SQLDelight is interesting for KMM projects: one .sq file with SQL generates Kotlin and Swift code.
Room on Android: Proper 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 you needed @Insert(onConflict = OnConflictStrategy.REPLACE). Soft delete via the is_deleted flag is standard practice for syncing databases, so a record is not lost until deletion is confirmed by the server.
Migrations — The Most Painful Point
Room checks exportedSchema when the schema changes. If fallbackToDestructiveMigration() is set, the database is recreated on every schema change. This is fine for debug but 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 the schema to JSON (room.schemaLocation in build.gradle) and store it in git. During code review, schema changes are immediately visible. Room can automatically generate a migration via AutoMigration for simple cases (adding a column), but renaming tables and columns requires @RenameTable/@RenameColumn annotations. A proper migration strategy saves up to 30% of maintenance time.
GRDB.swift on iOS
// Opening and configuration 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 is analogous to Room's Flow: it automatically restarts the query when the affected tables change.
How WAL Mode Improves Performance?
By default, SQLite works in journal mode. For mobile apps, WAL (Write-Ahead Logging) is better: readers do not block writers. Room enables WAL automatically. In GRDB: dbQueue.configuration.journalMode = .wal. Tests show that WAL reduces write latency by 3 times on Android and by 5 times on iOS. This is especially noticeable with frequent inserts — for example, when loading offline data. For more details about WAL, see SQLite WAL.
Which Indexes to Create for Query Acceleration?
Indexes on fields in WHERE and ORDER BY reduce scan time by 10–100 times. Without them, SQLite does a full table scan. For an orders table with 100,000 rows, a query by date without an index takes 2 seconds; with an index, 20 milliseconds. Create unique indexes on SKU, email — they guarantee integrity and speed up searches. Optimal balance: no more than 5 indexes per table; each index slows writes by 10-20%.
Typical Errors and How to Avoid Them
Common SQLite problems in mobile apps
A typical problem is N+1 queries in RecyclerView. SELECT * FROM orders returns 200 rows, then for each SELECT * FROM order_items WHERE order_id = ?. 200 queries on the UI thread — ANR within 5 seconds on a real device. Solution: JOIN or a separate batch query WHERE order_id IN (...). We always check these cases during code review. Another common mistake is storing images in BLOB: it's better to save file paths. Ignoring WAL leads to SQLiteDatabaseLockedException in multithreaded access.
What's Included in the Work
- Designing the database schema based on business logic
- Selecting and configuring the ORM (Room, GRDB, SQLDelight) for iOS/Android/KMM
- Writing migrations with schema storage in Git
- Enabling WAL mode and optimizing indexes
- Documentation on working with the database and migration instructions
- Code review and testing on real devices
Timelines and Cost
SQLite setup with Room or GRDB, migration strategy, indexes: from 1 week per platform. Cost is calculated individually — we evaluate the project in 1 day. Implement a stable local database — contact us for a consultation. Over 10+ years we have completed more than 50 projects with local data storage — average budget savings on rework is 30% with proper initial setup. Get a consultation for your project today.