Enable nested relationships in mutation inputs

[ilbertt]

Describe the bug
It's not possible to insert an object along with its related object(s) in a single mutation. But that's exactly the beauty of GraphQL!

To Reproduce
Consider the following simple extract of a schema (Query, update, delete and all other types omitted for simplicity), where basically we have a One-To-Many relationship between Post and PostImage tables:

type Post {
  id: UUID!
  content: String!
  createdAt: Datetime!
  postImageCollection(
    first: Int
    last: Int
    before: Cursor
    after: Cursor
    filter:PostImageFilter
    orderBy: [PostImageOrderBy!]
  ): PostImageConnection
}

type PostImage {
  id: UUID!
  postId: UUID!
  imageUrl: String!
  createdAt: Datetime!
  post: Post
}

type PostImageConnection {
  edges: [PostImageEdge!]!
  pageInfo: PageInfo!
}

type PostImageEdge {
  cursor: String!
  node: PostImage!
}

input PostInsertInput {
  id: UUID
  content: String!
  createdAt: Datetime
}

type PostInsertResponse {
  affectedCount: Int!
  records: [Post!]!
}

input PostImageInsertInput {
  id: UUID
  imageUrl: String!
  postId: UUID!
  createdAt: Datetime
}

type PostImageInsertResponse {
  affectedCount: Int!
  records: [PostImage!]!
}

type Mutation {

  insertIntoPostCollection(objects: [PostInsertInput!]!): PostInsertResponse

  insertIntoPostImageCollection(objects: [PostImageInsertInput!]!): PostImageInsertResponse

}

Since I usually create a PostImage row every time I create a Post row, I need to execute two separate mutations to achieve this.
First:

mutation CreatePost {
  insertIntoPostCollection(objects: {...}) {
    affectedCount
    records {
      id
    }
  }
}

And after getting the result from CreatePost, I can execute:

mutation CreatePostImage {
  insertIntoPostImageCollection(objects: {
    imageUrl: "whatever",
    postId: "" # here goes the Post id from previous mutation result
  }) {
    affectedCount
    records {
      id
    }
  }
}

Expected behavior
Expose nested objects in mutation inputs, so that I can directly create a Post along with a PostImage with just one mutation:

