Why does returning fetchOne(db) return an optional

[morstin]

Hi there,

I want to implent an upsert and I understand that the db transaction fetchOne(db) can fail - but not really why it should return an optional. Am I using returning + fetchOne(db) incorrectly?

Below code does not compile as it does not consider the optional return from try Record.upsert { draft }.returning(\.self).fetchOne(db)

import SharingGRDB
import Tagged

@Table("record")
struct Record {
    public typealias ID = Tagged<Self, Int>
    public let id: ID
    
    public var text: String
}

extension Record {
    func upsertRecord(_ draft: Record.Draft) -> Record {
        @Dependency(\.defaultDatabase) var database
        
        withErrorReporting {
            return try database.write { db in
                let record: Record =  try Record.upsert { draft }.returning(\.self).fetchOne(db) //fails here
                return record
            }
        }
    }
}

So basically if I want a non-optional Record, I need to unwrap and introduce my own error message - without really knowing why it did fail 🧐:

extension Record {
    func upsertRecord(_ draft: Record.Draft) throws -> Record {
        @Dependency(\.defaultDatabase) var database
        
        let record = try database.write { db in
            let record = try Record.upsert { draft }.returning(\.self).fetchOne(db)
            guard let record = record else { throw DatabaseError(message: "Could not upsert record")}
            return record
        }
        return record
    }
}

[mbrandonw]

Hi @morstin, fetchOne has no choice but to return an optional because pretty much any SQL query can return an empty set of rows. It may seem like your situation is guaranteed to return a result since it's an "INSERT … RETURNING", but fetchOne doesn't know that. And further, if you had done "INSERT OR IGNORE … RETURNING", then it would be possible for the query to not return any results.

Since you are already in an error throwing context the fact that have to deal with an optional doesn't seem that bad. You could also create your own helper of .requireOne(db) that unwraps the optional and throws an error if it's nil.

[morstin]

Thank you @mbrandonw - I think the .requireOne(db) is a great idea. Looks very clean doing so.

extension Record {
    func upsertRecord(_ draft: Record.Draft) throws -> Record {
        @Dependency(\.defaultDatabase) var database
        
        return try database.write { db in
            try Record.upsert { draft }.returning(\.self).requireOne(db)
        }
    }
}

extension StructuredQueriesCore.Statement {
    @inlinable
    public func requireOne(_ db: Database) throws -> QueryValue.QueryOutput
    where QueryValue: QueryRepresentable {
        let record = try fetchCursor(db).next()
        guard let record = record else { throw DatabaseError(message: "Could not upsert record and return the record")}
        return record
    }
}