In my iOS apps Eki Live and Eki Bright I’ve been using read-only SQLite databases shipped in the app bundle. It’s mostly worked fine so far (disregarding the App Group Containers problem).

My workflow involves:

  • Running a Swift script from a separate target that aggregates several json files into an sqlite file.
  • Copying the sqlite file to the app target; it gets included in the app bundle.
  • Opening the sqlite file as read-only on app launch.
// Opening an sqlite database from the bundle using the venerable GRDB library
guard let databasePath = Bundle.main.path(forResource: "db", ofType: "sqlite") else { fatalError("Database not found in bundle") }
var configuration = Configuration()
configuration.readonly = true
let database = try DatabaseQueue(path: databasePath, configuration: configuration)

Up to now, I had unwittingly avoided any problems with the write-ahead log (WAL) journaling mode because the database generated by the script was created as a DatabaseQueue and not DatabasePool, and otherwise did not inherit any settings via backup.

In Eki Live, I started using a read-write database to record GPS datapoints while out riding trains that I could play back in a bespoke app on my Mac. I again unwittingly avoided any problems with journaling mode because I was taking the intermediate step of processing this read-write database in a Swift script that produced a flat json file. The Swift script maintained read-write access to the file system because it ran from a target without a bundle.

My luck finally ran out when I decided to refactor my system to skip that intermediate step: I wanted to be able to export a read-write database from the Eki Live iOS app, include it in the bundle of the macOS debug app, and run it as read-only.

When trying this, SQLite threw errors:

os_unix.c:49450: (2) open(../viewer.app/Contents/Resources/db.sqlite-wal) - No such file or directory
SQLite error 14: unable to open database file - while executing `SELECT * FROM sqlite_master LIMIT 1`

You can check and change the journal mode of a database in SQLite with the following terminal commands:

# Check the journal mode of the database
$ sqlite3 db.sqlite "PRAGMA journal_mode;"
wal # this mode can cause problems when opening the database from a read-only part of the file system

# Change the journal mode of the database
$ sqlite3 db.sqlite "PRAGMA journal_mode=DELETE;"

# Check the journal mode of the database again
$ sqlite3 db.sqlite "PRAGMA journal_mode;"
delete # as long as we open the database in readonly mode, there's no problem with delete mode

Manually changing the journal mode to delete via the command line before copying the database to the app bundle solved my problem. However, I wanted to ensure any database I exported from the iOS app would be easily importable by the macOS app.

Here is the working export function from the iOS app that includes resetting the journal mode to delete for the exported database:

func exportDatabase(_ sourceDatabase: any DatabaseReader) throws -> URL {
    let fileName = UUID().uuidString.prefix(6) + "_" + Date().formatted(.iso8601)
    let url = URL.temporaryDirectory.appending(component: fileName).appendingPathExtension("sqlite")

    var configuration = Configuration()
    let targetDatabase = try DatabaseQueue(path: url.path, configuration: configuration)

    // Note: `backup` changes the journal mode of `targetDatabase`
    try sourceDatabase.backup(to: targetDatabase)

    try targetDatabase.write { db in
        // Optional: make any changes to the target database contents before exporting
    }

    // Ensure database size is as compact as possible after any deletes
    try targetDatabase.vacuum()

    // Change the journal mode from `wal` to `delete`
    try targetDatabase.writeWithoutTransaction { db in
        try db.execute(sql: "PRAGMA journal_mode=DELETE;")
    }

    // Housekeeping
    try targetDatabase.close()

    return url
}

In the above code, targetDatabase actually is created in journal mode delete. However, it’s the backup command that changes the journal mode of the targetDatabase to match that of the sourceDatabase (which, as read-write, is wal as intended). In retrospect, that makes sense I guess.

For the record, on the macOS app side, I tried copying the database from the bundle into the documents directory before opening it, but ran into the same problem with a “missing .sqlite-wal file”. I think because I hadn’t been properly closing the exported database and wasn’t including the wal file in the export, sqlite was getting confused. This is still a bit of a mystery though.

I’ll also note it’s possible to use VACUUM INTO in place of BACKUP and then VACUUM. However, in my use case, I need to optionally remove some data from the target database before returning it.

Using improperly maintained sqlite databases in a read-only context is probably a rare problem, but hopefully the info in this post helps other sqlite novices.

As a summary:

  • Journal mode is embedded in the sqlite database file itself.
  • The backup command changes the journal mode of the target database to match the source database.
  • Check the journal mode of a database with sqlite3 db.sqlite "PRAGMA journal_mode;".
  • Change the journal mode of a database with sqlite3 db.sqlite "PRAGMA journal_mode=DELETE;".

Sources