Why SQLite Might Be the Perfect Database for Mobile Apps

SQLite Overview

In the author's view, MySQL and Oracle are heavyweight, while SQLite is a cute, lightweight database worth learning.

SQLite is a lightweight, ACID‑compliant relational database management system designed for embedded use; it is built into Android and iOS devices and supports most of the SQL‑92 standard.

Key Features

Zero‑Configuration – no installation or configuration needed.

Serverless – does not require a separate server process.

Single Database File – all data stored in one disk file.

Stable Cross‑Platform Database File – file format works across endianness and 32‑ or 64‑bit machines.

Compact – full feature build is about 500 KiB; can be trimmed below 300 KiB.

Manifest Typing – column types are declared but actual storage class depends on the value.

Variable‑length Records – storage size matches actual data length.

SQL statements compile into readable virtual‑machine code.

Public Domain – completely open source.

SQL Language Extensions.

Major Drawbacks

Database‑level locking only; does not support high concurrency.

No support for stored procedures.

No built‑in user accounts; permissions are based on the file system, making quota enforcement difficult.

For mobile‑only use, SQLite’s advantages outweigh its drawbacks, making it a better choice than MySQL for many scenarios.

Transactions and Locks (pre‑3.7.0)

SQLite defines five lock states:

UNLOCKED – no lock.

SHARED – shared read lock.

RESERVED – reserved lock for writing.

PENDING – pending lock before exclusive access.

EXCLUSIVE – exclusive lock for writing.

Each connection can hold only one lock state at a time.

Read Operations

A connection starts UNLOCKED. To read data it must acquire a SHARED lock, allowing multiple connections to read concurrently. SQLite supports concurrent reads.

Write Operations

To write, a connection first obtains a RESERVED lock (compatible with existing SHARED locks). Data is written to a buffer, not directly to disk. When committing, the connection acquires a PENDING lock, then upgrades to EXCLUSIVE once all SHARED locks are released, finally writing buffered changes to the file. SQLite does not support concurrent writes.

Transaction Types

SQLite supports three transaction modes, specified in the BEGIN command:

DEFERRED – no lock is acquired until the first read or write.

IMMEDIATE – attempts to acquire a RESERVED lock immediately; other connections can still read but cannot start IMMEDIATE or EXCLUSIVE transactions.

EXCLUSIVE – acquires an EXCLUSIVE lock, preventing any other connections.

Deadlocks

If two connections start DEFERRED transactions and both hold SHARED locks while waiting for each other to acquire RESERVED locks, a deadlock occurs. BEGIN DEFERRED transactions can cause deadlocks.

Write‑Ahead Logging (WAL) (≥ 3.7.0)

WAL enables concurrent reads and a single writer. Before WAL, SQLite used a rollback journal to achieve atomic transactions.

In the rollback journal, pages are copied to a backup before being overwritten; on failure the backup is restored.

With WAL, changes are first written to a separate WAL file. On commit, changes are later checkpointed back to the main database file.

Checkpointing occurs automatically after the WAL file accumulates about 1,000 pages, or can be triggered manually.

WAL Advantages

Reads and writes can proceed concurrently (writes still serialize).

Generally better performance because only one file is written per transaction.

Disk I/O behavior is more predictable.

WAL Disadvantages

All processes must run on the same host and support shared memory.

Each database now consists of three files: .db, -wal, and -shm.

Performance degrades when the WAL file grows to gigabyte size.

Pre‑3.7.0 SQLite cannot read databases that have WAL enabled.

Enabling WAL changes the file format version, causing compatibility issues with older SQLite versions.

In extreme cases, WAL can reduce transaction performance, especially during long transactions or large data modifications that cause the WAL file to become large and delay checkpointing.

Overall, SQLite offers a compact, zero‑configuration, serverless database solution ideal for mobile applications, with clear trade‑offs in concurrency and advanced features.