The ultimate Data Access Layer with GraphQL Part 1 : Postgraphile

The problem

You probably know by now that I hate writing boilerplate data access code. When you have to write a data access layer, I used to think that the code should be generated from the database structure and could be regenerated on demand, otherwise you’re losing your time. That was before…

The previous solutions

In the early 2010s, in c#/.NET I’ve achieved this with partial classes and t4 templates. With an EDMX File I was able to generate a WCF API in 2012 and even a REST API in 2016. Not a single line of code written by hand. Only templates.

If you’re using PostgreSQL as your relational database, you’re in luck, believe me, because the future is now.

The ultimate solution

GraphQL…

The new way to go is GraphQL. If you’re new to GraphQL please take some time to read the article to get an idea of the problems that are solved.

In my previous APIs the main problems were overfetching and security. There’s still problems in those areas with GraphQL but you could mitigate them using query whitelistening and RSL.

…and Postgraphile

Enter Postgraphile.

With the help of this amazing project, you do not need to generate/use code with templates anymore. You’ve got a GraphQL API instantly inferred from your database schema.

Furthermore, when it will be optimised for AWS Lambda, you’ll be able to run it serverless. Awesome.

It also uses Row Level Security introduced in PostgreSQL 9.5 (RLS) with the role claim found in the JWT coming in and switching automatically to that role. You connect to the database with an application user to benefit from connection pooling but switch to the user in term of security. This is just fantastic in those GPDR times.

I absolutely love PostgreSQL and now Postgraphile

Stay tune for real case examples that are coming soon…