Mobile Database Patterns | The Garnet Wiki

Mobile databases are the backbone of offline-first, real-time, and low-latency mobile experiences. This reference page describes the most effective patterns for structuring, synchronizing, and maintaining data on mobile devices, focusing on the practical decisions engineers must make when designing database behavior across diverse network conditions and device lifecycles.

Data Modeling for Mobile

When modeling data for mobile, the goal is to reduce the number of round trips between device and server while minimizing memory and storage usage. The primary trade-off is between normalized and denormalized schemas.

Normalize for Read-Intensive Workflows

Use normalized tables when data is frequently updated and accessed in complex queries. For example, in a task management app, a tasks table with id, title, project_id, and due_date links to a projects table via project_id.

CREATE TABLE projects (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    owner_id INTEGER,
    created_at DATETIME
);

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    project_id INTEGER REFERENCES projects(id),
    due_date DATETIME,
    status TEXT CHECK (status IN ('todo', 'in-progress', 'done'))
);

On mobile, fetch related data with a single query using a JOIN:

SELECT t.id, t.title, t.due_date, p.name AS project_name
FROM tasks t
JOIN projects p ON t.project_id = p.id
WHERE t.status = 'in-progress';

Denormalize for Offline and High-Throughput Scenarios

Denormalization reduces the need for joins by duplicating data across tables. In a news reader app, the articles table includes author_name, author_avatar, and category_name directly, even though these are derived from authors and categories tables.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    author_name TEXT,
    author_avatar TEXT,
    category_name TEXT,
    published_at DATETIME,
    is_favorited BOOLEAN,
    updated_at DATETIME
);

This reduces sync time and read latency, especially when the device is offline. The cost is higher write overhead and potential data staleness.

Handle Schema Evolution with Versioned Tables

Mobile apps frequently update their schema. To ensure compatibility across versions, use versioned tables with a schema_version column:

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    author_name TEXT,
    published_at DATETIME,
    schema_version INTEGER DEFAULT 1,
    updated_at DATETIME
);

-- When schema evolves, add new columns incrementally:
ALTER TABLE articles ADD COLUMN reading_time INTEGER;
ALTER TABLE articles ADD COLUMN source_url TEXT;

On app startup, check schema_version and apply migrations:

if (db.schema_version < 2) {
    db.execute('ALTER TABLE articles ADD COLUMN reading_time INTEGER');
    db.execute('UPDATE articles SET schema_version = 2');
}

Offline-First Data Synchronization

Syncing data between mobile and backend systems must account for intermittent connectivity, high latency, and inconsistent device states.

Two-Way Sync with Conflict Resolution

Use a last_synced_at timestamp and a version or etag field to detect changes on both client and server.

On the client, record changes with a sync_log table:

CREATE TABLE sync_log (
    id INTEGER PRIMARY KEY,
    operation TEXT CHECK (operation IN ('create', 'update', 'delete')),
    table_name TEXT,
    record_id INTEGER,
    version INTEGER,
    payload TEXT, -- JSON blob of record
    sync_timestamp DATETIME,
    status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'acknowledged', 'failed'))
);

When syncing, send all pending operations to the server via a POST /sync endpoint:

{
  "operations": [
    {
      "operation": "create",
      "table_name": "tasks",
      "record_id": 123,
      "version": 3,
      "payload": {
        "title": "Review PR",
        "project_id": 45,
        "due_date": "2024-05-15T10:00:00Z"
      }
    },
    {
      "operation": "update",
      "table_name": "tasks",
      "record_id": 124,
      "version": 5,
      "payload": {
        "status": "done"
      }
    }
  ]
}

The server applies these operations, returns a 200 OK with updated version and last_synced_at:

{
  "last_synced_at": "2024-05-15T10:02:15Z",
  "applied_operations": [
    { "record_id": 123, "version": 4 },
    { "record_id": 124, "version": 6 }
  ]
}

The client updates sync_log with status = 'acknowledged' and applies server-side versions.

Resolve Conflicts Using Timestamps and Version Vectors

When a record is modified on both device and server, use last-write-wins or merge strategy.

For last-write-wins, store created_at and updated_at on every record. When conflict arises, apply the record with the later updated_at:

