Use existing SQLite db in Swift

You are opening the db in the app support directory. But what if the database hasn't been copied there yet? Then your sqlite3_open will create an empty database. And then, when sqlite3_prepare fails, you are not looking at sqlite3_errmsg, which would have told you why it failed (namely that the table was not found).

So:

• Remove the empty db in the app support directory. The easiest way is to just temporarily delete the app entirely from the device/simulator.

• Open database with sqlite3_open_v2 with SQLITE_OPEN_READWRITE, but without SQLITE_OPEN_CREATE. Then a blank database will never be created accidentally.

• If open failed, copy db from bundle (assuming that's how you're distributing it with the app), and then reopen.

• If a SQLite call fails, use sqlite3_errmsg to get the text of the error message. This error message often offers precise info about where and/or why it failed, which is very useful when diagnosing problems.

Thus:

public class Board {
    public static let shared = Board()

    private var db: OpaquePointer?

    private init() {
        startDB()
    }

    private func startDB()  {
        let fileURL = try! FileManager.default
            .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent("words.db")

        // see if db is in app support directory already
        if sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK {
            print("db ok")
            return
        }

        // clean up before proceeding
        sqlite3_close(db)
        db = nil

        // if not, get URL from bundle
        guard let bundleURL = Bundle.main.url(forResource: "words", withExtension: "db") else {
            print("db not found in bundle")
            return
        }

        // copy from bundle to app support directory
        do {
            try FileManager.default.copyItem(at: bundleURL, to: fileURL)
        } catch {
            print("unable to copy db", error.localizedDescription)
            return
        }

        // now open database again
        guard sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK else {
            print("error opening database", errorMessage())
            sqlite3_close(db)
            db = nil
            return
        }

        // report success
        print("db copied and opened ok")
        return
    }

    public func getCount() {
        let query = "select count(*) from words;"
        var statement: OpaquePointer?                         // not = db

        guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
            print("unable to prepare query", errorMessage())  // if fails, print the error message
            return
        }

        defer { sqlite3_finalize(statement) }                 // make sure to finalize or else you will leak

        if sqlite3_step(statement) == SQLITE_ROW {            // in this case, since it's one row only, `if` is acceptable alternative to `while`
            let count = sqlite3_column_int(statement, 0)
            print("\(count)")
        }
    }

    public func errorMessage() -> String {
        return sqlite3_errmsg(db)
            .flatMap { String(cString: $0) } ?? "Unknown error"
    }
}

A few unrelated observations:

• You have shared to create singleton instance. That's fine. But if singleton, you wouldn't generally make init a public. Make it private if you want this to be a singleton, i.e. to prevent creating other instances accidentally.

• Neither db, startDB, nor getCount should be static. You have have the singleton instance, so you should use that, e.g. call Board.shared.getCount().

• Make sure to call sqlite3_finalize whenever you call sqlite3_prepare_v2. Otherwise, you will leak.

• Since init is calling startDB, there's no need to expose that method. (Clearly, if you add a “close” method and later want to reopen it, then fine, make it public.) But as it is, it is strange to expose a method only used by init.

You said that you want to print("board db is \(Board.getCount())"). Well, first it should be changed to return the value. And obviously you should use your singleton.

E.g.

public func count() -> Int? {
    let query = "select count(*) from words;"
    var statement: OpaquePointer?

    guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
        print("unable to prepare query", errorMessage())
        return nil
    }

    defer { sqlite3_finalize(statement) }

    guard sqlite3_step(statement) == SQLITE_ROW else {
        return nil
    }

    return Int(sqlite3_column_int(statement, 0))
}

And then

if let count = Board.shared.count() {
    print("board db is \(count)")
}

If you do not want to make the return value to be optional, you could alternatively change it to a method that throws errors. Both patterns are acceptable. But you need to provide a mechanism for the caller to know that the call was successful or not.

For swift you must convert the string to c string in sqlite3_prepare, also use another pointer for the statement :

