iPhone SDK: Setting up a SQLite database before first use

It’s quite common to use SQLite databases in iPhone apps to serve as the backend for your product. While there is a way to create the database file dynamically from your objective-c code, it’s way simpler to create it in your Mac development machine, add it to your Xcode project, and then simply write the code to copy the database file from your app bundle to your app’s document directory.

I use the following code in my projects to do just that:

- (NSString *)copyDBToFinalPath {
    NSString *originalDBPath = [[NSBundle mainBundle] pathForResource:@"database_filename_here" ofType:@"db"];
    NSString *path = nil;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *appSupportDir = [paths objectAtIndex:0];
    NSString *appBundleName = [[[NSBundle mainBundle] infoDictionary] objectForKey:@"CFBundleName"];
    NSString *dbNameDir = [NSString stringWithFormat:@"%@/%@", appSupportDir, appBundleName];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL isDir = NO;
    BOOL dirExists = [fileManager fileExistsAtPath:dbNameDir isDirectory:&isDir];
    NSString *dbPath = [NSString stringWithFormat:@"%@/database_filename_here.db", dbNameDir];
    if (dirExists && isDir) {
        BOOL dbExists = [fileManager fileExistsAtPath:dbPath];
        if(!dbExists) {
            NSError *error = nil;
            BOOL success = [fileManager copyItemAtPath:originalDBPath toPath:dbPath error:&error];
            if (!success) {
                NSLog(@"error = %@", error);
            } else {
                path = dbPath;
            }
        } else {
            path = dbPath;
        }
    } else if (!dirExists) {
        NSError *error = nil;
        BOOL success =[fileManager createDirectoryAtPath:dbNameDir attributes:nil];
        if (!success) {
            NSLog(@"failed to create dir");
        }
        success = [fileManager copyItemAtPath:originalDBPath toPath:dbPath error:&error];
        if (!success) {
            NSLog(@"error = %@", error);
        } else {
            path = dbPath;
        }
    }
    return path;
}

I use that function like so:

- (void)applicationDidFinishLaunching:(UIApplication *)application {
    NSString *dbPath = [self copyDBToFinalPath];
    self.db = [FMDatabase databaseWithPath:dbPath];
    if (![self.db open]) {
        NSLog(@"Could not open database.");
    }
    //[self.db setTraceExecution:YES];
    //[self.db setLogsErrors:YES];
 
    // ...
    // rest of my code here
    // ...
}

To create that original SQLite database file, just use the standard “sqlite3” command found in Mac OS X like so:

$ sqlite3 database_filename_here.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .read ./schema.sql

The “schema.sql” file is where I store my table definitions, and standard inserts.

1 Comment »

  1. M Abbas said,

    June 3, 2009 @ 4:41 am

    Hello,

    I have couple of questions.

    1) How can we create Sqllite database programmatically in Objective C?
    2) What is standard practice of shipping (empty) database with iPhone application. Either sending db file with application or using schema file (e.g. ‘schema.sql’) to create database?

    Thanx,
    M Abbas

RSS feed for comments on this post · TrackBack URI

Leave a Comment