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.

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.

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.