Back to articles list
- 18 minutes read

SQLite Tools for Objective-C

The most common way to implement database functionalities in iOS applications is definitely Apple’s Core Data. However, its architecture is really Apple-like: it doesn’t let you see anything happening inside and the structure is hidden from you. That’s why some people dislike Core Data and prefer to stick with an SQLite database – even though SQLite is not supported natively.

Fortunately, you are not forced to use core SQLite API in pure C. The iOS developers’ community has already delivered tools to make communication with an SQLite database easier.

In this article, we’re going to look through the most popular SQLite tools for Objective-C. Here’s a quick overview of what we’ll cover:

SQLite Wrappers

ORMs

SQLite Wrappers

SQLite wrappers are one sort of database tool. They provide a convenient and safe Objective-C interface to ‘talk’ with an SQLite database. As they’re just wrappers, they operate only on raw queries, but they still make developers’ lives a lot easier.

Let’s examine three SQLite wrappers: FMDB, EGODatabase, and SQLiteManager4iOS.

FMDB

Without a doubt, FMDB is the most commonly used wrapper. Lots of other tools are built on top of it, and there’s an active community contributing to it. FMDB is still being developed (rather than being abandoned, which has happened to many other tools).

To connect with a database, you have to instantiate an FMDatabase class:

FMDatabase *db = [FMDatabase databaseWithPath:@"/path/to/database"];

To execute SELECT queries, you have an executeQuery: method:

FMResultSet *result = [db executeQuery:@"SELECT * FROM table"];

The return type of this method is FMResultSet, a class that allows you to iterate through query results and retrieve values. For the latter, FMResultSet offers a lot of methods, including:

  • intForColumn:
  • intForColumnIndex:
  • stringForColumn:
  • dateForColumn:

With these, you can extract the column’s value by the column’s name or index. And you can do it in your desired format. There are many more of these methods – one for almost every Objective-C type – that you can look up in FMDB’s documentation.

Let’s use FMDB to query the person table from the following database model:




FMResultSet *result = [db executeQuery:@"SELECT * FROM person"];
while([result next]) {
    NSLog(@"Person with ID:%d is %@ %@.", 
            [result intForColumn:@"id"],
            [result stringForColumn:@"first_name"],
            [result stringForColumnIndex:2]);
}

For any SQL statement other than SELECT, FMDatabase has an executeUpdate: method, shown below:

BOOL success = [db executeUpdate:@"INSERT INTO person (first_name, last_name) VALUES ('John', 'Smith')"];

The returned BOOL value indicates whether the statement execution succeeded. Of course, building statements like this makes your application vulnerable to injection attacks. Fortunately, you can put placeholders in your query and bind the values into the query using the same method:

BOOL success = [db executeUpdate:@"INSERT INTO person(first_name, last_name) VALUES (?, ?)", @"John", @"Smith"];

FMDB really fulfills its role as an SQLite wrapper. It simplifies database communication, and enables convenient and intuitive data retrieval. To learn more about FMDB, check out its GitHub.

EGODatabase

In the FMDB section, I mentioned that some tools were built on top of it. EGODatabase is one of them. It was created by a company called enormego. Here’s why they built it: “After extensively using FMDB in our applications, we saw a lot of room for improvements; the biggest was making it thread-safe”. According to the authors, the major difference between FMDB and EGODatabase is “when selecting data, EGODatabase populates its EGODatabaseRow class with the data from SQLite, as opposed to retaining the SQLite results like FMDB does.” EGODatabase seems to be an abandoned project, as the last contribution to its repository is dated February 4, 2014. However, it’s still worth looking into.

Let’s check how the selecting data in EGODatabase differs from the usage perspective:

EGODatabaseResult *result = [db executeQuery:@"SELECT * FROM person"];
for (EGODatabaseRow *row in result) {
    NSLog(@"Person with ID:%d is %@ %@.", 
            [row intForColumn:@"id"],
            [row stringForColumn:@"first_name"],
            [row stringForColumnIndex:2]);
}

