# Database queries function without network connectivity

- **Pattern:** `ab-001874` (`mobile-offline-storage.data-persistence.local-queries-work-offline`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-001874
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-001874)

## Why it matters

When every data query requires a live network round-trip, users on flaky connections — subways, elevators, rural areas — get a blank or broken screen. Mobile network reliability is not a product assumption; it is a variable you must handle. ISO 25010:2011 reliability.fault-tolerance and recoverability both penalize apps that have no local query path. Beyond connectivity, local queries are also faster by orders of magnitude: a SQLite `SELECT` takes microseconds; a network call takes hundreds of milliseconds even on good connections. An offline query layer is the foundation for every other pattern in this bundle.

## Severity rationale

Critical because apps with no offline query capability become completely unusable without network access, blocking users from data they already have on-device.

## Remediation

Add a local database layer using `expo-sqlite` or WatermelonDB, then route at least one primary data-loading function through it before hitting the network.

```tsx
// npm install expo-sqlite
import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabase('app.db');

export const initDB = () =>
  new Promise<void>((resolve, reject) =>
    db.transaction(tx =>
      tx.executeSql(
        'CREATE TABLE IF NOT EXISTS items (id TEXT PRIMARY KEY, data TEXT, updated_at INTEGER);',
        [],
        () => resolve(),
        (_, err) => { reject(err); return false; }
      )
    )
  );

export const queryItems = (): Promise<unknown[]> =>
  new Promise((resolve, reject) =>
    db.transaction(tx =>
      tx.executeSql(
        'SELECT * FROM items ORDER BY updated_at DESC;',
        [],
        (_, result) => resolve(result.rows._array),
        (_, err) => { reject(err); return false; }
      )
    )
  );
```

On first launch, seed the local DB from the network. On subsequent launches, read from local first, then sync in the background via `data-sync-on-reconnect`.

## Detection

- **ID:** `local-queries-work-offline`
- **Severity:** `critical`
- **What to look for:** Examine whether the app has a local database (SQLite, Realm, WatermelonDB) or caching layer that allows querying stored data when offline. Check for query logic that reads from AsyncStorage or a local database rather than always hitting the network.
- **Pass criteria:** Count all data query functions in the codebase. At least 1 query function reads from local storage or a local database without requiring a network request. Recently viewed or cached data must be queryable offline. Report: "Found X data query functions, Y read from local storage."
- **Fail criteria:** All data queries require a network request. No offline query capability found. No more than 0 primary data screens should depend entirely on network calls.
- **Skip (N/A) when:** Never — offline querying is critical for user experience in mobile apps.
- **Detail on fail:** `"App has no local database. All data queries hit the network API. When offline, users cannot access any cached data."`
- **Remediation:** Implement a local database or caching layer. Here's an example using SQLite with Expo:

  ```tsx
  // Install: npm install expo-sqlite

  import * as SQLite from 'expo-sqlite';

  const db = SQLite.openDatabase('myapp.db');

  export const initDB = () => {
    return new Promise((resolve, reject) => {
      db.transaction(tx => {
        tx.executeSql(
          'CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, title TEXT, data TEXT, created_at DATETIME);',
          [],
          () => resolve(),
          (_, err) => reject(err)
        );
      });
    });
  };

  export const queryItems = () => {
    return new Promise((resolve, reject) => {
      db.transaction(tx => {
        tx.executeSql(
          'SELECT * FROM items ORDER BY created_at DESC;',
          [],
          (_, result) => resolve(result.rows._array),
          (_, err) => reject(err)
        );
      });
    });
  };

  export const saveItem = (title, data) => {
    return new Promise((resolve, reject) => {
      db.transaction(tx => {
        tx.executeSql(
          'INSERT INTO items (title, data, created_at) VALUES (?, ?, datetime("now"));',
          [title, JSON.stringify(data)],
          (_, result) => resolve(result),
          (_, err) => reject(err)
        );
      });
    });
  };
  ```

  For complex schemas, WatermelonDB offers a more robust ORM:

  ```tsx
  // Install: npm install @nozbe/watermelondb

  import { Database } from '@nozbe/watermelondb';

  const database = new Database({
    adapter: new SQLiteAdapter({ dbName: 'WatermelonChat' }),
    modelClasses: [User, Message],
  });

  const messages = await database.get('messages').query(
    Q.where('user_id', userId)
  ).fetch();
  ```

## External references

- iso-25010 reliability.recoverability

Taxons: data-integrity, error-resilience

HTML version: https://auditbuffet.com/patterns/ab-001874