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:
objectsargument is necessary and you can pass multipleobjectsto the mutation.- You can pass an
on_conflictargument 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.
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:
Using variables:
insert_<object>_one will only be available if you have select permissions on the table, as it returns the inserted
row.
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:
Using variables:
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:
Insert an object along with its related objects through relationships
One-to-one / One-to-many relationships
Let's say an author has an object relationship called address to the addresses table and an array relationship
called articles to the articles table.
Example: Insert an author along with their address and a few articles.
How it works
A nested insert mutation is processed as follows:
The object relationship objects are inserted first, i.e. in this case, the
addressis inserted and itsidis collected in this step.The parent object is inserted next. i.e. in this case, the
authoris now inserted with theaddress_idbeing set to theidof the address that was inserted. Because of this, it is not allowed to passaddress_idin the author object if you are also providing data for the address relationship.The
idof the author is collected in this step.The array relationship objects are inserted at the end. i.e. in this case, the
articlesare now inserted with theirauthor_idset to the author'sidcollected in the step 2. Hence, it's not possible to specifyauthor_idin the data for the articles relationship.
The order of object insertion can be controlled using the
insertion_order option while creating a manual relationship.
This is necessary to ensure nested inserts in
one-to-one relationships are possible using either side as the parent which would otherwise error out with a
Not-NULL violation error in one of the cases.
Many-to-many relationships
Let's say the articles has a many-to-many relationship with
the tags table via a bridge table article_tags.
Example: Insert an article along with a few tags.
How it works
The parent object (from the perspective of
article) is inserted first i.e. thearticleis inserted.The
idof the article is collected in this step.The array relationship objects (from the perspective of
article) are inserted next i.e. thearticle_tagsare inserted.The object relationship objects (from the perspective of
article_tags) are inserted now i.e. thetagsare now inserted.The
idsof the tags are collected in this step.The parent object (from the perspective of
article_tags) is inserted at the end i.e. thearticle_tagsare now inserted with theirarticle_idset to the article'sidcollected in step 1. Thetag_idis set to the tag'sidcollected in step 2.1. Hence, it’s not possible to specifyarticle_idandtag_idin the data for the article_tags relationship.
on_conflict
on_conflict can be passed as an argument in a nested insert statement. In our example, we say that if the unique key
(label) already exists for a tag, we update the label of this respective tag (see
nested upsert caveats).
Insert an object with a JSONB field
Example: Insert a new author object with a JSONB address field:
Insert an object with an ARRAY field
Example: Insert a new author with a text array emails field:
Using variables:
To insert fields of nested array types, you have to pass them as a Postgres array literal.
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:
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:
OR