Accessing an SQLite Database in Swift

Question

I'm looking for a way to access an SQLite database in my app with Swift code.

I know that I can use an SQLite Wrapper in Objective C and use the bridging header, but I'd rather be able to do this project entirely in Swift. Is there a way to do this, if so, can someone point me to a reference that shows how to submit a query, retrieve rows, etc?

Answer 1

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:

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

   import SQLite3 

2. 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.

3. 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)") } 

4. 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) 

5. 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)") } 

6. 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 

7. 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 

8. 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.