Postgraphile: using postgraphql with apollo-server

[fortm]

Finding it really useful project for API generation

Had a doubt for existing apollo projects, if it is possible to modify existing graphql schema to run postgraphql without embedding postgraphql as node library and calling postgraphql from apollo-server ?

[calebmer]

Yes, but it may be prohibitively difficult. You can use the createPostGraphQLSchema function to create a schema and merge it in with your Apollo API. Then just take a look in createPostGraphQLRequestHandler to see how we execute queries.

This may be a little difficult at the moment, but this is definetly a usecase we want to look into supporting :+1:

[Levino]

Any example code on this one?

[benjie]

Does withPostGraphQLContext help?
https://github.com/postgraphql/postgraphql/blob/d4fd6a4009fea75dbcaa00d743c985148050475e/docs/library.md#custom-execution

See also #269

[lucasavila00]

I have a very ugly hack that transforms postgraphile into a local-remoteExecutableSchema.

Given that the types have been downloaded beforehand with something like get-graphql-schema, the hack is this:

const { postgraphile } = require("postgraphile");
import { makeRemoteExecutableSchema } from "graphql-tools";
import * as fs from "fs";
import { FetcherOperation } from "graphql-tools/dist/stitching/makeRemoteExecutableSchema";
import { print } from "graphql/language/printer";

const pg = postgraphile(
  process.env.DATABASE_URL
);

const fetcher = ({
  query,
  variables,
  operationName,
  context
}: FetcherOperation) => {
  const graphqlContext = context ? context.graphqlContext : {};
  const { Authorization } = graphqlContext;
  return new Promise(resolve => {
    pg(
      {
        url: "http://anything/graphql",
        method: "POST",
        headers: {
          Authorization,
          "Content-Type": "application/json"
        },
        body: { query: print(query), variables, operationName }
      },
      {
        setHeader: () => {},
        end: (result: string) => {
          resolve(JSON.parse(result));
        }
      }
    );
  });
};

const typeDefs = fs.readFileSync("./src/generated/schema.graphql", "utf-8");

const schema = makeRemoteExecutableSchema({ schema: typeDefs, fetcher });

export default schema;

Using it with graphql-binding one could replicate what the Prisma docs and samples do to run with apollo-server.

To use it:
graphql-binding --language typescript --input theFileAbove.ts --outputBinding out.ts

utils.ts

import { Binding } from "./generated/binding";
import { ContextParameters } from "graphql-yoga/dist/types";

export interface Context extends ContextParameters {
  db: Binding;
}

export const getBindingContext = (ctx: Context) => {
  return {
    context: { Authorization: ctx.request.get("Authorization") }
  };
};

query.ts

import { Context, getBindingContext } from "../utils";

export const me = async (
  _,
  __,
  ctx: Context,
  info
) => {
  return ctx.db.query.userById({ id: 1 }, info, getBindingContext(ctx));
};

This is also being very helpful because I want to implement Firebase Phone Auth without having to run another server in front of Postgraphile.

[benjie]

