iPhone SQLite Database Basics

In celebration of the lifting of the NDA on the iPhone SDK, I decided to write a tutorial on using SQLite on the iPhone.

This tutorial covers the basics of SQLite database creation, things to consider in the design of the database, and some particular things to be aware of when deploying these types of applications to the iPhone.

Once you have finished with these basics, take a look at the SQLiteBooks sample code from the iPhone dev center for details on how to interface with the database from your code.

Step 1: Create a SQLite Database File

There are lots of ways to create a SQLite database file. The easiest way that I have found is to download the SQLite Firefox extension, which provides a GUI to create and manage your SQLite database. The rest of this tutorial will assume you have that extension installed.

In this tutorial we’ll be creating a recipe application, so we are naming our database “recipes”.

To create the database:

1. Click the “New Database” toolbar button.
2. Enter the name of the database file.
3. Click OK.

You will then be prompted for a location to save the database. The ideal place is in the directory of your XCode project.

Step 2: Create the Database Schema

Now we are going to setup our database tables. This will be a very simple database with two tables:

There are entire textbooks about “proper” database design and if you are developing a super large-scale ultra robust data warehouse app, you might want to look them over. However rather than make your eyes glaze over with the merits of third normal form, let me just share a few guidelines that I picked up during several years as a DBA/Database application developer:

1. Every table should mirror an object, with each non-calculated object property mapping directly to a table field.
2. In addition to the object properties, every table should have an integer field that is autofilled with a unique identifier (commonly know as the primary key).
3. Every table that is dependent on a parent table should have a way to refer back to its parent. (This is sometimes called a Foreign Key)

In our application we’re going to have two model objects. A Recipe object, and an Ingredient object. Each recipe object can have 1 or more ingredient child objects. (The blue arrow in the above diagram signifies this, and is called a 1-to many relationship).

The Recipe object has a name and description property. These are both strings, and these map to database fields of type VarChar (meaning Variable length Character string). In addition, the Recipes table has a Recipe_ID field of type Integer. This field is the primary key of the Recipes table.

The ingredient object in our simple application only has one property, Description. Since this is a string it also maps to a VarChar field. Our Ingredients table also has a primary key (Ingredient_ID) which gives us a way to refer to a specific ingredient.

Finally, the Recipe_ID field in the Ingredients table is our foreign key back to the Recipes table. Using that key you can look at any ingredient and know what Recipe it belongs to. More commonly an application will use that key to get all of the ingredients that belong to a specific recipe.

To create the Recipes table, click the “New Table” toolbar button and enter the information as seen below:

Notice that we check the Primary Key and Autoinc checkboxes for the Recipe_ID field. The Primary Key checkbox tells SQLite to treat this field as a Unique Index, meaning that it can guarantee that the values in this field won’t be duplicated and should be kept sorted for faster queries. (Later we’ll talk a little more about optimizing queries by adding additional table indices.)

The Autoinc checkbox tells SQLite how to handle the automatic creation of primary key values. Without autoinc being checked, when a new row is added SQLite sets the primary key to one greater than the current highest primary key. With the box checked, SQLite sets the primary key to one greater than the highest primary key that the table has every had. (Which it keeps track of in its own table called sqlite_sequence). How you set this box will depend on how you want to handle deleted records.

For the Ingredients table, it is much the same:

Notice that we do NOT mark the Recipe_ID field of the Ingredients table as a primary key. It is the primary key of the Recipes table, not of this table. (In this table some people would call the Recipe_ID field the foreign key).

The last thing that we want to do regarding our database schema is to create an index on the Recipe_ID field of the Ingredients table. Since most of our queries that involve this table will involve looking up a specific value in the Recipe_ID field, it will speed things up to have an index on this field. (Note: You really won’t notice a significant speed increase unless your Ingredients table has a lot of records, but since it is a good design principle, we’re going to do it anyway.)

