sqlite3_open works in swift playground not swift app

Question

when I run the following code in swift playground it returned success. When I copied it to my Xcode project it returned failure. I figure it has something to do with the path so I googled and tried, path.cString(using: String.Encoding.utf8) but still no luck. what I don't understand is why it works in playground but not in the app itself.

var db: OpaquePointer? = nil;
let path:String="/Users/williamstorey/Documents/db182.db"
if sqlite3_open(path ,&db) == SQLITE_OK {
   print("SUCCESS")
} else {
   print("FAILURE");
}

Answer 1

The most likely problem is that your app is sandboxed and can’t just open the file there. You can build the URL dynamically, and you will get a safe file URL/path within the sandbox:

let fileURL = try! FileManager.default
    .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
    .appendingPathComponent("db182.db")

See File System Programming Guide: The Library Directory Stores App-Specific Files. Also see About App Sandbox.

If you really want to open files outside of the sandbox, you would need to request the user’s permission (e.g., with an NSOpenPanel, as discussed in Accessing files from the macOS App Sandbox).

---

A few additional observations:

1. I would capture the return code from any sqlite3_xxx function (such as sqlite3_open or sqlite3_open_v2) and use that to diagnose the source of the problem. You can use this return code to determine not only that it was not SQLITE_OK, but more importantly, what return code you actually received.

2. In the case of an error, I would also suggest retrieving the error message with sqlite3_errmsg. In some error conditions (esp. when preparing SQL statements), this can offer more information about what went wrong (though, admittedly, here, it is not that illuminating). But it translates the error into something easily understood (without having to pour through the error codes).

3. If sqlite3_open fails, do not forget to sqlite3_close. I know it seems strange, but we must close the database whether it succeeds or not, or else you will leak memory. As the documentation says:

   Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

   If you are storing that database connection, you might also want to nil that opaque pointer after when you sqlite3_close, to ensure that this property more accurately reflects the state of the database connection.

Thus, pulling that together, perhaps something along the lines of:

var db: OpaquePointer?

func open(
    fileURL: URL,
    flags: Int32 = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
) throws {
    let returnCode = sqlite3_open_v2(fileURL.path, &db, flags, nil)

    guard returnCode == SQLITE_OK else {
        let message = sqlite3_errmsg(db).flatMap { String(cString: $0) } ?? "Unknown error"
        print(#function, "error code:", returnCode, "error message:", message)
        close(db)
        throw …
    }
}

func close() {
    sqlite3_close(db)
    db = nil
}