This content originally appeared on DEV Community and was authored by Sivvie Lim
Introduction
Hi I am Sivvie. This blog is mainly for me to jot down what did I learn during the journey of Software Engineering. Please pardon my English grammar throughout the blog.
In this post, I’ll share what I discovered while integrating PostGraphile with a PostgreSQL database. I’ll cover why I chose these tools, the requirements I had, the challenges I faced, and how I solved them.
What is PostgreSQL
PostgreSQL is an open-source, relational database system that stores data in tables form. It’s known for its reliability, strong feature set, and SQL standards compliance.
You can run PostgreSQL locally, on a cloud provider like Heroku, or inside a container. In my case, I deployed it on AWS using Kubernetes (EKS).
If you’ve never tried PostgreSQL before, the official site is a great starting point: PostgreSQL: The world’s most advanced open source database.
What is PostGraphile
While PostgreSQL uses SQL to query and modify data, I wanted a more flexible and discoverable query interface for end-users. This is where GraphQL comes in — it lets clients explore available fields through a schema and retrieve exactly the data they need in a JSON-like structure like this:
{
user_record {
name
isSubscriber
profile {
age
}
}
}
However, if your data is already in PostgreSQL, how can you use GraphQL on it as it does not support it natively?
Well that is where the middleware coming in! Middleware that converts relational database into GraphQL-compliant schema and let the user able to query or operate using GraphQL.
PostGraphile is an open-source middleware that automatically generates a GraphQL API from an existing PostgreSQL database. It watches your schema for changes, supports queries, mutations, and subscriptions, and can be extended with plugins for custom logic.
There are other options like Prisma or Hasura, but I chose PostGraphile because:
- It works directly with PostgreSQL without requiring extra layers
- It’s actively maintained and well-documented
- It’s highly extensible — perfect for my need to customize schema behavior
You can check it out here: Graphile | Powerful, Extensible and Performant GraphQL APIs.
So what did I learn?
Specification
First and foremost, I need to know the specification of the request and feature. In my case, the specification indicates that the user should be able to:
- Adding new table whenever they want via GraphQL mutation
- Query any fields they want via GraphQL query
- Get an exposure of their own schema so they can know which field is available for query
- Only see their table and not other user's table
- Get notification on specific field or condition met when new row of data is inserted via GraphQL subscription
- Updates table field whenever they want via GraphQL mutation to their own table only
- Delete table whenever they want via GraphQL mutation to their own table only
These are the first hand specification and since what I am doing is essentially a spike to see if I am able to achieve these using GraphQL with our current database, I decide to give PostGraphile a go.
Thought Process
A new database called event is created inside the EKS deployed PostgreSQL instance, next I spin up a quick PostGraphile server application with vanilla javascript and connects it to my database.
To my wonderful delight, it works pretty much the first try and I can use GraphQL to query/mutate my data in the PostgreSQL DB.
That is a good sign that it all could work. Next I am listing one by one the pain point that need to be solved and try to solve them sequentially.
Reflect Changes In Database
The first pain point is reflect the changes in the PostgreSQL database. And this is because naturally, GraphQL schema is immutable. Once the schema is built, no changes can be done.
This can be hard for those who wants their database table to be flexible, eg: Adding table or update them freely. So in PostGraphile, there is a tremendously helpful feature which is watchPg:true properties. You can refer the details here, just to mention that here is the Library usage.
Once that properties is being enabled, it will install a trigger fixture into the PostgreDB and using pg_notify fixture to tell the PostGraphile server to rebuilt the schema based on the latest database changes. And it works really well!
Host it on AWS
Our product ecosystem lives on AWS as a docker container by EKS. All our APIs are hosted there and there should be no exception to this PostGraphile server as well. So the next step I do is to wrap the Javascript application into a docker image and hosted it on EKS by Kubernetes. And it did worked well. Now we have a EKS pod that is exposing the PostGraphile server.
Expose to Client
This is a headache step, since PostGraphile server is technically not an API, we could not expose certain endpoint for our client to call and connect. And so we need to find the other way to expose this service to the client. And by using AWS service currently, I sought to AWS ecosystem to find an interface service like API Gateway, which I found it, it is called AppSync.
AppSync is AWS answer to GraphQL API where it can directly connect to a DynamoDB and convert it to be GraphQL-compatible. However that is not what we want, we do not host our database in DynamoDB anyway.
However we can make use of AppSync as an interface that exposed to a client safely. So that is our next step - to integrate between PostGraphile server to AWS AppSync.
AWS AppSync Integration
The goal here is to integrate the PostGraphile application with the AppSync.
Apparently AWS AppSync accept lambda function as part of the Resolver, so Resolver here refers to a block of code that does the logic of bridging between the GraphQL Schema in the AppSync, and any backend that we have, so in our case, the backend will be the PostGraphile application. It can be any other backend or even a direct database.
Another pain point of this is, PostGraphile-generated-schema is a little bit different than AppSync formatted schema, for example there is one data type called AWSDateTime that of course will not get generated by the PostGraphile.
There are two ways to resolve this, for one being develop a plugin for the PostGraphile system and let it generate the AppSync style schema right off the bat. I will not go into details of developing a plugin but here is the official documentation of it: PostGraphile | GraphQL Schema Plugins
Second option is to use the Resolver to convert the data type before it reaches the AppSync. Both should work with some effort, but in the end I went with Option One as it is cleaner in terms of design.
Next, it is time to handle the Resolvers, I have three lambda function that serve as a Resolver to AppSync, one is called converter , another is called updater and the last one is called responder.
As their name suggested, converter is used when a client make a request to AppSync and AppSync will direct the request to the converter, then the converter will have responsibility to 'convert' the request to PostGraphile-understand format, because just like the schema, they have some differences on the request and there are more transformation that I would like to do before the actual request get sent in.
Then once the PostGraphile is ready to response, their response will go through responder as it will further transform the response if needed. Currently we just return as it is so this responder is a placeholder function. Finally the client will be able to get their results in an instance.
Now this is a complete flow, right? So what does updater used for? As I previously explained, GraphQL Schema was supposed to be immutable, however due to the request, the Schema had somewhat needed to be dynamic.
So we have another workflow handling that dynamic needs. As previously we already setup a watcher to watch over database change, it is time to make use of that triggers. Once the database had any changes on the table itself (not data, any row changes will not affect, only create/update/delete table), the PostGraphile App will know, then it will re-generate the up-to-date schema, and upload to AWS S3 storage container. This is what we used but generally any other storage option will work.
The reason we put it in the AWS S3 storage container has two, for one it serves as automatically backup as S3 had versioning feature, another one is for the triggers. Our updater have a watcher over the S3 changes, once the file had changes, the updater will start working by updating the schema directly onto the AppSync schema.
And the performance of it is really good, not more than a few seconds, AppSync will already have the latest schema in and ready for queried.
This 2 pipelines of workflow are worked in Queries, Mutation as well as Subscription, any limitation shall follows the AppSync limitation and PostGraphile limitation. I have connected this pipelines to our web application and it runs pretty fast to show the result or reflect changes on the table.
Final Design
I have drew a simple flow diagram with Excalidraw, but in the end this sub-system looks like this:
Conclusion
This is a demanding but also very fun project, almost every aspects of this project is new to me and so I am glad that I am able to learn so much from this project.
I documented all these so that I will not forget in the future, but also I hope that it can be even a tiny little help to the community. This design is not mature of course, maybe not even on par with the production grade level, but it is what I have right now, and improvements are always available.
Ta-ta for now!
This content originally appeared on DEV Community and was authored by Sivvie Lim
Sivvie Lim | Sciencx (2025-08-10T11:11:33+00:00) Turning PostgreSQL into GraphQL: Lessons from the Field. Retrieved from https://www.scien.cx/2025/08/10/turning-postgresql-into-graphql-lessons-from-the-field/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.