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 multipleobjects
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.
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
address
is inserted and itsid
is collected in this step.The parent object is inserted next. i.e. in this case, the
author
is now inserted with theaddress_id
being set to theid
of the address that was inserted. Because of this, it is not allowed to passaddress_id
in the author object if you are also providing data for the address relationship.The
id
of the author is collected in this step.The array relationship objects are inserted at the end. i.e. in this case, the
articles
are now inserted with theirauthor_id
set to the author'sid
collected in the step 2. Hence, it's not possible to specifyauthor_id
in 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. thearticle
is inserted.The
id
of the article is collected in this step.The array relationship objects (from the perspective of
article
) are inserted next i.e. thearticle_tags
are inserted.The object relationship objects (from the perspective of
article_tags
) are inserted now i.e. thetags
are now inserted.The
ids
of the tags are collected in this step.The parent object (from the perspective of
article_tags
) is inserted at the end i.e. thearticle_tags
are now inserted with theirarticle_id
set to the article'sid
collected in step 1. Thetag_id
is set to the tag'sid
collected in step 2.1. Hence, it’s not possible to specifyarticle_id
andtag_id
in 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