After executing the query, EGODatabase instantly incorporates the results into their own structures. That’s where EGODatabase’s cooperation with the SQLite interface ends (until the next query or statement, anyway). On the other hand, FMDB is more about translating Objective-C code to the SQLite interface in C. It doesn’t use any of its own data structure under the hood – it holds SQLite objects inside and manipulates them directly.

If you’re looking for an SQLite wrapper and thread-safety is your top concern, or if you prefer its way of handling the results, consider EGODatabase. To learn more, view its GitHub.

SQLiteManager4iOS

SQLiteManager4iOS is very simple to use, particularly when it comes to processing query results. Just look at the example below. Maybe using dictionaries is not the best choice in terms of performance, but it’s definitely very intuitive.

NSArray *result = [dbManager getRowsForQuery:@"SELECT * FROM person"];
for (NSDictionary *person in result) {
    NSLog(@"Person with ID:%@ is %@ %@.", 
            person[@"id"],
            person[@"first_name"],
            person[@"last_name"]);
}

This tool is solely based on one class named, surprisingly, SQLiteManager. You instantiate it in the standard way:

SQLiteManager dbManager = [[SQLiteManager alloc] initWithDatabaseNamed:@"db"];

The SQLiteManager class also allows you to execute statements with binded parameters:

[dbManager executeUpdate:@"INSERT INTO person (first_name, last_name) VALUES (?, ?)" withParams:@[@"John", @"Smith"]];

Despite its simplicity, SQLiteManager4iOS seems to be an abandoned project too, as its last contributions are dated March 2014. Visit its GitHub to learn more.

ORMs

Another available SQLite tool is an ORM (Object-Relational Mapping). Of course, some of these tools stick closer to the standard definition of an ORM than others. Either way, they all tend to provide the experience we know from using ORMs on other platforms. We’re going to take a look at what these ORMs have to offer in an iOS-SQLite environment.

Objective-C SQL Query Builder

Objective-C SQL Query Builder offers lots of features to make things related to SQLite easier. Let’s go through the typical workflow for this tool to see what it can do.

Before you start, you have to configure your data source in db.plist file. Objective-C SQL Query Builder comes with predefined configurations for data sources named live and test. We will reference the latter in our examples.

Database connections can be established in two ways. The first requires you to close the connection manually, while the second does not. This is illustrated below:

// first way
ZIMDbConnection *db = [[ZIMDbConnection alloc] initWithDataSource: @"test"];
// do something
[db close];

// second way
ZIMDbConnection *db = [[ZIMDbConnectionPool sharedInstance] connection: @"test"];
// do something

Objective-C Query Builder as SQLite Wrapper

The ZIMDbConnection class has a query method for SELECT statements and an execute method for other statements. They may only be used with raw queries.

NSArray *result = [db query:@"SELECT * FROM person"];

The resulting array consists of dictionaries, where the keys are the column names. Values can thus be accessed similarly to SQLiteManager4iOS.

for (NSDictionary *person in result) {
    NSLog(@"Person with ID:%@ is %@ %@.", 
            person[@"id"],
            person[@"first_name"],
            person[@"last_name"]);
}

However, instead of raw statements, you may use the provided query builder. It’s available for almost all types of SQL statements. Here’s an example:

// select
ZIMSqlSelectStatement *johns = [[ZIMSqlSelectStatement alloc] init];
[johns from:@"person"];
[johns where:@"first_name" operator:ZIMSqlOperatorEqualTo value:@"John"];

NSArray *result = [db query:[johns statement]];

// insert
ZIMSqlInsertStatement *newPerson = [[ZIMSqlInsertStatement alloc] init];
[newPerson into @"person"];
[newPerson column:@"first_name" value:@"Adam"];
[newPerson column:@"last_name" value @"Smith"];

[db execute:[newPerson statement]];

As you might have already noticed, the query builder converts your queries directly to strings, so they are vulnerable to injection attacks.

Objective-C Query Builder as ORM

The Objective-C SQL Query Builder is an ORM, so it can also handle mapping. The creation of the class representing each database table is automated by a bash script, which comes with the tool. The script takes the data from an existing database. For our example, containing only the person table, the result is as shown below.

