SQLite db create error in real iOS device

You are using appendingPathExtension. That is for adding an extension to a filename. You meant appendingPathComponent, which is for adding an entire filename (or other path component) to a URL.

That is why you are seeing a . between Documents and your filename, rather than a /, in the path shown in your error message. The simulator will blithely create a file called Documents.playToysDb.sqlite in your app’s root directory, but a device cannot. It expects it to be within the Documents directory, resulting in the failure when trying to create that file.

You asked about the correct way to get the database URL. A few observations:

1. You asked about urls(for:in:)[0] vs url(for:in:appropriateFor:create:). I would use the latter.

2. I would advise using .applicationSupportDirectory rather than .documentDirectory. The former is for the app’s support files. The latter is intended for user-facing files, which is not appropriate here. You can use .documentDirectory, but I would not advise it.

3. I would also avoid repeating this open in createDB and openDatabase. In fact, I would have a single method that would try to open the existing database, and if it fails, only then create the database and create/populate the tables.

4. Implicit in the above point, I would advise sqlite3_open_v2, in which you can chose to create a blank database or not. So I call it first without SQLITE_OPEN_CREATE (which, if successful, I know the database has already been created). Only if that fails do I try it again with that option (and then proceed to create the necessary tables).

Thus:

class DBHelper {
    static let shared = DBHelper()

    private var db: OpaquePointer?
    private let filename = "playToysDb.sqlite"

    private init() {
        openDatabase()
    }
}

private extension DBHelper {
    var errorMessage: String {
        sqlite3_errmsg(db).flatMap { String(cString: $0) } ?? "Unknown Error"
    }

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

        // try to open existing database (but don't create); if successful, return immediately

        if sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK {
            print("Database has been opened with path \(fileURL.path)")
            return
        }

        // if you got here, clean up and then try creating database

        sqlite3_close(db) // clean up after the above `sqlite3_open` call

        guard sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nil) == SQLITE_OK else {
            print("There is error in creating DB: ", errorMessage)
            sqlite3_close(db) // if creation failed, make sure to again clean up
            db = nil
            return
        }

        // if you got here, we created blank database, so populate however you want

        createTableCurrency()
        populateDefaultItems()

        print("Database has been created with path \(fileURL.path)")
    }

    func createTableCurrency() { ... }

    func populateDefaultItems() { ... }
}

Note, in addition to the above, I have tackled a few unrelated observations:

• As a matter of convention, I used shared for the name of the shared instance.

• I would mark the private properties and methods as such, to avoid external manipulation of them.

• If a query ever fails, I would call sqlite3_errmsg to get the error reported by SQLite.

• If open fails, always call sqlite3_close. As the docs say:

  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.

thank you for reply. I solved my issue using appendingPathComponent and using the first directory available for the user. Is that a correct way?

class  DBHelper {
    static let dbHelperInstance = DBHelper()
    var db : OpaquePointer?
    var path : String = "playToysDb.sqlite"
    private init() {}
    
    
    func initDatabase(){
        self.db = createDB()
        self.createTableCurrency()
        self.initDefaultItems()
    }
  func createDB() -> OpaquePointer? {
 let filePath = getDocumentsDirectory().appendingPathComponent(path);

          
          var dbPointer : OpaquePointer? = nil
          
          if sqlite3_open(filePath.path, &dbPointer) != SQLITE_OK {
              print("There is error in creating DB")
          
              return nil
          }else {
              print("Database has been created with path \(path)")
              return dbPointer
          }
        
        
      }
    
    
    func openDatabase()
    {

       let filePath = getDocumentsDirectory().appendingPathComponent(path);

        if sqlite3_open(fileURL.path, &db) == SQLITE_OK {
            print("Database Created !!")
        }

    }

    func getDocumentsDirectory() -> URL {
        // find all possible documents directories for this user
        let paths = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)

        // just send back the first one, which ought to be the only one
        return paths[0]
    }

}

This approach works too, which is the differences?

class  DBHelper {
        static let dbHelperInstance = DBHelper()
        var db : OpaquePointer?
        var path : String = "playToysDb.sqlite"
        private init() {}
        func initDatabase(){
            self.db = createDB()
            self.createTableCurrency()
            self.initDefaultItems()
        }
      func createDB() -> OpaquePointer? {
      let fileURL = try! FileManager.default
            .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
            .appendingPathComponent(path)
    
              
              var dbPointer : OpaquePointer? = nil
              
              if sqlite3_open(fileURL.path, &dbPointer) != SQLITE_OK {
                  print("There is error in creating DB")
              
                  return nil
              }else {
                  print("Database has been created with path \(path)")
                  return dbPointer
              }
            
            
          }
        
        
        func openDatabase()
        {
    
              let fileURL = try! FileManager.default
            .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
            .appendingPathComponent(path)

    
            if sqlite3_open(fileURL.path, &db) == SQLITE_OK {
                print("Database Created !!")
            }
    
        }
    
     
    
    }