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:

postgres://user:password@somehost:2345/somedb

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)
$$ LANGUAGE sql STABLE;

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: [
FullNamePlugin,
],
...
}

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

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...';

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

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
USING (
email = current_setting('current_user_email')
);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

Filtering records

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) {
id
email
name
}
}

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.