Home‎ > ‎iOS‎ > ‎

SQL Programming in iOS

iStayHealthy is using SQLite for iOS (and also Android) to store user data. On this page I want to summarise my experiences with setting up SQL for iOS.

XCode and Core Data

If you plan to use persistent storage in your iOS app chances are you will come across Core Data. Core Data is a framework (in the literal sense - as well as in the iOS definition), that takes a lot of tasks off your data handling. In this example I use it for SQL database handling - but Core Data can be extended to support other storage formats as well.

It should be said straight from the off - Core Data is not just a wrapper of SQL. The API does far more in the background. This will become more apparent during the discussion on table relationships.

XCode supports Core Data - but beware between differences for setting up and managing data storage in XCode 3 and XCode 4. On this site I will exclusively focus on XCode 4.

Setting up an XCode Project with Core Data

This is the straight forward part. When you create a new iOS project you will find a checkbox for supporting Core Data. Select this option.
XCode will generate a lot of code for you
  • it will create an empty "datamodel" for your application. You would define your tables (and relationships) in there. XCode comes with a database editor that allows you to graphically create tables (and relationships)
  • it will create Objective-C code - mainly in the application Delegate class.
Let's look at the AppDelegate Class code first

Core Data enabled in App Delegate

Let's start with the include file. The first thing you'll notice is that XCode includes an Objective-C Protocol (Interface in Object-Oriented parlance) called

To start with XCode, I would recommend with the code snippet below. For CoreData enabled project some of the code may have been automatically generated (in particular the inclusion of the NSFetchedResultsControllerDelegate Protocol).
For the code below was a good starting point - and in fact I am still using it for iStayHealthy App Delegate.

The CoreData specific part is highlighted in RED.

