Storing and Retrieving Image in Sqlite with swift

Question

How to store and fetch images in SQLite and in what format the images get saved? It would be more helpful if explained with an example.

Answer 1

 import Foundation
import SQLite3
import UIKit

class DBHelper
{
    init()
    {
        db = openDatabase()
        createTable()
    }

    let dbPath: String = "myDb.sqlite"
    var db:OpaquePointer?

    func openDatabase() -> OpaquePointer?
    {
        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent(dbPath)
        var db: OpaquePointer? = nil
        if sqlite3_open(fileURL.path, &db) != SQLITE_OK
        {
            print("error opening database")
            return nil
        }
        else
        {
            print("Successfully opened connection to database at \(dbPath)")
            return db
        }
    }
    
    func createTable() {
        let createTableString = "CREATE TABLE IF NOT EXISTS person(Id INTEGER PRIMARY KEY,name TEXT,age INTEGER, gender TEXT,image BLOB);"
        var createTableStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK
        {
            if sqlite3_step(createTableStatement) == SQLITE_DONE
            {
                print("person table created.")
            } else {
                print("person table could not be created.")
            }
        } else {
            print("CREATE TABLE statement could not be prepared.")
        }
        sqlite3_finalize(createTableStatement)
    }
    
    
    func insert(id: Int, name: String, age: Int, gender: String, image: UIImage?) {
        let persons = read()
        for p in persons {
            if p.id == id {
                return
            }
        }

        let insertStatementString = "INSERT INTO person (Id, name, age, gender, image) VALUES (?, ?, ?, ?, ?);"
        var insertStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            sqlite3_bind_int(insertStatement, 1, Int32(id))
            sqlite3_bind_text(insertStatement, 2, (name as NSString).utf8String, -1, nil)
            sqlite3_bind_int(insertStatement, 3, Int32(age))
            sqlite3_bind_text(insertStatement, 4, (gender as NSString).utf8String, -1, nil)

            // Convert UIImage to Data
            if let image = image, let imageData = image.jpegData(compressionQuality: 1.0) {
                sqlite3_bind_blob(insertStatement, 5, (imageData as NSData).bytes, Int32(imageData.count), nil)
            } else {
                sqlite3_bind_null(insertStatement, 5)
            }

            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
    }

    
    func read() -> [Person] {
        let queryStatementString = "SELECT * FROM person;"
        var queryStatement: OpaquePointer? = nil
        var persons: [Person] = []

        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let id = sqlite3_column_int(queryStatement, 0)
                let name = String(cString: sqlite3_column_text(queryStatement, 1))
                let age = sqlite3_column_int(queryStatement, 2)
                let gender = String(cString: sqlite3_column_text(queryStatement, 3))

                // Retrieve Image Data
                var image: UIImage? = nil
                if let imageDataPointer = sqlite3_column_blob(queryStatement, 4) {
                    let imageDataSize = sqlite3_column_bytes(queryStatement, 4)
                    let data = Data(bytes: imageDataPointer, count: Int(imageDataSize))
                    image = UIImage(data: data)
                }

                persons.append(Person(id: Int(id), name: name, age: Int(age), gender: gender, image: image))
            }
        } else {
            print("SELECT statement could not be prepared.")
        }
        sqlite3_finalize(queryStatement)
        return persons
    }

    
    func deleteByID(id:Int) {
        let deleteStatementStirng = "DELETE FROM person WHERE Id = ?;"
        var deleteStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, deleteStatementStirng, -1, &deleteStatement, nil) == SQLITE_OK {
            sqlite3_bind_int(deleteStatement, 1, Int32(id))
            if sqlite3_step(deleteStatement) == SQLITE_DONE {
                print("Successfully deleted row.")
            } else {
                print("Could not delete row.")
            }
        } else {
            print("DELETE statement could not be prepared")
        }
        sqlite3_finalize(deleteStatement)
    }
    
}

     db.insert(id: id, name: lblName.text ?? "", age: Int(lblGender.text ?? "") ?? 0, gender: lblGender.text ?? "", image: imgVw.image)
 persons = db.read()

Answer 2

You can also store your image directly as a BLOB, however it depends on which framework you use for SQLite access. In case you use SQLite.swift, then there is an option:

Set up a file SQLiteHelper.swift like that:

