How-To: Perform CRUD operations on a local database

One of the most requested items from our Skribit widget was a step by step example of how to do database operations in Titanium. Well, ask and ye shall receive! If you haven’t noticed it already, you can suggest topics for us to include on this blog by using the Skribit widget on the left-hand side of the blog home page (black box marked “Suggestions”). Heeding the will of the people, I thought I would write up a quick example of how to do Create, Read, Update, and Delete (CRUD) operations against a local database in Titanium. This example was written for Titanium Mobile, but the code works with minimal modifications in Titanium Desktop as well.

In this example, we will implement our database operations using the Module pattern – here’s a good tutorial if you’re not familiar with this useful JavaScript design pattern. We will implement standard CRUD against a local database that we will create upon initialization. If you’d prefer to just look at the code (it is well commented to show what is going on), it’s embedded from this Gist at the end of the article – just drop it in app.js to see it log messages to the console in a Titanium Mobile application. For more explanation on using a local database with Titanium Mobile, read on.

Database Basics

Titanium Mobile gives you low level access to a SQLite database. Most of your interaction with a local database (docs) will be through raw SQL, which you run against your database with the execute function. SQLite is a full featured SQL database, although it does have more limited data types than you might be used to. For more information on SQLite, please refer to their reference documentation. If you are brand new to programming with SQL, W3 Schools has a nice tutorial which starts at the very beginning.

Creating a Local Database

You have two options for creating a local database. You can either create one inline using SQL (as in the example code below), or you can use a pre-populated SQLite database in your Resources directory by calling Ti.Database.install. If you use the “install” method, don’t worry – it is safe to call multiple times. After the first time the database is loaded, subsequent calls will use a persistent version of the database in your application’s data directory.

Working with a Database

As I mentioned earlier, interaction with a database through the Titanium.Database API is typically done through raw SQL statements. You will use CREATE, UPDATE, INSERT, and DELETE actions to modify the contents of your database. For every action you execute against the database, you will receive a Result Set. The result set object allows you to iterate over the result (or results) of your query, and pull data out of the query. If you do iterate over the results of a query, it is important to call “close” on the result set when you are finished, as in the example.

Example Code: TODO List

In the sample code below, we define a JavaScript module which performs operations on our database. When the module is created initially, it populates the database with the tables it needs if they do not exist. Note that the slash in the SQL string simply denotes a multi-line JavaScript string:

//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)');

In this case, we have just a single table with TODO items in it. We also maintain a reference to our application database which we can use in all of our API functions. Our public API has operations to read, update, create, and delete records in our database. When working with a ResultSet, you can get properties from the table row using fieldByName:

//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();

This example shows how one might approach implementing database operations in a Titanium application. Typically, you would want to separate database logic into a JavaScript file that you could include in multiple windows, so that all your database interaction is contained in one place. The complete source code for this simple application is found below – to run it, simply copy the code into an app.js file in a brand new Titanium project. There is no UI for this application, so check the console in Titanium Developer for the results of the tests found at the bottom of the script. Hope that helps!