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

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

- Good Example code with wrapper: https://www.codeproject.com/Tips/1103368/SQLite-Wrapper-for-iOS-in-Swift