Using SQLite in your IOS application part II

In my previous article Using “SQLite in your IOS application Part I” introduced a new sample application that utilized an IOS static library containing the latest SQLite amalgamation source code.  In the sample application, a pair of classes were created for wrapping the basic SQLite functionality into Objective-C.  These classes were used to create and open a database.  Now that we have that database created and we have the code to open and close it, now we need to add the basic operations on a database.  In this article we will take the SQLiteManager and SQLiteObject classes and implement them in a better MVC architecture and add the capabilities to Add, Delete, and update records in a table.

SQLite classes

Just as a review, here are the classes that were introduced in the last article.  “SQLite in your IOS application Part I” introduced a pair of new classes that are designed to be used for SQLite database interaction.  That sample code really only covered the first class, the SQLiteManager which does not do that much.  In this sample code we are focusing on the second class, the SQLiteObject.  This class is really the main part of the application.

SQLite Manager

The SQLite manager class is a simple class that creates the pointer to the SQLite database instance.  The SQLiteManager class manages the database connection while the SQLiteObject class manages the queries against the database.  This class controls the initialization of the database on the first run of the application and maintains that connection for the lifetime of the application.  In the last example, we opened and closed the database connection on demand.  While this does work, leaving the connection open avoids hitting the file system.  It is possible to have multiple database connections open at one time, so tracking which database connection you queried your object against is important. The SQLiteObject is designed to track the open database connection because that object works directly with the data stored within the database.

@interface SQLiteManager : NSObject

+(void) initializeDatabase;
+(NSString *) databasePath;
+(sqlite3 *) newConnection;
+(sqlite3 *) newConnectionFromFilename:(NSString *)databaseFilePath;
+(void) closeConnection:(sqlite3 *)database;
SQLiteObject methods
  • +(void) initializeDatabase – This method initializes the database on the first run.  Inside this method we create the database file and populate the database with a table and add records to the table for this sample.
  • +(NSString *) databasePath – A method to return the absolute path to the database file on the IOS device.
  • +(sqlite3 *) newConnection – Open a new connection to the database.  If the database file has not been initialized, this will create it.  This will cause the initializeDatabase to fail if called before the database is initialized.
  • +(sqlite3 *) newConnectionFromFilename:(NSString *)databaseFilePath – open a database located at the absolute path in databasePath.
  • +(void) closeConnection:(sqlite3 *)database – close an open database connection.


The SQLite object class is a class that queries the database and tracks individual records retrieved from the database.  By itself, the SQLiteObject class does not contain any methods that directly query against the database connection held by its database property.  The real functionality resides within the class object that derives off this base class.  In this example, we have the Person Class which is derived from this SQLite class.  In the previous example the person object was not modified to run any queries against the database, we only used it to track the open connection and the primary key of the record.  Now we are going to add more methods to complete the missing database functionality.

