SQLite in Mobile Development: What It Is and How It Works

What Is SQLite?

SQLite is a C-language library that implements a relational DBMS without a dedicated server. It is embedded directly into the application, reading and writing data to an ordinary file on the device's file system. The library size is approximately 600 KB, making SQLite the lightest full-featured SQL database.

SQLite supports most of the SQL:1999 standard, including JOIN, subqueries, triggers, views, indexes, and window functions. Limitations concern ALTER TABLE (limited support) and full RIGHT/FULL OUTER JOIN. Nevertheless, for mobile applications, SQLite's functionality is sufficient in 99% of local storage cases.

According to the Stack Overflow developer survey (2025), SQLite is the most popular database for embedded solutions and ranks third in popularity among all DBMS after MySQL and PostgreSQL. In mobile development, SQLite is used in every application — either directly or through wrappers.

Key Characteristics of SQLite

Zero-configuration — SQLite requires no installation, permission setup, user creation, or service startup. The library is linked to the project, and the database is created by calling a single function. This radically simplifies deployment compared to client-server DBMS, which require server installation, port configuration, and user setup.

The SQLite database file is an ordinary cross-platform file that can be copied, analyzed, sent over a network, or restored from a backup. The file format is stable at the API level: SQLite 3 files created in 2004 open with the current version of the library, ensuring long-term data compatibility.

How SQLite Works: Architecture and Storage

Architecture of SQLite consists of eight virtual machines: Tokenizer, Parser, Code Generator, VM, B-Tree, Pager, OS Interface, and Utilities. An SQL query passes through the Tokenizer (breaking into tokens), Parser (building an AST), Code Generator (converting into bytecode), and is executed on the virtual machine, which reads data pages through B-Tree and Pager.

SQLite uses B-Tree for storing tables and indexes. Each table is stored as a separate B-Tree, where leaf nodes contain data rows. Indexes are also stored as B-Trees but with keys in the leaves. The Pager manages page loading (default 4096 bytes) from the file into memory, providing ACID transactions through a journal or WAL.

Journaling Modes

WAL (Write-Ahead Logging) is the recommended mode for mobile applications. Changes are first written to a separate WAL file and then periodically moved to the main database. WAL allows simultaneous reading from the database (old data) and writing to it (via WAL), which improves performance for multithreaded applications. The standard journal (rollback journal) blocks reading during writing.

Switching between modes is done with a single SQL query: PRAGMA journal_mode=WAL. For mobile applications with background synchronization and a UI thread simultaneously reading data, WAL provides better performance and no interface lockups.

SQLite vs Other Databases in Mobile Development

SQLite is not the only option for local data storage, but it is the most versatile. Realm offers higher speed of direct in-memory object access but uses its own NoSQL format and has a larger library size. Core Data on iOS is an ORM layer on top of SQLite that adds object graph management and operation undo.

For most applications, SQLite remains the optimal choice due to predictable performance, zero vendor lock-in, and time-tested stability. Realm and Core Data are justified in projects with complex object graphs, reactive queries, or cross-device synchronization requirements.

The choice between SQLite, Realm, and Core Data depends on the platform, object model requirements, and synchronization strategy. For cross-platform projects (KMP, Flutter), SQLite remains the only universal choice that works on all target platforms without changes to the data model.

SQLite on Android: Room and SQLiteOpenHelper

Room is a library from Android Jetpack that provides an ORM layer on top of SQLite. Room automatically generates SQL queries from annotated DAO interfaces, validates query correctness at compile time, and supports database migrations when the schema changes. Room is the recommended way to work with SQLite on Android.

SQLiteOpenHelper is a low-level API for direct SQLite management without ORM. The class handles database creation, opening, and upgrading. SQLiteOpenHelper is suitable for projects with simple SQL queries or when full control over SQL logic without Room abstraction is needed.

Example Entity and DAO for Room

An entity in Room is annotated with @Entity, and a DAO with @Dao. Room translates annotated methods into SQL queries: @Insert generates INSERT, @Query generates SELECT with specified SQL. Migrations are added via Migration with the old and new schema versions specified. Room validates SQL at compile time, eliminating syntax errors in production.

