How to create SQLite database in iOS

Ladies and Gentlemen… 🙂  If you have stayed with me from the beginning, so far I have covered three blog posts related to basic database connections and handling. Those are,

• MySQL with PHP
• MySQL with JAVA
• SQLite with Android

Today I’m going to start a series of posts that describe how to connect SQLite database with iOS and related basic functions just like we did in Android database related posts. 

As the first post of this SQLite database series, today I’m going to show you how we create a database in iOS programatically and how we browse that database with the help of a magnificent tool. In order to simplify this post, I’m going to use almost same UI and concept just like in the Android post.

First thing first. Create a iOS project in your Xcode and link libsqlite3.dylib library to it. Well, those who have no idea how to add a inbuilt library to your iOS project, I’ll explain to you for the first and last time 🙂

• Select your iOS project from Xcode Navigator pane
• From Projects and Targets list pane select your appropriate Target
• Select General tab
• Expand Linked Frameworks and Libraries
• Click on the + button
• Provide the library name in the pop up window
• Select it from the list and press Add button

Now you are good to go. Remind our last two blog posts about Singleton and File operations because with that knowledge, understand today’s code sample is a piece of cake.

Let’s jump to code. Import sqlite3.h in your header file like below.

#import <sqlite3.h>

Then in your implementation file you have to create singleton database instance and a function to create database.

//
// SQLiteDBManager.m
// SQLiteWithiOS
//
// Created by AnujAroshA on 10/26/14.
// Copyright (c) 2014 AnujAroshA. All rights reserved.
//

#import "SQLiteDBManager.h"
#import "AppConstants.h"

static NSString const *TAG = @"SQLiteDBManager";
static SQLiteDBManager *singletonInstance = nil;
static sqlite3 *database = nil;

@interface SQLiteDBManager () {

 NSString *databasePath;
}
@end


@implementation SQLiteDBManager

+ (SQLiteDBManager *)sharedInstance {
 if (debugEnable) NSLog(@"%s - %d", __PRETTY_FUNCTION__, __LINE__);
 
 if (!singletonInstance) {
 singletonInstance = [[super alloc] init];
 
 [singletonInstance createdDatabase];
 }
 
 return singletonInstance;
}

- (BOOL)createdDatabase {
 if (debugEnable) NSLog(@"%s - %d", __PRETTY_FUNCTION__, __LINE__);
 
 NSArray *directoryPathsArray = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
 NSString *documentsDirectory = [directoryPathsArray objectAtIndex:0];
 
 databasePath = [[NSString alloc] initWithString:[documentsDirectory stringByAppendingPathComponent:@"undergraduate_gpa_db"]];
 if (debugEnable) NSLog(@"%s - %d # databasePath = %@", __PRETTY_FUNCTION__, __LINE__, databasePath);
 
 BOOL fileExistency = [[NSFileManager defaultManager] fileExistsAtPath:databasePath];
 
 BOOL isSuccess = YES;
 
 if (fileExistency == NO) {
 
 const char *utf8Dbpath = [databasePath UTF8String];
 
// https://www.sqlite.org/c3ref/open.html
 if (sqlite3_open(utf8Dbpath, &database) == SQLITE_OK) {
 
 char *errorMsg;
 
 const char *sqlQueryToCreateUndergraduateDetailsTable = "create table if not exists undergraduate_details_table (undergraduate_name_column text, undergraduate_uni_id_column text primary key, undergraduate_gpa_column real)";
 
// https://www.sqlite.org/c3ref/exec.html
 if (sqlite3_exec(database, sqlQueryToCreateUndergraduateDetailsTable, NULL, NULL, &errorMsg) != SQLITE_OK) {
 
 isSuccess = NO;
 if (debugEnable) NSLog(@"%s - %d # errorMsg = %s", __PRETTY_FUNCTION__, __LINE__, errorMsg);
 }
 
// https://www.sqlite.org/c3ref/close.html
 sqlite3_close(database);
 return isSuccess;
 
 } else {
 isSuccess = NO;
 if (debugEnable) NSLog(@"%s - %d # Fail to open DB", __PRETTY_FUNCTION__, __LINE__);
 }
 }

 return isSuccess;
}

@end

Yes, that is how you gonna do it. Here I’m not going to explain the three SQLite functions that I’ve used for this code sample. Instead I’ve add the web links that define those functions in the SQLite official documentation page.

Is that it for today ? Nope… can you remember that I mentioned about a magnificent tool. It’s call SQLite browser. You can download it from their official web site. This is not a new tool those who have followed my SQLite posts related to Android. But if you are totally new, let me explain the process to you.

• Download the relevant .dmg file to your MAC and install SQLite browser
• Find your database file and copy it to a different location (this is the part where you need the knowledge of Basic file operations in iOS)
• Run your SQLite browser program and click on the Open Database button
• From the pop up window, find and select the database file that you have copied to a different location.

Once you add the database file you may not see any records when you click on the Browse Data option. That is because today we just create database but not did any insert functions. But if you need to check that your database file is properly created, just run following SQL command in Execute SQL tab and you will see the result.

insert into undergraduate_details_table (undergraduate_name_column, undergraduate_uni_id_column, undergraduate_gpa_column) 
values ('Anuja', '2007_ICT_052', 3.123)

You can learn how to insert data to SQLite database in my next posts and also I will upload all my source code to GitHub at the end of this SQLite database series. Till then have a good code hunting… 😉