Insert values except / merge

[CrownedPhoenix]

I'd like do be able to structure a query like:

INSERT INTO <table>
VALUES <values>  EXCEPT
SELECT <cols> FROM <table>

There might be a way to do this that I'm not sure of but some of the directions I went down were:

db.insert(into: <table>).values(...).except(...) // Not an API

db.insert(into: <table>).select(SQLSubQuery.select({
  $0.values(...).union(...) // Not an API
}))

db.insert(into: <table>).select(SQLSubQuery.except({
    $0.values(...)
  }).except(...) // Not an API
)

Context: My ultimate objective is to perform a merge but I know that is not general SQL so I'm trying to get as close an approximation I can with a single query.

[gwynne]

The union builder API is a little weird when it comes to subqueries, and I forgot to add proper support for inserts. There also isn't a query expression for Postgres's VALUES command yet, and unions only support SELECT queries. You can try this as a rather verbose (and slightly bizarre) workaround:

/// Implements a VALUES() query expression.
struct PostgreSQLValuesQuery: SQLExpression {
    var columns: [any SQLExpression] = []
    var orderBys: [any SQLExpression] = []
    var limit: Int?
    var offset: Int?

    func serialize(to serializer: inout SQLSerializer) {
        serializer.statement {
            $0.append("VALUES", SQLList(self.columns))
            if !self.orderBys.isEmpty {
                $0.append("ORDER BY", SQLList(self.orderBys))
            }
            if let limit = self.limit {
                $0.append("LIMIT", SQLLiteral.numeric("\(limit)"))
            }
            if let offset = self.offset {
                $0.append("OFFSET", SQLLiteral.numeric("\(offset)"))
            }
        }
    }
}

/// Provides a query builder for VALUES() queries
final class PostgreSQLValuesBuilder: SQLQueryBuilder, SQLUnqualifiedColumnListBuilder, SQLPartialResultBuilder {
    var database: any SQLDatabase
    var valuesQuery: PostgreSQLValuesQuery
    var query: any SQLExpression { self.valuesQuery }

    var columnList: [any SQLExpression] {
        get { self.valuesQuery.columns }
        set { self.valuesQuery.columns = newValue }
    }
    
    var orderBys: [any SQLExpression] {
        get { self.valuesQuery.orderBys }
        set { self.valuesQuery.orderBys = newValue }
    }

    var limit: Int? {
        get { self.valuesQuery.limit }
        set { self.valuesQuery.limit = newValue }
    }
    
    var offset: Int? {
        get { self.valuesQuery.offset }
        set { self.valuesQuery.offset = newValue }
    }
}

extension SQLDatabase {
    /// Provides a builder for a top-level VALUES() query
    func values() -> PostgreSQLValuesBuilder {
        .init(database: self, valuesQuery: .init())
    }
}

/// Provides a query builder for VALUES() subqueries
final class PostgreSQLValuesSubqueryBuilder: SQLUnqualifiedColumnListBuilder, SQLPartialResultBuilder {
    var valuesQuery: PostgreSQLValuesQuery

    var columnList: [any SQLExpression] {
        get { self.valuesQuery.columns }
        set { self.valuesQuery.columns = newValue }
    }
    
    var orderBys: [any SQLExpression] {
        get { self.valuesQuery.orderBys }
        set { self.valuesQuery.orderBys = newValue }
    }

    var limit: Int? {
        get { self.valuesQuery.limit }
        set { self.valuesQuery.limit = newValue }
    }
    
    var offset: Int? {
        get { self.valuesQuery.offset }
        set { self.valuesQuery.offset = newValue }
    }
}

extension SQLSubquery {
    /// Provides a builder for a VALUES() subuqery
    static func values(
        _ build: (PostgreSQLValuesSubqueryBuilder) throws -> PostgreSQLValuesSubqueryBuilder
    ) rethrows -> some SQLExpression {
        let builder = PostgreSQLValuesSubqueryBuilder(valuesQuery: .init())

        _ = try build(builder)
        return builder.valuesQuery
    }
}

/// Finally, you can now do this:
let builder = db.insert(into: <table>)
builder.insert.valueQuery = SQLList([
    SQLSubquery.values { $0
        .column(/* one of your VALUES() expressions goes here */)
        .column(/* another VALUES() expression can go here */)
        // etc.
    },
    SQLUnionJoiner(type: .except),
    SQLSubquery.select { $0
        // you can do all the usual SELECT query builder stuff here
    }
], separator: SQLRaw(" "))
try await builder.run()

It's a pretty hacky workaround and requires a bunch of support code, I know 😕 It also assumes Postgres's VALUES() syntax, which is quite different from MySQL's version, hence the naming of the types. Last but not least, this has all been typed in a Github comment field, I have not even checked if it compiles, so apologies for any typos or bugs 😰.

[gwynne]

Or, if you'd rather not do all that, you can always punt to a raw query string:

try await db.raw("""
    INSERT INTO \(ident: "table")
        VALUES (...)
        EXCEPT
        SELECT <cols> FROM <table>
    """).run()

😅 I tend not to recommend this approach because it's much too easy to forget to escape things or use bound parameters with raw queries, but it's considerably less code than the other in this case, to say the least.

[CrownedPhoenix]

I really appreciate this! I'm happy to use a workaround for now. I'd like to get more comfortable with extending the builder API as needed anyway so this is a helpful reference to follow.