How to insert data into two tables using one API call?

[jvvinoth]

I want to create a GraphQL mutation that inserts data into two tables: Table 1 (Main Table) and Table 2 (History Table). The columns in both tables are identical. In Table 1, the primary key is an auto-generated integer. In Table 2, the primary key is also an auto-generated integer, and there's a foreign key that references the primary key value from Table 1. My need is to have a single GraphQL API call that inserts data into both tables, with the foreign key value in Table 2 dependent on the primary key value generated during the insertion into Table 1. How can this be achieved in a GraphQL query?

[meetzaveri]

Hello @jvvinoth

In order to perform single API to insert data into two tables, there are some prerequisites to follow:

• There must be identical column with same column types in two separate tables
• Having one of the following relationships, either one-to-one/one-to-many/many-to-many relationship between these two tables. Reference - https://hasura.io/docs/latest/schema/postgres/table-relationships/index/#table-relationships

How to create relationships between tables in Hasura - https://hasura.io/docs/latest/schema/postgres/table-relationships/create/

In my case I have authors and articles table where, I have id with UUID type in “authors” table and created_by column with UUID type in “articles” table which contains ID of author.

I have created many-to-one relationship (Object based) from articles -> author and one-to-many (Array based) relationship from authors -> articles.

After all these above steps are completed, we can prepare nested insert mutation. We have already covered on how to insert an object with its related object(s) through relationships here in Hasura doc - https://hasura.io/docs/latest/mutations/postgres/insert/#pg-nested-inserts

Coming back to my example, if I want to insert author and it’s related article in one API call here’s how it would look

Query

  insert_authors_one(object: $object) {
    age
    articles {
      content
      title
    }
    displayName
    userName
    id
  }
}

And here’s how my query variables would look like

{"object": 
  {
    "userName": "Thomas Tuchel",
    "articles": {
      "data": [
				{"content": "ABC","title":"Double pivot with CDM"}
			]
		}
	}
}


This would give me response like

{
  "data": {
    "insert_authors_one": {
      "age": null,
      "articles": [
        {
          "content": "ABC",
          "title": "Double pivot with CDM"
        }
      ],
      "displayName": null,
      "userName": "Thomas Tuchel",
      "id": "e068d29b-cf65-45ea-9bfa-5e0fdbe8d7e8"
    }
  }
}

I hope you got the gist of what I am trying to explain. Feel free to drop your concerns/feedbacks.

[jvvinoth]

@meetzaveri Thank you so much! This works as expected.