Introduction
As a mobile developer it’s a common requirement to save data locally. While this can be as simple as saving a few user preferences, you may eventually meet a situation where supporting more complicated data is required. In a situation such as this, SQLite can be a good option to support the storage, security and performance of your mobile application’s data.
Understand the Limitations
The first step to using SQLite in your hybrid mobile app is to understand the limitations that it presents. When we think of a database we often think of large, high capacity systems worthy of serving the largest of datasets. Although SQLite in a mobile app is a very capable tool, it’s not a database server.
Using SQLite in a mobile environment requires careful planning and regular testing. You’ll find yourself constantly tuning and testing the environment to make sure the app remains responsive. The first key to success in using SQLite is that other than data integrity testing, it requires almost exclusive testing on your target devices to understand the true limitations it presents to your app.
Data Access Strategies
The first key decision you’ll often be faced with when using SQLite is how to ship the data with the app. There are two key ways you can accomplish this:
- Create and populate data dynamically in the app
- Ship the app with a pre-populated database
Create and Populate
Dynamically creating and populating a SQLite database will generally happen when your app has very little baseline or reference data. This is typically of apps that start with fresh table structures that are ‘filled in’ with use of the app.
Pre-populated
An app that uses a pre-populated database has an obvious need to reference a good amount of baseline data. This is typical in an app that doesn’t rely too heavily on remote web services, instead opting for local data access, resulting in maximum offline capability.
The use of a pre-populated database has some challenges to it. Although you can ship your app with a pre-populated database, you’ll still need to move the database file to the target environment’s data directory. These are different across platforms but luckily the Cordova API affords us with easy ways to access these locations. While this can be slightly painful to implement, the most painful part of all is that on android, it’s not possible to delete the original database, resulting in doubling the storage requirements of your app. For small apps, you may be able to get away with that but generally this is an unacceptable option.
To relieve the problem of shipping and moving a database, the only option left is to perform a direct download of your database from a remote location. This will obviously require your app to be in a connected state on initial download.
Understanding the Cordova Plugin Ecosystem
The version of Cordova plugin you choose will generally depend on the type of data access you want to employ (create/populate vs pre-populated). The most popular plugin for SQLite is Cordova-SQLitePlugin by Chris Brody https://github.com/brodysoft/Cordova-SQLitePlugin. The project is reasonably active and fits most needs. However if you do want to use a pre-populated database, this plugin doesn’t support the moving of the database to the proper system directories. There are plenty of resources that describe the native code modifications you’ll need to facilitate the moving of database files, however there is a plugin forked from Cordova-SQLitePlugin by Andrey Butov that will do this for you.
Your choice of plugin will have a number of implications on your project. For instance, one plugin forked from another may be any number of commits away from the other at any point in time. This puts the burden on you to keep a close watch for issues as you use your chosen plugin and patch as needed.
Security
Much like any other app that relies on a database, you’ll most certainly want to understand the security requirements surrounding your data. One of the key points to consider is that of encryption. Aside from rolling your own encryption routine, one of the well tested and proven extensions to SQLite to use for this task is SQLCipher, an open source library that will encrypt your SQLite database file. If you decide to use SQLCipher to handle the encryption of your project, you should be aware of the work involved. This work can be boiled down to three major steps; building, project integration and plugin modification.
Building
The first step is to build the SQLCipher binary. The process is straightforward and is outlined in the documentation (https://www.zetetic.net/sqlcipher/introduction/). Once you have built the SQLCipher binary, you’re able to use it to encrypt and decrypt your SQLite database file. This process takes just a few steps and is well documented in the API documentation (https://www.zetetic.net/sqlcipher/sqlcipher-api/#sqlcipher_export).
Project Integration
With your encrypted database ready to go, there are still a number of steps required to integrate and ship with your hybrid application. While SQLCipher’s documentation includes resources for iOS (https://www.zetetic.net/sqlcipher/ios-tutorial/) and Android (https://www.zetetic.net/sqlcipher/sqlcipher-for-android/) that you can use for guidance, sometimes it’s also best to inspect an example as well. There is an example application on Github (https://github.com/sqlcipher/SQLCipherSpeed/tree/Xcode5) that can serve as a particularly good example of integrating SQLCipher into an XCode 5 project.
Plugin Modification
The final step is to integrate SQLCipher with your Cordova plugin native code. Much of this is outlined in supporting resources for these plugins, but it is important that close attention is paid to the changes you’re making; Take the time to be familiar with it.
Once you’re up and running with your encrypted database, be sure to execute performance benchmarks on your queries. You can expect a certain amount of overhead when you add encryption to your data so it’s best to understand what the ‘new’ baseline performance of your app is.
Performance
Arguably the most important part of working with SQLite is performance. Again this is something that deserves regular testing and tuning. Although we can’t cover an exhaustive list of performance tips (better to go here for that (http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html), it is safe to say that a lot of performance problems can be accounted for with good data design. In the database world this usually means good design of your relational data but for our uses, we have more things to consider. For instance, the interface to our data is asynchronous which results in careful consideration of how data is organized. Performance is perhaps the biggest topic for which regular and repeated testing on actual devices yields the most benefit.
Conclusion
Using SQLite in your hybrid mobile application can be as simple or as complicated as you wish. While there is no single solution to every application, the security and performance tuning options afford a great deal of freedom for you to build your application to meet your needs.
