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.
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:
Using variables:
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:
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.
Note
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β
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:
Using variables:
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