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.