Use and Access Existing SQLite Database on iOS

Question

I have a fully populated database in SQLite that I'd like to use in my new app. It's rather large, so I'd like to avoid changing it to another format if possible. How can I use this database in such a way that it ships with my app?

EDIT: If I just drop the file into my Supported Files directory, for example, how can I access it? How do I reference it?

Answer 1

SQLite database interaction can be made simple and clean by using FMDB Framework. FMDB is an Objective-C wrapper for the SQLite C interface.

Reference worth reading:

FMDB Framework Docs

Sample Project With Storyboard

Initial Setup

Add the SQLite DB like any other file in your application's bundle then copy the database to documents directory using the following code then use the database from the documents directory

1. First download the FMDB framework
2. Extract the framework now copy all the file from src/fmdb folder (not the src/sample or src/extra folders).
3. Click your project in the left column of Xcode.
4. Click the main target in the middle column.
5. Click the “Build Phases” tab.
6. Expand the arrow next to “Link Binary With Libraries”.
7. Click the “+” button.
8. Search for libsqlite3.0.dylib and double click it.

Copying your existing database into app's document in didFinishLaunchingWithOptions: and maintain the database path through out the application.

In your AppDelegate add the following code.

AppDelegate.m

#import "AppDelegate.h"

@implementation AppDelegate

// Application Start
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
    
    // Function called to create a copy of the database if needed.
    [self createCopyOfDatabaseIfNeeded];
       
    return YES;
}

#pragma mark - Defined Functions

// Function to Create a writable copy of the bundled default database in the application Documents directory.
- (void)createCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    // Database filename can have extension db/sqlite.
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *appDBPath = [documentsDirectory stringByAppendingPathComponent:@"database-name.sqlite"];
    
    success = [fileManager fileExistsAtPath:appDBPath];
    if (success) {
        return;
    }
    // The writable database does not exist, so copy the default to the appropriate location.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"database-name.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:appDBPath error:&error];
    NSAssert(success, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}

YourViewController.m

Select Query

#import "FMDatabase.h"

- (void)getAllData {
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSString *sqlSelectQuery = @"SELECT * FROM tablename";

    // Query result 
    FMResultSet *resultsWithNameLocation = [database executeQuery:sqlSelectQuery];
    while([resultsWithNameLocation next]) {
        NSString *strID = [NSString stringWithFormat:@"%d",[resultsWithNameLocation intForColumn:@"ID"]];
        NSString *strName = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"Name"]];
        NSString *strLoc = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"Location"]];

        // loading your data into the array, dictionaries.
        NSLog(@"ID = %d, Name = %@, Location = %@",strID, strName, strLoc);
    }
    [database close];   
}

Insert Query

#import "FMDatabase.h"

- (void)insertData {

    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];    
    NSString *insertQuery = [NSString stringWithFormat:@"INSERT INTO user VALUES ('%@', %d)", @"Jobin Kurian", 25];
    [database executeUpdate:insertQuery];   
    [database close];
}

Update Query

- (void)updateDate {

    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"fmdb-sample.sqlite"];
    
    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];    
    NSString *insertQuery = [NSString stringWithFormat:@"UPDATE users SET age = '%@' WHERE username = '%@'", @"23", @"colin" ];
    [database executeUpdate:insertQuery];
    [database close];
}

Delete Query

#import "FMDatabase.h"

- (void)deleteData {

    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSString *deleteQuery = @"DELETE FROM user WHERE age = 25";
    [database executeUpdate:deleteQuery];   
    [database close];
}

Addition Functionality

Getting the row count

Make sure to include the FMDatabaseAdditions.h file to use intForQuery:.

#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"

- (void)gettingRowCount {

    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSUInteger count = [database intForQuery:@"SELECT COUNT(field_name) FROM table_name"];
    [database close];
}