Postgraphile: Is it possible to load the dynamically generated Postgraphql schema as part of a larger schema that is implemented in (say) NodeJS?

[w9]

What I mean is, say I have a schema like this:

query {
  func1(...) {
    ...
  }
  func2(...) {
    ...
  }
  allPeople(...) {
    ...
  }
  personById(...) {
    ...
  }
}

Is it possible to write the schema implementation for func1 and func2 in NodeJS, and include the Postgraphql-generated allPeople and personById as part of the schema?

[benjie]

Yes, there's two ways of doing that:

1. In v4 you can write plugins to extend the schema: https://www.graphile.org/postgraphile/extending/#adding-root-querymutation-fields
2. use something like graphql-weaver to stitch two GraphQL schemas together (the PostGraphile one and one containing your custom functions)

[dhsass]

One of the problems we are facing right now is that we have about 115 database functions defined but PostGraphile only recognizes a handful of them. My development manager found this issue and pointed me to the link you provided: https://www.graphile.org/postgraphile/extending/#adding-root-querymutation-fields. He has tasked me with writing a plugin to retrieve the list of functions and update the list of APIs in Documentation Explorer in GraphiQL and then left on vacation. I have found a query that will return a list of functions from postgresql but then how do I convert the list into APIs in the GraphiQL? I looked at the example at the link and that wasn't terribly helpful to my problem.

[benjie]

Could you expand why the functions aren't supported by PostGraphile? We've got pretty powerful function support in general. I'm guessing it's because they return anonymous types like returns record or returns table(x int, y text)? Would it be easier in your system to instead return named types by converting your functions, e.g.

create function foo() returns table(x int, y text) as ...

would become:

create type foo_result as (
  x int,
  y text
);
create function foo() returns foo_result as ...

If so you could then get support from PostGraphile automatically with very little effort and no maintenance overhead.

---

If that's not a suitable solution, you're going to want to look at PgQueryProceduresPlugin, which is the plugin responsible for adding root level fields to the Query type from functions in the database.

https://github.com/graphile/graphile-build/blob/master/packages/graphile-build-pg/src/plugins/PgQueryProceduresPlugin.js

I'm guessing it's around here that your functions are not being detected:

https://github.com/graphile/graphile-build/blob/465cac46fcbaba0f07491046b69d91063532e7f6/packages/graphile-build-pg/src/plugins/makeProcField.js#L137-L147

---

Note there's also a brand new (literally released on Saturday) graphile-utils interface for adding fields/types/etc; read more about it higher up on that page:

https://www.graphile.org/postgraphile/extending/#the-easy-way-graphile-utils

To use it you need to be running [email protected] or higher. Documentation is fairly limited at this stage.

Good luck!

[dhsass]

Maybe it would be helpful if I provided an example of one of the functions that isn't recognized. The "insert" functions aren't recognized. They create a new record and return the id of the new record. Below is an example of a function that PostGraphile doesn't recognize. All of our "insert" functions follow this pattern. Maybe you'll see something in this code that explains our problem. In the meantime, I'll look at the links you've provided. Thanks.

CREATE OR REPLACE FUNCTION public.bunny_Insert(
IN in_bunny_name character varying(100),
IN in_is_active Boolean,
OUT out_bunny_id bigint
) AS
$BODY$
BEGIN
    Insert into bunny
    (
    bunny_name,
    in_active
    )
    VALUES
    (
    in_bunny_name,
    in_is_active
    )

    RETURNING bunnyId into out_bunny_id;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
; 

[benjie]

Ah; I keep forgetting about IN/OUT/INOUT operators for functions - we don't support them. I've filed an issue to document this.

The function above would be supported if you rewrote it to:

CREATE OR REPLACE FUNCTION public.bunny_Insert(
in_bunny_name character varying(100),
in_is_active Boolean
) RETURNS bigint AS
$BODY$
DECLARE
    out_bunny_id bigint;
BEGIN
    Insert into bunny
    (
    bunny_name,
    in_active
    )
    VALUES
    (
    in_bunny_name,
    in_is_active
    )

    RETURNING bunnyId into out_bunny_id;
    RETURN out_bunny_id;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
; 

[benjie]

Hey @dhsass, how are you getting on?

[dhsass]

Hi Benjie,

Thanks for checking up on us. I had hoped I would have a better reply by
now. I'm a bit of a go-between at the moment. I passed your example to
our DBA. He was having trouble getting the pattern to work with one of our
other functions saying: "it wouldn't compile." That's where things stood
at the end of the day on Tuesday. Yesterday was a holiday here in the US.
It's still early here this morning so I'll be following up with the DBA
when he gets and see if he made any progress. I had hoped your example
would be the solution to our problem - and am still hopeful - but so far
I'm still waiting on the DBA to get it to work.

Thanks,
Darwin

On Thu, Jul 5, 2018 at 1:56 AM Benjie Gillam [e-mail]
wrote:

Hey @dhsass https://github.com/dhsass, how are you getting on?

—
You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub
https://github.com/graphile/postgraphile/issues/671#issuecomment-402637509,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AklkOCouneKqTUc99lJJtZpUsOS6B1W6ks5uDcbCgaJpZM4RryUn
.

[benjie]

Feel free to put us in touch directly; I might be able to give a pointer or two 👍

[benjie]

Using graphile-utils you can also add graphql-tools-style APIs into PostGraphile's schema directly:

https://www.graphile.org/postgraphile/extending/#the-easy-way-graphile-utils

[sspsinha]

I need to use postgraphile auto generated schema query,mutation to build the custom query, mutation in the same api. Is it possible? I do not want to use SQL approach. If its possible please provide some approach to access the auto generated schema, query inside the custom query.

[benjie]

Yes; you can do so with makeExtendSchemaPlugin; here’s what you need to know:

• the GraphQL function is available on build.graphql.graphql
• the schema is available on resolveInfo.schema (I think, something like that)

You can then execute the GraphQL function passing the schema, your query, any variables, and the context.

GraphQL function docs: https://graphql.org/graphql-js/graphql/#graphql

[sspsinha]

Thanks Benjie. It worked.