Creating a SQLite database

There are many reasons SQLite has become the relational database of choice for mobile handsets. It is scalable, fast, written in native C, and very portable, and has the added benefit of an exceptionally small footprint.

Storing data locally and caching remote data can help speed up data access times in our applications. This is particularly important when mobile devices may have limited connectivity and bandwidth.

There are two ways to create and implement SQLite databases in your application: one is by creating the database in code using SQL, and the other is by copying and attaching an existing database to your app via the install method. In this recipe, we'll explain how to create a database via SQL statements.

How to do it...

Create a new JavaScript file called database.js, and type the following code at the top of your new file:

//create an instance of a database
module.exports = (function() {
  //create the database object
  var db = Ti.Database.open('mydb');
  db.execute('CREATE TABLE IF NOT EXISTS favorites (ID INTEGER  PRIMARY KEY AUTOINCREMENT, TITLE TEXT, LINK TEXT, DESCRIPTION TEXT)');

  return db;
})();

Now we add this line at the top of each Window from which we need to reference our database functions. Do this to both your recipes.js and favorites.js files:

var db = require('database');

How it works...

One of the great things about SQLite is the simplicity of its creation. In the preceding example code, you can see that we are not even performing a create database query anywhere. Simply attempting to open a database that does not exist (mydb in this case) tells the SQLite engine to create it automatically!

From here, we can create our SQL table using standard SQL syntax. In our case, we have created a table with an ID that is an auto-incrementing number, along with a title, link and description field. The latter three fields match the data being returned from our recipe's data source, so in the next section we can use this table to locally store our recipe data.

There's more...

Let's take a look at attaching a prepopulated database file.

Attaching a prepopulated database file

Should you wish to create your database separately and attach it to your application at runtime, there is a method for you called Ti.Database.install(). Implementing this method is very easy, as it just accepts two parameters: the database file and the database name. Here is an example:

Var db = Ti.Database.install('data.db', 'packtData');

There are also numerous free SQLite applications for creating and managing SQLite databases. The open source SQLite DB Browser tool is freely available at http://sqlitebrowser.org/ and runs on Windows, Linux, and Mac OS X.