Where to locate Sqlite database in Xcode project

Question

Sorry for repeating a question albeit slightly differently but I've tried everything, searched this forum and google but can't solve this issue. I have a Sqlite database (only 20kb size) which I want to use in a Swift 4 project using Xcode. I've put the database (a .db file) in the folder where Xcode saved my project but when I run the app I get the 'no such table error'. Following the file path in the debug screen takes me to a file of 0kb i.e. empty. I thought I'd solved this by using the path name (i.e. FileManager.default.fileExists(atPath: "/Users/...") but it won't work on my iPhone (only in Simulator) so I've gone back to trying to use the URL approach. I've copied the main bit of code but my suspicion is that there is something either wrong with the first few lines or I haven't filed the .db file in the right place. I have added it to the project inspector, it is associated with the project as the target and it shows under the copy bundle resource bit of the build phases screen. I'm really struggling here so any help really appreciated.

//Function to open Cities database, add cities to array, sort alphabetically and then append "Please select city" at the start of the array
func populatePickerView() {
    let fileURL = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask).first!
    let databaseURL = fileURL.appendingPathComponent("Wanderer.db")
    let database = FMDatabase(path: databaseURL.absoluteString)

    guard database.open() else {
        print("Unable to open database")
        return
    }

    do {
        let cities:FMResultSet = try database.executeQuery("SELECT City from Cities", values: nil)

        while cities.next() {
            if let result = cities.string(forColumn: "City") {
                cityData.append(result)
            }
        }
    } catch {
        print("OOPS, some sort of failure")
    }

    cityData = cityData.sorted(by: <)
    cityData.insert("Please select city", at: 0)
}

It's in your app bundle, not the Documents folder. – rmaddy Oct 30 '18 at 23:46 — rmaddy, Oct 30 '18 at 23:46

Retterdesdialogs · Accepted Answer · 2018-10-31T15:51:57.307

If you drag and dropped your database to your Xcode project structure with "copy items if needed" and your target selected, your database is located here:

let databaseInMainBundleURL = Bundle.main.resourceURL?.appendingPathComponent("Wanderer.db")

If you just want to read the data this will be fine, because the main bundle is read only.

If you want to save something in your database you need to copy the data base to the documents directory.

You can use this function to check if your database exists in document directory and copy if needed.

func copyDatabaseIfNeeded(_ database: String) {

    let fileManager = FileManager.default

    let documentsUrl = fileManager.urls(for: .documentDirectory, in: .userDomainMask)

    guard documentsUrl.count != 0 else {
        return
    }

    let finalDatabaseURL = documentsUrl.first!.appendingPathComponent("\(database).db")

    if !( (try? finalDatabaseURL.checkResourceIsReachable()) ?? false) {
        print("DB does not exist in documents folder")

        let databaseInMainBundleURL = Bundle.main.resourceURL?.appendingPathComponent("\(database).db")

        do {
            try fileManager.copyItem(atPath: (databaseInMainBundleURL?.path)!, toPath: finalDatabaseURL.path)
        } catch let error as NSError {
            print("Couldn't copy file to final location! Error:\(error.description)")
        }

    } else {
        print("Database file found at path: \(finalDatabaseURL.path)")
    }
}

and use with:

copyDatabaseIfNeeded("Wanderer")

After that you will be able to use your populatePickerView function.

You're a star!! Just got home and tried that and it works like a dream - both in simulator and on my device. I'm only reading the data for now so just needed the one line. I guess if I update the list of cities I'll need to move the updated database into the bundle and re-link but thanks so much. I think I understand the idea of Bundle better now as well. — RobM, Oct 31 '18 at 17:59
You can not move the updated database into the bundle programmatically, because the bundle is read only. If you update your database, you need to update the database in Xcode and recompile the app or use the database in documents directory. — Retterdesdialogs, Nov 05 '18 at 08:28

Where to locate Sqlite database in Xcode project

1 Answers1

Linked