graphql-engine/rfcs/mssql-delete-mutations.md

# `DELETE` mutations on SQL Server

## Metadata
```
---
authors: Abby (@sassela), Gil (@soupi)
---
```

## User story

As a user, I would like to be able to delete rows from a certain mssql table using a predicate, similarly to how I'm able to do so for a postgres table.

Deleting rows from a table should be done via sending a GraphQL mutation to the `/v1/graphql` endpoint.

Delete mutations should respect [row-level permissions](https://hasura.io/docs/latest/graphql/core/auth/authorization/permission-rules.html#delete-permissions).


## Interface

### Request

<!-- Request and response from the users' perspective -->
Users can specify a deletion mutation in one of the following ways:

* `delete` syntax

    - Delete via a predicate on one of the root table fields:

      https://hasura.io/docs/latest/graphql/core/api-reference/graphql-api/mutation.html#delete-syntax

      https://hasura.io/docs/latest/graphql/core/databases/postgres/mutations/delete.html#delete-objects-based-on-their-fields

    - Delete via a predicate on one of the nested object fields:

      https://hasura.io/docs/latest/graphql/core/databases/postgres/mutations/delete.html#delete-objects-based-on-nested-objects-fields

    - Delete all objects in a table using the {} expression as the where argument:

      https://hasura.io/docs/latest/graphql/core/databases/postgres/mutations/delete.html#delete-all-objects

    `delete` syntax:

    ```
    mutation [<mutation-name>] {
        <mutation-field-name> (
            [where-argument!]
        ) {
            [mutation-response!]
        }
    }
    ```

* `delete_by_pk` syntax:

   https://hasura.io/docs/latest/graphql/core/api-reference/graphql-api/mutation.html#delete-by-pk-syntax

### Response

The mutation response is specified in the [GraphQL spec](https://spec.graphql.org/June2018/#sec-Mutation), including:

```graphql
{ 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 ...

```graphql
{ errors {
    extensions
    message
  }
}
```

## Success

<!-- How do we know if we've solved this problem?

This could include a specific list of acceptance criteria.

This could outline specific edge cases that need to be handled.

This section should be more high-level, with more detail added in the subsequent **What** section. -->

- [test_graphql_mutations.py](https://github.com/hasura/graphql-engine-mono/blob/main/server/tests-py/test_graphql_mutations.py) tests pass for MSSQL:
  - test for each of the above linked queries
  - basic deletes: `TestGraphqlDeleteBasic`
  - table permissions: `TestGraphqlDeletePermissions`
  - constraints and errors: `TestGraphqlDeleteConstraints`
  - custom schema
    - custom names: `TestGraphqlMutationCustomGraphQLTableName`
    - custom root fields: `TestGraphqlMutationCustomSchema`
- delete mutations are executable via the console and CLI
- delete mutations on SQL Server are documented in Hasura docs. The [existing Postgres docs](https://hasura.io/docs/latest/graphql/core/databases/postgres/mutations/delete.html) can be used as a guide.

## Checkpoints

<!-- Roughly, what does this look like in the product?

Include wireframes and mockups here.

Are there things that we don't yet know yet? Are we currently doing an R&D Spike to evaluate? -->

*These 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.*

- [generate `DELETE` mutations schema](https://github.com/hasura/graphql-engine/issues/7625) by completing the `msBuildTableDeleteMutationFields` function
- [generate and execute `DELETE` SQL by adding an `executeDelete` function](https://github.com/hasura/graphql-engine/issues/7626)
  - `convertDelete` can be used as a reference implementation for SQL generation
  - `executeInsert` can be used as a reference implementation for mutation response generation
  - We cannot retrieve `returning` fields once the delete statement is executed.Consider the following options to fetch `returning` output:
    1. Use [table variables](https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql) in conjunction with the [`OUTPUT` clause](https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sq) to capture the delete statement's output.
    2. `SELECT` then `DELETE`: Fetch the data for `returning` and `affected_rows` fields first, by running the `SELECT` query generated by `mkMutationOutputSelect` inclusive of the `DELETE` query's `WHERE` and permissions filters, i.e.
        ```sql
        WITH with_alias AS (
          SELECT * FROM table_name WHERE <where-from-delete-query>
        )
        <select statement generated by mkMutationOutputSelect function>
        ```
        ...before executing the generated `DELETE` statement

      Developers should consider the possible interaction with [transaction isolation levels](https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql) of both approaches when implementing. Whilst the first option is recommended after some internal discussion, developers should revive the discussion with [@hasura/server-data-sources](https://github.com/orgs/hasura/teams/server-data-sources) or do a short technical spike if there is any uncertainty about the better approach.

  - Nested return statements can be supported by drawing inspiration from the existing `mkMutationOutputSelect` function.
  - [Delete permissions](https://hasura.io/docs/latest/graphql/core/auth/authorization/permission-rules.html#delete-permissions) are bundled in the IR with the `WHERE` expression, in `mkDeleteObject`; the generated SQL should contain the filter expression and permissions in the same `WHERE` clause.
  - Regarding [`INSTEAD OF DELETE` triggers](https://docs.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers): we need not try and do anything better than the limitations of the `OUTPUT` statement.