@interface SQLiteObject : NSObject {
    sqlite3 *_database;
    NSInteger _primaryKey;
    BOOL      _dirty;
@property (assign, nonatomic, readonly) NSInteger primaryKey;
@property (nonatomic, readonly) sqlite3 *database;
@property (nonatomic, assign) BOOL dirty;

-(id) init;
-(id) initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db;
-(BOOL) isConnected;

SQLiteObject Properties
  • primaryKey – the primary key on this record item.
  • database – The database connection handle used to query this record.
  • dirty – Boolean flag to determine if the data on this object has changed.
SQLiteObject methods
  • -(id) init – initialize a blank SQLiteObject
  • -(id) initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db – initialize a new SQLiteObject with a primary key and database connection handle.
  • -(BOOL) isConnected – returns YES if the primary key and database connection handles are valid.

Person Class

The Person class contains the data retrieved from each record.  It is derived from the SQLiteObject class and it contains properties that define each record value retrieved from the database.  The Person object is a very small and light weight object.  It is designed to be small in size so it can easily and efficiently passed around to the different views that manipulate each object instance.

@interface Person : SQLiteObject

@property (nonatomic, copy) NSString *first;
@property (nonatomic, copy) NSString *last;
@property (nonatomic, copy) NSString *phone;

Person Properties
  • primaryKey – the primary key inherited from SQLiteObject
  • database – The database connection handle inherited from SQLiteObject.
  • dirty – Boolean flag inherited from SQLiteObject.
  • first – Contacts first name as a NSString
  • last – Contacts last name as a NSString
  • phone – Contacts phone as a NSString


Person Categories

In the last article “SQLite in your IOS application Part I“, we modified the Person class so it was derived from a SQLiteObject. All this did was to make the Person class SQLite aware, but no methods were implemented for the person class to access the database. At this point, the Person class tracks the primary index of the object in the SQL result set, and the opened database connection that was used in the last query.  The real functionality behind the person object is provided via categories.  Objective-C categories offer a good way to implement the database functionality we are looking for while keeping the base Person object lean and mean which helps keep our memory usage down.

Categories and SQLite operations

Categories are used to break down the  SQL operations into functional groups of selectors that are only defined within the scope of the source file in which the category was included.   For this example, we are going to use the following categories.  Most of the methods implemented in the categories are static, only the update method is implemented off the current instance.

// from Person+AddNewPerson.h
@interface Person (AddNewPerson)
+(id)addNewPersonWithFirstName:(NSString *)firstName LastName:(NSString *)lastName andPhone:(NSString *)phoneNumber intoDatbase:(sqlite3 *)database;

// from Person+DeletePerson.h
@interface Person (DeletePerson)
+(void)deletePersonWithIndex:(NSInteger)index fromDatabase:(sqlite3 *)database;

// from Person+ReadPersons.h
@interface Person (ReadPersons)
+(NSMutableArray *)getAllPersonsFromDatabase:(sqlite3 *)database;

// from Person+WritePerson.h
@interface Person (WritePerson)

To implement each of these categories in the IOS application we need to include the appropriate header in the source file where we need the functionality.  Before we talk about how the categories are implemented in the IOS sample application, we need to talk about the application itself first.

Application design

This sample is written using a storybook that manages a series of table views.  We’re not going to spend a whole lot of time on how to build the storybook here.  If you are curious, you can check out this good blog I found on the subject. “Storyboards tutorial in IOS7“.  That blog article does a good job at describing the tasks required to create the storyboard application we want using UINavigationController with UITableViewControllers.