var queryStatement : OpaquePointer?
sqlite3_prepare(Board.db, query.cString(using: .utf8), -1, &queryStatement, nil)

So, a bit frustrating, but many thanks to @Rob and @Ptit Xav for the persistent help. Since my requirements are very simple in use, I took a different direction. Dealing with the combination of things in play, I decided to focus on just getting a solution I could work with.

I shifted to GRDB, which is not exactly a breeze. But I got direct help from the developer/maintainer and that was enough to get me over the top. With GRDB, I can use raw SQL queries, but there's a (cryptic) system to connect app objects with table data. The code shows I'm not doing the latter.

After importing GRDB, and retaining the separate class WordDB to manage the DB, which at least gives me an interface to any updated db approach I attempt in the future, this works well enough to assure me that I can plug in the very simple queries I'll need:

public class WordDB {

    var dbPath : String = "words.sqlite3"
    var dbQueue : DatabaseQueue?
    
    init() {
        setup()
    }

func setup()  {
        
        var config = Configuration()
        config.readonly = true
        let filepath = Bundle.main.path(forResource: "words", ofType: "sqlite3")
        do {
            dbQueue = try DatabaseQueue(path: filepath!, configuration: config)
        } catch {
            print("damnation")
            return
        }
        
        do {
            try dbQueue!.read { db in
                if let row = try Row.fetchOne(db, sql: "SELECT * FROM words WHERE id = ?", arguments: [1]) {
                    let word: String = row["word"]
                    print(word)
                }
                
                if let row = try Row.fetchOne(db, sql: "SELECT count() FROM words") {

                    print(row)
                }
            }
            
        } catch {
            print("nope")
        }
    }
…
}

(I wanted to reuse a database that is in SQLite, in part because it will make a multilingual product much easier, and also because frequent updates to it will happen outside the app – the main reason I did not go the CoreData route. I now have what I want.)

I was confused about where exactly the database file should reside. Because the database is read by a class in a framework, which in turn is imported into the single-view app, I wasn't clear where it should be located as a resource. At the level of the framework (which makes the most sense), or the app? In the end I simply imported in both places, and I'll figure out which is redundant later.

Some improvements for clarity :

import Foundation
import SQLite3

public class Board {
public static let shared = Board()
var db: OpaquePointer?

public init() {
    self.startDB()
}

public func startDB()  {
    let fileURL = try! FileManager.default
        .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
        .appendingPathComponent("words.db")
    print(fileURL)
    // open database
    guard sqlite3_open(fileURL.path, &db) == SQLITE_OK else {
        print("error opening database")
        sqlite3_close(db)
        return
    }
    print(" db ok")
    return
}

public func getCount() {
    let query = "select count(*) from words;"
    print(query)
    var queryStatement : OpaquePointer?
    if sqlite3_prepare_v2(self.db, query.cString(using: .utf8), -1, &queryStatement, nil) == SQLITE_OK{
          while(sqlite3_step(queryStatement) == SQLITE_ROW){
               let count = sqlite3_column_int(queryStatement, 0)
               print("\(count)")
          }
          sqlite3_finalize(query_statement)
    } else {
        print("no go \(sqlite3_errmsg(db))")
    }
}
}

In viewController :

print ("board db is \(Board.shared.getCount())")   

Here is the working code for copy and open copied database file

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

    // see if db is in app support directory already
    if sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK {
        print("db ok")
        return
    }

    // clean up before proceeding
    sqlite3_close(db)
    db = nil

    // if not, get URL from bundle
    guard let bundleURL = Bundle.main.url(forResource: "securitymasterdatabase_db2", withExtension: "db") else {
        print("db not found in bundle")
        return
    }
    // copy from bundle to app support directory
    do {
        try FileManager.default.copyItem(at: bundleURL, to: fileURL)
    } catch {
        print("unable to copy db", error.localizedDescription)
        return
    }

    // now open database again
    guard sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK else {
        print("error opening database")
        sqlite3_close(db)
        db = nil
        return
    }

    // report success
    print("db copied and opened")
    return
}