That's awesome, thanks for sharing! Here's a pointer that might help improve things (sorry I don't have time to edit it fully). You can run the PostGraphile query inside fetcher more naturally by using the schema-only API; e.g.

const fetcher = async operation => {
  const postGraphileContextOptions = {
    // The options you'd pass to the PostGraphile middleware
    ...postgraphileSchemaOptions,

    // You can pass a JWT token
    jwtToken: null,

    // pgSettings must be an object (not a function) by the time it
    // hits withPostGraphileContext
    pgSettings:
      typeof pgSettings === "function"
        ? await pgSettings(fakeReq)
        : pgSettings,
  };
  return withPostGraphileContext(postGraphileContextOptions, context =>
    // import { execute } from 'graphql';
    // since the query shouldn't need re-validation?
    execute(
      // from `await createPostGraphileSchema(...)` or similar
      postgraphileSchema,
      operation.query,
      null,
      context,
      operation.variables,
      operation.operationName
    )
  );
}

(edited directly in GitHub editor, so probably contains mistakes)

Hope this helps.

[lucasavila00]

That's awesome! I will give it a shot!
I plan on releasing something like prisma-binding but for postgraphile.
I'd written and started using that code yesterday so that's very rough.
When I'm done I'll give a shout here.

[benjie]

Awesome; doing that is on my todo list for the examples repo I'm working on; it's very much work in progress right now though. I'm hoping having a concrete application example will help with the documentation as we can link out so you can see how it's used in context.

[lucasavila00]

I'll open source the code I'm working on.
It's a application server, not a library right now, let me just strip the application code and document it.
It may help you.

[lucasavila00]

@benjie, check it out: https://github.com/degroote22/postgraphile-apollo

[benjie]

Thanks for sharing! The bindings looks good, nice and clean 👍

[avkonst]

Continued work done by @degroote22 👍

Letting you know that I published starter project for backend development. It uses postgraphile as a library, based on Apollo Server 2.0 and leverages automated code generation and database schema migrations. Check it out here https://github.com/avkonst/graphql-postgraphile-typeorm-starter

[hansololai]

I didn't like the makeRemoteExecutableSchema, because that means it makes extra remote request, even tho if it's in the same machine, it still takes time.

For apollo server 1.0, the server takes an executable schema and can be directly used

import { graphqlExpress, graphiqlExpress } from 'apollo-server-express';
  const app = express();
  app.use('/graphql', (req, res, next) => {
    const handleGraphQL = graphqlExpress(() => ({
      schema: postgraphileSchema,
      context: {
           pgClient: pgClient
      },
      tracing: false, // tracking for postgraphile does not make much sense.
      cacheControl: true,
    }));
    handleGraphQL(req, res, next);
  });

This schema postgraphileSchema is generated by

import { createPostGraphileSchema } from 'postgraphile';
const postgraphileSchema = createPostGraphileSchema(postgresConfig, 'public')

One thing to note is, the created schema does not have the "context" to do postgres queries. so you'll need to put the context aka the pgClient. @benjie provided the withPostGraphileContext function, which does the same thing in the source code, it is also described in doc. But that function handles request directly, I was trying to give the schema to apollo server for more features they provide. such as apollo engine.

The pgClient . can be created as

import { Pool } from 'pg';
const pool = new Pool({connectionString:'...'})
const pgClient = pool.connect();

I striped a lot of my code, so this is the minimum code to use postgraphile schema with apollo server. I have not updated apollo server 2 so I am not sure if this will work with that. But I think this avoid the need to make removeSchema which should be much faster.

[benjie]

This approach has a few drawbacks:

• no transaction is used
• all queries share one client, so race conditions can occur and performance is much reduced
• setting configuration variables won’t work correctly so you can’t (shouldn’t) combine with JWT/pgSettings/etc

PostGraphile is currently designed around the concept of every query/mutation gaining a pgClient from the pool, setting up the transaction, running the GraphQL, and finally committing the transaction and releasing the client.

Have you benchmarked to see the performance difference between using this approach vs using makeRemoteExecutableSchema? I’d be interested in those numbers 👍

[hansololai]

@benjie Thank you very much for putting attention on my answer. You are the maintainer of postGraphile, your review on this means a lot to me. I did a quick benchmark back when I first set it up. I think the result was like 20-50ms slower, I could redo it sometime later to see the result.

And yes, I am glad you pointed out these drawbacks, when I was digging through your withContext function, I noticed it is a great function to handle a request with everything wrapped. But unfortunately my project needs a lot of customizations, for example, my project needs to connect two postgresql servers and combine them in one endpoint, so I had to use the schema-stitching function provided by apollo server. so I try to replicate what you did in that function with middleware. The code I gave was stripped to minimal function. In my project I actually added a lot of middlewares.

because my project was purely read only api, so I think transaction was not very necessary, but for future needs, I also added the transaction sql. Like so:

  app.use('/graphql', (req, res, next) => {
    // Throw errors for requests without expiration
    if (useJwtSecret && (!req.user || !req.user.exp)) {
      next({ name: 'UnauthorizedError' });
    } else {
      pgClient.query('BEGIN;');
      next();
    }
  });
  app.use('/graphql', (req, res, next) => {
    const handleGraphQL = graphqlExpress(() => ({
      schema: s,
      context: {
        coyotepgClient: coyoteClient,
        falcopgClient: falcoClient,
      },
      tracing: false,
      cacheControl: true,
    }));
    // Hacky wrap res.end() to call next() so that we know the request finished
    res.endbk = res.end;
    res.end = () => {
      res.endbk();
      next({ name: 'NotError' }); // This is to allow we know the query finished
    };
    handleGraphQL(req, res, next);
  });
  // Clean up after request
  app.use('/graphql', (err, req, res, next) => {
    // Always call COMMIT because we called BEGIN first
    coyoteClient.query('COMMIT;');
    if (err.name !== 'NotError') {
      next(err);
    }
  });

This is to wrap the request in a transaction.

From what I understand, for each request, the postgraphile construct one SQL. So for one express app, it can only take one request at a time. Therefore 1 client for a express app is sufficient. (I noticed query batching problem recently and am working on multiple client). I do create multiple express app, using cluster, and each app will have one pgClient.

And you are write, it was very hard to put JWT inside and use postGraphile's jwt authentication ability, so I added jwt middleware too. and switch roles etc before.

My approach basically have to reimplement jwt token and roles etc. But I have been running it for about 4 months, and so far it has been smooth.

Could you elaborate what you mean that setting configuration variables won't work correctly? Thank you again.
@benjie
I just did some benchmark where I only query for just 1 record.

query{
userById(id:1){
id
}
}

I compare the "direct postgraphile", "stitched schema", and "remote schema". The result is
remote x 121 ops/sec ±36.58% (69 runs sampled)
stitch x 229 ops/sec ±40.61% (73 runs sampled)
direct postgraphile x 369 ops/sec ±4.84% (76 runs sampled)

I used benchmark.js and node-fetch to send the requests.

[benjie]

⚠️ The above code is extremely vulnerable to race conditions, this will definitely cause issues with mutations, or if you introduce transaction variables / JWT ⚠️

From what I understand, for each request, the postgraphile construct one SQL. So for one express app,

That is only correct in a small subset of circumstances: for queries with one root field that do not require transactions and have no custom pgSettings/JWT/etc. For mutations a number of statements are ran:

1. begin
2. setup the transaction if necessary (pgSettings / JWT)
3. create savepoint
4. perform mutation
5. select results from database
6. release savepoint
7. steps 3-6 again for any additional mutation fields
8. commit

For queries with settings, we do this:

1. begin
2. setup transaction (lots of set_config calls)
3. run the root query field
4. repeat step 3 for the next root query field
5. commit (releases all the set_config settings)

Any time that Node.js is waiting for a response from the database it can be handling other requests, which means it can be issuing more SQL statements. Normally this is safe because we use one pgClient per transaction and Postgres handles keeping these separate for us. But if we use one pgClient then commands from one request will leak to the other and unexpected results will occur.

So for one express app, it can only take one request at a time. Therefore 1 client for a express app is sufficient.

This is definitely not true. Each single PostGraphile instance can handle hundreds or thousands of GraphQL requests concurrently - we use many different pgClients in parallel. You can see graphs of this working here:

https://medium.com/@Benjie/how-i-made-postgraphile-faster-than-prisma-graphql-server-in-8-hours-e66b4c511160

This approach is highly dangerous.

---

If you're just using this for unauthenticated queries with no custom pgSettings (which I gather you are) then it's safer, but your peak performance will be much lower due to recycling one pgClient (and only being able to run one SQL query at a time) rather than a pool (which can be configured to allow hundreds or thousands of queries concurrently).

If you re-run your benchmarks with a concurrency of 10, I suspect you'll find that remote will come out above stitch.

[hansololai]

@benjie Thank you for replying me at 3:00am! that's really hard work!

Ok, I think there's a misunderstand here. There are a couple of points you made, let me see if I understand.

1. This setup is vulnerable to race conditions for several reasons.

2. This setup does not create transaction/save point correctly for mutations, custom pgSettings, etc.

3. The statement of 1 client for an express app is sufficient is incorrect.

4. remotePostgraphile will behave better for concurrent requests.

I agree to most of these points, in fact, I did thought of some of them, but let's discuss it more.

Response to 4:
The benchmark here was NOT intend to show the stitched schema is better than remoteSchema. It was to show that there is definitely noticeable lag there.

with remoteSchema, the request went through
expressjs->apollo server-> postgraphile server -> postgresql

compare to stitchedSchema
expressjs->apollo server->postgresql

I think there's definately delay there, I did made concurrent request, but use expressjs to only handle request sequentially to make this point.
I agree with you completely that stitchedSchema will have a lot of problems, as it was not using a lot of good features of postGraphile server, such as custom pgSettings, Context, and most importantly, as you pointed out, using Pool instead of one pgClient.

But let's not kill this idea, (it was discouraging to find out I finally removed the lag with this idea, but it turns out to be not good), we are comparing a
no lag, but unsafe, less featured method vs lag, but safe, more featured method. In my opinion, we have 2 options. We can
either decrease the lag of remoteSchema, or make the stichedSchema safe and with the features.
For example, I also setup the client to use Pool instead of one pgClient, it is not a hard problem to change. I also moved lots of your code in the withContext
To the middleware to handle the context. (because context does not exist in the stitchedSchema).
In my opinion, adding features to stitchedSchema will step-by-step, make it more complete, and made the postGraphile schema more flexible, but the lag between expressapp and the remoteSchema is not easy to remote.

Response to 3:

I think you are right. I have changed the pgClient to be "grab a client for each request" and it is much faster and handles a lot of concurrent requests!

Response to 2 and 1:
The code I showed you is very simple, when I first worked on this I was just trying to make something work, and it was a prototype, I did have to handle JWT manually before sending to postGraphile, because the context and stuff are gone. But with time I have added a lot of features that you put in your withContext function in it to improve the setup.

The reason I had to choose stitched schema vs remote schema, is that there are a lot of customizations that I need to add. At the time (few months) ago, plugins were not that much, for example, makeExtendSchemaPlugin did not exist. So I had to stitch the schema with my defined query and resolvers.

Also, I had 2 postgres db, and I need to put them together to one end point, for duplicate tables, such as users, I'll rename one with a prefix. For this task, I'll have to use the transformSchema function provided by graphql-tools. But once I did that, the schema became "not executable" and I'll have to do that manually grab pgClient thing. I took a look back at the doc(actually I have being very actively checking the doc) and I realized lots of the needs can be done with plugin now, so I guess we don't even have to stitchedSchema, or even remoteSchema anymore. To be honest, I would love to just use postgraphile server, without all that stitching, remote hack.

But I still can't figure out if there's a way to just run one postgraphile server for 2 postgresql database. If I can do that with plugin, then I'll totally ditch my stitchedSchema, (this approach is so complex and I have to manually reinvent a lot of wheels), and use the postGraphile out of box with plugins.

[benjie]

Is it possible for you to use foreign data wrappers so that one of your postgres servers can expose the other?

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

Failing that, PostGraphile is not designed to handle multiple databases, so schema stitching is probably what you need. You can use makeAddInflectorsPlugin from graphile-utils to override the naming functions in the schema itself if that helps.

Version 5 will probably change how the system works, opting to have the schema grab its own pgClient rather than using one on context, but that’s a fair way away. That’ll enable much easier schema stitching/etc.

I understand your concerns with latency - 40ms is certainly a lot to add! Out of interest, was that after issuing a few thousand requests to give Node’s JIT time to warm up? If so, it seems quite high. Have you tried different stitching software like graphql-weaver to see if it’s any more efficient?

I can see your solution working with the changes you’re incorporating, I just wanted to ensure that others don’t simply copy/paste your earlier code an open their application up to some serious issues.

[hansololai]

@benjie Thank you for your help with my problem.
I did found makeAddInflectorsPlugin and successfullly renamed the functions.

Yeah the 40ms latency was exagerated, probably about 10ms. Yeah I did have a burn out for few seconds before starting the benchmarking.

I agree, my solution is quite hacky and needs a lot more thoughts and modifications for each app. In the future I would like to compact these code and make it more usable for others.

[benjie]

You can now use PostGraphile directly with Apollo Server 2.2.0+, here’s a module to help:

https://github.com/graphile/postgraphile-apollo-server