`INSERT OR REPLACE` - is it possible?

[wojciech-kulik]

Hi guys! I know .onConflict modifier is available, but is it possible to build a query that calls INSERT OR REPLACE? It would simplify the query.

I tried searching documentation and the repo but couldn’t find anything else than onConflict.

If it’s not available, would you consider it as a feature request or is there a reason why it shouldn’t be included?

[mheh]

You can create your own SQLExpression queries in your own code base and then you can call them as needed. INSERT OR REPLACE sounds very similar to regular insert. You could probably copy the insert SQLExpression from sql-kit along with associated extensions to make it almost the same as the insert.

As an example, here is the CREATE VIEW expression I have. This is the "root" which is what gets serialized into a SQL expression and sent to the server

struct PostgresSQLCreateView: SQLExpression {
    /// The name of the view being created
    var name: any SQLExpression
    
    /// The select query used to build the view.
    var select: SQLSelect
    
    /// Add or remove `OR REPLACE`
    var orReplace: Bool
    
    init(
        name: any SQLExpression,
        orReplace: Bool,
        select: SQLSelect = .init()
    ) {
        self.name = name
        self.select = select
        self.orReplace = orReplace
    }
    
    
    func serialize(to serializer: inout SQLSerializer) {
        serializer.statement {
            $0.append("CREATE")
            if self.orReplace {
                $0.append("OR REPLACE")
            }
            $0.append("VIEW", self.name)
            $0.append("AS", self.select)
        }
    }
}

There is a builder that makes it work like sqlDb.select().from("foo") or sqlDb.insert(into: "bar")

final class PostgresSQLCreateViewBuilder: SQLQueryBuilder, SQLSubqueryClauseBuilder {
    var createView: PostgresSQLCreateView
    var select: SQLKit.SQLSelect {
        get {
            self.createView.select
        }
        set {
            self.createView.select = newValue
        }
    }
    var query: any SQLKit.SQLExpression { self.createView }
    var database: any SQLKit.SQLDatabase
    
    init(
        name: any SQLExpression,
        orReplace: Bool,
        on database: any SQLDatabase
    ) {
        self.database = database
        self.createView = .init(
            name: name,
            orReplace: orReplace
        )
    }
    
    /// Add OR REPLACE to the CREATE VIEW statement
    @discardableResult
    func orReplace() -> Self {
        self.createView.orReplace = true
        return self
    }
    
    @discardableResult
    public func select(_ closure: (SQLSubqueryBuilder) throws -> SQLSubqueryBuilder) rethrows -> Self {
        let builder = SQLSubqueryBuilder()
        _ = try closure(builder)
        self.createView.select = builder.select
        return self
    }
}

And the relevant extension on SQLDatabase that lets me call it easily:

extension SQLDatabase {
    /// Create a new SQLViewBuilder
    func create(view name: String, orReplace: Bool) -> PostgresSQLCreateViewBuilder {
        .init(
            name: SQLIdentifier(name),
            orReplace: orReplace,
            on: self)
    }
}

Usage then looks like

try await database.sql
    .create(view: Self.viewName, orReplace: true)
    .select { query in
        query.from(Tax.self)
        query.column(\Tax.$id, as: Self.id)
        return query
    }
    .run()

[wojciech-kulik]

Thank you for sharing your snippets! I'm actually using SQLite (I should have mentioned that...), but it seems like this solutions should also work in my case. I'll try it out! Thanks again!

[wojciech-kulik]

I ended up keeping my implementation using onConflict but I like your snippet. It's good to know that something like that is possible.

I think it would be worth adding "insert or replace" in the library. But I leave the decision up to maintainers. If you don't want to add it, feel free to close this issue.