SQLite Setup: Migrations, Indexes, WAL for Mobile Apps

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:

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

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.

How to Choose a Local Data Storage Solution (Room, Core Data, Realm, Isar)?

We've all seen the scenario: the app loses data when the network drops — and it's not just a bug, it's a failure of the use case. The user fills out a form, taps "Submit", gets a timeout, and loses everything. Or worse: data gets sent twice due to incorrect retry logic. A properly chosen and configured storage layer solves this problem once and for all. The wrong choice can cost teams months of rewriting code and up to 70% of time spent on synchronization. Our experience — 10+ years in mobile development, over 50 projects with offline storage — confirms: the storage choice determines 80% of future performance and synchronization issues.

In practice, storage selection is driven by two factors: data type and synchronization requirements, not library popularity.

Room (Android) — a wrapper over SQLite with compile-time verification of SQL queries. If a query is invalid, the build fails — better than a SQLiteException at runtime. Room integrates well with Kotlin Flow and LiveData, making reactive UI updates straightforward. The main challenge is schema migrations. @Database(version = N, exportSchema = true) with migration files in assets/databases/ is mandatory; otherwise, fallbackToDestructiveMigration() will simply delete the user's data on app update.

Core Data (iOS) — not a database, but an object graph management framework over SQLite (or XML, or in-memory). NSPersistentContainer with viewContext for reading on the main thread and newBackgroundContext() for writing is the basic setup. The trouble begins when a developer calls save() on viewContext from a background thread: EXC_BAD_ACCESS at a random moment, happens once a week, with almost nothing useful in the crash log. You must use performAndWait or perform for each context strictly on its own thread. Apple Core Data Programming Guide recommends this approach.

Realm wins where you need speed with large object sets and built-in reactivity through Results + observe(). Realm stores objects directly without ORM mapping, so reads require no deserialization. According to our measurements, Realm processes reads 2–3 times faster than Core Data for volumes over 10,000 objects. On Flutter, the Realm SDK (ex-MongoDB Realm) supports Device Sync — but that's a managed service with separate infrastructure.

Hive and Isar are Flutter-specific solutions. Hive is a key-value store, fast, simple, suitable for settings and caches. Isar is a full document-oriented database with indexes, written in Rust, compiled to native code. For Flutter apps with offline functionality, Isar is now preferred: built-in query builder with type-safe filters, transactions, watchObject/watchQuery for reactivity.

Contact us for a free audit of your current storage and optimization recommendations — this will save you hundreds of development hours and up to 60% of server request traffic.

Why Is Offline Synchronization the Hardest Part?

Local storage itself is not complicated. The complexity lies in synchronizing with the server in the presence of conflicts.

The most common pattern is optimistic updates with rollback. The user edits a record, the UI reflects the change instantly, a background request goes to the server. If the server returns an error, we roll back the local state. Sounds simple. In practice: if the user has left the screen and returned before the rollback (which may take 3 seconds), the UX is broken. You need an explicit operation queue with states (PENDING, SYNCED, FAILED) in a separate table.

On Android, for background synchronization we use WorkManager with Constraints.Builder().setRequiredNetworkType(NetworkType.CONNECTED). Don't forget setInputMerger(ArrayCreatingInputMerger::class) when batching tasks — otherwise, concurrent runs will overwrite data. A typical operation queue implementation:

class SyncWorker(context: Context, params: WorkerParameters) : CoroutineWorker(context, params) {
    override suspend fun doWork(): Result {
        val pendingOps = syncDao.getPendingOperations()
        for (op in pendingOps) {
            try {
                apiClient.send(op.payload)
                syncDao.markSynced(op.id)
            } catch (e: Exception) {
                syncDao.markFailed(op.id, e.message)
                return Result.retry()
            }
        }
        return Result.success()
    }
}

On iOS, the equivalent is BGTaskScheduler with BGProcessingTaskRequest. iOS limitations on background execution time (~30 seconds for refresh tasks) mean that synchronization must be incremental: not "sync everything," but "sync the next N records, save the cursor."

Conflicts in multi-device scenarios are resolved with one of three approaches:

• Last-write-wins based on updated_at (simplest, loses data on concurrent edits)
• Server-wins (client always accepts server version)
• Three-way merge (complex, requires a common ancestor — suitable for documents)

For most B2C apps, last-write-wins with a user-level time vector is sufficient, but for collaborative editing, a CRDTs approach is needed — then look at Automerge or Yjs with mobile bindings.

How We Build the Storage Layer

The repository pattern is not optional — it's mandatory. UserRepository doesn't know where the data comes from: Room, Realm, or network. The ViewModel calls repository.getUser(id), gets a Flow/Stream, and displays data. Caching logic resides inside the repository.

For Flutter, a typical architecture: Isar for persistence, Riverpod for state management, ConnectivityPlus for network status, and a custom SyncService with an operation queue. Riverpod's AsyncNotifier conveniently covers the logic of "show cache, update from network, show new data." Example repository with caching:

class UserRepository {
  final Isar isar;
  final ApiClient api;

  Future<User> getUser(String id) async {
    // try from local storage first
    final cached = await isar.user.where().idEqualTo(id).findFirst();
    if (cached != null) return cached;
    // otherwise from network
    final remote = await api.fetchUser(id);
    // save locally
    await isar.writeTxn(() => isar.user.put(remote));
    return remote;
  }
}

Another important topic is encryption. If the app stores medical data, payment cards, or corporate documents, SQLCipher (Android) and NSFileProtection (iOS) are not optional. Realm supports encryption natively via a 64-byte key that must be stored in Keychain/Keystore, not in SharedPreferences. Skimping on security can lead to data leaks with serious consequences.

What the Work Includes

We guarantee a transparent process and document each stage:

Want to avoid common mistakes when designing storage? Contact us — we'll help design a reliable local storage from scratch or improve an existing one.

Stages of Work

We start with a requirements audit: what data, what volume, is synchronization needed, are conflicts possible. At this stage, it becomes clear whether Core Data or an SQLite-based solution is needed, whether Realm Sync is required or simple REST polling will suffice.

Next, we design the schema with migrations in mind. Schemas change in any project — the question is not "will there be migrations," but "how painful will they be." We export the schema as JSON, store it in the repository, and write tests for each version's migration.

Development includes unit test coverage for the repository layer: network layer mocks, a real in-memory database for query testing. Before release, we profile queries using Android Profiler (Database Inspector tab) or Core Data debug flags (-com.apple.CoreData.SQLDebug 1).

The implementation timeline for a storage layer with basic offline synchronization ranges from 2 to 6 weeks, depending on schema complexity and conflict resolution requirements. Contact us to get a consultation on choosing the optimal stack and migrations.