mutation CreatePost {
  insertIntoPostCollection(objects: {
     content: "blabla",
     postImageCollection: {
       objects: {
         imageUrl: "whatever"
       }
     }
  }) {
    affectedCount
    records {
      id
      content
      postImageCollection(...) {
        imageUrl
        postId # this is automatically assigned by the mutation itself
      }
    }
}

See Hasura docs on this topic for reference.

Versions:

• PostgreSQL: any (this issue does not relate to Postgres itself)
• pg_graphql commit ref: b1cc167

[olirice]

thanks, I'll take a look

[ilbertt]

Thanks @olirice.
I also just realized that the same can be applied to query filters, since right now there's no possibility to filter based on nested fields. See Hasura docs regarding this topic.

Let me know if and how I could help to achieve these enhancements.

[HadiSDev]

@olirice I made a post in the discord channel (ref: https://discord.com/channels/839993398554656828/1063824664520044557) and I think it might be related to this as well. Basically I want to insert one-to-one relationships in one go, but I am not sure you can do that as of now.

[olirice]

hi @HadiSDev

I was going the docs for Supabase GraphQL setup at https://supabase.github.io/pg_graphql/api/#insert

Specifically the insert part as I am trying to create a mutation with multiple inserts. The part I have not figured out yet is how to use the id of the first insert operation to set the foreign key on the second insert operation and if that is even possible. Any ideas?

the graphql spec doesn't have a method for referring to a values return in a previous operation in multi-mutation inserts.

The behavior you're looking for would be supported by the feature this thread describes. We're currently knocking out user defined functions and then revamping inserts will be the next feature on the agenda (nested inserts and upsert) so there should be movement on this soon

[michaelsnook]

Can I also request y'all add some notice to the docs that says "In case you're looking for this one popular GraphQL feature; we don't have it yet, JFYI"?

I am feeling pretty let down after spending a lot of time switching to GraphQL to take advantage of this specific capability, only to find out now that it isn't there at all. I decided to switch to GraphQL because once I got to some core parts of the user flow that require these multi-table inserts. I could already do multi-table queries with the supabase-js client; but I wanted these inserts done right; in a good "serverless" way that feels good for users. I thought, maybe I'll use a supabase server function, or maybe an RPC function, or maybe an edge function/lambda -- but I saw pg_graphql was here to solve exactly this problem for me.

I read your blog post and it gives a code example of a mutation and then says "For a complete example with relationships, check out the API docs." ... so... I thought, no problem. I even skimmed those docs at the time but it wasn't until I got back to the feature that started the whole switch that I read close enough to realize that mutations with relationships are just not mentioned on the page anywhere and I simply can't do them.

Anyway, my b. I will look forward to this feature when it is ready, and I appreciate your response and the context/info on this thread. But please consider adding a note to the docs and possibly also as an edit to the blog post, i.e. "For a complete example showing queries with relationships, check out the API docs. (Mutations with relationships are not yet supported.)" It would have saved me a lot of work.

[olirice]

The two major modes here are inserting nested objects where

• All objects are brand new
• Some objects are new and some are linkages to existing objects

I got the chance to review the Hasura style on conflict solution and it looks pretty good. In the pg_graphql API it'd looks something like this

mutation insertBookWithAuthor {
  insertIntoBookCollection(objects: [
    {
      title: "Some Title",
      author: {
        object: {
          id: 1
          name: "Some Author"
        },
        onConflict: {
          constraint: id,
          update_columns: [name]
          where: ...
        }
      },
    }
  ]) {
    returning {
     ...
    }
  }
 }

Pros:

• onConflict and where can be added to the existing insert types without a breaking change
• intuitive for SQL users
• usable at the top level and nested levels (no special logic)

Cons:

• requires author_id to be nullable in the GraphQL schema, even if it is not null in the SQL schema, because the value can be provided by id or as an object
• lots of new types: Unique constraints enum, update columns enum, "insert one" types for each table
• complex logic

---

The first step will be implementing upsert logic in the existing mutations so we'll start there and see how it goes

[jeaneric]

Another option for implementation inspiration, could be Laravel Lighthouse. I personnaly prefer it than the Hasura one (but I'm not a specialist), it's more clear what operation is done and that you can have multiple operation at the same time.

Basically they offer neasted mutation with those operations:

    connect it to an existing model
    create a new related model and attach it
    update an existing model and attach it
    upsert a new or an existing model and attach it
    disconnect the related model
    delete the related model and the association to it

That are reflected in the schema like this:

input CreateUserBelongsTo {
  connect: ID
  create: CreateUserInput
  update: UpdateUserInput
  upsert: UpsertUserInput
}

For reference see Lighthouse docs: Nested Mutations

[bpbastos]

Thank you for the work, guys. Do you are able to provide an ETA for this feature?

[imor]

Hi @bpbastos, we have work in progress on exposing user defined function as queries/mutations. Once we are done with that, then we can pick this up. I know this is not exactly an ETA but maybe enough information to give you an idea.

[bpbastos]

Hi @bpbastos, we have work in progress on exposing user defined functions as queries/mutations. Once we are done with that, then we can pick this up. I know this is not exactly an ETA but maybe enough information to give you an idea.

Thank you, @imor. Exposing user-defined functions as queries/mutations will do the trick for a while. Do you happen to know if I can define a JSON object as a parameter in my functions? If that's possible, I could implement my own logic for handling nested inputs.

[imor]

Yes, we do plan to support JSON types as function parameters.

[georgii-ivanov]

Any updates on when it's going to be available to use?

[olirice]

we're still working on UDF in #393 but thats down to the last few nits

no timeline on this releasing but work should start soon after ^

[FelixMalfait]

Hey, thanks for the amazing work on pg_graphql!
Is this still on the near-term roadmap? (I don't mean to add pressure, it's just to help us plan accordingly/see if we implement a workaround)

[olirice]

I can't say for sure that it's near-term as in weeks, but we now have UDF in a good place so the precursor to this feature (upsert functionality) is next on the agenda