UPDATE tasks
SET title = 'Final Review',
    updated_at = '2024-05-15T10:03:00Z'
WHERE id = 123
AND updated_at < '2024-05-15T10:02:30Z';

For merge strategy, use a version_vector field in JSON format:

"version_vector": {
  "device_a": 12,
  "server": 8
}

On conflict, merge fields from both sides, increasing version numbers. For example:

function mergeRecords(clientRecord, serverRecord) {
    const merged = { ...serverRecord };
    const clientVersion = clientRecord.version_vector.device_a;
    const serverVersion = serverRecord.version_vector.server;

    if (clientVersion > serverVersion) {
        // Apply client changes
        merged.title = clientRecord.title;
        merged.status = clientRecord.status;
        merged.version_vector.device_a = clientVersion;
    } else {
        // Apply server changes
        merged.title = serverRecord.title;
        merged.status = serverRecord.status;
        merged.version_vector.server = serverVersion;
    }

    merged.updated_at = new Date().toISOString();
    return merged;
}

Handle Sync Errors and Retry with Backoff

Sync operations may fail due to network issues or server errors. Use exponential backoff with jitter.

On failure, set sync_log.status = 'failed' and schedule retry:

const backoff = (attempt) => Math.min(1000 * Math.pow(2, attempt), 30000);

// Retry job queued by a background task
async function syncWithRetry() {
    const jobs = db.query('SELECT * FROM sync_log WHERE status = "pending"');
    for (const job of jobs) {
        let attempt = 0;
        while (attempt < 5) {
            try {
                const response = await fetch('/sync', {
                    method: 'POST',
                    headers: { 'Content-Type': 'application/json' },
                    body: JSON.stringify(job.payload)
                });

                if (response.ok) {
                    const result = await response.json();
                    db.execute(
                        'UPDATE sync_log SET status = ?, last_synced_at = ? WHERE id = ?',
                        ['acknowledged', result.last_synced_at, job.id]
                    );
                    break;
                }
            } catch (err) {
                db.execute(
                    'UPDATE sync_log SET status = ?, error = ? WHERE id = ?',
                    ['failed', err.message, job.id]
                );
            }

            const delay = backoff(attempt) + Math.random() * 1000;
            await new Promise(r => setTimeout(r, delay));
            attempt++;
        }
    }
}

Indexing and Query Optimization

Mobile databases must serve data quickly with limited CPU and memory.

Create Indexes on High-Frequency Query Columns

Index columns used in WHERE, JOIN, and ORDER BY clauses.

CREATE INDEX idx_tasks_project_id ON tasks(project_id);
CREATE INDEX idx_tasks_due_date ON tasks(due_date);
CREATE INDEX idx_tasks_status_updated_at ON tasks(status, updated_at);

For full-text search, use a MATCH query on a content column:

CREATE VIRTUAL TABLE articles_fts USING fts5(title, content, tokenize=porter);

-- Populate the full-text index
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

Query with:

SELECT id, title, content
FROM articles_fts
WHERE articles_fts MATCH 'review AND design'
ORDER BY rank;

Optimize for Memory and Disk Usage

Mobile devices have constrained memory and disk space. Use row-level compression and columnar storage where available.

For SQLite, enable WAL mode and set page size:

PRAGMA journal_mode = WAL;
PRAGMA page_size = 4096;
PRAGMA cache_size = 2000;
PRAGMA synchronous = NORMAL;

On startup, preload frequently accessed data:

db.execute('PRAGMA temp_store = MEMORY');
db.execute('BEGIN TRANSACTION');
db.execute('SELECT * FROM tasks WHERE status = "in-progress"');
db.execute('SELECT * FROM projects WHERE archived = 0');
db.execute('COMMIT');

Data Caching and Eviction

Caching reduces latency and network usage. Mobile databases use both in-memory and persistent caches.

Use a Two-Level Cache with TTL and LRU

Store recently accessed records in an in-memory cache (e.g., Map or WeakMap) and a persistent cache in SQLite.

Define a cache table:

CREATE TABLE cache (
    key TEXT PRIMARY KEY,
    value TEXT,
    expires_at DATETIME,
    fetched_at DATETIME,
    hit_count INTEGER DEFAULT 0
);

On query:

