How to use a sqlite3 database in your iPhone app

March 8, 2009

Using a sqlite database in an iPhone app is actually really easy. The only real requirement to get started is that you need to create a sqlite database to include, which is very easy to do.

Step 1 - Create the Database

Make sure you have it installed. If you don't have it, it is available through MacPorts. Once you have it, just type on the command line:

sqlite3 test.sqlite3

This will create a new database called "test.sqlite3" and set you up in the sqlite prompt. You will need to create your tables here. For this example, just type:

CREATE TABLE iphone_test ( id INTEGER PRIMARY KEY, value VARCHAR(255) );

Now you need to put some data in it:

INSERT INTO iphone_test (value) VALUES ("This is value 1"); INSERT INTO iphone_test (value) VALUES ("This is value 2");

Not too bad so far. Now just type .quit and you are all set. Copy your database into the Resources folder of your Xcode project and you are ready to move on.

Step 2 -Prepare Your Project

You need to link the sqlite3 framework library to your project. All you need to do is right-click on the Frameworks folder and select Add > Existing Frameworks... then select:

/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.2.1.sdk/usr/lib/libsqlite3.0.dylib

Step 3 - Add Some Code to Your AppDelegate.h

Open up your AppDelegate.h and add an import for sqlite3.h so you can use sqlite. Then this declaration inside your @interface so you have a nice handle to the database:

sqlite3 *database;

Then add these prototypes for the methods we are adding to the AppDelegate.m:

- (void)verifyDatabase:(NSString *)databaseName databasePath:(NSString *)databasePath;

- (NSString *) randomRecordContent;

Step 4 - Add Some Code to Your AppDelegate.m

Open up your appDelegate.m and add this method in somewhere. This will make sure that the database file exists in the user's file store and if it doesn't, it will copy it over from your application code.

- (void)verifyDatabase:(NSString *)databaseName databasePath:(NSString *)databasePath {
 NSFileManager *fileManager = [NSFileManager defaultManager];  if (![filemanager fileExistsAtPath:databasePath]) {
  NSString *databasePathFromApp = [[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
  [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
 }

 [fileManager release];
}

Then add this code inside your applicationDidFinishLaunching method to get the location of the database in the users phone:

NSString *databaseName = @"test.sqlite3";
NSString *documentsDir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex: 0];
NSString *documentsPath = [documentsDir stringByAppendingPathComponent:databaseName];

Now call the verifyDatabase method to copy the database from your app code to the users file store in case it isn't already there.

[self verifyDatabase: databaseName databasePath:databasePath];

Open the database so you can use it throughout your application:

sqlite3_open([databasePath UTF8String], &database);

Don't forget to close the connection in your dealloc method:

sqlite3_close(database);

Finally, add in a method that will read something from the database. In this case, we are adding the implementation of randomRecordContent that we specified in the header.

-(NSString *) randomRecordContent {
 NSString *theValue;
 const char *sqlStatement = "select value from iphone_test order by random() limit 1";
 sqlite3_stmt *compiledStatement;
 if (sqlite3__prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
  while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
   theValue = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
  }
 }
 sqlite3_finalize(compiledStatement);

 return theValue;
}

Now you are all set. As long as you have a handle to your AppDelegate, you can call this method and grab something from the database. For example, if you are in your view controller you can get a handle and call the method like this:

YourAppDelegate yourAppDelegate = (YourAppDelegate *)[[UIApplication sharedApplication] delegate];
NSString theValue = [yourAppDelegate randomRecordContent];

So there you have it. Hopefully that code is pretty readable. Please post comments or e-mail me if you have any questions.