class SQLiteHelper{
    var db: Connection!

    let personsTable = Table("person")
    let id = Expression<Int>("id")
    let firstName = Expression<String>("firstName")
    let lastName = Expression<String>("lastName")
    let profileImage = Expression<Data>("profileImage")
    let date = Expression<Date>("savedAt")

    init() {
        do{
            let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
            let dbTemp = try Connection("\(path)/myDb.sqlite3") //Create db if not existing
            self.db = dbTemp
        }
        catch {
            print("Error info: \(error)")
        }
    }

    public func insertData(firstNameVal: String,
                           lastNameVal: String,
                           profileImageVal: Data,
                           dateVal: Date){

        do{
            //Create a new table only if it does not exist yet
            try db.run(personsTable.create(ifNotExists: true) { t in      // CREATE TABLE "person" (
                t.column(id, primaryKey: true)          //     "id" INTEGER PRIMARY KEY NOT NULL,
                t.column(firstName)                     //     "firstName" TEXT,
                t.column(lastName)                      //     "lastName" TEXT,
                t.column(profileImage)                  //     "profileImage" BLOB,
                t.column(date)                          //     "savedAt" DATETIME)
            })
        }
        catch {
            print("The new SQLite3 Table could not be added: \(error)")
        }

        do{
            try db.run(personsTable.insert(firstName <- firstNameVal,
                                            lastName <- lastNameVal,
                                            profileImage <- profileImageVal,
                                            date <- dateVal
            ))
        }
        catch {
            print("Could not insert row: \(error)")
        }
    }

    public func getData() -> [Person]{
        var persons = [Person]()
        do{
            for row in try db.prepare(personsTable) {
                let person: Person = Person(firstName: row[firstName],
                                               lastName: row[lastName],
                                               profileImage: row[profileImage],
                                               savedAt: row[date])

                persons.append(person)
            }
        }
        catch {
            print("Could not get row: \(error)")
        }
        return persons
    }

Now create a file Person.swift and put the following struct inside of it:

import Foundation

struct Person: Identifiable {
    var id = UUID()
    var firstName: String
    var lastName: String
    var profileImage: Data
    var savedAt: Date
}

Store Data

In order to store data as a .png BLOB you would now basically do something like that:

var databaseHelper: SQLiteHelper = SQLiteHelper.init()
self.databaseHelper.insertData(firstNameVal: "yourFirstName",
                                   lastNameVal: "yourLastName",
                              profileImageVal: yourImageView.pngData(),
                              dateVal: Date())

Retreive Data

If you want to display the image later in another Imageview you would have to do this:

var persons = self.databaseHelper.getData()
let profileImage = UIImage(data: persons[0].profileImage)
let myImageView = UIImageView(image: profileImage)

Saving UIImage as BLOB

I have saved the image as a .png because I want to use my database outside of iOS and therefore want to ensure compatibility. If you want you can also store your UIImage directly. You would roughly need to change it like that:

let profileImage = Expression<UIImage>("profileImage")
...
profileImageVal: yourImageView,
...
let myImageView = persons[0].profileImage
...

import Foundation
import UIKit

struct Person: Identifiable {
    var id = UUID()
    var firstName: String
    var lastName: String
    var profileImage: UIImage
    var savedAt: Date
}

Note: SQLite.swift also supports lazy loading, which would probably make more sense in ascenario like that...

Answer 3

Alternative

1. save your image into document directory.
2. save your image file path or name of image in sqlite
3. Get the image path or name from sqlite and access that path from document directory.

Take Ref : Iphone : How to Display Document Directory images in Image View?

Answer 4

Image itself cannot be stored into a database columns but you can first convert it into a string and then store it. The string is called base64 string. As far as I know, any image can be converted to that and reversely.

To encode to base 64:

let image : UIImage = UIImage(named:"imageNameHere")!
let imageData:NSData = UIImagePNGRepresentation(image)!
let strBase64 = imageData.base64EncodedString(options: .lineLength64Characters)

Now your UIImage object is converted to a String! Save strBase64 to SQLite DB. Remember to use text as column type because this string is very long.

To decode back to UIImage:

let dataDecoded:NSData = NSData(base64EncodedString: strBase64, options: NSDataBase64DecodingOptions(rawValue: 0))!
let decodedimage:UIImage = UIImage(data: dataDecoded)!