Insert an Array to an SQLite Database in Appcelerator

When it comes to building out your mobile application chances are you need to store data, the fastest and easiest way in Appcelerator is through an SQLite database. This is a very simple and light weight solution that provides an easy way to store data. The problem is it can be somewhat hard to work with when writing javascript based queries. Here are some useful snippets that I have collected to make using SQLite a little easier.

Inserting a javascript array into SQLite

Many times you will run into a situation where you need to add an array to the database. A real world example of this is if you need to tag articles you will probably create an array of tags and then want to add them to your database table that contains all your tags.

So to start lets say you have an array.

var tagsArray = ['cool','new','world'];

For each of these tags in our array we want to insert a new table row for each tag. The problem is you need to make a dynamic sql query for each array item. The solution is through BEGIN IMMEDIATE TRANSACTION. This allows you to make multiple queries without locking up your database. When you have finished looping through your array you can close out and commit all the queries at once. This is done with COMMIT TRANSACTION. You can see an example below.

db.execute("BEGIN IMMEDIATE TRANSACTION");
for (var d=0;d var id = tagsArray[d];{
     Ti.API.info('the id: '+ id);
     var tagId = db.execute('SELECT ROWID FROM tags WHERE tag = ?', id);
     var tag = tagId.fieldByName('ROWID');
     Ti.API.info('the tag id: '+ tag);
     tagIdArray.push(tag);
}
db.execute("COMMIT TRANSACTION");

This is a nice and simple way to insert an array into your SQLite database. Hope this was helpful :)