Designing Reliable Local SQLite Storage in Titanium SDK Apps

Problem

Mobile apps built with Titanium SDK often require a lightweight, offline-first data store. While SQLite is the default choice, the SDK's thin wrapper (Ti.Database) leaves transaction handling, schema evolution, and thread safety entirely to the developer. Without a deliberate architecture, apps risk data corruption during crashes, UI freezes during large queries, and security vulnerabilities from raw SQL concatenation.

Requirements

• Atomic Writes: Multiple related statements must succeed or roll back together to prevent partial data states.
• Predictable File Location: Database files must be stored in platform-specific sandboxes for backup and debugging.
• Schema Versioning: A repeatable migration path is required to update tables across app versions.
• Non-blocking Reads: Large result sets must be handled without stalling the JavaScript UI thread.
• Injection Safety: All user-supplied values must be parameterized to prevent SQL injection.

Minimal Suitable Design

The most efficient design uses a single database file opened once at startup and maintained as a module-level singleton. Because Ti.Database.execute() runs synchronously, all multi-statement operations must be wrapped in explicit transactions using BEGIN, COMMIT, and ROLLBACK. A dedicated migration runner should track the current schema version in a metadata table.

Database Bootstrap (Titanium SDK ≥ 10.0)

// app/lib/db.js
const Db = require('ti.database');
const FS = require('ti.filesystem');

let db = null;
const DB_NAME = 'appdata.db';
const CURRENT_SCHEMA = 3;

function open() {
  if (db) return db;
  const file = FS.getFile(FS.applicationDataDirectory, DB_NAME);
  db = Db.open(file.nativePath);
  if (!db) throw new Error('Unable to open SQLite database');
  runMigrations();
  return db;
}

function runMigrations() {
  db.execute('CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT)');
  const row = db.execute('SELECT value FROM meta WHERE key = ?', 'schema_version');
  let version = row.isValidRow() ? parseInt(row.fieldByName('value'), 10) : 0;
  row.close();

  while (version < CURRENT_SCHEMA) {
    version++;
    applyMigration(version);
    db.execute('INSERT OR REPLACE INTO meta (key,value) VALUES (?,?)', 'schema_version', version.toString());
  }
}

function applyMigration(v) {
  switch (v) {
    case 1:
      db.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)');
      break;
    case 2:
      db.execute('CREATE INDEX idx_users_email ON users(email)');
      break;
    case 3:
      db.execute('ALTER TABLE users ADD COLUMN created_at INTEGER DEFAULT (strftime(\'%s\',\'now\'))');
      break;
  }
}

module.exports = { open };

By importing db.open(), all controllers share the same connection instance, reducing file handle overhead.

Trust and Data Boundaries

The database resides within the app's restricted sandbox, ensuring other apps cannot access the data. The specific paths are:

• iOS: <app>/Documents/Databases/
• Android: <app>/databases/

While the sandbox provides basic isolation, it does not provide encryption at rest. If sensitive data is stored, encryption keys should be managed via the platform's native keystore or keychain rather than stored within the SQLite file itself.

Operational Checks

Runtime Verification

To verify the database is active and accessible, log the native path and perform a sanity query during development:

const db = require('lib/db').open();
Ti.API.info('DB opened at: ' + db.file.nativePath);
const rs = db.execute('SELECT COUNT(*) AS c FROM users');
if (rs.isValidRow()) {
  Ti.API.info('User count: ' + rs.fieldByName('c'));
}
rs.close();

External File Inspection

On Android, use adb with a debuggable build to inspect the schema:

adb shell run-as com.example.myapp cat databases/appdata.db | sqlite3 -cmd ".schema" .

On iOS, use Xcode's Devices window to download the app container and navigate to the Documents/Databases folder.

Failure Modes

Scenario	Symptom	Mitigation
App killed during COMMIT	Partial writes or corruption	Wrap critical sections in try { ... COMMIT } catch(e) { ROLLBACK }
Unparameterized input	SQL injection	Use db.execute(sql, param1, param2) to bind values safely
Large SELECT on UI thread	UI freeze / ANR	Use LIMIT ? OFFSET ? for pagination or Ti.Worker for background fetching
Migration script failure	App stuck on old version	Run migrations inside a transaction; rollback and notify user on error

When to Re-evaluate the Design

• Data Volume > 50MB: Consider a native module like Realm or a sync-enabled backend to avoid performance degradation.
• Concurrent Writers: If using background services, enable Write-Ahead Logging (PRAGMA journal_mode=WAL;) to allow concurrent reads and writes.
• Regulatory Encryption: If HIPAA or GDPR requires encryption at rest, integrate SQLCipher via a native module.
• Reactive UI Requirements: If the UI must update instantly on DB changes, implement an observer pattern or a state management store (e.g., MobX) to wrap DB calls.

Limitations and Practical Verification

The provided bootstrap assumes a single-process application. Because Ti.Database is synchronous, any long-running query will block the main JavaScript thread. Additionally, SQLite's ALTER TABLE is limited; removing columns requires creating a new table, copying data, and dropping the old one.

Post-Release Health Check:

1. Launch the app on a clean device and confirm the DB opened at: log appears.
2. Execute a parameterized insert followed by a select to verify the round-trip.
3. Use the adb or Xcode method to ensure meta.schema_version matches the CURRENT_SCHEMA constant.