Example connection DB with SQLite

[SieczkowskiAL]

Hello,

Does anyone have a complete example of how to connect and work with SQLite in a Swift 5 / XCode / Mac app? The separate code examples from SQLite.swift do not work, nor can I connect the database that is already created. See # 982.

[nathanfallet]

Here is an example, taken from my app Delta: Math helper which works fine in production:

It shows how to init database, connect to database, read data, write data and update data.

//
//  Database.swift
//  Delta
//
//  Created by Nathan FALLET on 23/10/2019.
//  Copyright © 2019 Nathan FALLET. All rights reserved.
//

import Foundation
import SQLite
import StoreKit

class Database {

    // Static instance
    static let current = Database()

    // Properties
    private var db: Connection?
    let algorithms = Table("algorithms")
    let local_id = Expression<Int64>("local_id")
    let remote_id = Expression<Int64?>("remote_id")
    let name = Expression<String>("name")
    let lines = Expression<String>("lines")
    let owner = Expression<Bool>("owner")
    let last_update = Expression<Date>("last_update")
    let icon = Expression<String>("icon")
    let color = Expression<String>("color")

    // Initialize
    init() {
        do {
            // Get database path
            if let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first {
                // Connect to database
                db = try Connection("\(path)/delta-math-helper.sqlite3")

                // Initialize tables
                try db?.run(algorithms.create(ifNotExists: true) { table in
                    table.column(local_id, primaryKey: .autoincrement)
                    table.column(remote_id)
                    table.column(name)
                    table.column(lines)
                    table.column(owner)
                    table.column(last_update)
                    table.column(icon, defaultValue: AlgorithmIcon.defaultIcon)
                    table.column(color, defaultValue: AlgorithmIcon.defaultColor)
                })
            }
        } catch {
            print(error.localizedDescription)
        }
    }

    // Update database
    func updateDatabase(build_number: Int) {
        if build_number < 24 {
            // Add icon and color to table
            do {
                try db?.run(algorithms.addColumn(icon, defaultValue: AlgorithmIcon.defaultIcon))
                try db?.run(algorithms.addColumn(color, defaultValue: AlgorithmIcon.defaultColor))
            } catch {
                print(error.localizedDescription)
            }
        }
    }

    // Get algorithms
    func getAlgorithms() -> [Algorithm] {
        // Initialize an array
        var list = [Algorithm]()

        do {
            // Get algorithms data
            if let result = try db?.prepare(algorithms.order(last_update.desc)) {
                // Iterate data
                for line in result {
                    // Create algorithm in list
                    list.append(AlgorithmParser(try line.get(local_id), remote_id: try line.get(remote_id), owned: try line.get(owner), named: try line.get(name), last_update: try line.get(last_update), icon: AlgorithmIcon(icon: try line.get(icon), color: try line.get(color)), with: try line.get(lines)).execute())
                }
            }
        } catch {
            print(error.localizedDescription)
        }

        // Return found algorithms
        return list
    }

    // Get algorithm by id
    func getAlgorithm(id_local: Int64 = -1, id_remote: Int64 = -1) -> Algorithm? {
        do {
            // Get algorithm data
            if let result = try db?.prepare(algorithms.filter(local_id == id_local || remote_id == id_remote)) {
                // Iterate data
                for line in result {
                    // Create the algorithm
                    return AlgorithmParser(try line.get(local_id), remote_id: try line.get(remote_id), owned: try line.get(owner), named: try line.get(name), last_update: try line.get(last_update), icon: AlgorithmIcon(icon: try line.get(icon), color: try line.get(color)), with: try line.get(lines)).execute()
                }
            }
        } catch {
            print(error.localizedDescription)
        }

        // No algorithm found
        return nil
    }

    // Add an algorithm into database
    func addAlgorithm(_ algorithm: Algorithm) -> Algorithm {
        do {
            // Insert data
            let id = try db?.run(algorithms.insert(remote_id <- algorithm.remote_id, name <- algorithm.name, lines <- algorithm.toString(), owner <- algorithm.owner, last_update <- algorithm.last_update, icon <- algorithm.icon.icon, color <- algorithm.icon.color)) ?? 0

            // Update id
            algorithm.local_id = id
        } catch {
            print(error.localizedDescription)
        }

        // Return algorithm
        return algorithm
    }

    // Update an algorithm
    func updateAlgorithm(_ algorithm: Algorithm) -> Algorithm {
        do {
            // If id is 0
            if algorithm.local_id == 0 {
                // Insert the algorithm
                return addAlgorithm(algorithm)
            }

            // Get line
            let line = algorithms.filter(local_id == algorithm.local_id)

            // Update data
            try db?.run(line.update(remote_id <- algorithm.remote_id, name <- algorithm.name, lines <- algorithm.toString(), owner <- algorithm.owner, last_update <- algorithm.last_update, icon <- algorithm.icon.icon, color <- algorithm.icon.color))
        } catch {
            print(error.localizedDescription)
        }

        // Return algorithm
        return algorithm
    }

    // Delete an algorithm
    func deleteAlgorithm(_ algorithm: Algorithm)  {
        do {
            // If id is 0
            if algorithm.local_id == 0 {
                return
            }

            // Get line
            let line = algorithms.filter(local_id == algorithm.local_id)

            // Delete data
            try db?.run(line.delete())
        } catch {
            print(error.localizedDescription)
        }
    }

}

Usage:

// Get list of algorithms for example
let algorithms = Database.current.getAlgorithms()

[raf-fun]

If I had multiple tables in my database with multiple columns would it be better to just add the top initialization you preformed into its own class to keep this class from getting to big? @NathanFallet

[nathanfallet]

@raf-fun You can organize your code the way you want, here it is just an example code. If you want to make a class dedicated for each table, you can. But keep only one instance of the Connection object (shared across your classes).