@interface iStayHealthyAppDelegate : NSObject <UIApplicationDelegate, NSFetchedResultsControllerDelegate> {
    UIWindow *window;
    NSFetchedResultsController *fetchedResultsController_;
    NSManagedObjectContext *managedObjectContext_;
    NSManagedObjectModel *managedObjectModel_;
    NSPersistentStoreCoordinator *persistentStoreCoordinator_;

@property (nonatomic, retain) IBOutlet UIWindow *window;
@property (nonatomic, retain, readonly) NSManagedObjectContext *managedObjectContext;
@property (nonatomic, retain, readonly) NSManagedObjectModel *managedObjectModel;
@property (nonatomic, retain, readonly) NSPersistentStoreCoordinator *persistentStoreCoordinator;
@property (nonatomic, readonly) NSFetchedResultsController *fetchedResultsController;

Each instantiated object plays an important part in the management and creation of persistent data storage. For now, let's not worry about it too much and let's just take it as it is.

XCode Generated code in the AppDelegate Class

As always with Objective-C properties, you would need to use the @synchronise keyword - and finally make sure you release the objects in the dealloc methods.
Insert this after your @implementation code.

@implementation iStayHealthyAppDelegate
@synthesize window;
@synthesize fetchedResultsController = fetchedResultsController_;

between @implementation and @end make sure you have a dealloc method, that clears up the memory.

- (void)dealloc {   
    [super dealloc];
    [fetchedResultsController_ release];
    [managedObjectContext_ release];
    [managedObjectModel_ release];
    [persistentStoreCoordinator_ release];
    [window release];

The NSFetchedResultsController is used to actually get access to the CoreData managed data. But before we even go there we need to ensure that the database is actually created. The NSFetchedREsultsController protocol also requires the implementation of one method, which I will show later.

Creating the SQL database

Most of the code below is actually generated by XCode when you create an CoreData enabled project.
The method below creates a persistent store co-ordinator object - which essentially means your SQLite database.

 Returns the persistent store coordinator for the application.
 If the coordinator doesn't already exist, it is created and the application's store added to it.
- (NSPersistentStoreCoordinator *)persistentStoreCoordinator {
    if (persistentStoreCoordinator_ != nil) {
        return persistentStoreCoordinator_;
    NSURL *storeURL = [[self applicationDocumentsDirectory] URLByAppendingPathComponent:@"<<YourDataBaseName Goes Here>>.sqlite"];
    NSError *error = nil;
    NSDictionary *options = [NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithBool:YES], NSMigratePersistentStoresAutomaticallyOption, [NSNumber numberWithBool:YES], NSInferMappingModelAutomaticallyOption, nil];
    persistentStoreCoordinator_ = [[NSPersistentStoreCoordinator alloc] initWithManagedObjectModel:[self managedObjectModel]];
    if (![persistentStoreCoordinator_ addPersistentStoreWithType:NSSQLiteStoreType configuration:nil URL:storeURL options:options error:&error]) {
         Replace this implementation with code to handle the error appropriately.
         abort() causes the application to generate a crash log and terminate. You should not use this function in a shipping application, although it may be useful during development. If it is not possible to recover from the error, display an alert panel that instructs the user to quit the application by pressing the Home button.
         Typical reasons for an error here include:
         * The persistent store is not accessible;
         * The schema for the persistent store is incompatible with current managed object model.
         Check the error message to determine what the actual problem was.
         If the persistent store is not accessible, there is typically something wrong with the file path. Often, a file URL is pointing into the application's resources directory instead of a writeable directory.
         If you encounter schema incompatibility errors during development, you can reduce their frequency by:
         * Simply deleting the existing store:
         [[NSFileManager defaultManager] removeItemAtURL:storeURL error:nil]
         * Performing automatic lightweight migration by passing the following dictionary as the options parameter:
         [NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithBool:YES],NSMigratePersistentStoresAutomaticallyOption, [NSNumber numberWithBool:YES], NSInferMappingModelAutomaticallyOption, nil];
         Lightweight migration will only work for a limited set of schema changes; consult "Core Data Model Versioning and Data Migration Programming Guide" for details.
    return persistentStoreCoordinator_;

Let's look at the code in more detail.
  1.     NSURL *storeURL = [[self applicationDocumentsDirectory] URLByAppendingPathComponent:@"<<YourDataBaseName Goes Here>>.sqlite"];

XCode usually creates a database name for you. If your app project is called Foo - then the code would read     NSURL *storeURL = [[self applicationDocumentsDirectory] URLByAppendingPathComponent:@"Foo.sqlite"];

The applicationDocumentsDirectory method finds the database in your app datapath.

Enable Versioning of your database

CoreData has a great mechanism that helps you migrate your database from one version to another. Although you wouldn't expect to change the structure of your database tables fundamentally - or frequently - changes WILL occur as your application will improve and mature.

Per default, XCode generates code that does NOT enable database versioning. Look at the lines below:

    if (![persistentStoreCoordinator_ addPersistentStoreWithType:NSSQLiteStoreType configuration:nil URL:storeURL options:nil error:&error]) {

You will notice that the options argument in the last line is set to nil.
This means if you create your datamodel (see below), compile and build your project - and at any stage later make changes to the datamodel (e.g. add an attribute) your project will build ok - BUT CRASH.

To enable CoreData to migrate your database from one version to the next is quite easy. Simply add the following lines to the code (as shown in the complete code example above).

    NSDictionary *options = [NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithBool:YES], NSMigratePersistentStoresAutomaticallyOption, [NSNumber numberWithBool:YES],

and then

    if (![persistentStoreCoordinator_ addPersistentStoreWithType:NSSQLiteStoreType configuration:nil URL:storeURL options:options error:&error]) {

You see that instead of nil - we pass a NSDictionary object into the options argument. You may also have noticed that the NSDictionary is declared with the
NSMigratePersistentStoresAutomaticallyOption parameter in its argument - sealing the migration ability of CoreData.

I would strongly, strongly recommend you do this straight away - as it will save you endless hassle later.

Set up Fetching Results from the Database

The NSFetchedResultsDelegate protocol comes with methods, one of which is the fetchedResultsController, which in fact returns a NSFetchedResultsController object.
Below is the complete code. Again, this should be generated by XCode when you enable a project with Core Data.

- (NSFetchedResultsController *)fetchedResultsController{
    if (fetchedResultsController_ != nil) {
        return fetchedResultsController_;
    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    iStayHealthyAppDelegate *appDelegate = (iStayHealthyAppDelegate *)[[UIApplication sharedApplication] delegate];
    NSManagedObjectContext *context = appDelegate.managedObjectContext;
    NSEntityDescription *entity = [NSEntityDescription entityForName:@"iStayHealthyRecord" inManagedObjectContext:context];
    NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc]initWithKey:@"Name" ascending:YES];
    NSArray *allDescriptors = [[NSArray alloc] initWithObjects:sortDescriptor, nil];
    [request setSortDescriptors:allDescriptors];
    [request setEntity:entity];
    NSFetchedResultsController *tmpFetchController = [[NSFetchedResultsController alloc]
    tmpFetchController.delegate = self;
    fetchedResultsController_ = tmpFetchController;
    [request release];
    return fetchedResultsController_;

This code snippet contains a number of important Core Data concepts.
  • you get data using the NSFetchedResultsController - this will be demonstrated below
  • you manipulate data using the NSManagedObjectContext
  • the actual data are NSManagedObjects
  • NSEntityDescription: in CoreData an Entity is essentially your database table (of which there can be many).
  • the way you get the data returned, i.e. in which order they are sorted, is facilitated by the NSSortDescriptor. In this example we simply list the data alphabetically. But you could also list them by date, or number....
For now we park the method - expect to say that the name of your database (in this example iStayHealthyRecord) needs to match the NSString in the NSEntityDescription declaration EXACTLY.
The same holds for the attribute you want to sort the returned data in the NSSortDescriptor.

ManagedObject and ManagedObjectContext

If you enable CoreData in your project, the following methods should also be automatically generated. You shouldn't worry about them too much and I just list them for reference. You should not have to modify this code.
 Returns the managed object context for the application.
 If the context doesn't already exist, it is created and bound to the persistent store coordinator for the application.
- (NSManagedObjectContext *)managedObjectContext {
    if (managedObjectContext_ != nil) {
        return managedObjectContext_;
    NSPersistentStoreCoordinator *coordinator = [self persistentStoreCoordinator];
    if (coordinator != nil) {
        managedObjectContext_ = [[NSManagedObjectContext alloc] init];
        [managedObjectContext_ setPersistentStoreCoordinator:coordinator];
    return managedObjectContext_;

 Returns the managed object model for the application.
 If the model doesn't already exist, it is created from the application's model.
- (NSManagedObjectModel *)managedObjectModel {
    if (managedObjectModel_ != nil) {
        return managedObjectModel_;
    NSURL *modelURL = [[NSBundle mainBundle] URLForResource:@"iStayHealthy" withExtension:@"momd"];
    managedObjectModel_ = [[NSManagedObjectModel alloc] initWithContentsOfURL:modelURL];   
    return managedObjectModel_;
Make sure that the name of the URLForResource Iin this case iStayHealthy is in line with the database name in the persistentStoreCoordinator method above.
Typically, XCode makes sure the names are consistent - but it's worth checking.