Choosing Ti.Database over Ti.App.Properties: A Practical Guide to Local Persistence in Titanium

When a Key‑Value Store Isn’t Enough

In early prototypes, Ti.App.Properties feels like a drop‑in replacement for local storage. It’s great for a handful of flags or a user preference string, but once you need to keep dozens or hundreds of records, the JSON blob you store in a single property grows fast, consumes memory, and makes filtering a manual, error‑prone loop.

For structured data, Titanium offers Ti.Database, a thin wrapper around SQLite. Combined with Titanium.Utils for data conversion, it lets you keep your data in a relational store, query it efficiently, and sync it with remote services.

Designing a Minimal SQLite Schema with Ti.Database

The first engineering decision is the schema. SQLite is schema‑first: you must create tables and define column types. A typical table for a note‑taking app might look like this:

var db = Ti.Database.open('notes.db');
var create = 'CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, body TEXT, created_at INTEGER);';
db.execute(create);

Running Ti.Database.open creates the file notes.db in the app’s sandbox if it doesn’t already exist. All subsequent operations use this db handle.

Inserting Records

When inserting, parameter binding protects against SQL injection and avoids manual string escaping. Titanium’s execute accepts placeholders (question marks) and a list of values:

var insert = 'INSERT INTO notes (title, body, created_at) VALUES (?, ?, ?);';
var now = new Date().getTime();
var title = 'My First Note';
var body = 'Hello, world!';

// Execute with bound parameters
var result = db.execute(insert, title, body, now);

Because execute is synchronous, keep the statement short. Heavy inserts in a loop should be wrapped in a transaction:

db.execute('BEGIN');
for (var i = 0; i < 1000; i++) {
    db.execute(insert, 'Title ' + i, 'Body ' + i, now + i);
}
db.execute('COMMIT');

Querying and Reading Results

Querying returns a Ti.Database.ResultSet object. Call next() to iterate rows, and getString, getInt, etc., to read individual columns:

var rows = db.execute('SELECT id, title, body, created_at FROM notes WHERE created_at > ?', now - 86400000); // last 24h
while (rows.isValidRow()) {
    var note = {
        id: rows.fieldByName('id'),
        title: rows.fieldByName('title'),
        body: rows.fieldByName('body'),
        createdAt: rows.fieldByName('created_at')
    };
    console.log(JSON.stringify(note));
    rows.next();
}
rows.close();

Using Titanium.Utils for Data Conversion

When you need to push data to a REST API or store a complex object in a single column, Titanium.Utils offers efficient JSON helpers. While native JSON.stringify works, Ti.Utils.stringify can handle large objects faster and is guaranteed to be consistent across Titanium runtimes.

var obj = {foo: 'bar', array: [1,2,3]};
var json = Ti.Utils.stringify(obj);
// Store in DB
var jsonInsert = 'INSERT INTO notes (title, body, created_at) VALUES (?, ?, ?);';
db.execute(jsonInsert, 'JSON Note', json, now);

Performance Trade‑Offs

| Feature | Ti.App.Properties | Ti.Database | |---|---|---| | Data Volume | < 1 KB per key | GBs of structured data | | Query Speed | Manual filtering | SQL with indexes | | API Complexity | Simple get/set | SQL statements | | Threading | Main thread safe | Main thread sync (avoid heavy ops) | | Migration | Manual | Manual (no auto‑migration) |

For small, flat settings, Ti.App.Properties is unbeatable. For lists, relational data, or when you need to search or sort, Ti.Database shines. Remember that every execute call blocks the UI thread; heavy work should be off‑loaded to background threads or batched in transactions.

Schema Migration Strategy

Titanium does not ship a migration tool. A common pattern is to store a schema version in Ti.App.Properties and run an upgrade routine on app start:

var currentVersion = Ti.App.Properties.getInt('db_version', 1);
if (currentVersion < 2) {
    db.execute('ALTER TABLE notes ADD COLUMN updated_at INTEGER;');
    Ti.App.Properties.setInt('db_version', 2);
}

Practical Checklist Before Deploying Ti.Database

1. Run Ti.Database.open in app.js and catch exceptions.
2. Wrap all bulk inserts/updates in BEGIN/COMMIT transactions.
3. Index columns used in WHERE clauses: db.execute('CREATE INDEX IF NOT EXISTS idx_created_at ON notes(created_at);');
4. Close the ResultSet after iteration.
5. Keep a db_version property for migrations.

Conclusion: When to Pick Which Store

If your app only needs a handful of flags, Ti.App.Properties remains the simplest choice. When you start storing items like contacts, messages, or any data that benefits from relational queries or large volumes, switch to Ti.Database. Pair it with Titanium.Utils for efficient JSON handling, and always remember that synchronous calls can freeze the UI—use transactions, indexes, and background threads where appropriate.

With this approach, you’ll keep your Titanium app responsive, maintainable, and ready for the next feature you want to add.