async function getCachedRecord(table, id) {
    const key = `${table}:${id}`;

    // Check in-memory cache first
    const cached = memoryCache.get(key);
    if (cached) {
        return cached;
    }

    // Then persistent cache
    const row = db.queryOne('SELECT * FROM cache WHERE key = ?', [key]);
    if (row && row.expires_at > new Date().toISOString()) {
        const value = JSON.parse(row.value);
        memoryCache.set(key, value);
        db.execute('UPDATE cache SET hit_count = hit_count + 1 WHERE key = ?', [key]);
        return value;
    }

    // Fetch from server
    const response = await fetch(`/api/${table}/${id}`);
    const data = await response.json();

    // Store in both caches
    const expiresAt = new Date(Date.now() + 300_000).toISOString(); // 5 min
    db.execute(
        'INSERT OR REPLACE INTO cache (key, value, expires_at, fetched_at) VALUES (?, ?, ?, ?)',
        [key, JSON.stringify(data), expiresAt, new Date().toISOString()]
    );
    memoryCache.set(key, data);

    return data;
}

Evict Caches Using LRU with Time-Based Purging

Periodically clean up old or unused cache entries:

setInterval(() => {
    // Remove expired entries
    db.execute('DELETE FROM cache WHERE expires_at < ?', [new Date().toISOString()]);

    // Evict least recently used entries if memory is full
    const allKeys = db.query('SELECT key, hit_count FROM cache ORDER BY hit_count ASC LIMIT 10');
    for (const row of allKeys) {
        memoryCache.delete(row.key);
        db.execute('DELETE FROM cache WHERE key = ?', [row.key]);
    }
}, 300_000); // Every 5 minutes

Handling Database Failures

Mobile databases silently fail. Recognize and respond to common failure modes.

Detect and Handle Database Corruption

SQLite databases can become corrupted due to power loss, crashes, or I/O errors.

Use PRAGMA integrity_check and PRAGMA quick_check:

PRAGMA integrity_check;

On failure, log error and attempt repair:

try {
    const result = db.execute('PRAGMA integrity_check');
    if (result.rows[0].integrity_check !== 'ok') {
        console.error('Database integrity check failed:', result.rows[0]);
        db.execute('PRAGMA journal_mode = WAL');
        db.execute('PRAGMA synchronous = FULL');
        db.execute('PRAGMA cache_size = 5000');
    }
} catch (err) {
    console.error('Database error:', err);
}

Recover from Out-of-Memory and Disk Pressure

When memory is low, flush database changes to disk and reduce cache size.

On low_memory or disk_full events:

window.addEventListener('low-memory', () => {
    db.execute('PRAGMA journal_mode = WAL');
    db.execute('PRAGMA synchronous = FULL');
    db.execute('PRAGMA auto_vacuum = INCREMENTAL');
    memoryCache.clear();
});

window.addEventListener('disk-full', () => {
    db.execute('PRAGMA journal_mode = WAL');
    db.execute('PRAGMA synchronous = FULL');
    db.execute('VACUUM');
});

Handle Concurrent Access and Transaction Conflicts

Use transactions for consistency and atomicity.

db.execute('BEGIN TRANSACTION');
try {
    db.execute('UPDATE tasks SET status = ? WHERE id = ?', ['done', 123]);
    db.execute('INSERT INTO logs (action, task_id) VALUES (?, ?)', ['mark_done', 123]);
    db.execute('COMMIT');
} catch (err) {
    db.execute('ROLLBACK');
    console.error('Transaction failed:', err);
}

When multiple threads or processes access the database, use row-level locking and versioned transactions.

Summary of Key Patterns

Use normalized schemas for read-heavy, server-centric apps.
Denormalize for offline-first and low-latency apps.
Version tables to handle schema evolution.
Implement two-way sync with sync_log and versioned records.
Resolve conflicts using timestamps or version vectors.
Apply exponential backoff with jitter for sync retries.
Index on frequently queried columns and use full-text search where needed.
Maintain two-level cache with TTL and LRU eviction.
Detect and recover from corruption, memory, and disk pressure.
Use transactions and locking for concurrent access.

These patterns form a robust foundation for mobile data management, enabling reliable, performant, and resilient database interactions across diverse mobile environments.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.