Postgres: 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!]!
  on_conflict: article_on_conflict
): 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 (supported from v1.2.0)
insert_article_one (
  object: article_insert_input!
  on_conflict: article_on_conflict
): article

As you can see from the schema:

  • objects argument is necessary and you can pass multiple objects to the mutation.
  • You can pass an on_conflict 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.

See the insert mutation API reference for the full specifications.

Note

If a table is not in the public Postgres 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:

mutation insert_single_article {
  insert_article_one(
    object: {
      title: "Article 1",
      content: "Sample article content",
      author_id: 3
    }
  ) {
    id
    title
  }
}
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:

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

with variables:

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

Note

insert_<object>_one will only be available if you have select permissions on the table, as it returns the inserted row.

Supported from

The insert_<object>_one mutation is supported in versions v1.2.0 and above.

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
    }
  }
}
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 } } }
{ "data": { "insert_article": { "affected_rows": 2, "returning": [ { "id": 22, "title": "Article 2" }, { "id": 23, "title": "Article 3" } ] } } }

Using variables:

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

with variables:

{
  "objects": [
    {
      "title": "Article 2",
      "content": "Sample article content",
      "author_id": 4
    },
    {
      "title": "Article 3",
      "content": "Sample article content",
      "author_id": 5
    }
  ]
}
mutation insert_multiple_articles($objects: [article_insert_input!]! ) { insert_article(objects: $objects) { returning { id title } } }
{ "data": { "insert_article": { "affected_rows": 2, "returning": [ { "id": 22, "title": "Article 2" }, { "id": 23, "title": "Article 3" } ] } } }
{ "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
      }
    }
  }
}
mutation insert_article { insert_article( objects: [ { title: "Article 1", content: "Sample article content", author_id: 3 } ] ) { returning { id title author { id name } } } }
{ "data": { "insert_article": { "affected_rows": 1, "returning": [ { "id": 21, "title": "Article 1", "author": { "id": 3, "name": "Sidney" } } ] } } }

Insert an object with a JSONB field

Example: Insert a new author object with a JSONB address field:

mutation insert_author($address: jsonb) {
  insert_author (
    objects: [
      {
        name: "Ash",
        address: $address
      }
    ]
  ) {
    affected_rows
    returning {
      id
      name
      address
    }
  }
}

with variables:

{
  "address": {
    "street_address": "161, 19th Main Road, Koramangala 6th Block",
    "city": "Bengaluru",
    "phone": "9090909090",
    "state": "Karnataka",
    "pincode": 560095
  }
}
mutation insert_author($address: jsonb) { insert_author ( objects: [ { name: "Ash", address: $address } ] ) { affected_rows returning { id name address } } }
{ "data": { "insert_author": { "affected_rows": 1, "returning": [ { "id": 1, "name": "Ash", "address": { "city": "Bengaluru", "phone": "9090909090", "state": "Karnataka", "pincode": 560095, "street_address": "161, 19th Main Road, Koramangala 6th Block" } } ] } } }
{ "address": { "street_address": "161, 19th Main Road, Koramangala 6th Block", "city": "Bengaluru", "phone": "9090909090", "state": "Karnataka", "pincode": 560095 } }

Insert an object with an ARRAY field

To insert fields of array types, you currently have to pass them as a Postgres array literal.

Example: Insert a new author with a text array emails field:

mutation insert_author {
  insert_author (
    objects: [
      {
        name: "Ash",
        emails: "{ash@ash.com, ash123@ash.com}"
      }
    ]
  ) {
    affected_rows
    returning {
      id
      name
      emails
    }
  }
}
mutation insert_author { insert_author ( objects: [ { name: "Ash", emails: "{ash@ash.com, ash123@ash.com}" } ] ) { affected_rows returning { id name emails } } }
{ "data": { "insert_author": { "affected_rows": 1, "returning": [ { "id": 1, "name": "Ash", "emails": ["ash@ash.com", "ash123@ash.com"] } ] } } }

Using variables:

mutation insert_author($emails: _text) {
  insert_author (
    objects: [
      {
        name: "Ash",
        emails: $emails
      }
    ]
  ) {
    affected_rows
    returning {
      id
      name
      emails
    }
  }
}

with variables:

{
  "emails": "{ash@ash.com, ash123@ash.com}"
}
mutation insert_author($emails: _text) { insert_author ( objects: [ { name: "Ash", emails: $emails } ] ) { affected_rows returning { id name emails } } }
{ "data": { "insert_author": { "affected_rows": 1, "returning": [ { "id": 1, "name": "Ash", "emails": ["ash@ash.com", "ash123@ash.com"] } ] } } }
{ "emails": "{ash@ash.com, ash123@ash.com}" }

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 Postgres 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
    }
  }
}
mutation insert_article_with_def_id { insert_article( objects: [ { title: "Article 1", content: "Sample article content", author_id: 3 } ] ) { returning { id title } } }
{ "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:

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

OR

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