iPhone Database Storage Using SQLite3

iPhone Database Storage Using SQLite3


For simple iPhone applications, you can write the data you want to persist to a simple text file. For more structured data, you can use a property list. For large and complex data, it is more efficient to store them using a database. The iPhone comes with the SQLite3 database library, which you can use to store your data. With your data stored in a database, your application can populate a Table view or store a large amount of data in a structured manner.

Using SQLite3

To use a SQLite3 database in your application, you first need to add the libsqlite3.dylib library to your Xcode project. Use the following Try It Out to find out how. You will need to download the code files indicated for this and the rest of the Try It Out features in this chapter.

This project [Databases.zip] is available for download at Wrox.com.

Try It Out Preparing Your Project to Use SQLite3

1. Using Xcode, create a new View-based Application project and name it Databases.

2. Right-click the Frameworks folder in your project and choose Project ? Add to Project from the menu bar.

3. Navigate to /Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator<version>.sdk/usr/lib and select the file named libsqlite3.dylib.

NOTE: <version> represents the version of the iPhone SDK you are using. For example, if the version you are using is 3.1, the path is /Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator3.1.sdk/usr/lib.

4. When the Add dialog is displayed, deselect the Copy Items into Destination Group’s Folder (If Needed) check box, and for the reference type, select Relative to Current SDK (see Figure 12-1).

Figure 12-1

5. In the DatabasesViewController.h file, declare a variable of type sqlite3 as well as a method named filePath (see the code in bold):

#import <UIKit/UIKit.h>

#import "sqlite3.h" 

@interface DatabasesViewController : UIViewController {

    sqlite3 *db;



-(NSString *) filePath; 


6. In the DatabasesViewController.m file, define the filePath method as shown in bold:

#import "DatabasesViewController.h"


@implementation DatabasesViewController


-(NSString *) filePath {

    NSArray *paths = NSSearchPathForDirectoriesInDomains(

                         NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *documentsDir = [paths objectAtIndex:0];

    return [documentsDir stringByAppendingPathComponent:@"database.sql"];

- (void)viewDidLoad {

    [super viewDidLoad];




How It Works

To work with SQLite3, you need to link your application to a dynamic library called libsqlite3.dylib. The libsqlite3.dylib that you selected is an alias to the latest version of the SQLite3 library. On an actual iPhone device, the libsqlite3.dylib is located in the /usr/lib/ directory.

To use a SQLite database, you need to create an object of type sqlite3:

    sqlite3 *db;

The filePath method returns the full path to the SQLite database that will be created in the Documents directory on your iPhone (within your application’s sandbox):

-(NSString *) filePath {

    NSArray *paths = NSSearchPathForDirectoriesInDomains(

                         NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *documentsDir = [paths objectAtIndex:0];

    return [documentsDir stringByAppendingPathComponent:@"database.sql"];


Creating and Opening a Database

After the necessary library is added to the project, you can open a database for usage. You will use the various C functions included with SQLlite3 to create or open a database, as demonstrated in the following Try It Out.

Try It Out Opening a Database

1. Using the same project created previously, define the openDB method in the DatabasesViewController.m file:

#import "DatabasesViewController.h"


@implementation DatabasesViewController


-(NSString *) filePath {




-(void) openDB {

    //—create database—

    if (sqlite3_open([[self filePath] UTF8String], &db) != SQLITE_OK )



        NSAssert(0, @"Database failed to open.");


- (void)viewDidLoad {

    [self openDB];

    [super viewDidLoad];




How It Works

The sqlite3_open() C function opens an SQLite database whose filename is specified as the first argument:

[[self filePath] UTF8String]

In this case, the filename of the database is specified as a C string using the UTF8String method of the NSString class because the sqlite3_open() C function does not understand an NSString object.

The second argument contains a handle to the sqlite3 object, which in this case is db.

If the database is available, it opens the database. If the specified database is not found, a new database is created. If the database is successfully opened, the function will return a value of 0 (represented using the SQLITE_OK constant).

The following list from http://www.sqlite.org/c3ref/c_abort.html shows the result codes returned by the various SQLite functions:

#define SQLITE_OK           0   /* Successful result */

#define SQLITE_ERROR        1   /* SQL error or missing database */

#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */

#define SQLITE_PERM         3   /* Access permission denied */

#define SQLITE_ABORT        4   /* Callback routine requested an abort */

#define SQLITE_BUSY         5   /* The database file is locked */

#define SQLITE_LOCKED       6   /* A table in the database is locked */

#define SQLITE_NOMEM        7   /* A malloc() failed */

#define SQLITE_READONLY     8   /* Attempt to write a readonly database */

#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/

#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */

#define SQLITE_CORRUPT     11   /* The database disk image is malformed */

#define SQLITE_NOTFOUND    12   /* NOT USED. Table or record not found */

#define SQLITE_FULL        13   /* Insertion failed because database is full */

#define SQLITE_CANTOPEN    14   /* Unable to open the database file */

#define SQLITE_PROTOCOL    15   /* NOT USED. Database lock protocol error */

#define SQLITE_EMPTY       16   /* Database is empty */

#define SQLITE_SCHEMA      17   /* The database schema changed */

#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */

#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */

#define SQLITE_MISMATCH    20   /* Data type mismatch */

#define SQLITE_MISUSE      21   /* Library used incorrectly */

#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */

#define SQLITE_AUTH        23   /* Authorization denied */

#define SQLITE_FORMAT      24   /* Auxiliary database format error */

#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */

#define SQLITE_NOTADB      26   /* File opened that is not a database file */

#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */

#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */

Examining the Database Created

If the database is created successfully, it can be found in the Documents folder of your application’s sandbox. As discussed in Chapter 13, you can locate the Documents folder of your application on the iPhone Simulator in the ~/Library/Application Support/iPhone Simulator/User/Applications/<App_ID>/Documents/ folder. Figure 12-2 shows the database.sql file.

Figure 12-2

This article is excerpted from chapter 12 "Database Storage Using SQLite3" of the book Beginning iPhone SDK Programming with Objective-C by Wei-Meng Lee (ISBN: 978-0-470-50097-2, Wrox, 2010, Copyright Wiley Publishing Inc.)



2 Responses to “iPhone Database Storage Using SQLite3”

  1. Anonymous says:

    can any 1 tell me about the iphone database best practices regarding selecting inserting deleting updating … ?

  2. Anonymous says:

    Hey all,
    we are starting a beta pilot programme for a platform that could help with this problem. 

    It’s called Kumulos and it removes all the nitty-gritty, repetitive stuff associated with creating database driven apps for iPhone.

    You can create hosted online databases for iPhone apps – without writing APIs, designing complex data structures or wasting time with servers and hosting.

    We’re looking for beta testers and would love to hear your thoughts on this,

    Check it out here: www.kumulos.com



Leave a Reply

What is 11 + 4 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)