MS SQL Server: Insert mutation¶

Auto-generated insert mutation schema¶

For example, the auto-generated schema for the insert mutation field for a table article looks like the following:

insert_article (
  objects: [article_insert_input!]!
  if_matched: article_if_matched
): article_mutation_response

# response of any mutation on the table "article"
type article_mutation_response {
  # number of affected rows by the mutation
  affected_rows: Int!
  # data of the affected rows by the mutation
  returning: [article!]!
}

# single object insert
insert_article_one (
  object: article_insert_input!
  if_matched: article_if_matched
): article

As you can see from the schema:

• objects argument is mandatory and you can pass multiple objects to the mutation.
• You can pass an if_matched argument to convert the mutation to an upsert mutation.
• You can return the number of affected rows and the affected objects (with nested objects) in the response.
• You can use the single object insert to get the inserted object directly as the mutation response.

Insert a single object¶

Example: Insert a new article object and return the inserted article object in the response:

mutation insert_single_article {
  insert_article_one(
    object: {
      title: "Article 1",
      content: "Sample article content",
      author_id: 3
    }
  ) {
    id
    title
  }
}

Using variables:

mutation insert_single_article($object: article_insert_input! ) {
  insert_article_one(object: $object) {
    id
    title
  }
}

{
  "object": {
    "title": "Article 1",
    "content": "Sample article content",
    "author_id": 3
  }
}

Insert multiple objects of the same type in the same mutation¶

Example: Insert 2 new article objects and return both the article objects in the response:

mutation insert_multiple_articles {
  insert_article(
    objects: [
      {
        title: "Article 2",
        content: "Sample article content",
        author_id: 4
      },
      {
        title: "Article 3",
        content: "Sample article content",
        author_id: 5
      }
    ]
  ) {
    returning {
      id
      title
    }
  }
}

Using variables:

mutation insert_multiple_articles($objects: [article_insert_input!]! ) {
  insert_article(objects: $objects) {
    returning {
      id
      title
    }
  }
}

{
  "objects": [
    {
      "title": "Article 2",
      "content": "Sample article content",
      "author_id": 4
    },
    {
      "title": "Article 3",
      "content": "Sample article content",
      "author_id": 5
    }
  ]
}

Insert an object and get a nested object in response¶

Example: Insert a new article object and return the inserted article object with its author in the response:

mutation insert_article {
  insert_article(
    objects: [
      {
        title: "Article 1",
        content: "Sample article content",
        author_id: 3
      }
    ]
  ) {
    returning {
      id
      title
      author {
        id
        name
      }
    }
  }
}

Set a field to its default value during insert¶

To set a field to its default value, just omit it from the input object, irrespective of the default value configuration i.e. via MS SQL Server defaults or using column presets.

Example: If the default value of id is set to auto-incrementing integer, there’s no need to pass the id field to the input object:

mutation insert_article_with_def_id {
  insert_article(
    objects: [
      {
        title: "Article 1",
        content: "Sample article content",
        author_id: 3
      }
    ]
  ) {
    returning {
      id
      title
    }
  }
}

Set a field to NULL during insert¶

If a field is nullable in the database, to set its value to null, either pass its value as null or just omit it from the input object.

Example: If age is a nullable field, to set it to null, either don’t pass the age field to the input object or pass it as null:

mutation insert_author_with_null_age {
  insert_author(
    objects: [
      {
        name: "Jeff"
      }
    ]
  ) {
    returning {
      id
      name
      age
    }
  }
}

OR

mutation insert_author_with_null_age {
  insert_author(
    objects: [
      {
        name: "Jeff",
        age: null
      }
    ]
  ) {
    returning {
      id
      name
      age
    }
  }
}