How do I read data using GRDB in a SwiftUI application?

Question

I am trying to follow this guide here: https://elliotekj.com/2019/12/11/sqlite-ios-getting-started-with-grdb and while helpful, it's not exactly a tutorial.

So far I have this code:

AppDatabase

import GRDB

var dbQueue: DatabaseQueue!

class AppDatabase {

    static func setup(for application: UIApplication) throws {
        let databaseURL = try FileManager.default
            .url(for: .applicationDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
            .appendingPathComponent("db.sqlite")

        dbQueue = try DatabaseQueue(path: databaseURL.path)
    }
}

And in my AppDelegate this code:

    func application(_ application: UIApplication, didFinishLaunchingWithOptions launchOptions: [UIApplication.LaunchOptionsKey: Any]?) -> Bool {
        // Override point for customization after application launch.
        try! AppDatabase.setup(for: application)
        return true
    }

It think the above is correct. Currently, I'm manipulating my db via Navicat, so I know my table is fine. But now what do I need to do to be able to simply read my table?

Here is my SwiftUI ContentView:

import SwiftUI
import GRDB

struct ContentView: View {

    @State private var firstName: String = "Saul"
    @State private var dateOfBirth: String = "1992-05-12"

    var body: some View {
        ZStack {
            VStack{
                HStack {
                    Text("Name")
                    Spacer()
                    TextField(" Enter text ", text: $firstName)
                    .frame(width: 160, height: 44)
                    .padding(4)
                    .border(Color.blue)
                }.frame(width:300)
            HStack {
                Text("Date of Birth")
                Spacer()
                TextField(" Enter text ", text: $dateOfBirth)
                .frame(width: 160, height: 44)
                .padding(4)
                .border(Color.blue)
                }.frame(width:300)
            }.foregroundColor(.gray)
                .font(.headline)
            VStack {
                Spacer()
                Button(action: {


                }) {
                    Text("Add").font(.headline)
                }
                .frame(width: 270, height: 64)
                .background(Color.secondary).foregroundColor(.white)
                .cornerRadius(12)
            }
        }
    }
}

private func readPerson() {



}

struct ContentView_Previews: PreviewProvider {
    static var previews: some View {
        ContentView()
    }
}

struct Person {
    var personID: Int64?
    var firstName: String
    var lastName: String?
    var dateOfBirth: String
}

extension Person: Codable, FetchableRecord, MutablePersistableRecord {
    // Define database columns from CodingKeys
    private enum Columns {
        static let personID = Column(CodingKeys.personID)
        static let firstName = Column(CodingKeys.firstName)
        static let lastName = Column(CodingKeys.lastName)
        static let dateOfBirth = Column(CodingKeys.dateOfBirth)
    }



