Steps to setting up relational database (sqlite) in swift

Question

I am working on an iOS app and I want to set up a relational database for it. I read that SQLite is a good database to use. I'm making the app in Swift. I've looked at several tutorials but it seems like all the ones that I found have the DBManager class or libraries in objective-c. Therefore, I would need a wrapper for the db class. I'm not really sure how the wrapper works and how I would be calling objective-c methods using swift syntax.

I was wondering if someone could help clarify the whole process for this from creating the database file and adding it to your xcode project to using objective-c libraries with swift syntax in order to run queries against the database file.

Also, is it worth it to just use Core Data which seems easier to use instead?

Answer 1

Objective-C tutorials are still more-or-less relevant, but obviously won't bring Swift-specific niceties into the fold (nor does FMDB, currently, as recommended by another commenter). I ended up writing SQLite.swift to utilize some of the more interesting aspects of Swift (type-safety and generics, optionals):

https://github.com/stephencelis/SQLite.swift

SQLite.swift provides compile-time safety/confidence, and removes the need for a lot of error handling. Statements and expressions are built in Swift proper, so runtime SQL syntax errors are unlikely.

See the documentation for more information on creating the database file and where to store it (depending on your needs):

https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#connecting-to-a-database

As far as Core Data is concerned, it (like most Apple libraries at the time of this answer) doesn't take advantage of Swift, but has a rich legacy and may be the way to go, especially for a smaller, persistent object graph. However, if you want control over a relational database, or if you plan on storing a large dataset that changes frequently, you may become frustrated with Core Data (and the domain-specific knowledge you'll need to attain),

Answer 2

I have myself followed this step by step and well explained tutorial by techtopia.

http://www.techotopia.com/index.php/Swift_iOS_8_Database_Implementation_using_SQLite

http://www.techotopia.com/index.php/An_Example_SQLite_based_iOS_8_Application_using_Swift_and_FMDB

It uses FMDB wrapper.

Creating the Database and Table

override func viewDidLoad() {
super.viewDidLoad()

let filemgr = NSFileManager.defaultManager()
let dirPaths =
NSSearchPathForDirectoriesInDomains(.DocumentDirectory,
        .UserDomainMask, true)

let docsDir = dirPaths[0] as! String

databasePath = docsDir.stringByAppendingPathComponent(
                "contacts.db")

if !filemgr.fileExistsAtPath(databasePath as String) {

    let contactDB = FMDatabase(path: databasePath as String)

    if contactDB == nil {
        println("Error: \(contactDB.lastErrorMessage())")
    }

    if contactDB.open() {
        let sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)"
        if !contactDB.executeStatements(sql_stmt) {
            println("Error: \(contactDB.lastErrorMessage())")
        }
        contactDB.close()
    } else {
        println("Error: \(contactDB.lastErrorMessage())")
    }
}
}

The code in the above method performs the following tasks:

-Identifies the application’s Documents directory and constructs a path to the contacts.db database file.

-Creates an NSFileManager instance and subsequently uses it to detect if the database file already exists.

-If the file does not yet exist the code creates the database by creating an FMDatabase instance initialized with the database file path. If the database creation is successful it is then opened via a call to the open method of the new database instance.

-Prepares a SQL statement to create the contacts table in the database and executes it via a call to the FMDB executeStatements method of the database instance.

-Closes the database.

SAVE DATA TO DATABASE

@IBAction func saveData(sender: AnyObject) {
let contactDB = FMDatabase(path: databasePath as String)

if contactDB.open() {

    let insertSQL = "INSERT INTO CONTACTS (name, address, phone) VALUES ('\(name.text)', '\(address.text)', '\(phone.text)')"

    let result = contactDB.executeUpdate(insertSQL, 
        withArgumentsInArray: nil)

    if !result {
        status.text = "Failed to add contact"
        println("Error: \(contactDB.lastErrorMessage())")
    } else {
        status.text = "Contact Added"
        name.text = ""
        address.text = ""
        phone.text = ""
    }
} else {
    println("Error: \(contactDB.lastErrorMessage())")
}
}

FETCH DATA FROM DATABASE

@IBAction func findContact(sender: AnyObject) {
let contactDB = FMDatabase(path: databasePath as String)

if contactDB.open() {
    let querySQL = "SELECT address, phone FROM CONTACTS WHERE name = '\(name.text)'"

    let results:FMResultSet? = contactDB.executeQuery(querySQL,
     withArgumentsInArray: nil)

    if results?.next() == true {
        address.text = results?.stringForColumn("address")
        phone.text = results?.stringForColumn("phone")
        status.text = "Record Found"
    } else {
        status.text = "Record not found"
        address.text = ""
        phone.text = ""
    }
    contactDB.close()
} else {
    println("Error: \(contactDB.lastErrorMessage())")
}
}