Your app needs to work offline. Not "gracefully degrade" -- actually work. That means local storage, and that means SQLite.

The constraints on mobile are different from a server. Limited storage. Migrations running on millions of user devices you cannot SSH into. Concurrent access from the main thread, background sync jobs, and push notification handlers all hitting the same database file. Schema design for a phone is its own discipline, and getting it wrong means jank, data loss, or both.

The Short Version on SQLite

Self-contained. Single file on disk. No server process. ~700KB. Ships on every Android and iOS device already. ACID transactions with rollback. Public domain.

You probably know all that. The interesting parts are what happens after you pick it.

Setting Up SQLite on Android with Room

Raw SQLiteOpenHelper was string-concatenated SQL and runtime crashes. Room fixed that. Compile-time SQL verification, automatic row-to-object mapping, built-in Flow support. Dependencies for your module-level build.gradle.kts:

// build.gradle.kts (Module level)dependencies {
 val roomVersion = "2.7.0"implementation("androidx.room:room-runtime:$roomVersion")
 implementation("androidx.room:room-ktx:$roomVersion")
 ksp("androidx.room:room-compiler:$roomVersion")
 // Optional - testingtestImplementation("androidx.room:room-testing:$roomVersion")
}

Entity definition:

import androidx.room.*
// Entity represents a table in the database
@Entity(tableName = "notes")
data classNote(
 @PrimaryKey(autoGenerate = true)
 val id: Long = 0,
 @ColumnInfo(name = "title")
 val title: String,
 @ColumnInfo(name = "content")
 val content: String,
 @ColumnInfo(name = "created_at")
 val createdAt: Long = System.currentTimeMillis(),
 @ColumnInfo(name = "updated_at")
 val updatedAt: Long = System.currentTimeMillis(),
 @ColumnInfo(name = "is_synced")
 val isSynced: Boolean = false
)

The DAO:

@DaointerfaceNoteDao {
 @Query("SELECT * FROM notes ORDER BY updated_at DESC")
 fungetAllNotes(): Flow<List<Note>>
 @Query("SELECT * FROM notes WHERE id = :noteId")
 suspend fungetNoteById(noteId: Long): Note?
 @Query("SELECT * FROM notes WHERE title LIKE '%' || :query || '%' OR content LIKE '%' || :query || '%'")
 funsearchNotes(query: String): Flow<List<Note>>
 @Insert(onConflict = OnConflictStrategy.REPLACE)
 suspend funinsertNote(note: Note): Long
 @Updatesuspend funupdateNote(note: Note)
 @Deletesuspend fundeleteNote(note: Note)
 @Query("SELECT * FROM notes WHERE is_synced = 0")
 suspend fungetUnsyncedNotes(): List<Note>
}

Database class:

@Database(entities = [Note::class], version = 1, exportSchema = true)
abstract classAppDatabase : RoomDatabase() {
 abstract funnoteDao(): NoteDaocompanion object {
 @Volatile
 private var INSTANCE: AppDatabase? = nullfungetDatabase(context: Context): AppDatabase {
 return INSTANCE ?: synchronized(this) {
 val instance = Room.databaseBuilder(
 context.applicationContext,
 AppDatabase::class.java,
 "notes_database"
 )
 .addMigrations(MIGRATION_1_2)
 .build()
 INSTANCE = instance
 instance
 }
 }
 }
}

exportSchema = true generates a JSON schema file for every database version. Set it. Six months from now when a migration breaks on some user's phone running a version you forgot existed, that schema history saves you.

Setting Up SQLite on iOS

Core Data is Apple's official answer here. Object graph management with change tracking, undo support, lazy loading -- a lot of machinery. Sometimes too much. But it is the default path and Apple keeps investing in it, so you should know it even if you end up choosing something lighter.

Core Data with SQLite Backend

Persistence stack:

import CoreData
classPersistenceController {
 static let shared = PersistenceController()
 let container: NSPersistentContainerinit(inMemory: Bool = false) {
 container = NSPersistentContainer(name: "NotesModel")
 if inMemory {
 container.persistentStoreDescriptions.first?.url =
 URL(fileURLWithPath: "/dev/null")
 }
 // Enable WAL mode for better performancelet description = container.persistentStoreDescriptions.first
 description?.setOption(
 ["journal_mode": "WAL"] asNSDictionary,
 forKey: NSPersistentStoreFileProtectionKey
 )
 container.loadPersistentStores { description, error inif let error = error {
 fatalError("Core Data store failed: \(error)")
 }
 }
 container.viewContext.automaticallyMergesChangesFromParent = true
 container.viewContext.mergePolicy = NSMergeByPropertyObjectTrumpMergePolicy
 }
}

Direct SQLite with GRDB.swift

GRDB.swift is SQLite without the object graph overhead. No relationship management, no undo support. Just tables and queries with a Swift-native API:

import GRDB
// Define your record typestructNote: Codable, FetchableRecord, PersistableRecord {
 var id: Int64?
 var title: Stringvar content: Stringvar createdAt: Datevar updatedAt: Datevar isSynced: Boolmutating funcdidInsert(_ inserted: InsertionSuccess) {
 id = inserted.rowID
 }
}
// Set up the databasefuncopenDatabase(atPath path: String) throws ->DatabasePool {
 let dbPool = tryDatabasePool(path: path)
 try dbPool.write { db intry db.create(table: "note", ifNotExists: true) { t in
 t.autoIncrementedPrimaryKey("id")
 t.column("title", .text).notNull()
 t.column("content", .text).notNull()
 t.column("createdAt", .datetime).notNull()
 t.column("updatedAt", .datetime).notNull()
 t.column("isSynced", .boolean).notNull().defaults(to: false)
 }
 }
 return dbPool
}

Type-safe queries, straightforward migrations, and it works well with Swift concurrency. For apps where Core Data's object graph is overkill -- which is most apps -- GRDB is the better fit.

Database Schema Design for Mobile

Stop normalizing so aggressively on mobile. Third normal form is for servers with fast disks and no users watching a spinner. On a phone, a JOIN to fetch an author name that never changes is wasted work on every single list scroll.

Design tables around your screens. Author name on the notes table? Yes. Data duplication. Worth it.

-- Normalized approach (server-style)CREATE TABLEauthors (
 id INTEGERPRIMARY KEY,
 name TEXT NOT NULL,
 email TEXT NOT NULL
);
CREATE TABLEnotes (
 id INTEGERPRIMARY KEY,
 title TEXT NOT NULL,
 content TEXT NOT NULL,
 author_id INTEGERREFERENCES authors(id),
 created_at INTEGER NOT NULL
);
-- Denormalized approach (mobile-friendly)CREATE TABLEnotes (
 id INTEGERPRIMARY KEY,
 title TEXT NOT NULL,
 content TEXT NOT NULL,
 author_id INTEGER,
 author_name TEXT,
 created_at INTEGER NOT NULL,
 updated_at INTEGER NOT NULL,
 is_synced INTEGER DEFAULT 0,
 sync_version INTEGER DEFAULT 0
);

But not everything should be flattened. If a piece of data changes often and lives in many records, duplication becomes an update nightmare. Roughly second normal form with targeted denormalization for read-heavy screens. That is the sweet spot.

And add sync metadata columns now. is_synced, sync_version, updated_at, deleted_at for soft deletes. Even if you have no sync feature today. Because adding them later means a migration on every user's device, a data backfill, and testing you did not budget for.

CRUD Operations: Both Platforms

Android CRUD with Room and ViewModel

classNoteRepository(
 private val noteDao: NoteDao
) {
 val allNotes: Flow<List<Note>> = noteDao.getAllNotes()
 suspend funcreateNote(title: String, content: String): Long {
 val note = Note(
 title = title,
 content = content,
 createdAt = System.currentTimeMillis(),
 updatedAt = System.currentTimeMillis()
 )
 return noteDao.insertNote(note)
 }
 suspend funupdateNote(note: Note) {
 noteDao.updateNote(
 note.copy(updatedAt = System.currentTimeMillis(), isSynced = false)
 )
 }
 suspend fundeleteNote(note: Note) {
 noteDao.deleteNote(note)
 }
 funsearchNotes(query: String): Flow<List<Note>> {
 return noteDao.searchNotes(query)
 }
}
classNoteViewModel(
 private val repository: NoteRepository
) : ViewModel() {
 val notes = repository.allNotes
 .stateIn(viewModelScope, SharingStarted.WhileSubscribed(5000), emptyList())
 funcreateNote(title: String, content: String) {
 viewModelScope.launch {
 repository.createNote(title, content)
 }
 }
 fundeleteNote(note: Note) {
 viewModelScope.launch {
 repository.deleteNote(note)
 }
 }
}

