In this SQLite with Swift tutorial, you’ll learn to use a SQLite database with Swift projects by creating tables and inserting, updating and deleting rows.
Thanks Adam
I have got this working well in a MacOS application, but have run into problems with sandboxing. I have not worked out how to open a “new” file somewhere external to my sandbox, and then point the SQLite database into it. The SQLite calls fail since they cannot find the database thereafter. It only works if the db is in my application sandbox. Can you suggest a resource or way around this?
Hey ddarby,
Can you share some sample code on what you’re trying to do?
Thanks,
Adam
Some success in getting this working as expected, but it’s clunky, since I have to show a please allow access to your documents folder each time the app runs (then the files save in the database properly). If I don’t show it, loading bookmarks does not re-constitute the same permissions outside of the sandbox.
I am using a shared Bookmark singleton class (as suggested in macos - File access in a sandboxed Mac app with swift - Stack Overflow).
I included the info.plist key for NSDocumentsFolderUsageDescription. This does not seem to do anything. It’s meant to prompt the user for permission to access their Documents folder but I couldn’t work out how to trigger this.
In AppDelegate applicationDidFinishLaunching, I show the allowFolder() function (which opens an NSOpenPanel on their Documents directory). This is the minimal code in applicationDidFinishLaunching which works each application instantiation (see below for the complete code for Bookmark class):
Bookmark.shared.allowFolder()
But I originally put in the following code, as suggested in the Stack Overflow posting, but it does not work when I re-run the application beyond the first time, even though it is loading the prior bookmark(s).
if let url = Bookmark.shared.bookmarkURL(), !Bookmark.shared.fileExists(url) {
Bookmark.shared.allowFolder()
Bookmark.shared.saveBookmarks()
} else {
Bookmark.shared.loadBookmarks()
}
In AppDelegate applicationWillTerminate, I also put a Bookmarks.shared.saveBookmarks() call just to ensure they are saved each time.
If I now override the newDocument method in a subclass of NSDocumentController, I can show an save panel and create a new database which works (as long as allowFolder() was called). It does not help that the user selects the location in the NSSavePanel without calling allowFolder() initially. So, I’m stuck with presenting an ugly NSOpenPanel at the app start or it doesn’t work.
Here is the Bookmark class code:
class Bookmark {
static var shared = Bookmark() // singleton
private init() {}
var bookmarks = [URL: Data]()
func fileExists(_ url: URL) -> Bool {
var isDir = ObjCBool(false)
let exists = FileManager.default.fileExists(atPath: url.path, isDirectory: &isDir)
return exists
}
func bookmarkURL() -> URL? {
let url = URL.documentsDirectory().appendingPathComponent("Bookmarks.dict")
return url
}
func loadBookmarks() {
if let url = bookmarkURL(), fileExists(url) {
do {
let fileData = try Data(contentsOf: url)
if let fileBookmarks = try NSKeyedUnarchiver.unarchiveTopLevelObjectWithData(fileData) as! [URL: Data]? {
bookmarks = fileBookmarks
for bookmark in bookmarks {
restoreBookmark(bookmark)
}
}
} catch {
print("Couldn't load bookmarks")
}
} else {
print("Bookmarks URL does not exist...")
allowFolder()
saveBookmarks()
}
}
func saveBookmarks() {
if let url = bookmarkURL() {
do {
let data = try NSKeyedArchiver.archivedData(withRootObject: bookmarks, requiringSecureCoding: false)
try data.write(to: url)
} catch {
print("Couldn't save bookmarks")
}
}
}
func storeBookmark(url: URL) {
do {
let data = try url.bookmarkData(options: NSURL.BookmarkCreationOptions.withSecurityScope, includingResourceValuesForKeys: nil, relativeTo: nil)
bookmarks[url] = data
} catch {
Swift.print("Error storing bookmarks")
}
}
func restoreBookmark(_ bookmark: (key: URL, value: Data)) {
let restoredUrl: URL?
var isStale = false
Swift.print("Restoring \(bookmark.key)")
do {
restoredUrl = try URL.init(resolvingBookmarkData: bookmark.value, options: NSURL.BookmarkResolutionOptions.withSecurityScope, relativeTo: nil, bookmarkDataIsStale: &isStale)
} catch {
Swift.print("Error restoring bookmarks")
restoredUrl = nil
}
if let url = restoredUrl {
if isStale {
Swift.print("URL is stale")
} else {
if !url.startAccessingSecurityScopedResource() {
Swift.print("Couldn't access: \(url.path)")
}
}
}
}
@discardableResult func allowFolder(directoryURL: URL = URL.documentsDirectory()) -> URL?
{
let openPanel = NSOpenPanel()
openPanel.title = "Please choose a folder for database files."
openPanel.nameFieldLabel = "Folder:"
openPanel.prompt = "Allow Access To Documents"
openPanel.directoryURL = directoryURL
openPanel.allowsMultipleSelection = false
openPanel.canChooseDirectories = true
openPanel.canCreateDirectories = true
openPanel.canChooseFiles = false
openPanel.begin { (response) -> Void in
if response == NSApplication.ModalResponse.cancel {
return
}
if response == NSApplication.ModalResponse.OK {
if let url = openPanel.url {
Bookmark.shared.storeBookmark(url: url)
}
}
}
return openPanel.url
}
}
Any suggestions as to how to:
Adam thank you for the really nice tutorial! But I stuck on the Wrapping Table Creation point.
extension SQLiteDatabase {
func createTable(table: SQLTable.Type) throws {
//1
let createTableStatement = try prepareStatement(sqlStatement: table.createStatement)
//2
defer {
sqlite3_finalize(createTableStatement)
}
//3
guard sqlite3_step(createTableStatement) == SQLITE_OK else {
throw SQLiteError.Step(message: errorMessage)
}
print(“(table) table created”)
}
}
The guard statement doesn’t work properly in my case.
I added these two lines into else part of the guard statement:
let errorCode = sqlite3_step(createTableStatement)
print(“error code is (errorCode)”)
and figured out that sqlite3_step(createTableStatement) gives out (21) SQLITE_MISUSE result code and “not an error” in my console.
Please help me with that issue! I’d like to complete this wonderful tutorial!
Thank you!
Hi Kimitriy,
Glad you’re enjoying the tutorial :]
Can you try and change;
guard sqlite3_step(createTableStatement) == SQLITE_OK else {
to
guard sqlite3_step(createTableStatement) == SQLITE_DONE else {
Let me know if that helps? This error usually means you’re hitting a statement before the DB is ready.
Thanks,
Adam
Hi Adam! It helped! Thank you very much!
But could you explain something to me, please!
I added this:
let resultCode = sqlite3_step (createTableStatement)
print (“result code is \ (resultCode)”)
to the “True” part of the guard statement.
And the result code was “21”. Exactly the same as it was previously.
But the value of the SQLITE_DONE was “101”.
Thus, I just can’t figure out how the expression
sqlite3_step(createTableStatement) == SQLITE_DONE could be true.
But given that the guard statement gave out “Contact table created”, it was true.
But how?
Hi Kimitriy,
Forgive me if I understand the question correctly, but I think what was happening is changing to SQLITE_OK means the SQL is okay and almost setup but then it was continuing and trying to hit a statement when actually it wasn’t quite finished. Changing to SQLITE_DONE means the DB is ready to be used and is fully initialised.
Thank you,
Adam
Adam, thank you a lot!
I don’t know if its okay to write a comment here for that but I just wanted to let you know that this is a PERFECT tutorial!! Thank you so much!
Hey, pok12616 thanks for the lovely comments and glad you like the tutorial :]
No. I fixed it to my satisfaction. But the Forum closed the topic before I could post the solution.
Hi.
I wanted to ask what will be the value of part1DbPath variable in step " Opening a Connection" ?
Hi
looks nice the tutorial but also very useless for beginners. You do not even explain anything. You are expecting an advanced knowledge of swift. A good tutorial would be to explain also how to embed the sqlite binary in a new project for example. Presenting only a finished project without any explanations makes no sense.
Good stuff in here; excuse my ignorance but just wondering how can we pass on values into the code as opposed to have them hardcoded for this example?.
Also, how do we transfer this from the playground to the actual code? copy and paste? where to?
Hi @adamrush, I have a question about the topic of your article. Using your method to wrap API-c code to handle SQLite db, can I create relationships between tables? If yes I can see these relationships using core data?
Hi. I’m in Xcode 13.4 on an M1 Mac Mini and at the very beginning of “Open a Connection”. I’ve typed in the listed text. The console is open and there is no output when I run the playground.
If I paste in the text, still there is no output in the console.
There is an error message at the top of the screen stating “Failed to launch process. Failed to attach to stub for playground execution: error: debugserver x86_64 binary running in translation, attached failed…” And there is a right arrow in a circle to the right of this message but clicking on it does nothing.
Apparently, this is an Xcode Rosetta issue. Turning off Rosetta gets my by this error, but I’m sorting out code signing requirements, even though I’m building for a simulator.
OK. Past that.
Still no output in the console. The app compiles fine. When I run it, there is no output. At the top of the screen, there is text that states “Ready to continue The C API.” Am I missing something here?
OK. Had to redefine the Xcode build tools in settings. Why not having them defined doesn’t throw an error when you try to build is beyond me.
Now it’s this error.
expression failed to parse:
error: Couldn’t lookup symbols:
Tutorial_Sources.part1DbPath.unsafeMutableAddressor : Swift.Optional<Swift.String>
Also, when you next state to “Add the following to open the database:”
“let db = openDatabase()”
You don’t state where to add it. When I put that before the final }, there is an error message stating redefinition of variable.
Looking into your completed sample, your first example adds code that causes this error. It is not present in the completed example. When I comment out that code, and add the “let db = openDatabase()”, I get output in the console. Please remove this code below from the Open a Connection section of your tutorial.
guard let part1DbPath = part1DbPath else {
print(“part1DbPath is nil.”)
return nil
}