//
//  Person.h
//  MyApplication
//
//  Created by pposkrobko on 12/30/15.
//  Copyright 2015 pposkrobko. All rights reserved.
//

#import "ZIMOrmModel.h"

/*!
 @class Person
 @discussion This class represents a record in the "person" table.
 @updated 2015-12-30
 */
@interface Person : ZIMOrmModel {

	@private
		NSNumber *_identifier;
		NSString *_first_name;
		NSString *_last_name;

}

@property (strong, nonatomic) NSNumber *identifier;
@property (strong, nonatomic) NSString *first_name;
@property (strong, nonatomic) NSString *last_name;

@end

//
//  Person.m
//  MyApplication
//
//  Created by pposkrobko on 12/30/15.
//  Copyright 2015 pposkrobko. All rights reserved.
//

#import "Person.h"

@implementation Person

@synthesize identifier = _identifier;
@synthesize first_name = _first_name;
@synthesize last_name = _last_name;

- (instancetype) init {
	if ((self = [super init])) {
		_saved = nil;
	}
	return self;
}

+ (NSString *) dataSource {
	return @"test";
}

+ (NSString *) table {
	return @"person";
}

+ (NSArray *) primaryKey {
	return [NSArray arrayWithObjects: @"identifier", nil];
}

+ (BOOL) isAutoIncremented {
	return YES;
}

@end

The generator doesn’t allow columns to be named id, since it’s a reserved word in Objective-C. So we had to change the column name. Having generated the classes, we can use them to communicate with the database. All records from the table can be retrieved in the following way:

ZIMOrmSelectStatement *select = [[ZIMOrmSelectStatement alloc] initWithModel: [Person class]];
NSArray *result = [select query];

for (Person *person in result) {
    NSLog(@"Person with ID:%@ is %@ %@.", 
            person.identifier,
            person.first_name,
            person.last_name);
}

Now, typical CRUD operations get much simpler:

// insert
Person *person = [[Person alloc] init];
person.first_name = @"John";
person.last_name = @"Smith";
[person save];

// select
Person *person = [[Person alloc] init];
[person setIdentifier:@42];
[person load];

// update
person.first_name = @"Adam";
[person save];

// delete
[person delete];

The Objective-C SQL Query Builder also offers a feature that defines foreign keys by declaring additional methods within classes. There are three types of distinguished relationships: belongs to, has one, and has many. To define such a relationship, we have to add a method to access the referenced object. For the has many relationship, it can look like this:

/* in Person.m */
- (NSArray *) cars {
     return [self hasMany:[Car class] foreignKey: @[@"person_id"]];
}

In this example, each person can have many cars, so there is a person_id column in the car table that references the owner. With this method declared, the array of owned cars for every person may be accessed directly. However, be aware that such a declaration of relationships will disappear with the regeneration of entity classes.

Despite not being too popular, Objective-C SQL Query Builder seems to be an advanced database tool and it has plenty to offer its users. You can learn more about it on its GitHub.

DBAccess

DBAccess is another Objective-C ORM, which was released in 2014. Unlike some other tools, it’s still being developed – its latest release is from October 2015. Let’s take a look at it.

To configure DBAccess for your application, you have to set a delegate and open a database – and that’s besides adding the framework to your project. The suggested approach is to do this in AppDelegate by adding the DBDelegate protocol to the class declaration.

@interface AppDelegate : UIResponder 

@property (nonatomic, strong) UIWindow *window;

@end

You also have to invoke the following methods. Bear in mind that this has to be done before any usage of DBAccess functionalities.

[DBAccess setDelegate:self];
[DBAccess openDatabaseNamed:@"db"];

In DBAccess, you create your database model by defining classes extending from DBObject. For our person table, it might look like this:

@interface Person : DBObject
 
@property NSString *firstName;
@property NSString *lastName;
 
@end

@implementation Person

@dynamic firstName, lastName;

@end

You have to use @dynamic in the implementation to tell the ORM that these properties are the ones it should take care of. Properties declared as @synthesize will not be stored in a database. They won’t cause any problems either, so you can use @synthesize if you need a property in a class that you don’t want to be saved in a database. Note that we didn’t add any id property to the Person class, since it’s already defined in DBObject. With DBAccess, every table in a database has an integer primary key ID column. However, you can still use your own primary key.

