Using Titanium.Database for Offline SQLite Storage in Cross‑Platform Apps

Problem: needing reliable offline storage without native modules

When building a Titanium app that must work offline, adding a native SQLite module increases build complexity and maintenance. The core Titanium.Database module offers a JavaScript‑only wrapper around SQLite that is available on iOS, Android and Windows.

Where Titanium.Database is a good fit

The module exposes the SQLite engine through Ti.Database.open, db.execute and Ti.Database.ResultSet. It supports parameter binding, transactions and schema introspection via PRAGMA statements. Because it is part of the SDK, no extra dependencies are required and the database file lives in the app sandbox, automatically included in platform backups.

It is suitable for modest data sets—typically a few megabytes to a couple of gigabytes—where launch time and memory usage stay acceptable. For high‑throughput or large binary blobs, a server‑side store or an alternative local database is preferable.

Schema versioning with pragma user_version

To evolve the schema safely, read the current user version, apply incremental changes inside a transaction, and bump the version. This works consistently from SDK 7.x onward; older versions should be tested for transaction quirks.

Worked example: a simple notes store

The following snippet shows opening a database, ensuring a table exists, migrating a column, inserting a row with bound parameters, and reading it back. Run it in any Titanium project where the app has filesystem access (granted by default).

// open (or create) the database in the app sandbox
var db = Ti.Database.open('notesDB');

// ensure base table exists
db.execute('CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, body TEXT, updated_at INTEGER)');

// read current schema version
var verRs = db.execute('PRAGMA user_version');
var version = verRs.field(0);
verRs.close();

// example migration: add a tags column if version is 0
if (version === 0) {
  db.execute('ALTER TABLE notes ADD COLUMN tags TEXT');
  db.execute('PRAGMA user_version = 1');
}

// insert a note using a transaction and bound parameters
db.beginTransaction();
try {
  var insertSql = 'INSERT INTO notes (title, body, updated_at) VALUES (?, ?, ?)';
  db.execute(insertSql, 'First note', 'Sample content', Math.floor(Date.now()/1000));
  db.commit();
} catch (err) {
  db.rollback();
  Ti.API.error('Insert failed: ' + err);
}

// query the note we just added
var selectRs = db.execute('SELECT id, title, updated_at FROM notes WHERE title = ?', 'First note');
while (selectRs.isValidRow()) {
  Ti.API.info('Note id: ' + selectRs.fieldByName('id') +
              ', title: ' + selectRs.fieldByName('title') +
              ', updated: ' + selectRs.fieldByName('updated_at'));
  selectRs.next();
}
selectRs.close();
db.close();

Remember to close the database handle; leaving it open can lock the file on Android. Index columns that appear in WHERE clauses (e.g., updated_at) to keep queries fast as the table grows.

Trade‑off and verification

The main limitation is that data stays local; there is no built‑in sync across devices. If cross‑device synchronization is required, you must add your own sync layer or choose a different storage solution. Performance also degrades with very large tables or unindexed queries, so keep the dataset bounded and index frequently filtered columns.

To verify the implementation, close the database, check that the file exists in the app sandbox (e.g., /data/data/<package>/files/notesDB on Android or Library/Application Support/<bundleID>/notesDB on iOS), reopen it and run a SELECT to confirm the expected rows are returned. Inspect the ResultSet with isValidRow() and fieldByName() rather than assuming a count.