Filtering results in GraphQL using PostGraphile

Question

I'm trying to wrap my head around GraphQL and I though using PostGraphile to easily and quickly map my PostgreSQL database and expose it using GraphQL. However, I've been stuck a long time on some things that in simple SQL would be a matter of minutes to do -

First, I'm trying to get all records from my database after a defined date, couldn't do this so far, and I end up getting all records which is highly inefficient.

Second, I'd like to get all records which a nullable field in them isn't null (meaning, only if it has something in it, it will show up in the GraphQL results)

If anyone could shed some light on how to do this, or point me to a good tutorial that explains in a simple way how to write custom filtering functions that would be great.

Facility

Answer 1

PostGraphile has a small but growing list of community plugins; for your needs you probably want postgraphile-plugin-connection-filter which adds a number of filters to PostGraphile connections that you'd expect (less than, greater than, in a list, not in a list, and/or/not, like, contains, etc). It's also possible to implement your own plugins, or to achieve this goal via custom queries.

Bloomsbury

Thanks @Benjie! This is exactly what I did eventually, worked like a charm! – Facility

Answer 2

To extend @Benjie's answer, first install the plugin:

yarn add postgraphile-plugin-connection-filter

Then you can run postgraphile from the console:

postgraphile --append-plugins <plugin path> --connection <dbname>

e.g. on Linux:

postgraphile --append-plugins `pwd`/node_modules/postgraphile-plugin-connection-filter/index.js --connection mydb

or on Windows:

postgraphile --append-plugins /users/bburns/desktop/moveto/site/node_modules/postgraphile-plugin-connection-filter/index.js --connection mydb

Then you can try the new filters using the graphiql endpoint at http://localhost:5000/graphiql. You can run queries like

{
  allProperties(first: 5, filter: {
    appraisedValue: {lessThan: 100000}
  }) {
    nodes {
      propertyId
      appraisedValue
      acres
    }
  }
}

Note: The documentation at https://www.graphile.org/postgraphile/extending/ says you can just give the name of the npm package, but that doesn't seem to work on Windows.

Farinaceous

Thanks for expanding on my answer 👍 You don't need to use the full path if you've installed from npm, you can just use --append-plugins postgraphile-plugin-connection-filter; the full-path stuff is only necessary if you're using a local module (because we use require(...) to load it). – Bloomsbury

Thanks @Benjie, and for your work on postgraphile - it's a great library! – Farinaceous