While you should probably use one of the many SQLite wrappers, 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 or later, you can simply do:
import SQLite3
-
Create/open database.
let fileURL = try! FileManager.default .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true) .appendingPathComponent("test.sqlite") // open database var db: OpaquePointer? guard sqlite3_open(fileURL.path, &db) == SQLITE_OK else { print("error opening database") sqlite3_close(db) db = nil return }
Note, I know it seems weird to close the database upon failure to open, but the
sqlite3_open
documentation makes it explicit that we must do so to avoid leaking memory: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. -
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
For Swift 2 and older versions of Xcode, see previous revisions of this answer.