iOS CRUD with GRDB

classNoteRepository {
 private let dbPool: DatabasePoolinit(dbPool: DatabasePool) {
 self.dbPool = dbPool
 }
 // CreatefunccreateNote(title: String, content: String) throws ->Note {
 try dbPool.write { db invar note = Note(
 id: nil,
 title: title,
 content: content,
 createdAt: Date(),
 updatedAt: Date(),
 isSynced: false
 )
 try note.insert(db)
 return note
 }
 }
 // Read allfuncfetchAllNotes() throws -> [Note] {
 try dbPool.read { db intryNote
 .order(Column("updatedAt").desc)
 .fetchAll(db)
 }
 }
 // UpdatefuncupdateNote(_ note: inoutNote) throws {
 note.updatedAt = Date()
 note.isSynced = falsetry dbPool.write { db intry note.update(db)
 }
 }
 // DeletefuncdeleteNote(_ note: Note) throws {
 try dbPool.write { db in
 _ = try note.delete(db)
 }
 }
 // SearchfuncsearchNotes(query: String) throws -> [Note] {
 try dbPool.read { db inlet pattern = "%\(query)%"return tryNote
 .filter(Column("title").like(pattern) || Column("content").like(pattern))
 .fetchAll(db)
 }
 }
}

Migrations and Versioning Strategies

This is the hard part. Not conceptually -- adding a column is simple SQL. The hard part is that migrations run on the user's device, on their actual data, across every version of your app they might be upgrading from. You cannot roll back. You cannot SSH in. The migration either works or the app is broken for that user until your next App Store update gets approved, which could be days.

A user on version 1 updates directly to version 5. Can your migration chain handle that? What about the user who somehow still has a database from the beta? These are real scenarios. Room migrations are objects with a start version, end version, and the SQL to execute. Each one must be tested against real-world database files, not just empty schemas:

val MIGRATION_1_2 = object : Migration(1, 2) {
 override funmigrate(db: SupportSQLiteDatabase) {
 // Add a tags column to the notes table
 db.execSQL("ALTER TABLE notes ADD COLUMN tags TEXT DEFAULT ''")
 // Create an index for faster tag-based lookups
 db.execSQL("CREATE INDEX index_notes_tags ON notes(tags)")
 }
}
val MIGRATION_2_3 = object : Migration(2, 3) {
 override funmigrate(db: SupportSQLiteDatabase) {
 // Create a new categories table
 db.execSQL("""
 CREATE TABLE categories (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 color TEXT NOT NULL DEFAULT '#000000'
 )
 """)
 // Add category_id to notes
 db.execSQL("ALTER TABLE notes ADD COLUMN category_id INTEGER")
 }
}
Keep database files from every shipped version of your app. Test every migration against them. Clean-schema tests miss the weird data shapes that real users create.

SQLite's ALTER TABLE is limited. No dropping columns before 3.35.0. No renaming before 3.25.0. No changing types ever. The workaround is the four-step dance: create new table, copy data, drop old, rename. Room has @AutoMigration for simple cases. Write complex ones by hand.

Performance Optimization

SQLite is fast. But "fast" and "60fps scrolling while a background sync writes 200 records" are different things. Two orders of magnitude difference between an unoptimized query and one with proper indexes plus WAL mode. Same data, same device.

Indexing

Column in a WHERE, ORDER BY, or JOIN? It needs an index. But every index slows writes. Index what you query. Leave the rest alone.

