Establishing a Read-Write connection to a database in SQLite.swift

Question

I'm working a project where I need to use a SQLite database to read, create , and edit different objects. I thought I had established the connection properly but, it turns out I had only established a read only connection. How do I modify this code to be a read-write connection using SQLite.swift

import Foundation
import SQLite
import UIKit


let path = Bundle.main.path(forResource: "Assignment2", ofType: "sqlite3")
//Array of customer structs to populate the table
var customerArray: [Customer] = []


class CustomerPageVC: UIViewController, UITableViewDelegate, UITableViewDataSource {

//IBOutlets
@IBOutlet weak var tableView: UITableView!
@IBOutlet weak var addCustButton: UIButton!



override func viewDidLoad() {
    super.viewDidLoad()
    //Additional Setup
    do {
        //Search for DB in documents directory
        let db = try Connection(path!)
        let customers = Table("Customers")
        //Define the columns of the table as expressions
        let id = Expression<Int64>("CustomerID")
        let name = Expression<String>("CustomerName")
        let contactName = Expression<String>("ContactName")
        let address = Expression<String>("Address")
        let city = Expression<String>("City")
        let postalCode = Expression<String>("PostalCode")
        let country = Expression<String>("Country")
        
        //Load the data from db file into customerArray
        for customer in try db.prepare(customers) {
            let cust = Customer(Int(customer[id]), customer[name], customer[contactName], customer[address], customer[city], customer[postalCode], customer[country])
            customerArray.append(cust)
        }
    }
    catch {
        print(error)
    }
    tableView.delegate = self
    tableView.dataSource = self
} 
}

Edit there's a func copyDatabaseIfNeeded in the documentation so maybe my true question is in what context do I use this func to copy the database to the application support directory?

func copyDatabaseIfNeeded(sourcePath: String) -> Bool {
let documents = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
let destinationPath = documents + "/db.sqlite3"
let exists = FileManager.default.fileExists(atPath: destinationPath)
guard !exists else { return false }
do {
    try FileManager.default.copyItem(atPath: sourcePath, toPath: destinationPath)
    return true
} catch {
  print("error during file copy: \(error)")
    return false
}
}

You can find the documentation for SQLite.swift here https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#connecting-to-a-database

Bundle.main.path(forResource: "Assignment2", ofType: "sqlite3"), anything in the app bundle is read only so for starters you need to move your db file — Joakim Danielson
– Joakim Danielson, Commented Feb 21, 2022 at 21:08
So how would I copy the file to the application support directory? — JonGrimes20
– JonGrimes20, Commented Feb 21, 2022 at 21:12
@JonGrimes20 in their documentation they talk about how to establish read and write connection, did you run it on your code? Document read write link — Reza Khonsari
– Reza Khonsari, Commented Feb 21, 2022 at 21:26
@RezaKhonsari In what context would I use that func to copy? Would I just use it in my first viewDidLoad() and not assign it to a var or what? Sorry I'm very new to using databases. — JonGrimes20
– JonGrimes20, Commented Feb 21, 2022 at 21:40
@RezaKhonsari When I put it into my code it shows a warning that the result is never used, is that something to worry about? — JonGrimes20
– JonGrimes20, Commented Feb 21, 2022 at 21:42

Reza Khonsari · Accepted Answer · 2022-02-21 22:29:20Z

1

When database created in your document directory it will be there permanently till user delete the app or you delete that directory.
so read and write connection will occur when you save your sql file in this directory.
as you wanted I make a new example for you that I created recently.

import UIKit
import SQLite

class ViewController: UIViewController {
    
    let customer = Table("Customer")
    let id = Expression<Int64>("CustomerID")
    let name = Expression<String>("CustomerName")
    
    override func viewDidLoad() {
        super.viewDidLoad()
        
        let db = makeDBConnection()
        createTable(db: db)
        insertNewCustomer(db: db)
        fetchDatabase(db: db)
    }
    
    private func makeDBConnection() -> Connection {
        let path = NSSearchPathForDirectoriesInDomains(
            .documentDirectory, .userDomainMask, true
        ).first!
        
        let sourcePath = "\(path)/db.sqlite3"
        
        _ = copyDatabaseIfNeeded(sourcePath: sourcePath)

        return try! Connection(sourcePath)
    }
    
    private func createTable(db: Connection) {
        //Define the columns of the table as expressions
        
        do {
            try db.run(customer.create(block: { table in
                table.column(id, primaryKey: true)
                table.column(name)
            }))
        } catch {
            // This tells you table already created for second time you running this code
        }
    }
    
    private func insertNewCustomer(db: Connection) {
        // This will insert a new customer into your table each time app runs
        let insert = customer.insert(name <- "Reza")
        try! db.run(insert)
    }
    
    private func fetchDatabase(db: Connection) {
        for customer in try! db.prepare(customer) {
            print("id: \(customer[id]), name: \(customer[name])")
        }
    }
    
    func copyDatabaseIfNeeded(sourcePath: String) -> Bool {
        let documents = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
        let destinationPath = documents + "/db.sqlite3"
        let exists = FileManager.default.fileExists(atPath: destinationPath)
        guard !exists else { return false }
        do {
            try FileManager.default.copyItem(atPath: sourcePath, toPath: destinationPath)
            return true
        } catch {
          print("error during file copy: \(error)")
            return false
        }
    }
}

the result is when I run the app each time:

answered Feb 21, 2022 at 22:29

Reza Khonsari

4763 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

JonGrimes20 Over a year ago

The connection is working but when I try to replicate the insert function I start to have some problems. I have another View Controller 'NewCustomerPage' that pops up with UITextFields to enter the customers name, contact name, address, city, postalCode, and country. The save button at the bottom of the page when tapped takes the text in the above textFields and uses that to run an insert command, but when I test it no new customer appears, I'll edit my question for you to look at the code.

Reza Khonsari Over a year ago

@JonGrimes20 I think this is another problem rather than this question, if my answer helps you to understand your question, please upvote my answer, and also please upload your codes through public git and share it in your question, so people can help and contribute on your code

JonGrimes20 Over a year ago

I've edited the question for you to now see the 'NewCustomerPageVC' code, because I don't know if it's actually a writeable database or not. Also I apparently don't have enough reputation to upvote

Reza Khonsari Over a year ago

@JonGrimes20 as I tell you, you should upload your project over git, ask people to help you. there are more edge cases in your code for example you don't have makeDBConnection in NewCustomerPage but you call it.

Reza Khonsari Over a year ago

@JonGrimes20 My response is related to your question about Establishing a Read-Write connection, not fixing your project codes.

|

Collectives™ on Stack Overflow

Establishing a Read-Write connection to a database in SQLite.swift

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related