I'll admit it, backend development can occasionally be intimidating. Often times frontend developers are more concerned with the UI or UX of their web site or app and leave the heavy data lifting to the backend developers. However, in a short amount of time I'm going to show you how you can leverage the power of a relational database inside your hybrid mobile app.
If the words "database" or "SQL" strike fear into your heart, or even if you are somewhat comfortable in this realm, this is the blog post for you. Databases are the foundation of every data-driven app out there. And while you can certainly write an amazing app without knowing any SQL, I'm here to show you how you can simply and easily set up and manage a lightweight, but very powerful, database within your own hybrid mobile app using the Cordova SQLite plugin.
If you are writing a hybrid mobile app and want to store, manage, and retrieve data in an efficient and reliable manner, SQLite is for you. There are certainly alternatives (like cloud storage), but you can't deny the raw speed of dealing with data locally (not to mention the offline experience). So what exactly is SQLite? As explained on the SQLite
...a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
This means that we have at our disposal a powerful relational database that is easy to set up and doesn't require a server to run. The database is created within your app and runs within the context of your app. In no
Luckily for us, a group of kind-hearted individuals created a plugin for Apache Cordova that makes setting up and interfacing with
While not a mandatory step, you may be curious to read more about the Cordova SQLite plugin. There is separate documentation for the iOS and Android versions.
At this point we know what SQLite is, so
Go ahead and create a new project from the Graphite or Mist Dashboard. I am going to assume you have a certain familiarity with one of the two
The first thing I'm going to do is include the SQLite plugin in my app. Navigate to your project properties by double-clicking on the Properties option in the Project Navigator.
In your project properties, navigate to the Plugins pane, check the box next to SQLite, and then hit the save button. That's it! Your project is now configured to use the SQLite plugin.
When your mobile app initializes, you're going to want to create your SQLite database. To do this we are going to add an event listener that will execute a JavaScript function when Cordova has told us that the device is ready:
document.addEventListener("deviceready", init, false);
var app = {};
app.db = null;
app.openDb = function() {
if (window.sqlitePlugin !== undefined) {
app.db = window.sqlitePlugin.openDatabase("My Database");
} else {
// For debugging in simulator fallback to native SQL Lite
app.db = window.openDatabase("My Database", "1.0", "Cordova Demo", 200000);
}
}
function init() {
app.openDb();
}
At this
We have a blank SQLite database, but we need a place to insert some records. An important note about SQLite - it is different from other relational databases in that it does not enforce data types. So, in theory, you could insert text into an integer column. From the SQLite FAQ:
This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Any data can be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)
app.createTable = function() {
app.db.transaction(function(tx) {
tx.executeSql("CREATE TABLE IF NOT EXISTS MyTable (id INTEGER PRIMARY KEY ASC, text_sample TEXT, date_sample DATETIME)", []);
});
}
In this
We can add this to our init function to create the table as soon as our database is created.
function init() {
app.openDb();
app.createTable();
}
We have a database, we have a table, but we don't have any data.
app.insertRecord = function(t) {
app.db.transaction(function(tx) {
var cDate = new Date();
tx.executeSql("INSERT INTO MyTable(text_sample, date_sample) VALUES (?,?)",
[t, cDate],
app.onSuccess,
app.onError);
});
}
This example shows us how we can pass a parameterized query to the SQLite engine. If you remember, we created the id field as a primary key
You'll also notice that we have two other functions called, app.onSuccess and app.onError. You can probably guess that those are called when the result of the insert was successful or if it failed. For now you can just log those to the console like this:
app.onSuccess = function(tx, r) {
console.log("Your SQLite query was successful!");
}
app.onError = function(tx, e) {
console.log("SQLite Error: " + e.message);
}
Once we have data in there, we're probably going to want to update it at some point.
app.updateRecord = function(id, t) {
app.db.transaction(function(tx) {
var mDate = new Date();
tx.executeSql("UPDATE MyTable SET text_sample = ?, date_sample = ? WHERE id = ?",
[t, mDate, id],
app.onSuccess,
app.onError);
});
}
If you understood the insertRecord function, this one should make sense as well. We're
You should be getting the hang of it by now (especially if you are at all familiar with SQL!). Deleting a record is just as easy - and oh so powerful:
app.deleteRecord = function(id) {
app.db.transaction(function(tx) {
tx.executeSql("DELETE FROM MyTable WHERE id = ?",
[id],
app.onSuccess,
app.onError);
});
}
We've gone over how to get data into the database, updated, and removed.
app.selectAllRecords = function(fn) {
app.db.transaction(function(tx) {
tx.executeSql("SELECT * FROM MyTable ORDER BY id", [],
fn,
app.onError);
});
}
This is a little different, as we have to send a callback function (the variable fn) along to do something with the data after the query is executed. Here is an example of how you could call the above query and do something with the data:
function getAllTheData() {
var render = function (tx, rs) {
// rs contains our SQLite recordset, at this point you can do anything with it
// in this case we'll just loop through it and output the results to the console
for (var i = 0; i < rs.rows.length; i++) {
console.log(rs.rows.item(i));
}
}
app.selectAllRecords(render);
}
We
We've gone over how we can easily include a SQLite database in an Icenium project. We've also seen examples of how to insert, update, delete, and select data from the database. You can also use the Icenium SQLite sample as a way to start your next project - it contains everything we discussed and a little more to get you on your way. I hope you've seen how easy it is to use a SQLite database with your next Icenium project!
A maker at heart and a supporter of the open web, Rob is Developer Relations Lead at Blues Wireless. You can find Rob rambling incoherently on Twitter @RobLauer.