Native Mutations

Introduction

Native Mutations allow you to run custom SQL queries on your PostgreSQL database that can be exposed via the Hasura GraphQL engine and modify the database state.

Permissions not yet supported

Native Mutations do not yet support permissions. This means that any user with access to the GraphQL API can execute Native Mutations. Ensure that you have appropriate security measures in place to prevent unauthorized access. An update on this feature will be provided in an upcoming release.

Structure

A Native Mutation is a single SQL statement that returns results and can take arguments, and might modify the database. The SQL structure of a Native Mutation is specified in the Native Operation syntax page, but require an important tweak:

The SQL should include a RETURNING clause, listing the returned columns (use RETURNING * to return all columns).

Create a Native Mutation

To create a new Native Mutation, create a new SQL file inside the connector configuration directory, then use the ddn CLI to add it to the connector configuration. For example:

1. Create a new directory structure under the connector configuration:

   mkdir -p my_subgraph/connector/chinook_pg/native_operations/mutations/

2. Create a new SQL file my_subgraph/connector/chinook_pg/native_operations/mutations/insert_artist.sql with the following content:

   INSERT INTO public."Artist" VALUES (
     ( SELECT "ArtistId" + 1
       FROM "Artist"
       ORDER BY "ArtistId" DESC
       LIMIT 1
     ),
     {{name}}
   )
   RETURNING *

3. Create a new entry in the ndc-postgres configuration:

   ddn connector plugin --connector my_subgraph/connector/chinook_pg/connector.yaml -- \
     native-operation create --operation-path native_operations/mutations/insert_artist.sql --kind mutation

4. Update your metadata to track the new native query:

   $ ddn connector-link update chinook_pg --add-all-resources

Other operations

You can also create bespoke mutations for updates and deletes. As an example, this mutation can be used to update an artist's name:

-- in update_artist_name.sql
UPDATE public."Artist"
SET "Name" = {{new_name}}
WHERE "ArtistId" = (
  SELECT "ArtistId"
  FROM public."Artist"
  WHERE "Name" = {{current_name}}
  LIMIT 1
)
RETURNING *;

List Native Operations

To list the existing Native Operations for a specific connector, use the list command. For example:

ddn connector plugin --connector my_subgraph/connector/chinook_pg/connector.yaml -- \
  native-operation list

Delete a Native Mutation

A Native Query can be deleted with the delete command. For example:

ddn connector plugin --connector my_subgraph/connector/chinook_pg/connector.yaml -- \
  native-operation delete --name insert_artist --kind mutation

Usage

With the example above, you can then run the mutation in your GraphQL API like this:

mutation {
  insert_artist(name: "New Artist") {
    ArtistId
    Name
  }
}