Database tables are created by DBAccess automatically, so you can start working with a database straightaway. If you add some new properties in the next version of your application, DBAccess will handle the migration process.

Adding a new Person object to the database is very simple:

// create an object
Person *person = [[Person alloc] init];
person.firstName = @"John";
person.lastName = @"Smith";

// insert it into database
[person commit];

Just like updating and deleting:

// update
person.firstName = @"Adam";
[person commit];

// delete
[person remove];

To get a person from a database by ID, we can use a class method from DBObject:

Person *person = [Person objectWithPrimaryKeyValue:@42];

Queries to the database can be built in a following way. Note that the API of DBAccess is a fluent one, which is quite an unusual choice for Objective-C.

DBResultSet *result = [[[[Person query]
                          whereWithFormat:@"lastName = %@", @"Smith"]
                          orderBy:@"firstName"]
                          fetch];

The returned type DBResultSet is just an array of Person objects with the additional methods commitAll and removeAll.

for (Person *person in result) {
    NSLog(@"Hello! My name is %@ %@.", person.firstName, person.lastName);
}

You can define relationships between objects, which will obviously also be reflected in a database. If the Person object contained by a Car object is not already inserted into a database and you invoke the commit method on the Car object first, the ORM will still handle it and make insertions to both tables. You don’t have to worry about the order of insertions.

@interface Car : DBObject
 
@property NSString *vin;
@property Person *owner;
 
@end

@implementation Car

@dynamic vin, owner;

@end

There’s no direct method to make use of a relationship, like getting all the cars owned by one person, but you can make things a little easier by using objects in your queries:

DBResultSet *cars =[[[Car query] whereWithFormat:@"owner = %@", person] fetch];

DBAccess offers some advanced features, like JOINs or async queries. However, the most interesting feature in DBAccess is how it handles events. There are three types of events: insert, update and delete. Handlers store a block of code inside, which is invoked every time registered events happen. This is especially useful when you want to keep your application up-to-date with the database.

For example, if you have a list of contacts in your app, you can define it so the event handler reloads the list after any change in the contacts’ database table. You can define an event handler for a whole class or a specific object. The events might be combined using bitwise OR (‘|’) operator. Defining a class event handler looks like the code shown below:

[[Person eventHandler] registerBlockForEvents:DBAccessEventDelete |
                                              DBAccessEventInsert | 
                                              DBAccessEventUpdate
                                    withBlock:^(DBEvent *event) {
    // do something
} onMainThread:YES];

The code in this block will be triggered after any insertion, deletion, or update in the database table representing the Person class. Below, we have an example of an object event handler definition. Note that only the deletion of a specific database record represented by a Person object will invoke this handler.

[person registerBlockForEvents:DBAccessEventDelete
                     withBlock:^(DBEvent *event) {
    NSLog(@"Hi! My name is %@ %@ and I'm being deleted.", person.firstName, person.lastName); 
}];

DBAccess is an interesting tool. It can make things much easier – especially with its unique event-handling mechanism – and it’s very simple to use. As DBAccess is still being developed, it’s definitely worth following to see what new features are added to upcoming versions.

Vertabelo Mobile ORM

Vertabelo Mobile ORM is a brand new Vertabelo product. We’re going to look through its version for Objective-C.

To get started with Vertabelo Mobile ORM, you need to create a valid model of your database in Vertabelo.




Then you can generate an ORM for your model:

In the generated archive, we receive all source files that Vertabelo Mobile ORM requires. We don’t need any additional libraries; all we have to do is add these files to our project. Each database table from our model is represented as a separate generated class. This is the interface of the one representing the person table from our example:

@interface Person : NSObject

// Property representing column id.
@property (nonatomic) NSNumber *columnId;

// Property representing column first_name.
@property (nonatomic) NSString *columnFirstName;

// Property representing column last_name.
@property (nonatomic) NSString *columnLastName;

// Object factory
+ (Person *)person;

