Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Accessing an SQLite Database in Swift

Tags:

sqlite

swift

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?

like image 636
Jase Avatar asked Jun 08 '14 03:06

Jase


People also ask

What is SQLite database in Swift?

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. It is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

How do I see SQLite database in Xcode?

Xcode 9.1:Open Finder, press Shift + Command + G, paste the path and press Go. Use DB Browser for SQLite to view the . sqlite file.


1 Answers

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.

like image 73
Rob Avatar answered Oct 24 '22 15:10

Rob