PostGraphile β€” The Gateway Drug To GraphQL | by Sameer Kumar | Oct, 2023

Image credits

Preparing the database

This is going to be the old simple Postgres setup with no additional steps. Create a schema explicitly if you need access control to certain tables. Make sure to add necessary foreign keys to connect various tables. In the end, all we need is a valid database URL to feed in PostGraphile initialization. The format looks like this:


Creating a custom field

It’s pretty common to need a composite/computed field in your response object. The simplest example is combining the first and last name into full name. There are two ways to achieve it:

  1. Use database-level functions: This method is the fastest and most optimized because code lives right into the database. For this example, you just need to open the database console and run the following:
CREATE OR REPLACE FUNCTION users_fullname(users users) RETURNS text AS
SELECT CONCAT(users.first_name, ' ', users.last_name)

Notice that it follows a certain format in naming. The name of the function will always be tableName_fieldName, allowing PostGraphile to connect it as the resolver for that specific field automatically. You can keep it as a db migration script or whatever fits your use case.

2. Write resolver manually when things are more complex: We need to go this path to cleanly define custom functionalities in the PostGraphile system. For the given example of fullName, we can create it as an extension in a separate file and import it into the main file where we initialize the app.

fullname.js contains this custom resolver to extend what we already have. The name of the file is totally up to you.

const { makeExtendSchemaPlugin, gql } = require("graphile-utils");

// Create a GraphQL schema extension to add the computed full_name field
const FullNamePlugin = makeExtendSchemaPlugin({
typeDefs: gql`
extend type User {
fullName: String
resolvers: {
User: {
fullName: (user, args, context, resolveInfo) => {
return `${user.firstName} ${user.lastName}`;

module.exports = FullNamePlugin;

Now we can import this file into our main file as:

const FullNamePlugin = require("./fullname");

const postgraphileOptions = {
appendPlugins: [

With just this much work, our custom field and resolver are ready to use in our application. And, it works as expected.

Querying custom fields in postgraphile

Adding a description to fields in GraphQL Explorer

It makes sense, and it is hardcore simple. Add a comment to the field using SQL, and it will be available in the explorer.

comment on column tracks.bpm is E'Beats per Minute...';
Auto-generated documentation for the bpm field

Control visibility/modification of fields or tables

Again magic comments are to our rescue. Postgraphile can be instructed to exclude any field or table from GraphQL access by using smart tags such as:

// users table is marked as read only
comment on table users is E'@omit create, update, delete';

// version field from tracks table is completely removed from graphql
comment on column tracks.version is E'@omit read';

Access control to records

Image credits

Since it is so deeply tied to Postgres, it makes full use of Postgres roles and schemas for security. Postgraphile comes with a built-in implementation of JWT and can be used to fine-tune access further. A simple authorization looks like:

GRANT SELECT ON users TO app_users;
GRANT DELETE ON users TO app_admins;

Also, row-level security features can be used to finely restrict people from accessing a certain row in the table.

create policy user_policy_select
on public.users for select to users
email = current_setting('current_user_email')

Filtering records

Image credits

Filtering is supported natively to a certain extent, and it can be enhanced with full relational operator support using a connection filter plugin. Sample syntax:

query getOneUser {
user(id: 1) {

Postgraphile Filtering

Connection Filter Plugin

One good thing to know is that postgraphile behind the scenes optimizes your query conversion from GQL to SQL. The result is one single top-level query, thus eliminating the N+1 problem we discussed earlier.

You can go on with postgraphile documentation to continue your exploration further.

Source link

2023. All Rights Reserved.