Accessing an SQLite Database in Swift

Solution 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:

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.

Solution 2:

The best you can do is import the dynamic library inside a bridging header:

Add libsqlite3.dylib to your "Link Binary With Libraries" build phase
Create a "Bridging-Header.h" and add #import <sqlite3.h> to the top
set "Bridging-Header.h" for the "Objective-C Bridging Header" setting in Build Settings under "Swift Compiler - Code Generation"

You will then be able to access all of the c methods like sqlite3_open from your swift code.

However, you may just want to use FMDB and import that through the bridging header as that is a more object oriented wrapper of sqlite. Dealing with C pointers and structs will be cumbersome in Swift.

Solution 3:

I too was looking for some way to interact with SQLite the same way I was used to doing previously in Objective-C. Admittedly, because of C compatibility, I just used the straight C API.

As no wrapper currently exists for SQLite in Swift and the SQLiteDB code mentioned above goes a bit higher level and assumes certain usage, I decided to create a wrapper and get a bit familiar with Swift in the process. You can find it here: https://github.com/chrismsimpson/SwiftSQLite.

var db = SQLiteDatabase();
db.open("/path/to/database.sqlite");

var statement = SQLiteStatement(database: db);

if ( statement.prepare("SELECT * FROM tableName WHERE Id = ?") != .Ok )
{
    /* handle error */
}

statement.bindInt(1, value: 123);

if ( statement.step() == .Row )
{
    /* do something with statement */
    var id:Int = statement.getIntAt(0)
    var stringValue:String? = statement.getStringAt(1)
    var boolValue:Bool = statement.getBoolAt(2)
    var dateValue:NSDate? = statement.getDateAt(3)
}

statement.finalizeStatement(); /* not called finalize() due to destructor/language keyword */

Solution 4:

I've created an elegant SQLite library written completely in Swift called SwiftData.

Some of its feature are:

Bind objects conveniently to the string of SQL
Support for transactions and savepoints
Inline error handling
Completely thread safe by default

It provides an easy way to execute 'changes' (e.g. INSERT, UPDATE, DELETE, etc.):

if let err = SD.executeChange("INSERT INTO Cities (Name, Population, IsWarm, FoundedIn) VALUES ('Toronto', 2615060, 0, '1793-08-27')") {
    //there was an error during the insert, handle it here
} else {
    //no error, the row was inserted successfully
}

and 'queries' (e.g. SELECT):

let (resultSet, err) = SD.executeQuery("SELECT * FROM Cities")
if err != nil {
    //there was an error during the query, handle it here
} else {
    for row in resultSet {
        if let name = row["Name"].asString() {
            println("The City name is: \(name)")
        }
        if let population = row["Population"].asInt() {
            println("The population is: \(population)")
        }
        if let isWarm = row["IsWarm"].asBool() {
            if isWarm {
                println("The city is warm")
            } else {
                println("The city is cold")
            }
        }
        if let foundedIn = row["FoundedIn"].asDate() {
            println("The city was founded in: \(foundedIn)")
        }
    }
}

Along with many more features!

You can check it out here

Solution 5:

Yet another SQLite wrapper for Swift 2 and Swift 3: http://github.com/groue/GRDB.swift

Features:

An API that will look familiar to users of ccgus/fmdb
A low-level SQLite API that leverages the Swift standard library
A pretty Swift query interface for SQL-allergic developers
Support for the SQLite WAL mode, and concurrent database access for extra performance
A Record class that wraps result sets, eats your custom SQL queries for breakfast, and provides basic CRUD operations
Swift type freedom: pick the right Swift type that fits your data. Use Int64 when needed, or stick with the convenient Int. Store and read NSDate or NSDateComponents. Declare Swift enums for discrete data types. Define your own database-convertible types.
Database Migrations
Speed: https://github.com/groue/GRDB.swift/wiki/Performance