rfcs/mssql-upsert-mutations.md
Teams Involved: Data Sources, Docs and Console
As a user, I would like to be able to upsert rows in from a certain mssql table using a predicate, similarly to how I'm able to do so for a postgres table.
Upserting rows into a table should be done via a GraphQL mutation to the /v1/graphql endpoint.
Upserts should respect:
mutation [<mutation-name>] {
<mutation-field-name> (
[<input-object>!]
[<matched-clause>]
)
[<mutation-response>!]
}
| Key | Required | Schema | Description |
|---|---|---|---|
matched-clause | false | if_matched | columns that are allowed to be selected, this determines the 'ON' expression |
input-object | true | objects | same as postgres. columns that can should be updated when matched |
mutation-response | true | mutation response | same as postgres |
if_matched argumentThe if_matched clause is used to convert an insert mutation to an upsert mutation, similar to Postgres' on_conflict clause. Upsert respects the table’s update permissions before editing an existing row in case of a match. Hence the if_matched clause is permitted only if a table has update permissions defined.
if_matched {
# columns that are allowed to be selected
# this determines the 'ON' expression
match_columns: [table_select_column]
# columns that can should be updated when matched
# (same as postgres' update_columns in on_conflict)
update_columns: [table_update_column]
# same as postgres, I think we can 'AND' this with
# the 'ON' condition derived from 'match_columns'
where: table_bool_exp
}
mutation {
insert_author(
objects: { id: 1, name: "aaa" }
if_matched: { match_columns: author_pkey, update_columns: name }
) {
returning {
id
name
}
}
}
Comparable to the Postgres insert/upsert API
The mutation response is specified in the GraphQL spec, including:
{ data # the returned data is specified by the `mutation-response` section
{
affected_rows
returning { # the `returning` statement can include nested objects
response-field1
response-field2
..
}
}
}
...or, in the event of an unsuccessful mutation:
{ errors {
extensions
message
}
}
test_on_conflict_updatetest_order_on_conflict_wheretest_on_conflict_ignoreThese checkpoints do not necessarily need to be delivered in the same PR. In fact, prefer smaller PRs where they are functional, tested, and self-contained.
XOnConflict and ExtraInsertDataupsert mutation schemaXOnConflict and ExtraInsertDataThe way we have been using the XFeatureFlag trick is something of an anti-pattern.
Differences in behavior across backends should not manifest itself as "case switching" in shared code (be that at runtime or compile-time case-switching). Rather, they should happen in the type class instance declarations, and code sharing should be achieved via shared building blocks that individual backends may put together in ways appropriate to them, in a similar fashion to how #2741 does for updateOperators.
Currently, the main thing that type XOnConflict b :: * achieves is that it lets a backend guarantee that the _on_conflict-related IR nodes never appear, by letting a backend B define type XOnConflict B = Void, making such nodes statically un-instantiateable.
While that's of course neat in some ways (i.e. it ensures the schema generators don't create IR nodes that the execution layer won't handle), it's also in itself a bit heavy, and worst of all it's one more thing that shared schema generating code needs to case switch over.
A better approach for dealing with XOnConflict and ExtraInsertData would be to have ExtraInsertData also handle the backend-specific concern of upserts (and other backend-specific insert stuff in the future), by removing _aiConflictClause from data AnnIns and have backends that support on-conflict clauses put something similar into their ExtraInsertData.
This would give us the same coherency guarantees between schema and execution as mentioned above, and centralize backend specific behavior in the backend specific type class instances, where they belong. This will require some refactoring of the schema code and the existing Postgres upsert implementation though.
+----------+ +----+ +--------------+ +-----+ +-----------------+
| Metadata | --> | IR | --> | Mutation AST | --> | SQL | --> | Execution on DB |
+----------+ +----+ +--------------+ +-----+ +-----------------+
upsert mutation schema+----------+ +----+
| Metadata | --> | IR |
+----------+ +----+
Generate schema for insert mutations with an if_matched clause, with permissions enforced. This broadly involves:
implementing the upsert object parser: implement defaultConflictObject for an MSSQL backend. Consider renaming the class method to use a more generic term like "upsert", e.g. upsertObject.
introducing a generic field parser for upsert arguments: introduce a new method on the BackendSchema typeclass to parse the upsert argument of an insert field for a given backend. For example:
mkUpsertParser ::
MonadParse m =>
Maybe (Parser 'Input m a) ->
InputFieldsParser m (Maybe a)
implementing the field parser for MSSQL: implement mkUpsertParser to correctly parse the if_matched argument. The parser should return InputFieldsParser [...] Nothing when permissions are not met. The existing mkConflictArg can be used as a reference implementation.
update the AnnInsert IR: update insertIntoTable and insertOneIntoTable (at least) to use the newly introduced BackendSchema methods instead of mkConflictArg.
optionally, update objectRelationshipInput and arrayRelationshipInput to also use the BackendSchema field parser; however, this may not be neccesary as nested inserts are not yet supported on SQL Server.
To verify: The generated schema can be verified locally in Hasura Console's Documentation Explorer. This change, if successful, should result in the following generated schema diff for an example author table:
type mutation_root {
insert_author(
objects: [author_insert_input!]!
+ if_matched: author_if_matched
): author_mutation_response
insert_author_one(
object: author_insert_input!
+ if_matched: author_if_matched
): author
}
+input author_if_matched {
+ match_columns: author_match_columns!
+ update_columns: [author_update_column!]! = []
+ where: author_bool_exp
+}
The mutation, if attempted, won't succeed until the next step is implemented.
+--------------+ +-----+ +-----------------+
| Mutation AST | --> | SQL | --> | Execution on DB |
+--------------+ +-----+ +-----------------+
This broadly involves:
data Merge in Hasura.Backends.MSSQL.Types.
MERGE statement in context of HGE upserts.SQLConflict datatype can be used as a reference implementation.Insert recordMERGE statement: update the fromInsert query printer to generate the correct MERGE statement when the upsert condition is met.To verify: simple insert tests such as test_on_conflict_update, at a minimum, should now pass.
MERGE docs