  • The Application delegate implements the ReadPerson and WritePerson categories.
  • The Contacts View controller  implements the Read and Delete categories
  • The AddContactViewcontroller  implements the Add New Person category.
  • The Update view controller implements the Write Person category.


Screen Shot 2014-03-22 at 11.15.48 AM

The storybook layout for the sample application

Creating the storyboards is one of the easier tasks in this sample application.  Storyboards are nice because they let you get a running prototype up quickly.  Once you have the prototype all we need to do is plug in the calls to the SQLite classes and objects and we have an app!

Opening the database

In this sample, we put the main database connection into the application delegate so we can handle the main IOS events for launching, entering the background, and termination.  The application delegate contains two private variables, the database connection pointer and the list of contacts retrieved from the last query into the database.

@implementation AppDelegate {
    NSMutableArray *_contacts;

    sqlite3 *_database; // the pointer to the open database connection.

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
    // initialize database on first run.
    [SQLiteManager initializeDatabase];

    _database = [SQLiteManager newConnection];  //open the database
    _contacts = [Person getAllPersonsFromDatabase:_database];

    UINavigationController *navigationController = (UINavigationController *)self.window.rootViewController;
    ContactsViewController *contactsViewController = [navigationController viewControllers][0];
    contactsViewController.contactList = _contacts;
    contactsViewController.database = _database;

    return YES;

Opening the database is easy, we first have to check to see if the database has been installed correctly, then we can initialize a new database connection.  To get the list of contacts from the database, we use the ReadPersons category from the Person object.

#import "Person+ReadPersons.h"

@implementation Person (ReadPersons)

+(NSMutableArray *)getAllPersonsFromDatabase:(sqlite3 *)database
    NSMutableArray *results = nil;
    if (database) {
        // now we have opened the database, retrieve some records.
        sqlite3_stmt *statement = NULL;

        // execute the Sql statement
        int error = sqlite3_prepare_v2(database, "SELECT idx, firstname, lastname, phone FROM Contacts", -1, &statement, NULL);

        if (error == SQLITE_OK) {
            // got some results, now parse the records returned.
            results = [[NSMutableArray alloc] init];

            while (sqlite3_step(statement) == SQLITE_ROW) {
                // get the primary key
                int primaryKey = sqlite3_column_int(statement, 0);

                // get the data
                NSString *firstName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
                NSString *lastName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
                NSString *phone = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];

                // insert into an object
                Person *contact = [[Person alloc] initWithPrimaryKey:primaryKey AndDatabase:database];
                if (contact) {
                    contact.first = firstName;
                    contact.last = lastName;
           = phone;

                    [results addObject:contact];
        // clean up the SQL statement when your done.
        statement = NULL;
    return results;

if for what ever reason you are not using ARC, then you need to be careful about handing the strings and other data that might have blocks of memory to move around. The string data returned by SQLite is managed by a different memory pool than the rest of your IOS environment.  Internally, the string data should be treated as SQLITE_TRANSIENT by default.  This means that we treat the strings as volatile, so we would need to retain the strings as they are extracted from the result set.  ARC takes care of this automatically.

Adding a new record

Adding a new record to the Contacts table is done through the Add Person View controller.  The AddContactViewController has 2 methods for handling the navigation bar button events for “Cancel” and “Done”.  The cancel action is self evident, all it does is close the view.  The done action performs the actual record insert before closing the view.

    // Create a new Person object and insert it into the database
    Person *person = [Person addNewPersonWithFirstName:self.firstName.text

    // cause the calling tableView controller to reubild it's data.
    [self.delegate addContactViewControllerDidAddContact:self didAddContact:person];


Inserting into the database with simple datatypes is easy enough. For this sample, we are just constructing a string that contains all the values within the string. This is impractical for large amounts of data in strings or blobs, but for now it will work.

#import "Person+AddNewPerson.h"

@implementation Person (AddNewPerson)

+(id)addNewPersonWithFirstName:(NSString *)firstName LastName:(NSString *)lastName andPhone:(NSString *)phoneNumber intoDatbase:(sqlite3 *)database
    Person *contact = nil;
    NSString *query = [NSString stringWithFormat:@"INSERT INTO Contacts (firstname, lastname, phone) VALUES ('%@','%@','%@')", firstName, lastName, phoneNumber ];
    int success = -1;
    sqlite3_stmt *statement = nil;

    int error = sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, NULL);
    if ( error != SQLITE_OK) {
        // error
        NSLog(@"Error failed to prepare sql with err %s", sqlite3_errmsg(database));
        success = sqlite3_step( statement );
        if ( success == SQLITE_DONE ) {
            // successful insertion into the database, now create the person object.
            success = (int)sqlite3_last_insert_rowid(database);
            contact = [[Person alloc] initWithPrimaryKey:success AndDatabase:database];
            contact.first = firstName;
            contact.last = lastName;
   = phoneNumber;

    return contact;

Deleting a record

Deleting a record is done through deleting table cells on the contacts view.  The edit button enables or disables the UITableView editing feature.  Once we have deleted a cell the application is notified with a call to the following method.

- (void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath
    if (editingStyle == UITableViewCellEditingStyleDelete) {
        // Delete the row from the data source
        int index = (int)indexPath.row;
        // get the person
        Person *person = (Person *)[_contactList objectAtIndex:index];
        if (person) {

            // delete the person from the database.
            [Person deletePersonWithIndex:person.primaryKey fromDatabase:person.database];
            // now remove it from the datasource for the tableview.
            [_contactList removeObject:person];
        [tableView deleteRowsAtIndexPaths:@[indexPath] withRowAnimation:UITableViewRowAnimationFade];

Deleting a record is easy. When the application commits the changes to the UITableView, we update the list of contacts, and with the retrieved person object, we delete the record from the database.  We use the primary key to identify the record to delete, and your done.

@implementation Person (DeletePerson)

+(void)deletePersonWithIndex:(NSInteger)index fromDatabase:(sqlite3 *)database
    const char *query = "DELETE FROM Contacts WHERE idx=?";
    sqlite3_stmt *statement = NULL;
    int error = sqlite3_prepare_v2(database, query, -1, &statement, NULL);
    if ( error != SQLITE_OK){
        // error
        NSLog(@"Error failed to prepare sql with err %s", sqlite3_errmsg(database));

    // bind the primary index to the statement
    sqlite3_bind_int(statement, 1, index);
    // execute the statement
    sqlite3_step( statement );
    // clean up
    statement = NULL;



Updating a record

Updating a record is a little more tricky. The SQLiteObject has a dirty flag that needs to be set before you can trigger an update. You can certainly hack the object and manually set the dirty flag when you want too. A better method is to overload the setter method when the property is synthesized.  Overloading the synthesized getter method lets us automatically set the dirty flag when we want to with little changes to the code.

Modify the person object

In the Person object we want to modify the source to include the overloads after creating synthesizer statements for each of the properties used in the person object.

@implementation Person

@synthesize first;
@synthesize last;
@synthesize phone;

// let the database connection know that something has changed
// by overloading the setters that are created by the @systhesize.
// now we can update the database anytime values change in the object.

-(void) setFirst:(NSString *)value
    if (first == nil) {
        first = value;
    } else if ([first compare:value] != NSOrderedSame) {
        first = value;
        self.dirty = YES;

-(void) setLast:(NSString *)value
    if (last == nil) {
        last = value;
    } else if ([last compare:value] != NSOrderedSame) {
        last = value;
        self.dirty = YES;

-(void) setPhone:(NSString *)value
    if (phone == nil) {
        phone = value;
    } else if ([phone compare:value] != NSOrderedSame) {
        phone = value;
        self.dirty = YES;

That’s all the changes we need to support the updating of records in the Person object. Now anytime one of the properties changes in value, we set the dirty flag for that object instance. We do not set the dirty flag when the property is nil because that is the first assignment when the current object instance is first created. This only happens on the first application load, or when a new object is added to the database.

@implementation Person (WritePerson)

    int error = SQLITE_OK;

    if ([self isConnected] && _dirty) {
        sqlite3_stmt *statement = NULL;
        const char *query = "UPDATE Contacts SET firstname = ?, lastname = ?, phone = ? WHERE idx = ?";
        error = sqlite3_prepare_v2(_database, query, -1, &statement, NULL);
        if (error != SQLITE_OK) {
            NSLog(@"Error failed to prepare sql with err %s", sqlite3_errmsg(_database));
            // bind the data to the query.  We need to make sure that we label the string
            // data as SQLITE_TRANSIENT so that SQLite knows how to handle the memeory.
            // the order of the binding is the order of the values on the update query string.
            sqlite3_bind_text(statement, 1, [self.first UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 2, [self.last UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 3, [ UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(statement, 4, self.primaryKey);

            // execute the query
            error = sqlite3_step(statement);
            if (error != SQLITE_DONE) {
                NSLog(@"Error failed to save with err %s", sqlite3_errmsg(_database));
            } else _dirty = NO;  // data was written to the database, reset dirty flag.
    return error;

Updates are used to save the list of objects in the application delegate when we receive moving into background and termination events.  This should not be necessary with the approach this example takes.  Any time that data is changed we immediately write the data to the database to prevent data loss.  That said, for redundancy we add some extra calls to update the contacts in the list stored in the application delegate.

- (void)applicationDidEnterBackground:(UIApplication *)application
// save out any updates in the contacts array. Any person object marked as dirty, will be updated to the database.
[_contacts makeObjectsPerformSelector:@selector(updateTheDatabase)];

- (void)applicationWillTerminate:(UIApplication *)application
// save out any updates in the contacts array. Any person object marked as dirty, will be updated to the database.
[_contacts makeObjectsPerformSelector:@selector(updateTheDatabase)];

// close the opened database connection
[SQLiteManager closeConnection:_database];
_database = NULL;

Updating all the objects is really easy with Objective-C and its fast enumeration. To flush the data out to the database, we call the updateDatabase selector on all the objects in the list. If any objects have been marked dirty, they are written out to the database.

That’s it! it is really easy to integrate SQLite into your IOS application. Included with this blog entry is a copy of the working project that you can take a look at. I would recommend that you look at the app, this blog was written in 3 languages. English, Objective-C, and C/C++. Everything is crystal clear in the computer languages, the english lost a bit in translation. so please take a look at the sample code

Source code is located on GitHub, SQLiteExamplePart2

Links & references

Programming With Objective-C – Customizing existing classes

Tutorial: Storyboards

Storyboard tutorial in IOS7


Using SQLite in your IOS applications Part I

Building SQLite in your IOS applications


About these ads

3 thoughts on “Using SQLite in your IOS application part II

  1. Pingback: Building SQLite into your IOS applications | conedogers

  2. Pingback: Using SQLite in your IOS applications part III | conedogers

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s