// Designated initializer.
- (id)initWithId:(NSNumber *)anId firstName:(NSString *)aFirstName lastName:(NSString *)aLastName;

// Compares two instances of class Person.
- (BOOL)isEqualToPerson:(Person *)person;

@end

By default, properties in the Person class are prefixed with ‘column’ to avoid collisions with Objective-C reserved words. However, you can choose your own property prefix or even decline having any. (In that case, you will have to watch out for name conflicts yourself.) Vertabelo Mobile ORM also allows you define a prefix for generated class names, like VBPerson.

Besides the implementations of methods from the interface, the implementation files also override some NSObject methods: init, isEqual:, hash and description. To communicate with a database, each object uses a corresponding DAO class. Below is the interface of PersonDAO:

@interface PersonDAO : NSObject

@property (readonly) MOTableExpression *TABLE_EXPRESSION;

@property (readonly) MOColumn *ID;
@property (readonly) MOColumn *FIRST_NAME;
@property (readonly) MOColumn *LAST_NAME;

@property (readonly) id  rowHandler;

- (void)verbose:(BOOL)isSet;

+ (PersonDAO *)daoWithDataSource:(id )dataSource;
- (id)initWithDataSource:(id )dataSource;

- (NSArray *)select:(MOSelectQuery *)query;
- (NSArray *)select:(MOSelectQuery *)query withRowHandler:(id )rowHandler;
- (Person *)selectOne:(MOSelectQuery *)query;

- (Person *)getById:(NSNumber *)anId;

- (NSArray *)getPersonArray;
- (NSArray *)getPersonArrayOrderAscByColumn:(MOAExp *)orderBy;
- (NSArray *)getPersonArrayOrderDescByColumn:(MOAExp *)orderBy;
- (NSArray *)getPersonArrayWhere:(MOLExp *)where;
- (NSArray *)getPersonArrayWhere:(MOLExp *)where orderAscByColumn:(MOAExp *)orderBy;
- (NSArray *)getPersonArrayWhere:(MOLExp *)where orderDescByColumn:(MOAExp *)orderBy;

- (NSUInteger)getCount;
- (NSUInteger)getCountWhere:(MOLExp *)where;

- (id)invokeFunction:(NSString *)name withArgument:(MOAExp *)arg;
- (id)invokeFunction:(NSString *)name withArgument:(MOAExp *)arg where:(MOLExp *)where;
- (id)invokeForAllFunction:(NSString *)name;

- (void)insert:(MOInsertStatement *)query;
- (void)insertPerson:(Person *)person;
- (void)insertPersonArray:(NSArray *)array;

- (void)update:(MOUpdateStatement *)query;
- (void)updatePerson:(Person *)person;
- (void)updatePersonArray:(NSArray *)array;

- (void)delete:(MODeleteStatement *)query;
- (void)deletePerson:(Person *)person;
- (void)deletePersonArray:(NSArray *)array;
- (void)deleteById:(NSNumber *)anId;
- (void)deleteWhere:(MOLExp *)where;

- (BOOL)isEqualByPK:(Person *)person1 and:(Person *)person2;

@end

As you can see, DAOs offer many ways to perform different database operations. To instantiate the DAO, we need an object of a class conforming to MOSQLiteDataSource protocol.

@protocol MOSQLiteDataSource 

- (sqlite3 *)getConnection;

@end

You can create this on your own, or you can use the provided solution – the MOSQLiteOpenHelper class. To use MOSQLiteOpenHelper, first create your own helper class extending MOSQLiteOpenHelper. It should implement the configure method by setting the database version and adding creation scripts.

In the next versions of your application, you can change the database version within the helper class and add SQL migration scripts via the addMigrationScript: method if necessary.

@interface DatabaseHelper : MOSQLiteOpenHelper
@end

@implementation DatabaseHelper

- (void)configure {
    [self setDbVersion:1];
    [self addCreateScript:"create.sql"];
}

@end

Obviously, all scripts referenced by the configure method have to be added to your application project. Using Vertabelo, you can generate creation scripts for your database directly from its model.

