Insert mutation

Auto-generated insert mutation schema

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

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!]!
}

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.

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>.
  • To fetch nested objects using relationships in the mutation response, the table needs to have either a primary key or a unique constraint with not null columns.

Insert a single object

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

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

Insert a single object (using variables)

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

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

with variables:

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

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

Note

For this to work, the parent table (in this case, article) needs to have either a primary key or a unique constraint.

Insert an object and its nested object in the same mutation

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

mutation insert_article {
  insert_article(
    objects: [
      {
        id: 21,
        title: "Article 1",
        content: "Sample article content",
        author: {
          data: {
            id: 11,
            name: "Cory"
          }
        }
      }
    ]
  ) {
    affected_rows
    returning {
      id
      title
      author {
        id
        name
      }
    }
  }
}
mutation insert_article { insert_article( objects: [ { id: 21, title: "Article 1", content: "Sample article content", author: { data: { id: 11, name: "Cory" } } } ] ) { affected_rows returning { id title author { id name } } } }
{ "data": { "insert_article": { "affected_rows": 2, "returning": [ { "id": 21, "title": "Article 1", "author": { "id": 11, "name": "Cory" } } ] } } }

Note

For this to work, the parent table (in this case, article) needs to have either a primary key or a unique constraint.

Insert an object with a JSONB column

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

mutation insert_author($address: jsonb) {
  insert_author (
    objects: [
      {
        id: 1,
        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: [ { id: 1, 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 } }

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 default value of id is set to auto-incrementing integer, no need to pass id field in 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, either don’t pass age field in input object or pass it as null to set it to 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 } ] } } }