DBSQLite

A simple, fast and object-oriented Objective-C framework for working with SQLite3 databases.

Getting Started

To create a fully-functional SQLite database file in the Documents directory of your application, simply create an instance of the DBSQLite class with a single line of code:

DBSQLite *database = [[DBSQLite alloc] initWithDocumentsFile:@"database.sqlite"];

Let's create a new user table to store future users within an exclusive transaction:

[database startExclusiveTransaction];
[database executePlainQuery:@"CREATE TABLE IF NOT EXISTS user (\
     userID         INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT, \
     firstName      TEXT, \
     lastName       TEXT, \
     dateCreated    REAL \
     )"];
[database commitTransaction];

We can then insert a new user, create an index and then drop it. We can use Objective-C classes like NSString, NSNumber, NSDate, NSData and even NSArray and NSDictionary (as long as they only contain JSON obejcts) as arguments for insertion. DBSQLite will automatically convert them appropriately:

NSDate *now = [NSDate date]; // Will be stored as a timeIntervalSince1970 (REAL number)
 
[database executeQuery:@"INSERT INTO user (firstName, lastName, dateCreated) VALUES (?, ?, ?)",
     @"John",
     @"Appleseed",
     now, 
     ];
[database createIndex:@"userNameIndex" table:@"user" column:@"firstName"];
[database dropIndex:@"userNameIndex"];

Complete list of supported object and scalar types inludes:

NSNull / nil      - stored as NULL
NSString          - stored as TEXT
NSNumber          - stored as INTEGER
NSURL             - stored as TEXT
NSData            - stored as BLOB
NSDate            - stored as REAL number
UIImage / NSImage - stored as PNG data / TIFF data
NSArray           - stored as TEXT
NSDictionary      - stored as TEXT

Scalar-types are supported using NSString functions:
     NSStringFromCGRect
     NSStringFromCGSize
     NSStringFromCGPoint
     NSStringFromCGAffineTransform

Making Queries

We can fetch all users, without creating subclasses, with a simple query. We then iterate over the collection using fast enumeration:

NSArray *results = [database fetchDictionary:@"SELECT * FROM users"];
for (NSDictionary *user in results) {
     NSLog(@"First Name: %@", user[@"firstName"]); // Prints: John
}

A better way, is to create a model object instead and adopt the DBSQLiteModelProtocol with just one method.

@interface XYUser : NSObject <DBSQLiteModelProtocol>

@property (strong, nonatomic, readonly) NSNumber *userID;
@property (strong, nonatomic, readonly) NSString *firstName;
@property (strong, nonatomic, readonly) NSString *lastName;
@property (strong, nonatomic, readonly) NSDate *dateCreated;

+ (NSDictionary *)keyMapForModelObject;

@end

@implementation XYUser

+ (NSDictionary *)keyMapForModelObject {
     return DBSQLiteKeyMapDefault;
}

@end

The + (NSDictionary *)keyMapForModelObject; method should return a mapping of the SQLite table column names to the property names of the model object IF they are NOT the same. It is important to explicitly declare that a model object conforms to DBSQLiteModelProtocol, otherwise an exception will be thrown upon registering the object.

In this example the property names and column names match exactly, so we let DBSQLite automatically generate the mapping by returning the DBSQLiteKeyMapDefault constant.

If they were to differ, as it in this table schema:

+ (void)createUserTable {
     [database executePlainQuery:@"CREATE TABLE IF NOT EXISTS user (\
          id             INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT, \
          first_name     TEXT, \
          last_name      TEXT, \
          date_created   REAL \
          )"];
}

The mapping would be as follows:

+ (NSDictionary *)keyMapForModelObject {
    return @{
             @"id"           : @"userID",
             @"first_name"   : @"firstName",
             @"last_name"    : @"lastName",
             @"date_created" : @"dateCreated",
             };
}

A model object that conform to DBSQLiteModelProtocol provides a container for data when performing a fetch. Since it's very light-weight, it is actually faster than creating an NSDictionary for every returned row of data. It also has the benefit of converting the returned data to the correct types, which means dateCreated will contain a real NSDate object.

Before we can use XYUser, we MUST register it with DBSQLite. Doing so is very easy:

[DBSQLite registerModelClass:[XYUser class]];

We can then fetch and use the user objects. We pass in the name of the class that will hold the data and the query used. Here we use a simple SQL query to return all users from the user table.

NSArray *results = [database fetchObject:@"XYUser" query:@"SELECT * FROM user"];
for (XYUser *user in results) {
     NSLog(@"First Name: %@", user.firstName); // Prints: John
}

JSON Support

Using DBSQLite you can insert NSArray and NSDictionary objects, provided that they only contain JSON-compatible objects: NSString, NSNumber, NSNull, NSArray and NSDictionary. You can set reading and writing options for the JSON serialization and deserialization process via these methods:

- (void)setJsonWritingOptions:(NSJSONWritingOptions)jsonWritingOptions;
- (void)setJsonReadingOptions:(NSJSONReadingOptions)jsonReadingOptions;

Pragma

There are several convenience methods that allow for quick-n-easy access to SQLite pragma options. There are various string constants like kDBSQLiteModeOn that help eliminate spelling errors. These include:

[database setBusyTimeout:10]; // Seconds
[database setForeignKeysEnabled:kDBSQLiteModeOn];
[database setCaseSensitiveLike:kDBSQLiteModeOff];
[database setSynchronous:kDBSQLiteModeNormal];
[database setJournalMode:kDBSQLiteModeDelete];
[database setTemporaryStore:kDBSQLiteModeMemory];

Setting the above (as well as other) values via an SQL query is identical to the above, with the exception that internal state of these values will no longer be tracked.

Concurrency

You can easily perform concurrent tasks on the same open database file using a DBSQLiteQueue. Creating one is just like creating a new DBSQLite object:

DBSQLiteQueue *queue = [DBSQLiteQueue queueWithDocumentsFile:@"database.sqlite"];

You can then perform tasks synchronously on a background queue:

[queue syncExecution:^(DBSQLite *database) {
   [database executeQuery:@"INSERT INTO user (firstName, lastName, dateCreated) VALUES (?, ?, ?)",
     @"Adam",
     @"Smith",
     now, 
     ];
}];

or asynchronously on the background queue:

[queue asyncExecution:^(DBSQLite *database) {
     NSNumber *lastInsertedUserID = [database lastInsertID];
     [database executeQuery:@"UPDATE user SET firstName = ? WHERE id = ?", @"Kevin", lastInsertedUserID];
}];

Be careful not to nest two synchronous calls or you will deadlock (in practice, DBSQLiteQueue will actually throw an exception to save you the pain and suffering of a deadlock). Nesting async calls, however, isn't a problem.