Finally you need to populate the table with some data. You can do that using the SQLite Manager by selecting the table and then clicking the “Add new Record” button, or by selecting the “Execute SQL” tab and using several Insert statements.

For example to insert this data into my Recipes table:

Recipe_ID	Name	Description
1	Omelet	A delicious and eggy breakfast.
2	Grilled Cheese Sandwich	A delicious and cheese filled lunch.
3	Pizza	A classic dinner from the old world.

I could use these three insert statements:

INSERT into Recipes(Name, Description) VALUES(’Omelet’, ‘A delicious and eggy breakfast.’);

INSERT into Recipes(Name, Description) VALUES(’Grilled Cheese Sandwich’, ‘A delicious and cheese filled lunch.’);

INSERT into Recipes(Name, Description) VALUES(’Pizza’, ‘A classic dinner from the old world.’);

Notice that I did not insert the values for the Recipe_ID field. As I mentioned above, since it is the primary key the database calculates those values automatically.

Next comes the Ingredients table:

Ingredient_ID	Recipe_ID	Description
1	1	Eggs
2	1	Water
3	1	Salt
4	2	Bread
5	2	Cheese
6	2	Butter
7	3	Pizza Dough
8	3	Pizza Sauce
9	3	Cheese
10	3	Toppings

Which I could use the following statements for:

INSERT into Ingredients(Recipe_ID, Description) VALUES(1, ‘Eggs’);

INSERT into Ingredients(Recipe_ID, Description) VALUES(1, ‘Water’);

INSERT into Ingredients(Recipe_ID, Description) VALUES(1, ‘Salt’);

INSERT into Ingredients(Recipe_ID, Description) VALUES(2, ‘Bread’);

…

INSERT into Ingredients(Recipe_ID, Description) VALUES(3, ‘Toppings’);

Again notice that I did not specify the values for the table’s primary key (Ingredient_ID), but I did specify the foreign key values (Recipe_ID).

Step 3: Add the Database File to Your XCode Project

To do this, just right click on the Resources folder and select “Add->Existing Files…” and then select the database file that you created.

XCode should automatically add the file that you select to the “Copy Bundle Resources” build phase of your project target. (Meaning that when the program is compiled, your database file is automatically stored in the application bundle’s resource folder so that you can access it from your application.)

Update: Jonathan Wight suggested that instead of embedding the SQLite db file directly into the XCode project, it is better to embed the SQL source file into the project and then setup a compile rule in XCode to generate the db file and embed it into the bundle at compile time. As he points out this has a particular advantage related to source code control. He provides an example of how to do this here.

Step 4: Link to the SQLite Library

Finally you are ready to link to the library that contains all of the SQLite functions. To link to that library, from your XCode project right click the frameworks folder and select “Add->Existing Frameworks…”.

Next, navigate to the your iPhoneSDK folder and select the usr/lib/libsqlite3.0.dylib file. (Don’t be confused by the other libsqlite*.lib files in that folder, they are all just aliases to this one.)

You are now ready to start writing some SQLite code. (Again see the SQLiteBooks sample code for some tips on doing that.)

Update: Jonathan Wight also suggested that it is better to use the other linker flags setting in the the Build tab of the Project settings to link to the SQLite library instead of using the framework method. You can do this by adding the -lsqlite3 flag to that field as shown here:

This method has the advantage of linking to the correct library no matter what platform you are targeting.

SQLite on the iPhone Nuances

1. SQLite on the actual iPhone is much slower for some things than on the simulator. In particular the same queries that run in the blink of an eye on the simulator take several seconds to run on the actual device.
2. If you need to write to your database, make sure you make a copy of it in a location that you can write to. See the SQLiteBooks sample code for an example of how to do this.
3. Be aware that SQLite extensions such as Full Text Search don’t appear to be compiled into the iPhone SQLite libraries.
4. Plan for the future! If you plan on adding a feature that relies on a certain table or field in the database make that part of your database design now. This will save you the trouble of having to write all the necessary SQL commands for versioning your database later when you release an application update.