Communicate with SQLite database

Communication with SQLite database iOS

In this tutorial, i will cover how to read, update, create tables inside an SQLite database using the FMDB library. For installation, the best way is to use CocoaPods and add pod ‘FMDB’ inside your Podfile.

After the install import the header file import “FMDB.h”, but note that you will need to create a BridgingHeader in order to import it in Swift.

The first thing we need to do is to open the database file:

    var database = FMDatabase()
    
    func openDB(){
        let filemgr = NSFileManager.defaultManager()
        let dirPaths =
        NSSearchPathForDirectoriesInDomains(.DocumentDirectory,
            .UserDomainMask, true)
        
        let docsDir = dirPaths[0]
        
        let databasePath = docsDir.stringByAppendingString("/dbname.sqlite")
        let originalPath = NSBundle.mainBundle().resourcePath!.stringByAppendingString("/dbname.sqlite")
        var success = filemgr.fileExistsAtPath(databasePath)
        
        if !success {
            do {
                try filemgr.removeItemAtPath(databasePath)
            } catch {
                print("this code runs when the function exits, even when it exits by an exception")
                // Error - handle if required
            }
            do {
                try filemgr.copyItemAtPath(originalPath, toPath: databasePath)
            } catch {
                print("this code runs when the function exits, even when it exits by an exception")
                // Error - handle if required
            }
            success = filemgr.fileExistsAtPath(databasePath)
        }
        
        if success{
            database = FMDatabase(path: databasePath)
            
            if database.open(){
                loadObjectsFromDB()
                print("Database opened succesfully!")
            }
        }
    }

If the database is located and successfully opened, we can proceed with performing actions to the database.

READ TABLE:

    func loadObjectsFromDB(){
        if let rs = database.executeQuery("select *, rowid from YourTableName", withArgumentsInArray: nil) {
            while rs.next() {
                let rowid = rs.intForColumn("rowid")
                let exampleString = rs.stringForColumn("example")
                // parse the columns here
            }
        } else {
            print("select failed: \(database.lastErrorMessage())")
        }
    }

UPDATE TABLE:

    func insertValuesInTable(){
        let rs = database.executeUpdate("INSERT INTO myTable VALUES (?)", withParameterDictionary: ["exampleKey":"exampleValue"]);
        if rs == true {
            //Inserted
        }
    }

CREATE TABLE:

    func createTableInDb(){
    let rs = database.executeUpdate("create table test(x text, y text, z text)", values: nil)
        if rs == true {
            //Created
        }
    }

Leave a Reply

Your email address will not be published. Required fields are marked *