SQLite in Swift (Offline Mode functionality iOS) - Mobikul

Nows-a days offline mode is a very common feature in app development. Every developer wants to run minimum app functionality without internet and device connect with internet all data automatically sync on remote DB. CoreData, SQLite, Firebase, etc. is handling offline functionality. Today we discuss about SQLite using swift.

What is SQLite?

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.
Source: https://www.sqlite.org/

Let’s implement SQLite in swift for offline applications.

In this example, we will use the FMDatabase library to access SQLite.

Swift version: 5.2
iOS version: 14
Xcode: 12.0

First Create a project from Xcode.

File ▸ New ▸ Project…. Choose the iOS ▸ Application ▸ Single View App template and create a new project.

Step 1: First copy .sqlite file from FileManager to application.

class Util: NSObject {
    func getPath(_ fileName: String) -> String {
        let documentsURL = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)[0]
        let fileURL = documentsURL.appendingPathComponent(fileName)
        return fileURL.path
    }
    
    func copyFile(_ fileName: NSString) {
        let dbPath: String = getPath(fileName as String)
        let fileManager = FileManager.default
        if !fileManager.fileExists(atPath: dbPath) {
            let documentsURL = Bundle.main.resourceURL
            let fromPath = documentsURL!.appendingPathComponent(fileName as String)
            var error : NSError?
            do {
                try fileManager.copyItem(atPath: fromPath.path, toPath: dbPath)
            } catch let error1 as NSError {
                print(error)
                error = error1
            }
        }
    }
 
}

class Util: NSObject {

func getPath(_ fileName: String) -> String {

let documentsURL = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)[0]

let fileURL = documentsURL.appendingPathComponent(fileName)

return fileURL.path

}

func copyFile(_ fileName: NSString) {

let dbPath: String = getPath(fileName as String)

let fileManager = FileManager.default

if !fileManager.fileExists(atPath: dbPath) {

let documentsURL = Bundle.main.resourceURL

let fromPath = documentsURL!.appendingPathComponent(fileName as String)

var error : NSError?

do {

try fileManager.copyItem(atPath: fromPath.path, toPath: dbPath)

} catch let error1 as NSError {

print(error)

error = error1

}

Note: Make sure .sqlite already added on your project.
Step 2: Now create a DBHelper class, where we create all the oparations.

class DBHelper: NSObject {
    
    var database: FMDatabase? = nil
    
    class func getInstance() -> DBHelper
    {
        if(dbpath.database == nil)
        {
            dbpath.database = FMDatabase(path: Util.getPath("test.sqlite"))
        }
        return dbpath
    }
}

class DBHelper: NSObject {

var database: FMDatabase? = nil

class func getInstance() -> DBHelper

{

if(dbpath.database == nil)

{

dbpath.database = FMDatabase(path: Util.getPath("test.sqlite"))

}

return dbpath

}

Step 3: Now We will create Insert update,delete and select oprations.
For Insert:

func insert(_ name: String) -> Bool {
        var isInserted = false
        isInserted = dbpath.database?.executeUpdate("INSERT INTO tableList (name) VALUES (?)", withArgumentsIn: [name])
        return isInserted
           
}

func insert(_ name: String) -> Bool {

var isInserted = false

isInserted = dbpath.database?.executeUpdate("INSERT INTO tableList (name) VALUES (?)", withArgumentsIn: [name])

return isInserted

}

For Update value in the DB:

func update( name: String, id:Int) -> Bool{
        dbpath.database?.open()
        let isupdate = dbpath.database?.executeUpdate("update tableList set name=? WHERE id=?", withArgumentsIn: [name,id])
        dbpath.database?.close()
        return isupdate
}

func update( name: String, id:Int) -> Bool{

dbpath.database?.open()

let isupdate = dbpath.database?.executeUpdate("update tableList set name=? WHERE id=?", withArgumentsIn: [name,id])

dbpath.database?.close()

return isupdate

}

To delete Row form the table:

func delete(_ id: int) -> Bool {
     dbpath.database?.open()
     let isDeleted = dbpath.database?.executeUpdate("DELETE FROM tableList WHERE id=?", withArgumentsIn: [id])
     dbpath.database?.close()
     return isDeleted
 }

func delete(_ id: int) -> Bool {

dbpath.database?.open()

let isDeleted = dbpath.database?.executeUpdate("DELETE FROM tableList WHERE id=?", withArgumentsIn: [id])

dbpath.database?.close()

return isDeleted

}

Now get all the row from the table:

func get() -> [String] {
        dbpath.database?.open()
        let resultSet: FMResultSet = dbpath.database?.executeQuery("SELECT * FROM tableList", withArgumentsIn: [])
        let names = [String]()
        if (resultSet != nil) {
            while resultSet.next() {
                names.append(resultSet.string(forColumn: "name"))
            }
        }
        dbpath.database?.close()
        return names
 }

func get() -> [String] {

dbpath.database?.open()

let resultSet: FMResultSet = dbpath.database?.executeQuery("SELECT * FROM tableList", withArgumentsIn: [])

let names = [String]()

if (resultSet != nil) {

while resultSet.next() {

names.append(resultSet.string(forColumn: "name"))

}

dbpath.database?.close()

return names

}

Step 4: Now we call the function that we already created.

DBHelper.getInstance().insert("dev");
DBHelper.getInstance().delete(100);
DBHelper.getInstance().update(name:"Jhon",id:100);
DBHelper.getInstance().get();

DBHelper.getInstance().insert("dev");

DBHelper.getInstance().delete(100);

DBHelper.getInstance().update(name:"Jhon",id:100);

DBHelper.getInstance().get();

I hope this code will help you better to understand SQLite in Swift. If you feel any doubt or query please comment below.

Thanks for the read this blog and if you want to visit my other blog click here.

FMDatabase sqlite swift

Debabrata Roy

Specializes in Core Data, GCD, Swift and Storyboards. Expert in Firebase Push Notifications, threading and performance tuning. Proficient in managing Apple Developer Account and optimizing iOS app performance.

30 Jul, 2021
Created by - Debabrata Roy

. . .

SQLite in Swift

Leave a Comment Cancel Reply

Message Sent!