dbtest.js

// Using the JavaScript module pattern, create a persistence module for CRUD operations
// One tutorial on the Module Pattern: http://www.adequatelygood.com/2010/3/JavaScript-Module-Pattern-In-Depth
var db = (function() {
  
  //create an object which will be our public API
  var api = {};
  
  //maintain a database connection we can use
  var conn = Titanium.Database.open('todos');
  //Initialize the database
  conn.execute('CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, todo TEXT)');
  conn.execute('DELETE FROM todos'); //This will delete all data from our table - probably wouldn't do this in production ;)
  
  //Create a TODO - db.create(todo)
  api.create = function(text) {
    conn.execute('INSERT INTO todos (todo) VALUES(?)',text);
    return conn.lastInsertRowId; //return the primary key for the last insert
  };
  
  //List all TODOs - db.read()
  api.all = function() {
    var results = [];

    //Get TODOs from database
    var resultSet = conn.execute('SELECT * FROM todos');
    while (resultSet.isValidRow()) {
      results.push({
        id: resultSet.fieldByName('id'),
        todo: resultSet.fieldByName('todo')
      });
    	resultSet.next();
    }
    resultSet.close();

    return results; //return an array of JavaScript objects reflecting the todo
  };
  
  //Get a todo by a specific ID
  api.get = function(id) {
    var result = null;
    var resultSet = conn.execute('SELECT * FROM todos WHERE id = ?', id);
    if (resultSet.isValidRow()) {
      result = {
        id: resultSet.fieldByName("id"),
        todo: resultSet.fieldByName("todo")
      };
    }
    resultSet.close();
    return result;
  };
  
  //Update an exisiting TODO - db.update(todo)
  api.update = function(todoItem) {
    conn.execute("UPDATE todos SET todo = ? WHERE id = ?", todoItem.todo, todoItem.id);
    return conn.rowsAffected; //return the number of rows affected by the last query
  };
  
  //Delete a TODO - db.del(todo)
  api.del = function(id) {
    conn.execute("DELETE FROM todos WHERE id = ?", id);
    return conn.rowsAffected; //return the number of rows affected by the last query
  };
  
  //return our public API
  return api;
}());


// ****************************
// BEGIN DATABASE TEST EXAMPLES
// ****************************

// CREATE AND GET
var lastInsert = db.create("Pick up the milk"); // this should create a TODO
var ourTodo = db.get(lastInsert); //this should fetch the TODO we just created
Ti.API.info("Just created a TODO (Should be 'Pick up the milk'): "+ourTodo.todo);

// ALL
var anotherTodo = db.create("Conquer the world");
var all = db.all();
Ti.API.info("Records in database (Should be 2): "+all.length);

// UPDATE
ourTodo.todo = "Pick up Mountain Dew instead of milk";
var rowsAffected = db.update(ourTodo);
Ti.API.info("Rows affected (Should be 1): "+rowsAffected);
ourTodo = db.get(ourTodo.id);
Ti.API.info("Just got our updated record (Should be 'Pick up Mountain Dew instead of milk'): "+ourTodo.todo);

//DELETE
db.del(ourTodo.id);
var stillThere = db.get(ourTodo.id);
Ti.API.info("Just tried to get a record that aint there.  Result should be null: "+stillThere);

Hi Kevin,

thanks, this code is great. I also love to write my queries on multiple lines, however I'm getting warnings in the console:
[WARN] JavaScript compiler reported "Bad escapement." at home.js:32
[WARN] JavaScript compiler reported "Unclosed string." at home.js:32
[WARN] JavaScript compiler reported "Stopping, unable to continue. (6% scanned)." at home.js:32

I'm getting these warnings but everything is working as expected.

if you want to do multi-line strings in JavaScript, you would do:

var str = 'My cool \
string goes on \
multiple lines';

Thanks Kevin. Your example works as well but produces the same [WARN]ings in the console in Titanium Developer. This actually matters since the rest of the file won't be checked for errors.