SQLite on Mobile: iOS and Android Best Practices (2026)

SQLite on mobile is less about the database itself and more about managing its lifecycle and state across app updates and user interactions.

Let’s see it in action. Imagine a simple to-do list app.

import SQLite3

// Assume db is an initialized SQLite3 pointer, and we've opened our database file.
// Example: sqlite3_open_v2("my_todos.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nil, nil)

let createTableSQL = """
CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    task TEXT NOT NULL,
    completed INTEGER DEFAULT 0
);
"""
var statement: OpaquePointer?
if sqlite3_prepare_v2(db, createTableSQL, -1, &statement, nil) == SQLITE_OK {
    if sqlite3_step(statement) == SQLITE_DONE {
        print("Table 'todos' created or already exists.")
    } else {
        print("Failed to create table.")
    }
} else {
    print("Error preparing statement: \(sqlite3_errstr(sqlite3_errcode(db)))")
}
sqlite3_finalize(statement)

let insertSQL = "INSERT INTO todos (task) VALUES (?);"
if sqlite3_prepare_v2(db, insertSQL, -1, &statement, nil) == SQLITE_OK {
    let taskToInsert = "Buy groceries"
    sqlite3_bind_text(statement, 1, taskToInsert, -1, nil)
    if sqlite3_step(statement) == SQLITE_DONE {
        print("Task inserted successfully.")
    } else {
        print("Failed to insert task.")
    }
} else {
    print("Error preparing insert statement: \(sqlite3_errstr(sqlite3_errcode(db)))")
}
sqlite3_finalize(statement)

let selectSQL = "SELECT id, task, completed FROM todos;"
if sqlite3_prepare_v2(db, selectSQL, -1, &statement, nil) == SQLITE_OK {
    while sqlite3_step(statement) == SQLITE_ROW {
        let id = sqlite3_column_int(statement, 0)
        if let task_c = sqlite3_column_text(statement, 1) {
            let task = String(cString: task_c)
            let completed = sqlite3_column_int(statement, 2)
            print("ID: \(id), Task: \(task), Completed: \(completed)")
        }
    }
} else {
    print("Error preparing select statement: \(sqlite3_errstr(sqlite3_errcode(db)))")
}
sqlite3_finalize(statement)

// Remember to close the database when done
// sqlite3_close(db)

The core problem SQLite solves on mobile is persistent, structured data storage that’s accessible offline and survives app restarts. Unlike simple file storage, it gives you ACID compliance and a query language. On iOS, you’re typically dealing with NSSQLiteStoreType in Core Data or direct sqlite3 C API calls. For Android, it’s Room (which abstracts SQLiteOpenHelper) or direct SQLiteOpenHelper and SQLiteDatabase calls. The key is to treat the database file itself as a versioned asset.

The mental model for SQLite on mobile revolves around the database file and its schema. The file (your_database.db) lives in the app’s sandboxed documents directory. The schema defines the tables, columns, and relationships. When your app is installed or updated, you need a mechanism to manage the schema: creating it initially, and crucially, migrating it to new versions when you change your data model (add a column, rename a table, etc.). This migration process is where most mobile SQLite issues arise.

When you first create a database, you specify a version number, say 1. If you later decide to add a priority column to your todos table, you’ll increment the version to 2. Your app logic then needs to execute SQL commands to alter the table only when the database version is 1 and the new version is 2. This is handled by SQLiteOpenHelper’s onUpgrade method in Android or Core Data’s lightweight/heavyweight migration strategies in iOS.

The one thing most people don’t know is that SQLite transactions are critical for performance and data integrity during migrations. While BEGIN TRANSACTION; ... COMMIT; is standard SQL, on mobile, not wrapping your schema alteration scripts in a transaction can lead to partial updates if the app crashes mid-migration. This leaves the database in an inconsistent state, often unrecoverable without manual intervention or a full data reset. Ensure every migration step is part of a single, atomic transaction.

The next concept to grapple with is handling large data sets and efficient querying, especially with background processing.