@Entity
data class User(
    @PrimaryKey val id: Long,
    val name: String,
    @ColumnInfo(name = "created_at")
    val createdAt: Long
)

@Dao
interface UserDao {
    @Query("SELECT * FROM user ORDER BY name ASC")
    suspend fun getAllUsers(): List<User>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: User)

    @Query("DELETE FROM user WHERE id = :id")
    suspend fun deleteUser(id: Long)
}

Room automatically generates the UserDao_Impl implementation, which contains runtime SQLite queries through the internal RoomDatabase. Thanks to coroutines (suspend), DAO methods execute asynchronously on a background thread without blocking the UI. Flow return types in @Query automatically update the result when the table changes.

SQLite on iOS: FMDB and GRDB

FMDB is an Objective-C wrapper over the SQLite C API, historically the first popular library for iOS. It provides FMDatabase and FMResultSet objects for executing queries and retrieving results. FMDB is simple and minimalistic but does not support Swift-specific constructs — optionals, Codable, async/await.

GRDB is a modern Swift library for working with SQLite. It provides a type-safe API, Codable support, Combine Publishers, async/await, migrations, and real-time change observation. GRDB is preferred for new Swift projects thanks to full integration with Swift Concurrency and better code readability.

GRDB Example in Swift

GRDB defines tables through Record classes conforming to FetchableRecord and TableRecord protocols. Queries are written in Swift with type-safe syntax rather than raw SQL. GRDB also supports DatabaseMigrator for schema versioning and migrations between app versions.

struct User: Codable, FetchableRecord, TableRecord {
    var id: Int64
    var name: String
    var createdAt: Date
}

let dbPool = try DatabasePool(path: dbPath)
var migrator = DatabaseMigrator()
migrator.registerMigration("v1") { db in
    try db.create(table: "user") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
        t.column("createdAt", .datetime).notNull()
    }
}

let users = try await dbPool.read { db in
    try User.order(Column("name")).fetchAll(db)
}

DatabasePool uses SQLite's WAL mode for concurrent reading. Multiple readers can access the database simultaneously while a single writer updates data through WAL. GRDB automatically manages connections and transactions, providing thread-safe database access from any thread without manual synchronization.

SQLite Performance Optimization

Indexes are the most effective way to speed up SQLite queries. An index is created on columns used in WHERE, JOIN, and ORDER BY clauses. For a table with 100000 records, searching by an indexed column takes milliseconds instead of seconds. However, indexes slow down INSERT and UPDATE operations, so their number should be balanced with write frequency.

Batch insert within a single transaction radically speeds up mass data loading. Inserting 1000 records one by one incurs an overhead of ~1 second. The same 1000 records in a single transaction takes ~5-10 milliseconds. The difference is explained by each individual INSERT creating a new transaction with synchronous disk write.

Performance PRAGMAs

PRAGMA are SQLite commands for configuring library behavior. Key optimization pragmas: PRAGMA synchronous=NORMAL (reduces fsync frequency), PRAGMA cache_size=-8000 (allocates 8 MB cache), PRAGMA temp_store=MEMORY (temporary tables in memory). For mobile applications with large data volumes, combining these pragmas speeds up queries by 2-3 times.

Another important optimization is pre-compilation of SQL queries (prepared statements). If a query is executed repeatedly (e.g., inserting 10000 rows), compiling SQL once and then reusing the statement reduces CPU load by 30-50%. Room and GRDB automatically cache prepared statements, but when using the SQLite C API directly, compilation must be done manually.

class UserRepository(private val db: RoomDatabase) {

    suspend fun insertBatch(users: List<User>) {
        db.withTransaction {
            users.chunked(500).forEach { batch ->
                batch.forEach { user ->
                    insertUser(user)
                }
            }
        }
    }
}

Batch insert with withTransaction ensures all INSERT operations execute within a single transaction. Chunking into sub-batches prevents a single transaction from becoming too large, which could block other threads for an extended period. For background synchronization, a sub-batch size of 500 records provides the optimal balance of speed and UI responsiveness.