-- Good: index columns used in WHERE and ORDER BYCREATE INDEX idx_notes_updated ON notes(updated_at);
CREATE INDEX idx_notes_synced ON notes(is_synced) WHERE is_synced = 0;
-- Composite index for queries that filter AND sortCREATE INDEX idx_notes_category_date ON notes(category_id, updated_at DESC);
-- Use EXPLAIN QUERY PLAN to verify your indexes are being usedEXPLAIN QUERY PLAN SELECT * FROM notes WHERE category_id = 5 ORDER BY updated_at DESC;

That partial index on is_synced -- if 95% of rows are synced, a full index wastes space. The partial index covers only unsynced rows. Tiny and fast.

WAL Mode

Non-negotiable. Default journal mode means readers block writers. WAL mode allows concurrent reads and writes:

-- Enable WAL mode (do this once, it persists)PRAGMA journal_mode=WAL;
-- Also useful pragmas for mobilePRAGMA synchronous=NORMAL; -- Slightly less durable but much fasterPRAGMA cache_size=-2000; -- 2MB page cachePRAGMA temp_store=MEMORY; -- Keep temp tables in memory

Room 2.0+ enables WAL by default on API 16+. You might already have it without knowing.

Batch Operations

100 inserts without a transaction: 100 disk flushes. 100 inserts inside a transaction: one flush. Most people miss this entirely.

// Android Room - batch insert with transaction
@DaointerfaceNoteDao {
 @Insertsuspend funinsertAll(notes: List<Note>)
 @Transactionsuspend funreplaceAllNotes(notes: List<Note>) {
 deleteAll()
 insertAll(notes)
 }
 @Query("DELETE FROM notes")
 suspend fundeleteAll()
}

A sync dumping a few hundred records one by one: seconds of visible hang. Same inserts in a transaction: fraction of a second. Two extra lines of code.

Handling Concurrent Access and Thread Safety

This will crash your app:

// BAD: This will cause "Cannot access database on the main thread" crashfunloadNotes() {
 val notes = database.noteDao().getAllNotesSync() // Blocks main thread!
 adapter.submitList(notes)
}

"Cannot access database on the main thread." Every Android developer hits this exactly once.

SQLite allows multiple concurrent readers but one writer, even in WAL mode. On mobile you have the main thread, background sync, push notification handlers -- all hitting the database. Room handles most of it (suspend functions run off main, Flow queries observe changes), but you still need the right patterns:

// GOOD: Observe changes reactivelyfunobserveNotes() {
 viewModelScope.launch {
 database.noteDao().getAllNotes().collect { notes ->
 _uiState.update { it.copy(notes = notes) }
 }
 }
}

On iOS, DatabasePool over DatabaseQueue when you need concurrent reads. WAL mode, multiple readers, single writer. GRDB's ValueObservation gives you reactive change tracking:

// iOS GRDB - observe database changeslet observation = ValueObservation.tracking { db intryNote
 .order(Column("updatedAt").desc)
 .fetchAll(db)
}
let cancellable = observation.start(in: dbPool) { error inprint("Error: \(error)")
} onChange: { notes in// Update UI with fresh notesself.notes = notes
}

Data Synchronization with Remote Servers

This is one approach. There are others. None of them are simple.

Change-tracking with timestamp-based conflict resolution. Every record has updated_at and is_synced. Modify offline, mark unsynced. Connectivity returns, push then pull.

