Designing a Minimal SQLite Layer with Titanium SDK's Ti.Database

Problem

When building a cross‑platform mobile app with Titanium SDK you need a reliable way to store small amounts of structured data locally. Opening a new SQLite connection on every query can exhaust file descriptors, and running long queries on the UI thread freezes the interface. The goal is to create a single, safe data‑access layer that hides the Ti.Database API, guarantees one open connection, and provides clear error handling.

Requirements

• One database connection per app lifecycle.
• Lazy opening – the database is created only when first needed.
• Schema version tracking so migrations run exactly once.
• All write operations wrapped in a transaction to avoid partial updates.
• Errors from Ti.Database.execute must be inspected and logged.
• UI thread must never block on database work.

Smallest Suitable Design

A singleton DatabaseManager satisfies the requirements. It exposes open(), close(), and CRUD helpers that internally use prepared statements. The manager caches the Ti.Database object after the first successful open and returns it on subsequent calls.

// DatabaseManager.js (CommonJS)
var Ti = require('ti.sdk');
var db = null;
const DB_NAME = 'appdata.sqlite';
const SCHEMA_VERSION = 1;

function open() {
  if (db) { return db; }
  try {
    db = Ti.Database.open(DB_NAME);
    // Ensure the file exists; Ti.Database.open creates it if missing.
    _ensureSchema(db);
    return db;
  } catch (e) {
    Ti.API.error('Database open failed: ' + e.message);
    throw e;
  }
}

function close() {
  if (db) {
    db.close();
    db = null;
  }
}

function _ensureSchema(conn) {
  var ver = conn.execute('PRAGMA user_version;').fieldByName('user_version');
  if (ver == SCHEMA_VERSION) { return; }
  conn.execute('BEGIN;');
  try {
    if (ver === 0) {
      // First‑time creation
      conn.execute('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, name TEXT, qty INTEGER);');
    } else {
      // Example migration from version 0 to 1
      conn.execute('ALTER TABLE items ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;');
    }
    conn.execute('PRAGMA user_version = ' + SCHEMA_VERSION + ';');
    conn.execute('COMMIT;');
  } catch (migrErr) {
    conn.execute('ROLLBACK;');
    Ti.API.error('Migration failed: ' + migrErr.message);
    throw migrErr;
  }
}

function insertItem(name, qty) {
  var conn = open();
  var stmt = conn.prepare('INSERT INTO items (name, qty) VALUES (?, ?);');
  try {
    conn.execute('BEGIN;');
    stmt.execute(name, qty);
    conn.execute('COMMIT;');
    return stmt.lastInsertRowId;
  } catch (err) {
    conn.execute('ROLLBACK;');
    Ti.API.error('Insert failed: ' + err.message);
    throw err;
  } finally {
    stmt.close();
  }
}

function fetchAllItems() {
  var conn = open();
  var rows = [];
  var rs = conn.execute('SELECT id, name, qty FROM items;');
  while (rs.isValidRow()) {
    rows.push({ id: rs.fieldByName('id'), name: rs.fieldByName('name'), qty: rs.fieldByName('qty') });
    rs.next();
  }
  rs.close();
  return rows;
}

module.exports = {
  open: open,
  close: close,
  insertItem: insertItem,
  fetchAllItems: fetchAllItems
};

Trust / Data Boundaries

The manager treats the SQLite file as the sole source of truth for the items table. No other module opens Ti.Database.open directly; all data flow goes through the singleton. This boundary prevents multiple connections and makes it easy to audit where reads and writes occur.

Operational Checks

1. Verify the database file exists after the first open: Ti.Filesystem.getFile(Ti.Filesystem.applicationDataDirectory, 'appdata.sqlite').exists() should return true.
2. Check schema version at runtime: Ti.Database.open('appdata.sqlite').execute('PRAGMA user_version;').fieldByName('user_version') must equal SCHEMA_VERSION.
3. Ensure write operations are inside a transaction – the manager’s BEGIN/COMMIT block guarantees this; any error triggers a ROLLBACK.
4. Inspect error codes: Ti.Database.execute returns false on failure; the manager catches exceptions and logs e.message.

Failure Modes and Mitigations

• Database corruption – if Ti.Database.open throws, the app can fall back to an in‑memory SQLite instance (Ti.Database.open(':memory:')) for non‑critical UI, while persisting only essential data via a sync‑when‑recovered strategy.
• Insufficient storage – catch Error code 13 (SQLITE_FULL) and disable further writes, showing a user‑friendly "storage full" message.
• Permission issues – on Android, ensure the android.permission.WRITE_EXTERNAL_STORAGE is not required; the app’s internal data directory is always writable. On iOS, exclude the file from iCloud backup by adding NSURLIsExcludedFromBackupKey = true via a ti.plist entry if the file may grow large.

When the Design Should Change

• If queries become complex (joins, full‑text search) and performance degrades, consider a native module that exposes SQLite’s FTS5 or a lightweight ORM.
• When the database size approaches several megabytes, the overhead of copying the file for backup or sync may become problematic; a cloud‑based store (e.g., Firebase) or a segmented table strategy may be preferable.
• If the app needs concurrent writes from multiple background workers, a single connection may become a bottleneck; a connection pool or serialized queue via setInterval could be introduced.

Practical Verification

To confirm the layer works as intended:

1. Run the app on iOS and Android simulators (or devices).
2. From the Titanium console, invoke require('DatabaseManager').insertItem('widget', 42) and note the returned row ID.
3. Call require('DatabaseManager').fetchAllItems() and verify the array contains the inserted object.
4. On the device, locate the SQLite file:
• Android: adb run‑org.appid pull /data/data/org.appid/files/appdata.sqlite .
• iOS: xcrun simctl get_app_container booted org.appid data/Containers/Data/Application/<UUID>/Documents/appdata.sqlite
5. Open the file with the SQLite CLI: sqlite3 appdata.sqlite ".schema" should show the items table and the user_version pragma set to 1.
6. Enable Titanium debug mode (tiapp.xml <property name="ti.android.debug" type="bool">true</property>) and watch the console for messages like "Database open", "Transaction commit", and any error logs.

These steps give confidence that the singleton manager opens exactly one connection, applies schema migrations safely, and handles errors without silent data loss.