Saving database changes into .db file with SQLite (Swift)

You cannot modify the contents of the bundle. So you will want to copy the file to some a directory where it can be modified. Historically we might have advised using “documents” folder for this. But nowadays we use the “application support directory”. See iOS Storage Best Practices.

Anyway, the basic idea is:

• include the original database in the bundle (by adding it to your app’s target, if you haven’t already);

  

• try to open the database (with SQLITE_READWRITE option but not the SQLITE_CREATE option ... we don’t want it creating a blank database if it doesn’t find it) in the application support directory; and

• if that fails (because the file is not found), copy the database from the bundle to the application support directory and try again

Thus, perhaps something like:

var db: OpaquePointer?

enum DatabaseError: Error {
    case bundleDatabaseNotFound
    case sqliteError(Int32, String?)
}

func openDatabase() throws {
    let fileURL = try FileManager.default.url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
        .appendingPathComponent("database.db")

    // try opening database and just return if it succeeded

    if sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK {
        return
    }

    // if it failed, clean up before trying again ...

    sqlite3_close(db)
    db = nil

    // then copy database from bundle ...

    guard let bundlePath = Bundle.main.url(forResource: "database", withExtension: "db") else {
        throw DatabaseError.bundleDatabaseNotFound
    }
    try FileManager.default.copyItem(at: bundlePath, to: fileURL)

    // and try again

    let rc = sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE, nil)

    if rc == SQLITE_OK { return }

    // and if it still failed, again, clean up, and then throw error

    let errorMessage = sqlite3_errmsg(db)
        .flatMap { String(cString: $0) }

    sqlite3_close(db)
    db = nil

    throw DatabaseError.sqliteError(rc, errorMessage)
}