Feature Request: Expose Stored Procedures as GraphQL Mutations

[wolframm]

Summary

It's rare that the standard CRUD ops generated by schema introspection will cover our needs. Mutations exposing stored procedures would enable us to write exactly the business logic we need with access to all of our data wrapped up in a PostgreSQL function.

Rationale

For most real world apps, frontend devs will not know how to write GraphQL mutations that insert or update data. For example, my own app (e-commerce, currently still on Firebase, but soon hopefully on Supabase...) uses 100s of cloud functions such as "createTangibleProduct" or "grantWritePrivilegeOnProductToUser", which give frontend devs a defined set of arguments they can specify to create a new product or grant specific rights to specific users. Such functions check and update multiple tables (collections) in a single transaction. This would be very hard and error prone to do with CRUD ops on the frontend.

Design

The solution is to write SQL and PLPGSQL functions that ask for well defined arguments to handle all the complexity and are then exposed via GraphQL to clients.

Examples

See example usage in Graphile

Drawbacks

I don't see any.

Alternatives

Functions are already exposed by Postgrest. But this requires working with two interfaces and generating two sets of Typescript types (openapi-typescript and graphql-codegen). It would be much cleaner and more efficient to work only with the widely used GraphQL standard.

[olirice]

thanks for opening. Its in the pipe!

[wolframm]

@olirice are you already able to provide an ETA for this feature?

[olirice]

Not an exact ETA but in terms of priorities there are 2 more things to buttoned down for the table interface

• null literals (enables setting a value to null)
• expanded filter support for and and or composition

and then UDF should be right at the top of the list. I'd guess Q1 of 2023

[MikeyUchiha]

@olirice I just wanted to follow up on the ETA for this feature. I need to be able to do POSTGIS queries, such as finding all nearby users. From my understanding, this is not possible right now with GraphQL. is that correct?

[olirice]

this is not possible right now with GraphQL. is that correct?

you could use computed relationships to do this as soon as 1.2.2 (I just pinged about it) but that interface might not be ideal

---

we're aware that some of these tasks have been hanging out for a while and are hiring for another rust developer to get them un-stuck

[imor]

@olirice please assign to me. I'm starting on this.

[EloB]

Does this also include root queries (not mutations)?

I mean todo something like this:
https://supabase.com/docs/guides/database/extensions/postgis#order-by-distance

I would like todo:

query getRestaurants($lat: float, $lng: float) {
  nearbyRestaurants(lat: $lat, lng: $lng) {
    edges {
      node {
        name
        distance(lat: $lng, lng: $lng) # ability to pass args to computed fields.
      }
    }
  }
}

Alternative:

query getRestaurants($lat: float, $lng: float) {
  nearbyRestaurants(lat: $lat, lng: $lng) {
    edges {
      node {
        distance
        restaurant {
          name
        }
      }
    }
  }
}

Without this support I will have to leave for postgraphile... 😢

[imor]

@EloB the design is not fully fleshed out yet so can't say what the exact query for your case will look like (probably will be closer to the alternative you suggested). But you should be able to call a function, pass it arguments and get a result back.

[jilpi]

@imor very much appreciate you spending time on this issue. We're developing a new app based on Supabase, and feel bothered that we still have to use PostREST to get our data (same reason as @EloB 😃 ). Thank you!