With DatabaseHelper, we can create an object of the PersonDAO class:

DatabaseHelper *helper = [[DatabaseHelper alloc] initWithDbName:"db"];
PersonDAO dao = [PersonDAO daoWithDataSource:helper];

Alternatively, you can also use MODAOProvider class to create DAO objects:

MODAOProvider *provider = [MODAOProvider providerWithDataSource:helper];
PersonDAO personDao = [provider personDAO];
CarDAO carDao = [provider carDAO];

With DAOs instantiated, we can start integrating our application with a database – for instance by inserting a new record:

// create object
Person *person = [Person person];
person.columnFirstName = @"John";
person.columnLastName = @"Smith";

// insert into database
[personDao insertPerson:person];

During the insertion, the columnId property is assigned automatically, since it was declared as an integer primary key in the database model. That’s why after insertion we can update or delete the object and the database will still understand our intentions.

// update
person.columnLastName = @"Simpson";
[personDao updatePerson:person]

// deletion
[personDao deletePerson:person];

Using DAO, we can get an array of all records existing in a database:

NSArray *people = [personDao getPersonArray];
for (Person *person in people) {
    NSLog(@"Hello! My name is %@ %@.", person.columnFirstName, person.columnlastName);
}

Or we can get a specific person by ID:

Person *person = [personDao getById:@42];

We can also retrieve only those people that fulfill a specific condition and order them by a certain column:

NSArray *smiths = [personDao getPersonArrayWhere:
                                 [personDao.LAST_NAME isEqualToString:@"Smith"]             
                             orderAscByColumn:personDao.FIRST_NAME];

For more complicated queries, you can use a query builder with row handlers. For example, you can get the array of last names of everyone called “John” in the following way:

MOSelectQuery *query = [MOSelectQuery selectColumn:personDAO.LAST_NAME 
                                         fromTable:personDAO.TABLE_EXPRESSION];
query.where = [personDAO.FIRST_NAME isEqualToString:@"John"];

NSArray *result = [personDao select:query withRowHandler:[MORowHandlerForString new]];
for (NSString *lastName in result) {
    NSLog(@"There is a John with a last name: %@.", lastName);
}

If you want to select multiple columns using the query builder, you have to use either MORowHandlerRawData or create your own row handler. MORowHandlerRawData will leave records as arrays of primitive objects, like NSString or NSNumber, so the select:withRowHandler: method will return an array of arrays. Your own implementation can wrap the query results into more sophisticated objects.

If your database model contains references, you will see additional methods in referencing and referenced tables’ DAOs.




With this kind of one-to-many relationship, we will get these additional methods:

// PersonDAO.h
- (Person *)getByCar:(Car *)car;

// CarDAO.h
- (NSArray *)getByPerson:(Person *)person;
- (NSArray *)getByPerson:(Person *)person orderAscendingBy:(MOAExp *)orderBy;
- (NSArray *)getByPerson:(Person *)person orderDescendingBy:(MOAExp *)orderBy;

However, the relationship may be one-to-one too.



This will also be reflected in the DAOs:

// PersonDAO.h
- (Person *)getByCar:(Car *)car;

// CarDAO.h
- (Car *)getByPerson:(Person *)person;

Vertabelo Mobile ORM is a tool for Objective-C developers that feel comfortable with databases and want to be aware what’s going on under the hood. If you prefer to insert and select then save and load, then this is a tool that may suit you well.

SQLite Wrappers and ORMs in iOS Mobile Development

Despite the dominance of Core Data, there is a significant group of iOS developers that prefer to work with SQLite databases. Witness the popularity of the FMDB wrapper. Other available SQLite tools for Objective-C haven’t gained that popularity yet, but they still have lots to offer.

The interesting thing is that there aren’t any commonly-used ORMs for Objective-C development. Is there no need for such a tool? Or are there simply no quality tools available? If it is the latter that’s slowing adoption, then the emergence of tools like DBAccess or Vertabelo Mobile ORM may change the situation. DBAccess is still quite young and Vertabelo Mobile ORM is completely new, but they’re both comprehensive solutions that can improve the development process of many iOS applications.

go to top