Swift iOS SQLite 3

While you should probably use one of the many SQLite wrappers (I prefer FMDB, myself), if you wanted to know how to call the SQLite library yourself, you would:

Configure your Swift project to handle SQLite C calls. If using Xcode 9, you can simply do:

import SQLite3

In earlier versions of Xcode, you can:

1. Create bridging header file to the project. See the Importing Objective-C into Swift section of the Using Swift with Cocoa and Objective-C. This bridging header should import sqlite3.h:

#import <sqlite3.h>

2. Add the libsqlite3.tbd (or for even older versions, the libsqlite3.dylib) to your project.

Create/open database.

let DBpath = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true).appendingPathComponent("mainDB.sqlite")

var db: OpaquePointer? = nil
        
if (sqlite3_open(DBpath.path, &db) != SQLITE_OK) {
    NSLog("Error!!! Can't open database")
} else {
    NSLog("Database OK :)")
}

Use sqlite3_exec to perform SQL (e.g. create table).

    if sqlite3_exec(db, "create table if not exists test (id integer primary key autoincrement, name text)", nil, nil, nil) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error creating table: \(errmsg)")
    }

Use sqlite3_prepare_v2 to prepare SQL with ? placeholder to which we'll bind value.

    var statement: OpaquePointer?

    if sqlite3_prepare_v2(db, "insert into test (name) values (?)", -1, &statement, nil) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing insert: \(errmsg)")
    }

    if sqlite3_bind_text(statement, 1, "foo", -1, SQLITE_TRANSIENT) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure binding foo: \(errmsg)")
    }

    if sqlite3_step(statement) != SQLITE_DONE {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure inserting foo: \(errmsg)")
    }

Note, that uses the SQLITE_TRANSIENT constant which can be implemented as follows:

    internal let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
    internal let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

Reset SQL to insert another value. In this example, I'll insert a NULL value:

    if sqlite3_reset(statement) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error resetting prepared statement: \(errmsg)")
    }

    if sqlite3_bind_null(statement, 1) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure binding null: \(errmsg)")
    }

    if sqlite3_step(statement) != SQLITE_DONE {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure inserting null: \(errmsg)")
    }

Finalize prepared statement to recover memory associated with that prepared statement:

    if sqlite3_finalize(statement) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error finalizing prepared statement: \(errmsg)")
    }

    statement = nil

Prepare new statement for selecting values from table and loop through retrieving the values:

    if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing select: \(errmsg)")
    }

    while sqlite3_step(statement) == SQLITE_ROW {
        let id = sqlite3_column_int64(statement, 0)
        print("id = \(id); ", terminator: "")

        if let cString = sqlite3_column_text(statement, 1) {
            let name = String(cString: cString)
            print("name = \(name)")
        } else {
            print("name not found")
        }
    }

    if sqlite3_finalize(statement) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error finalizing prepared statement: \(errmsg)")
    }

    statement = nil

Close database:

    if sqlite3_close(db) != SQLITE_OK {
        print("error closing database")
    }

    db = nil

SQLite Wrapper for iOS in Swift (old Xcode)

Introduction

This is an easy One-Class Wrapper for most of your SQL needs while working with iOS. Background

I am working on an app that will be searching and manipulating a large amount of data, so I decided to seed and store the data in a database instead of holding it in memory and praying the app will not crash. I was unable to find anything easy to use, and even the tutorials did not give instructions on actually making anything work. They just gave a few commands, so I decided to write my own wrapper. Using the Code

To add SQLite functions to your project:

   Make sure that the libsqlite3.tbd is linked to the project and you can do that in the General Settings of the app
   Add a header file to the project and call it BridgingHeader.h and type the following line to include the C API:
   Hide   Copy Code

   #include <sqlite3.h>

   Go to "Build Settings" and find "Objective-C Bridging Header." Use the search bar to find it quickly. Double-click and type "BridgingHeader.h". If you get "Could not import Objective-C Header," try "my-project-name/BridgingHeader.h"
   Go to "Build Phases," "Link Binary With Libraries," and add libsqlite3.tbd
   Add in the SQLDataIO.swift to your project (or just create a new one and copy/paste the code in from this sample project)

Functions:

   updateDatabase
   dbValue
   dbInt
   nextID
   getRows

To use: Hide Shrink Copy Code

var dbCommand: String = ""
dbCommand = "CREATE TABLE Family(ID INT PRIMARY KEY NOT NULL,
FirstName CHAR(100), LastName CHAR(100), Age INT);"
updateDatabase(dbCommand)

var databaseRows: [[String]] = [[]]
var id: Int = 0

for i in 0...6
{
    id = nextID("Family")

    dbCommand = "insert into Family(ID, FirstName, LastName, Age)
    values (\(id), '\(firstName[i])', '\(lastName[i])', '\(age[i])')"

    updateDatabase(dbCommand)
}

dbCommand = "select ID, FirstName, LastName, Age from Family"
databaseRows = getRows(dbCommand, numColumns: 4)

dbCommand = "UPDATE Family SET FirstName = 'Adam' WHERE ID = 1;"
updateDatabase(dbCommand)

dbCommand = "select LastName from Family where ID = 1"
let lName: String! = dbValue(dbCommand)

dbCommand = "select Age from Family where ID = 2"
let ageInt: Int = dbInt(dbCommand)

dbCommand = "select Age from Family where ID = 2"
let ageString: String  = dbValue(dbCommand)

dbCommand = "DELETE FROM Family WHERE ID = 1;"
updateDatabase(dbCommand)

References:

- For Swift 2, see previous revision of this answer. (https://stackoverflow.com/revisions/28642293/7) - FMDB old and great wrapper: http://www.theappguruz.com/blog/use-sqlite-database-swift