classSyncManager(
 private val noteDao: NoteDao,
 private val api: NotesApi,
 private val prefs: SharedPreferences
) {
 suspend funsync() {
 // Step 1: Push local changes to serverval unsyncedNotes = noteDao.getUnsyncedNotes()
 if (unsyncedNotes.isNotEmpty()) {
 val response = api.pushNotes(unsyncedNotes)
 if (response.isSuccessful) {
 // Mark pushed notes as synced
 unsyncedNotes.forEach { note ->
 noteDao.updateNote(note.copy(isSynced = true))
 }
 }
 }
 // Step 2: Pull remote changes since last syncval lastSyncTime = prefs.getLong("last_sync", 0)
 val remoteChanges = api.getChangesSince(lastSyncTime)
 // Step 3: Merge remote changes with local data
 remoteChanges.forEach { remoteNote ->val localNote = noteDao.getNoteById(remoteNote.id)
 if (localNote == null) {
 // New remote note, insert it
 noteDao.insertNote(remoteNote.copy(isSynced = true))
 } else if (localNote.isSynced) {
 // No local changes, accept remote version
 noteDao.updateNote(remoteNote.copy(isSynced = true))
 } else {
 // Conflict! Both modified. Last-write-wins strategy:if (remoteNote.updatedAt > localNote.updatedAt) {
 noteDao.updateNote(remoteNote.copy(isSynced = true))
 }
 // Otherwise keep local version, it will push on next sync
 }
 }
 // Step 4: Update last sync timestamp
 prefs.edit().putLong("last_sync", System.currentTimeMillis()).apply()
 }
}

Last-write-wins covers most apps. If you need concurrent edits to the same document, you are looking at operational transformation or CRDTs, and that is a different article entirely.

Encryption with SQLCipher

Health records, financial data, personal messages. If your app stores any of these, encrypt the database at rest. SQLCipher: 256-bit AES on the entire file.

On Android with Room:

// Add dependencyimplementation("net.zetetic:android-database-sqlcipher:4.5.6")
implementation("androidx.sqlite:sqlite-ktx:2.4.0")
// Use SupportFactory when building Room databaseval passphrase = SQLiteDatabase.getBytes("your-secret-key".toCharArray())
val factory = SupportFactory(passphrase)
val db = Room.databaseBuilder(
 context,
 AppDatabase::class.java,
 "encrypted_notes.db"
)
.openHelperFactory(factory)
.build()
Never hardcode the encryption key. Android Keystore or iOS Keychain. If the key is lost, the data is gone -- no backdoor. And storing the key in SharedPreferences or UserDefaults defeats the entire point.

iOS with GRDB:

var config = Configuration()
config.prepareDatabase { db intry db.usePassphrase("your-secret-key")
}
let dbPool = tryDatabasePool(
 path: dbPath,
 configuration: config
)

Testing Database Code

Broken queries surface weeks after release when a specific data shape hits a code path you never tested. Test your database code or pay for it later.

Android: In-Memory Database Tests

classNoteDaoTest {
 private lateinit var database: AppDatabaseprivate lateinit var noteDao: NoteDao
 @Beforefunsetup() {
 database = Room.inMemoryDatabaseBuilder(
 ApplicationProvider.getApplicationContext(),
 AppDatabase::class.java
 )
 .allowMainThreadQueries() // Only for testing!
 .build()
 noteDao = database.noteDao()
 }
 @Afterfunteardown() {
 database.close()
 }
 @TestfuninsertAndRetrieveNote() = runTest {
 val note = Note(title = "Test", content = "Content")
 val id = noteDao.insertNote(note)
 val retrieved = noteDao.getNoteById(id)
 assertNotNull(retrieved)
 assertEquals("Test", retrieved?.title)
 }
 @TestfunsearchFindsMatchingNotes() = runTest {
 noteDao.insertNote(Note(title = "Kotlin Tips", content = "Great stuff"))
 noteDao.insertNote(Note(title = "Swift Guide", content = "Also great"))
 noteDao.insertNote(Note(title = "Recipes", content = "Kotlin curry"))
 val results = noteDao.searchNotes("Kotlin").first()
 assertEquals(2, results.size)
 }
}

Testing Migrations

Room's MigrationTestHelper:

@RunWith(AndroidJUnit4::class)
classMigrationTest {
 @get:Ruleval helper = MigrationTestHelper(
 InstrumentationRegistry.getInstrumentation(),
 AppDatabase::class.java
 )
 @Testfunmigrate1To2() {
 // Create database at version 1
 helper.createDatabase("test-db", 1).apply {
 execSQL("INSERT INTO notes (title, content, created_at, updated_at, is_synced) VALUES ('Old Note', 'Content', 0, 0, 0)")
 close()
 }
 // Run migration and validateval db = helper.runMigrationsAndValidate("test-db", 2, true, MIGRATION_1_2)
 // Verify the new column exists and old data is preservedval cursor = db.query("SELECT tags FROM notes")
 assertTrue(cursor.moveToFirst())
 assertEquals("", cursor.getString(0))
 }
}

