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 multipleobjects
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.
If a table is not in the dbo
MS SQL Server schema, the insert mutation field will be of the format
insert_<schema_name>_<table_name>
.
Insert a single object
Example: Insert a new article
object and return the inserted article object in the response:
xxxxxxxxxx
mutation insert_single_article {
insert_article_one(
object: {
title: "Article 1",
content: "Sample article content",
author_id: 3
}
) {
id
title
}
}
{
"data": {
"insert_article_one": {
"id": 21,
"title": "Article 1"
}
}
}
Using variables:
xxxxxxxxxx
mutation insert_single_article($object: article_insert_input! ) {
insert_article_one(object: $object) {
id
title
}
}
xxxxxxxxxx
{
"object": {
"title": "Article 1",
"content": "Sample article content",
"author_id": 3
}
}
xxxxxxxxxx
{
"data": {
"insert_article_one": {
"id": 21,
"title": "Article 1"
}
}
}
The insert_<object>_one
mutation will only be available if you have select permissions on the table, as it returns
the inserted row.
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:
xxxxxxxxxx
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
}
}
}
xxxxxxxxxx
{
"data": {
"insert_article": {
"affected_rows": 2,
"returning": [
{
"id": 22,
"title": "Article 2"
},
{
"id": 23,
"title": "Article 3"
}
]
}
}
}
Using variables:
xxxxxxxxxx
mutation insert_multiple_articles($objects: [article_insert_input!]! ) {
insert_article(objects: $objects) {
returning {
id
title
}
}
}
xxxxxxxxxx
{
"objects": [
{
"title": "Article 2",
"content": "Sample article content",
"author_id": 4
},
{
"title": "Article 3",
"content": "Sample article content",
"author_id": 5
}
]
}
xxxxxxxxxx
{
"data": {
"insert_article": {
"affected_rows": 2,
"returning": [
{
"id": 22,
"title": "Article 2"
},
{
"id": 23,
"title": "Article 3"
}
]
}
}
}
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:
xxxxxxxxxx
mutation insert_article {
insert_article(
objects: [
{
title: "Article 1",
content: "Sample article content",
author_id: 3
}
]
) {
returning {
id
title
author {
id
name
}
}
}
}
xxxxxxxxxx
{
"data": {
"insert_article": {
"affected_rows": 1,
"returning": [
{
"id": 21,
"title": "Article 1",
"author": {
"id": 3,
"name": "Sidney"
}
}
]
}
}
}
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:
xxxxxxxxxx
mutation insert_article_with_def_id {
insert_article(
objects: [
{
title: "Article 1",
content: "Sample article content",
author_id: 3
}
]
) {
returning {
id
title
}
}
}
xxxxxxxxxx
{
"data": {
"insert_article": {
"affected_rows": 1,
"returning": [
{
"id": 21,
"title": "Article 1"
}
]
}
}
}
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
:
xxxxxxxxxx
mutation insert_author_with_null_age {
insert_author(
objects: [
{
name: "Jeff"
}
]
) {
returning {
id
name
age
}
}
}
xxxxxxxxxx
{
"data": {
"insert_author": {
"returning": [
{
"id": 11,
"name": "Jeff",
"age": null
}
]
}
}
}
OR
xxxxxxxxxx
mutation insert_author_with_null_age {
insert_author(
objects: [
{
name: "Jeff",
age: null
}
]
) {
returning {
id
name
age
}
}
}
xxxxxxxxxx
{
"data": {
"insert_author": {
"returning": [
{
"id": 11,
"name": "Jeff",
"age": null
}
]
}
}
}