From ba4b3d346a73f2c748cab97d793d99b9658e87d7 Mon Sep 17 00:00:00 2001 From: rakeshkky Date: Thu, 19 Jul 2018 17:51:58 +0530 Subject: [PATCH] add upsert related docs --- graphql/manual/mutations/index.rst | 1 + graphql/manual/mutations/upsert.rst | 83 ++++++++++++++ graphql/manual/mutations/upsert.rst.wip | 146 ------------------------ 3 files changed, 84 insertions(+), 146 deletions(-) create mode 100644 graphql/manual/mutations/upsert.rst delete mode 100644 graphql/manual/mutations/upsert.rst.wip diff --git a/graphql/manual/mutations/index.rst b/graphql/manual/mutations/index.rst index 47cf6f5..9adb6dc 100644 --- a/graphql/manual/mutations/index.rst +++ b/graphql/manual/mutations/index.rst @@ -24,6 +24,7 @@ Let's use this reference Authors/Articles schema to look at different types of m :maxdepth: 1 Insert + Upsert Update Delete multiple-mutations diff --git a/graphql/manual/mutations/upsert.rst b/graphql/manual/mutations/upsert.rst new file mode 100644 index 0000000..c337bb4 --- /dev/null +++ b/graphql/manual/mutations/upsert.rst @@ -0,0 +1,83 @@ +Upsert mutation +=============== + +To convert an **insert** mutation into an **upsert** one, you need to specify the unique or primary key constraint(s) and the action +to be taken in the case of a conflict or violation. You can specify a constraint using the ``constriant`` argument. +On conflict, you can choose to either ignore the mutation (``action: ignore``) or update the row that caused the conflict (``action: update``). +``ignore`` and ``update`` are enum values for ``action``. +For the following examples, assume there's a unique constraint on the ``name`` column of the ``author`` table. + +.. note:: + + You can fetch the name of unqiue or primary key constraints by quering the ``information_schema.table_constraints`` table. + GraphQL Engine will automatically generate constraint names as enum values for ``constraint`` (try autocompleting in GraphiQL). + Typically, the constraint is automatically named as ``__key``. + +With constraint name and update +------------------------------------ +Insert a new object in the author table or, if the unique constraint, ``author_name_key``, is violated, update +the existing object: + +.. graphiql:: + :view_only: true + :query: + mutation upsert_author { + insert_author( + objects: [ + {name: "John", id: 10} + ], + on_conflict: { + constraint: author_name_key, + action: update + } + ) { + affected_rows + } + } + :response: + { + "data": { + "insert_author": { + "affected_rows": 1 + } + } + } + +The response shown above assumes that the name of the author in our object is not unique and then +*updates* the corresponding row in the database. + +With constraint name and ignore +------------------------------------ +Insert a new object into the author table or, if the unique constraint, ``author_name_key``, is violated, +ignore the request: + +.. graphiql:: + :view_only: true + :query: + mutation upsert_author { + insert_author( + objects: [ + {name: "John", id: 10} + ], + on_conflict: { + constraint: author_name_key, + action: ignore + } + ) { + affected_rows + } + } + :response: + { + "data": { + "insert_author": { + "affected_rows": 0 + } + } + } + +In this case, the insert mutation is ignored because there is a conflict. + +.. note:: + + ``constraint`` is optional when ``action`` is ``ignore``. diff --git a/graphql/manual/mutations/upsert.rst.wip b/graphql/manual/mutations/upsert.rst.wip deleted file mode 100644 index 6fa6868..0000000 --- a/graphql/manual/mutations/upsert.rst.wip +++ /dev/null @@ -1,146 +0,0 @@ -Upsert mutation -=============== - -To convert an *insert* mutation into an *upsert* one, you need to specify the unique constraint(s) and the action -to be taken in the case of a conflict or violation. There are two ways to specify unique constraints, either -specify the name of a unique constraint (using the ``constraint`` argument) or a list of columns that have -unique constraints on them (using the ``constraint_on`` argument). On conflict, you can choose to either -ignore the mutation (``action: "ignore"``) or update the row that caused the conflict (``action: "update"``). - -For the following examples, assume there's a unique constraint on the ``name`` column of the ``author`` table. - -.. note:: - - You can fetch the name of unqiue constraints by quering the ``information_schema.table_constraints`` table. - Typically, the constraint is automatically named as ``__key`` when using the console - to add it. The API-console will soon carry this information in the ``Data`` section. - -With unique constraint name (update) ------------------------------------- -Insert a new object in the author table or, if the unique constraint, ``author_name_key``, is violated, update -the existing object: - -.. graphiql:: - :view_only: true - :query: - mutation upsert_author { - insert_author( - objects: [ - {name: "John", id: 10} - ], - on_conflict: { - constraint: "author_name_key", - action: "update" - } - ) { - affected_rows - } - } - :response: - { - "data": { - "insert_author": { - "affected_rows": 1 - } - } - } - -The response shown above assumes that the name of the author in our object is not unique and then -*updates* the corresponding row in the database. - -With unique constraint name (ignore) ------------------------------------- -Insert a new object into the author table or, if the unique constraint, ``author_name_key``, is violated, -ignore the request: - -.. graphiql:: - :view_only: true - :query: - mutation upsert_author { - insert_author( - objects: [ - {name: "John", id: 10} - ], - on_conflict: { - constraint: "author_name_key", - action: "ignore" - } - ) { - affected_rows - } - } - :response: - { - "data": { - "insert_author": { - "affected_rows": 0 - } - } - } - -In this case, the insert mutation is ignored because there is a conflict. - -With columns having unique constraint (update) ----------------------------------------------- -Insert a new object into the author table or, if a unique constraint on the specified columns, in this case -- ``name``, is violated, update the existing object with values from the fields (in this case - ``id``): - -.. graphiql:: - :view_only: true - :query: - mutation upsert_author { - insert_author( - objects: [ - {name: "Jane", id: 10} - ], - on_conflict: { - constraint_on: ["name"], - action: "update" - } - ) { - affected_rows - } - } - :response: - { - "data": { - "insert_author": { - "affected_rows": 1 - } - } - } - -With columns having unique constraint (ignore) ----------------------------------------------- -Insert a new object into the author table or, if a unique constraint on the specified columns, in this case - ``name``, is violated, ignore the request: - -.. graphiql:: - :view_only: true - :query: - mutation upsert_author { - insert_author( - objects: [ - {name: "Jane", id: 10} - ], - on_conflict: { - constraint_on: ["name"], - action: "ignore" - } - ) { - affected_rows - } - } - :response: - { - "data": { - "insert_author": { - "affected_rows": 0 - } - } - } - -.. note:: - - Primary key constraint is not the same as a unique constraint. So, if you include a column that is only part of - a primary key as one of the ``constraint_on`` argument's parameters, you will run into the following error: - ``there is no unique or exclusion constraint on target column(s)``. \ No newline at end of file