Common Pitfalls and Debugging Techniques

Database locked errors. Multiple connections, one holding a write lock too long. Single database instance. Wrap long operations in transactions.

Leaked cursors. Raw SQLite requires manual cursor closing. Skip it and you leak memory until the app crashes. Room handles this. One more reason to use it.

Storing large blobs. Store paths, not pixels. SQLite handles blobs up to 2GB, sure. Performance tanks and the database file balloons. Images go on disk. The path goes in the database.

String-concatenated queries. SQL injection is real on mobile too. Parameterized queries. Room enforces this at compile time.

Main-thread queries. Dropped frames. Visible jank. Room throws an exception on Android. On iOS you have to be disciplined about dispatching to a background queue yourself.

Debugging Tools

Android Studio's Database Inspector: browse tables, run queries, modify data live on a connected device. On iOS, pull the database file from ~/Library/Developer/CoreSimulator/Devices/ and open it with the SQLite CLI.

SQL logging in debug builds:

// Enable SQL query logging in debug buildsif (BuildConfig.DEBUG) {
 val db = Room.databaseBuilder(context, AppDatabase::class.java, "notes.db")
 .setQueryCallback({ sqlQuery, bindArgs ->Log.d("RoomSQL", "Query: $sqlQuery Args: $bindArgs")
 }, Executors.newSingleThreadExecutor())
 .build()
}

Case Study: Building an Offline-First Notes App

Requirements: create and edit text notes, sync across devices, work fully offline. Simple on paper.

Architecture Decisions

Room on Android, GRDB on iOS. Notes table plus a sync_queue table for pending operations:

CREATE TABLEsync_queue (
 id INTEGERPRIMARY KEY AUTOINCREMENT,
 entity_type TEXT NOT NULL,
 entity_id INTEGER NOT NULL,
 operation TEXT NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
 payload TEXT, -- JSON of the changed data
 created_at INTEGER NOT NULL,
 retry_count INTEGER DEFAULT 0,
 last_error TEXT
);

Why a queue instead of the simpler is_synced flag? Deletes. A deleted row cannot be marked unsynced because it is gone. The queue preserves intent until the server acknowledges it.

The Sync Flow

Every modification writes to the notes table and enqueues a sync operation. Same transaction. WorkManager processes the queue when connectivity returns:

classSyncWorker(
 context: Context,
 params: WorkerParameters,
 private val syncDao: SyncQueueDao,
 private val api: NotesApi
) : CoroutineWorker(context, params) {
 override suspend fundoWork(): Result {
 val pendingOps = syncDao.getPendingOperations()
 for (op in pendingOps) {
 try {
 when (op.operation) {
 "INSERT", "UPDATE" -> api.upsertNote(op.payload)
 "DELETE" -> api.deleteNote(op.entityId)
 }
 syncDao.deleteOperation(op.id)
 } catch (e: Exception) {
 syncDao.incrementRetryCount(op.id, e.message)
 if (op.retryCount >= 5) {
 // Move to dead letter queue or notify user
 syncDao.markAsFailed(op.id)
 }
 }
 }
 returnResult.success()
 }
}

Conflict Resolution

Last-write-wins with one addition: when both client and server modified the same note, the server returns both versions. The app shows a simple diff. User picks. Not collaborative editing. Covered the realistic scenarios.

Results

Sync under a second for most users in beta. Database under 5MB even for heavy users -- text and metadata only, images on the file system.

Design the offline experience first. Build the app against local data. Treat sync as a separate layer you add on top. The teams that build network-first and bolt offline on later are the ones still debugging sync bugs six months after launch.

Sync is the hard part and it always will be.

What I Would Do Differently Next Time

Add sync metadata columns and a sync queue table from day one. Even if the first version has no sync. Adding them later means a migration, a backfill, and testing you will wish you had budgeted for. And test every migration against database files pulled from real devices. Not clean test databases. Real ones, with the weird data your users actually created.