Appcelerator Blog

The Leading Resource for All Things Mobile

How-To: Perform CRUD operations on a local database

0 Flares 0 Flares ×

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 ='todos');
//Initialize the database
  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()) {
      id: resultSet.fieldByName('id'),
      todo: resultSet.fieldByName('todo')

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!

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- 0 Flares ×


  1. sj101

    Sweet! I submitted this :)

    Pepe Le Pew, build from the head.

  2. Alan Bourke

    Great example, thanks.

  3. jhill

    I just checked the Q&A and found nothing useful (pertaining to the following question) so I’ll ask it here since @sj101 brought it up :)

    Are there instructions anywhere on how to “build from the head”?

    Thanks in advance!

  4. James Wragg

    I use a similar technique in my current iPhone app.
    I also couple some of my tableviews with update events that are fired from the del, create & update methods you’ve demonstrated above. This allows me to automatically update tableviews in other windows that aren’t currently in view, adding a huge perceived performance increase as you don’t have the redraw on focus etc.

  5. Jeremy Raines

    So, I take it you only have to call .close() after SELECT operations?

  6. Wayne

    Thanks for this, it’s awesome. I am having some trouble tho implementing it and have posted a question on the Q&A if anyone reading this blog can help I would be very grateful. here’s the Q&A link.

  7. William Xue

    Great tutorial!

    Awesome technique and work-through.

    Thanks a lot for sharing.

  8. Great post Kevin,

    2 quick questions if it’s OK – should there be a single database connection opened per context? I’ve had a few issues (trying to be a good bunny) closing the at the end of every ‘data access’ function.

    And is the connection closed automatically when the window is closed? Putting db.close just before win.close had caused some problems (I think)


  9. Kevin Whinnery

    One database connection per context is a good rule. You should be able to keep the database connection open, though – it ought to be destroyed when the context is destroyed, since Titanium cleans up pretty aggressively when that happens.

  10. I’ve noticed that when trying the code in ‘Desktop’ I have to use this for anyone that tries it out.

    var conn =‘todo’); // This does not seem to work
    // I must use this below instead
    var adp = Titanium.API.Application.getDataPath();
    var dbpath = adp+””+”todo.db”;
    var conn = Titanium.Database.openFile(dbpath);

  11. Hey

    Thanks for the great post! Quick question, is it possible for titanium web view to share a database with mobile safari?

  12. Kevin Whinnery

    @Chris G – not that I’m aware of :(

  13. Thanks Kevin, that makes sense.

Comments are closed.

Sign up for updates!

Become a mobile leader. Take the first step to scale mobile innovation throughout your enterprise.
Get in touch
computer and tablet showing Appcelerator software
Start free, grow from there.
0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- 0 Flares ×