How to add a where condition based on context in a Postgraphile plugin?

Question

I'm evaluating Postgraphile for a small experiment and was trying to add an implicit where condition to the generated SQL query for all the query operations based on the resolver context (for example, something like, where(user_id = context.user_id).

I tried searching docs for a way to access queryBuilder in a resolver so I can do something like

queryBuilder.where(sql.query`user_id = ${sql.value(context.user_id)}`);

but I did not find a way after searching for a lot of time. I'm sure there's a way - I just couldn't find it. Can anyone just point me in the right direction or just paste a link to the documentation? Thanks!

I already know that we can use RLS policy to add this condition but I have a complex where condition based on business logic that'd I'd like to write in Node.js

Answer 1

If you want your custom WHERE clause generated purely in javascript without utilizing RLS, the cleanest mechanism (using 4.12.9) would be creating a graphile plugin that utilizes a field hook. Inside the context argument of your hook function, you will find addArgDataGenerator which will allow you to append your WHERE clause to the relevant fields with queryBuilder. Additionally, addArgDataGenerator has the added benefit of allowing you to get the argument values and other resolve information for your field if you need to utilize that information as well.

import type { Plugin } from "graphile-build";
import type { QueryBuilder, sql as sqlType } from "graphile-build-pg";
/**
 * Adds a custom where condition to fields meeting user defined criteria using a field hook
 */

const customWherePlugin: Plugin = (builder) => {
  builder.hook(
    "GraphQLObjectType:fields:field",
    (field, build, context) => {
      const { pgSql } = build;
      const sql = pgSql as typeof sqlType;
      const {
        scope: {
          isPgFieldConnection,
          isPgFieldSimpleCollection,
          pgFieldIntrospection: procOrTable,
          pgFieldIntrospectionTable: tableIfProc,
        },
        addArgDataGenerator,
      } = context;

      // use the scope to eliminate fields you aren't interested in adding custom logic to.
      // this particular example uses any connection type referencing the `posts` table.
      // The scope object gives you access to nearly all metadata from the schema and the database catalog itself,
      // so you can customize this to your heart's desire.
      const table = tableIfProc || procOrTable;
      if (
        (!isPgFieldConnection && !isPgFieldSimpleCollection) ||
        !table ||
        table.kind !== "class" ||
        table.namespaceName !== "my_schema" ||
        table.name !== "posts"
      ) {
        return field;
      }

      // this appends an additional where clause to the query builder when the field is specified in a gql query
      addArgDataGenerator(() => {
        return {
          pgQuery: (queryBuilder: QueryBuilder) => {
            const { userId } = queryBuilder.context;
            const tableAlias = queryBuilder.getTableAlias();
            queryBuilder.where(
              sql.fragment`${tableAlias}.user_id = ${sql.literal(userId)}`
            );
          },
        };
      });
      return field;
    },
    [],
    ["PgConnectionArgOrderBy"],
    []
  );
};

export default customWherePlugin;