Using SQLite databases in iOS apps

Recently I’ve been working on an application that has to store a significant amount of data on a device. When storing data on the iPhone there are a few options.  You can use straight up text or xml files that are then read when the app runs, you can use the Core Data framework that comes with iOS, or you can use an SQLite database that is then deployed with the app.

My choice was very straightforward since we needed to store relatively complex data and would like the possibility of building a similar app for other platforms (Android and Windows Phone 7 also support SQLite databases).

Creating the Database

There are all kinds of tools with which to create your SQLite database, and you will easily find a handful through a Google search.  We will stick with the command line SQLite3 utility that comes with your Mac. To start building your database, open a terminal window and start the sqlite3 utility.  Pass it one argument – the name of the database file you want to create or edit.  The ‘Terminal’ application can be found in your ‘Applications/Utilities’ folder.

This will both create or open your database file, and start the console application to work with your database. We can know create our tables and possibly insert data using SQL queries.

The sqlite3 utility automatically saves your data to the file; when you are done, close the utility with Ctrl+Z.  Now we’re ready to add our database to our app.

Adding the Database to your app

You can now add the file to your iOS project in Xcode.  Be sure that you copy the file; do not reference your original database.  This is especially important for later updates.

The file is now included in your app, but cannot be used for storing data yet. To achieve this you need to copy the database file to the documents folder for your app on the device. The best place to do this is when you start the app; we check whether the database already exists at the location, and if not, we add it. It is easy to make a single class containing all database related functions.  These two functions are in the ‘DBAccess’ class.  Make them static/class functions so they can be used easily from AppDelegate without having to create an instance of the class where it is not needed.

Using the Database in your app

Now that we have added the file to our project we are ready to use it.  First we need to add the sqlite3 library to our project. In Xcode, navigate to your project in the file explorer, select the appropriate target, go to the ‘Build Phases’ tab and expand the ‘Link Binary with Libraries’  field, then add (+) the ‘libsqlite3.0.dylib’ library to your project. Next you have to import the ‘sqlite3.h’ header in the class where you are going to use it with the following line of code: #import <sqlite3.h>

Now, assuming we have a Patient object in our app, let’s query our database for some patients.

First we create a sqlite3 object which we’ll pass to the sqlite3_open() function to open our database. Note that the sqlite3 library is a C library, therefore the functions you will be using are in the traditional C format.  This also means we need to use character arrays instead of NSString objects.  Luckily, the NSString  class has a method to help us with that: UTF8String .

Next is the query.  As you might have noticed, we added the date of birth into an INTEGER field.  SQLITE does not know a DATE or DATETIME type, but it allows dates to be stored as TEXT or INTEGER and provides the functions to process those properly (more about this in next week’s follow up).

The steps for querying the database are as follows:

1. Create a prepared statement object with the sqlite3_prepare_V2 function
2. Check that the return value is SQLITE_OK; if it is, continue processing your statement
3. Execute the statement using sqlite3_step(); this function returns one row at a time and will return the value SQLITE_ROW when a row is returned in the statement object
4. Store the values returned in a Patient object
5. Repeat step 3 and 4 as long as SQLITE_ROW is returned
6. To clean up the statement, call sqlite3_reset and sqlite3_finalize functions on the statement; the first resets the statement so it can be used again, the second will destroy it

For further documentation, be sure to check the SQLite website about statements.

In the example app, the next step is to print the names of the patients.  You can download the example and play with it to try out the database. Download it here.