    // Update a person id after it has been inserted in the database.
    mutating func didInsert(with rowID: Int64, for column: String?) {
        personID = rowID
    }
}

I really don't understand what to write in readPerson() or where to place it in my view. For now, I'd be happy to populate my textFields from the table, but ext of course, I'd like to add persons using the button.

Answer 1

Ok, so now that I've had time to delve into this, here is the solution I found.

Assuming a database is already attached, I created an envSetting.swift file to hold an ObservableObject. Here is that file, which I feel is fairly self-explanatory (It's a basic ObservableObject set-up see https://www.hackingwithswift.com/quick-start/swiftui/how-to-use-observedobject-to-manage-state-from-external-objects):

import UIKit
import GRDB

class EnvSettings: ObservableObject {
    @Published var team: [Athlete] = getAthletes(withQuery: "SELECT * FROM Athlete ORDER BY lastName")
    func updateAthletes() {
        team = getAthletes(withQuery: "SELECT * FROM Athlete ORDER BY lastName")
    }

}

In this code, the getAthletes function returns an array of Athlete objects. It resides in an Athlete.swift file, the bulk of which comes from the GRDB demo app with specific edits and functions for my case:

import SwiftUI
import GRDB

// A plain Athlete struct
struct Athlete {
    // Prefer Int64 for auto-incremented database ids
    var athleteID: Int64?
    var firstName: String
    var lastName: String
    var dateOfBirth: String
}

// Hashable conformance supports tableView diffing
extension Athlete: Hashable { }

// MARK: - Persistence
// Turn Player into a Codable Record.
// See https://github.com/groue/GRDB.swift/blob/master/README.md#records
extension Athlete: Codable, FetchableRecord, MutablePersistableRecord {
    // Define database columns from CodingKeys
    private enum Columns {
        static let id = Column(CodingKeys.athleteID)
        static let firstName = Column(CodingKeys.firstName)
        static let lastName = Column(CodingKeys.lastName)
        static let dateOfBirth = Column(CodingKeys.dateOfBirth)
    }

    // Update a player id after it has been inserted in the database.
    mutating func didInsert(with rowID: Int64, for column: String?) {
        athleteID = rowID
    }
}

// MARK: - Database access
// Define some useful player requests.
// See https://github.com/groue/GRDB.swift/blob/master/README.md#requests
extension Athlete {
    static func orderedByName() -> QueryInterfaceRequest<Athlete> {
        return Athlete.order(Columns.lastName)
    }
}


// This is the main function I am using to keep state in sync with the database. 

func getAthletes(withQuery: String) -> [Athlete] {
    var squad = [Athlete]()
    do {
    let athletes = try dbQueue.read { db in
        try Athlete.fetchAll(db, sql: withQuery)
        }
        for athlete in athletes {
            squad.append(athlete)
            print("getATHLETES: \(athlete)")// use athlete
        }
    } catch {
       print("\(error)")
    }
    return squad
}

func addAthlete(fName: String, lName: String, dob: String) {
    do {
        try dbQueue.write { db in
            var athlete = Athlete(
                firstName: "\(fName)",
                lastName: "\(lName)",
                dateOfBirth: "\(dob)")
            try! athlete.insert(db)
        print(athlete)
        }
    } catch {
        print("\(error)")
    }
}

func deleteAthlete(athleteID: Int64) {
    do {
        try dbQueue.write { db in
            try db.execute(
                literal: "DELETE FROM Athlete WHERE athleteID = \(athleteID)")
            }
        } catch {
            print("\(error)")
    }
}


//This code is not found in GRDB demo, but so far has been helpful, though not 
//needed in this StackOverflow answer. It allows me to send any normal query to 
//my database and get back the fields I need, even - as far as I can tell - from 
//`inner joins` and so on.

func fetchRow(withQuery: String) -> [Row] {

    var rs = [Row]()

    do {
        let rows = try dbQueue.read { db in
            try Row.fetchAll(db, sql: withQuery)
        }
        for row in rows {
            rs.append(row)
        }
    } catch {
        print("\(error)")
    }
   return rs
}

And this is my ContentView.swift file:

import SwiftUI

struct ContentView: View {

    @EnvironmentObject var env: EnvSettings

    @State var showingDetail = false

    var body: some View {

        NavigationView {
            VStack {
                List {
                    ForEach(env.team, id: \.self) { athlete in
                        NavigationLink(destination: DetailView(athlete: athlete)) {
                            HStack {
                                Text("\(athlete.firstName)")
                                Text("\(athlete.lastName)")
                            }
                        }
                    }.onDelete(perform: delete)
                }
                Button(action: {
                    self.showingDetail.toggle()
                }) {
                    Text("Add Athlete").padding()
                }.sheet(isPresented: $showingDetail) {

                 //The environmentObject(self.env) here is needed to avoid the         
                 //Xcode error "No ObservableObject of type EnvSettings found.
                 //A View.environmentObject(_:) for EnvSettings may be missing as
                 //an ancestor of this view which will show when you try to 
                 //dimiss the AddAthlete view, if this object is missing here. 

                    AddAthlete().environmentObject(self.env)

                }
            }.navigationBarTitle("Athletes")
        }
    }

    func delete(at offsets: IndexSet) {
        deleteAthlete(athleteID: env.team[(offsets.first!)].athleteID!)
        env.updateAthletes()
      }

}


struct AddAthlete: View {
    @EnvironmentObject var env: EnvSettings
    @Environment(\.presentationMode) var presentationMode: Binding<PresentationMode>
    @State private var firstName: String = ""
    @State private var lastName: String = ""
    @State private var dob: String = ""

    var body: some View {
        VStack {
            HStack{
                Button(action: {
                    self.presentationMode.wrappedValue.dismiss()
                }) {
                    Text("Cancel")
                }
                Spacer()
                Button(action: {
                    addAthlete(fName: self.firstName, lName: self.lastName, dob: self.dob)
                    self.env.updateAthletes()
                    self.presentationMode.wrappedValue.dismiss()

                }) {
                    Text("Done")
                }
            }
            .padding()
            VStack (alignment: .leading, spacing: 8) {
                Text("First Name:")
                TextField("Enter first name ...", text: $firstName).textFieldStyle(RoundedBorderTextFieldStyle())
                Text("Last Name:")
                TextField("Enter last name ...", text: $lastName).textFieldStyle(RoundedBorderTextFieldStyle())
                Text("Date Of Birth:")
                TextField("Enter date of birth ...", text: $dob).textFieldStyle(RoundedBorderTextFieldStyle())
            }.padding()
            Spacer()
        }
    }
}

struct DetailView: View {
   let athlete: Athlete
    var body: some View {
        HStack{
            Text("\(athlete.firstName)")
            Text("\(athlete.lastName)")
        }
    }
}

struct ContentView_Previews: PreviewProvider {
    static var previews: some View {
        ContentView().environmentObject(EnvSettings())
    }
}

And don't forget to add the environemnt to the SceneDelegate:

 func scene(_ scene: UIScene, willConnectTo session: UISceneSession, options connectionOptions: UIScene.ConnectionOptions) {
        // Use this method to optionally configure and attach the UIWindow `window` to the provided UIWindowScene `scene`.
        // If using a storyboard, the `window` property will automatically be initialized and attached to the scene.
        // This delegate does not imply the connecting scene or session are new (see `application:configurationForConnectingSceneSession` instead).


        //  HERE

        var env = EnvSettings()

        // Create the SwiftUI view that provides the window contents.
        let contentView = ContentView()

        // Use a UIHostingController as window root view controller.
        if let windowScene = scene as? UIWindowScene {
            let window = UIWindow(windowScene: windowScene)

        // AND HERE ATTACHED TO THE contentView

            window.rootViewController = UIHostingController(rootView: contentView.environmentObject(env))
            self.window = window
            window.makeKeyAndVisible()
        }
    }

For me this works, so far, after limited testing. I'm not sure it is the best way to go.

Essentially, we are setting up the ObservableObject to query the DB file anytime we make a pertinent change to it. That's why you see me calling the env.updateAthletes() funtion in .onDelete and in the "Done" button action for 'AddAthlete()'.

I'm not sure otherwise how to let SwiftUI know the DB has changed. GRDB does have some kind of observation code going on, but it's really, really opaque to me how to use that, or even if that is the correct solution here.

I hope this is helpful to people.

Answer 2

SwiftUI App Lifecycle

Here is an update showing one way to use GRDB in a "SWiftUI App Lifecycle"

First, create an environmentObject similar to this:

import UIKit
import GRDB

class EnvSettings: ObservableObject {
    @Published var players: [Player] = getData(withQuery: "SELECT * FROM Player")
}

Second, use @StateObject in your App struct, like so:

import SwiftUI

    @main
    struct myNewApp: App {

        @StateObject private var env = EnvSettings()

    //I initialize here with a modified setupDataBase function,
    //removing the AppDelegate paramters

        init() {
            try! setupDatabase()
          }
            
        var body: some Scene {
            WindowGroup {
                StartView()
                    .environmentObject(env) // Don't forget to add the environmentObject here
            }
        }
    }

As mentioned above, I modified setupDatabase() as follows, removing the _ application: UIApplication parameter:

import Foundation
import GRDB

func setupDatabase() throws {
     
     let path = NSSearchPathForDirectoriesInDomains(.applicationSupportDirectory, .userDomainMask, true)[0] as String
     let url = NSURL(fileURLWithPath: path)
     if let pathComponent = url.appendingPathComponent("db.sqlite") {
         let filePath = pathComponent.path
         let fileManager = FileManager.default
         if fileManager.fileExists(atPath: filePath) {
             print("FILE AVAILABLE")
             try openGSdb()
         } else {
             print("FILE NOT AVAILABLE")
             try fileManager.copyfileToApplicationSupportDirectory(forResource: "db", ofType: "sqlite")
             try openDatabase()
         }
     } else {
         print("FILE PATH NOT AVAILABLE")
     }
 }
 
 // MARK: Open the databaseQueue
 
 func openDatabase() throws {
     let databaseURL = try FileManager.default
         .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
         .appendingPathComponent("db.sqlite")
     dbQueue = try AppDatabase.openDatabase(atPath: databaseURL.path)
 }

Getting your db file into the app requires the same FileManager extension as in my answer above:

extension FileManager {
    //lets us copy in the database, creating the Application Support Directory if needed.
    func copyfileToApplicationSupportDirectory(forResource name: String,
                                         ofType ext: String) throws
    {
        
        let fileManager = FileManager.default
        let urls = fileManager.urls(for: .applicationSupportDirectory, in: .userDomainMask)
        if let applicationSupportURL = urls.last {
            do{
                try fileManager.createDirectory(at: applicationSupportURL, withIntermediateDirectories: true, attributes: nil)
                print("WE ARE CREATING THE APPLICATION SUPPORT FOLDER ...")
                print("APP SUPPORT FOLDER PATH:\n\(applicationSupportURL.path)")
            }
            catch{
                print(error)
            }
        }
        
        if let bundlePath = Bundle.main.path(forResource: name, ofType: ext),
            let destPath = NSSearchPathForDirectoriesInDomains(.applicationSupportDirectory,
                                .userDomainMask,
                                true).first {
            let fileName = "\(name).\(ext)"
            let fullDestPath = URL(fileURLWithPath: destPath)
                                   .appendingPathComponent(fileName)
            let fullDestPathString = fullDestPath.path

            if !self.fileExists(atPath: fullDestPathString) {
                try self.copyItem(atPath: bundlePath, toPath: fullDestPathString)
                print("WE COPIED THE FILE OVER")
                print("BUNDLE:\n\(bundlePath)")
                print("DEST:\n\(fullDestPath.path)")
            }
        }
    }
}

And I stil have an AppDatabase.swift file:

import GRDB

// The shared database queue
var dbQueue: DatabaseQueue!

/// A type responsible for initializing the application database.
///
/// See AppDelegate.setupDatabase()
struct AppDatabase {
    
    /// Creates a fully initialized database at path
    static func openDatabase(atPath path: String) throws -> DatabaseQueue {
        // Connect to the database
        // See https://github.com/groue/GRDB.swift/blob/master/README.md#database-connections
        let dbQueue = try DatabaseQueue(path: path)
        print("OPEN DATABASE")
        print("DATAEBASE PATH:\n\(path)")
        // Define the database schema
        try migrator.migrate(dbQueue)
        
        return dbQueue
    }
    
    /// The DatabaseMigrator that defines the database schema.
    ///
    /// See https://github.com/groue/GRDB.swift/blob/master/README.md#migrations
    static var migrator: DatabaseMigrator {
        var migrator = DatabaseMigrator()
        print("TRYING MIGRATOR")
        
 // removed database specific code, see the Demo in GRDB ...

        
        return migrator
    }
}

Then, in the actual view struct, you can do something like this:

struct ContentView: View {

    @EnvironmentObject var env: EnvSettings

    var body: some View {
        VStack {
            ForEach(players, id: \.id) { player in
                Text("\(player.name)")
            }
        }
    }
}

I hope that is more or less clear. Anyone who can add to this is absolutely welcome to. It hasn't been tested extensively, just in one app so far. But, it's working there very nicely.

Answer 3

I am currently working on a package that make accessing GRDB from SwiftUI easy. See the demo project for example usage.

https://github.com/apptekstudios/AS_GRDBSwiftUI

In your model:

extension YourModelType
{
    // You could define multiple different request types as needed
    struct Request: GRDBFetchRequest
    {
        var maxCount = 100 //An example of how you can make this request configurable.
        func onRead(db: Database) throws -> [YourModelType] {
            let models = try YourModelType
                .limit(maxCount)
                .fetchAll(db)
            return models
        }
    }

In your view:

struct YourView: View
{
    @GRDBFetch(request: YourModelType.Request(maxCount: 20))
    var result

    var body: some View {
        //Use the result as needed here
    }