Using SQLite on the iPhone

Most of the time, your iPhone application data storage needs will be taken care of by simple object serialization and flat file storage. However, there comes a point when that type of storage just won't work anymore - when you start storing thousands or tens of thousands of objects. Apple recognized that fact, and so gave app developers the ability to use SQLite inside their applications.

What is SQLite? Well SQLlite is a small but powerful database engine that takes virtually no configuration to set up and lives in a single file. It is extremely handy for quick and easy databases. We have actually talked about SQLite here before at Switch On The Code a couple of times. For a thorough understanding of how to interact with SQLite, I would suggest reading through the Writing a .NET Wrapper for SQLite tutorial - because we won't be going too deep in to SQLite today in this tutorial.

Ok, so first things first - we need to get an iPhone Xcode project set up to use SQLite. This isn't terribly hard - we just need to add a library to our project. So right click on the Framworks folder in Xcode and choose "Add Existing File". This is because while what we are adding is a library, it isn't a "Framework" in the standard frameworks folder. You will want to navigate to the following convoluted path: /Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSilumator3.0.sdk/usr/lib and choose the file libsqlite3.dylib:

Ok, so I lied a little bit about the path above. You actually will want to modify that path a bit depending on the current SDK you have Xcode set to use (for instance, mine currently is "Simulator - 3.0"). This is because of an option we are about to set in the second Add dialog - the "Reference Type":

By setting the "Reference Type" as "Relative to Current SDK", this makes sure that Xcode will always use the correct libsqlite3.dylib file for whichever SDK we are using. We also don't want to "copy items into destination group's folder" - we want the library to stay right where it is - so uncheck that if it is checked.

Ok, now it is time to make some use of this library in code. I'm going to be making a very silly application here today that records every time you start up the application, and displays all the start times in alert dialogs. Extremely useless, but it will show off inserting into and selecting from SQLite tables. We have a single view application with nothing on the view, and all it does is display alerts:

Code time!

First off, we need to include a header file to be able to use any of the SQLite functions - /usr/include/sqlite3.h. All this gives us is a pure C function interface to SQLite - no classes, no ORM layer, no nothing. Just hardcore database calls. This makes for some ugly code, but hey, it is what we have to work with.

Now that we have those calls, we can open/create a database. That is what the sqlite_open call does. If there is a file at that path, it tries to open it as a SQLite3 database file - and if not, a new file is created. If stuff goes wrong, an error code will be returned, which we can check. If everything went well, the return code will be SQLITE_OK.

Technically, you should check the return value of every SQLite call for errors. I'm going to be lax and not do it today - but that doesn't mean you should!

Because this database may or may not have exited before we opened the file, the first thing we need to do is ensure that our tables exist before we do anything. We do this using the sqlite3_exec call - which is used for sql statements that don't return a result set. Once we have a table, we can do an insert of today's date and time - using some NSDateFormatter and stringwithFormat action.

One thing to note (and is very important) - the SQLite function calls expect C-Strings - not NSStrings. This is why for most of these function calls, you don't see the "@" sign at the start of the string. For this insert call, we needed to use an NSString to take advantage of the date formatter - but we have to get to a C-String before calling the sqlite function. This is done through the UTF8String call on a NSString - it returns a good ol' char array that sqlite can deal with.

Next we have the select statement. Since these are pure C calls, we don't get any of the niceties like a "result set object" or anything like that. We "prepare" a statement using the sqlite3_prepare_v2 call and then iterate through the results using the sqllite3_step call. And just like with that insert statement, we have to convert the string data returned (although this time from a C-String into an NSString). This is just as easy - we just call the initWithUTF8String, and we get a populated NSString object. Oh, and when you are done with a select, don't forget to sqlite3_finalize the statement.

Then we close the database, and that is it for this whirlwind tour of SQLite on the iPhone! You can grab the project for this silly example below, and if you have any questions, leave them in the comments or in the forums.