Generating SQLite Databases at Build Time with Xcode

Background #

Some years ago I worked on v2.0 of the Fly Delta app for Delta Air Lines. As an initial part of that effort, we mapped out the data model of what we would need in order to support all of the features that were planned. When it came time to start implementing, we instinctively reached for Core Data and ended up building out a moderately large Core Data model.

Unfortunately, we ran into several issues with Core Data. I have forgotten most of the details now, but I believe they had to do with threading/merging changes from multiple managed object contexts and bulk deleting data (e.g. if you deleted your user account from the app we would need to purge all of your trip information). Ultimately, we decided that Core Data was more trouble than it was worth and ended up switching to SQLite.

Note: This may not be the best fit in every situation, and Core Data has come a long way since iOS 5.

Xcode Build Rule #

One of the data sets that we wanted to bundle with the app was worldwide airports — name, IATA code, coordinates, etc. A neat trick I learned from Tom Wilcoxen’s blog (Wayback machine link, because the original site is no more) is that you can have Xcode create the SQLite database for you at build time from raw SQL commands by using the sqlite3 command line tool and a build rule. This is a huge win, because the plaintext SQL file works much better with your version control system than a binary SQLite database:

$ git diff
diff --git a/raw.sql b/raw.sql
index 2c29b02..b1a7ba0 100644
--- a/raw.sql
+++ b/raw.sql
@@ -6,6 +6,7 @@ CREATE TABLE countries(

 INSERT INTO countries(alpha2, alpha3, name) VALUES
 ("US", "USA", "United States"),
+("CA", "CAN", "Canada"),
 ("GB", "GBR", "United Kingdom"),
 ("AU", "AUS", "Australia"),
 ("JP", "JPN", "Japan"),

By using an Xcode build rule, errors in your SQL statements will be caught as part of the build process.

Setup #

To set up the build rule in Xcode:

1. Add your plaintext *.sql file to your Xcode project.
2. In the target settings, navigate to the “Build Rules” tab.
3. Click the “+” button to add a new rule.
4. In the “Process” menu ensure that “Source files with names matching:” is selected (it should be the default).
5. Enter *.sql in the file name pattern text field to the right
6. Ensure that “Custom script:” is selected in the “Using” menu (again, it should be the default).
7. In the “Output Files” section, add $(DERIVED_FILE_DIR)/$(INPUT_FILE_BASE).db
8. Enter the following in the script text view:

# Remove the previously-built db
cd "${DERIVED_FILES_DIR}"
if [ -f "${INPUT_FILE_BASE}.db" ]; then
  rm ${INPUT_FILE_BASE}.db;
fi

# Build the new one
cat "${INPUT_FILE_PATH}" | sqlite3 "${DERIVED_FILES_DIR}/${INPUT_FILE_BASE}.db"

This will convert any file ending in .sql into a SQLite database at build time. Xcode will treat this converted database as the “processed” version of the input file, so whatever target membership you have set up for the raw SQL file will apply to the newly created database. i.e. If your SQL file is part of the “Copy Bundle Resources” build phase of your app or framework target (which it should be), the SQLite database will be copied to your resource bundle not the SQL file.

Cross-Platform #

One of the unforeseen benefits of switching to SQLite was that we got to use the same source of truth (the raw SQL files) on both iOS and Android to create the various pre-packaged data sets.