Introduction: in this article based on Turning PostgreSQL into GraphQL Lessons from the Field, I share the experience of integrating PostgreSQL with GraphQL using PostGraphile and how this can help real custom software projects.
I am Sivvie and I documented this experiment while working in a real environment. The goal was to check whether it was possible to offer end users a flexible GraphQL interface over a relational database without sacrificing security or performance.
What is PostgreSQL: PostgreSQL is an open-source relational database system, known for its reliability, SQL standard compliance, and extensibility. It can run locally, in containers, or in the cloud on services such as AWS or Azure.
What is PostGraphile: PostGraphile is middleware that automatically generates a GraphQL API from an existing PostgreSQL database. It supports queries, mutations, and subscriptions and can be extended with plugins for custom logic. It is an interesting option compared to alternatives like Prisma or Hasura when looking to work directly on PostgreSQL.
Requirements and specification: in this project, we needed to allow users to create tables via GraphQL mutations, freely query fields, view their own schema, receive notifications by subscription when rows were inserted with specific conditions, and be able to update and delete only their own tables. Additionally, the system had to integrate with cloud services and secure APIs.
Thought process: we created an event database in a PostgreSQL cluster deployed on EKS, set up a PostGraphile server with JavaScript, and verified that in the first test we could already query and mutate data from GraphQL. It was a clear sign that the approach was viable.
Reflecting database changes: the main challenge was that GraphQL schemas are usually static, while the database had to be dynamic. PostGraphile has an option to watch for changes in PostgreSQL and regenerate the schema automatically. Enabling that functionality installs triggers that notify the server to rebuild the schema according to actual changes in the database.
Cloud deployment: we wrapped the PostGraphile application in a Docker image and deployed it on EKS. The container exposes the PostGraphile server within the AWS ecosystem, making it easy to integrate with other managed services.
Secure client exposure: since PostGraphile is not an API designed to expose public endpoints directly, we looked for a secure interface layer. On AWS, we used AppSync as a managed GraphQL gateway, which allowed us to control access and combine resolvers with custom logic without exposing the service internally directly to the client.
Integration with AppSync: AppSync accepts lambda functions as resolvers. We designed three lambda functions that acted as a bridge between AppSync and PostGraphile: converter to adapt the client request to the format PostGraphile understands, responder to transform the response before returning it, and updater to manage dynamic changes in the schema and orchestrate the update in AppSync.
Type management and compatibility: some type differences between schemas generated by PostGraphile and the format expected by AppSync required normalizations. Creating a PostGraphile plugin to directly generate a compatible schema was considered, but it was also possible to transform requests and responses in the resolvers. We chose a solution that balances maintenance and design cleanliness.
Schema update pipeline: every time PostGraphile detected a schema change in the database, the server regenerated the schema and uploaded it to an S3 bucket. A watcher on S3 triggered the updater function that updated the schema in AppSync. This dual pipeline allowed AppSync to have the updated schema within seconds.
Support for queries, mutations, and subscriptions: the solution supports queries, mutations, and subscriptions while respecting the limitations inherent to AppSync and PostGraphile. In tests with the web application, responses and schema changes were reflected with low latency and good user experience.
Final design: the overall flow included PostgreSQL on EKS, PostGraphile in a Docker pod, schema synchronization to S3, and public exposure through AppSync with lambda resolvers acting as converter, responder, and updater. This allowed us to offer a dynamic GraphQL API over a relational database with control and auditing.
Conclusion: it was a challenging and highly educational project. We learned to combine the best of PostgreSQL and GraphQL with cloud infrastructures to offer flexible and secure APIs. The solution is neither the only possible nor definitive one, but it demonstrates that it is viable to build dynamic GraphQL services over relational databases for real use cases.
About Q2BSTUDIO: at Q2BSTUDIO, we are a software development company that offers custom applications and custom software designed to solve specific business challenges. We are specialists in artificial intelligence, cybersecurity, and AWS and Azure cloud services. Our capabilities include business intelligence services, implementation of artificial intelligence solutions for companies, development of AI agents, and dashboards with Power BI to improve decision-making.
If your organization needs secure and scalable custom applications or wants to incorporate artificial intelligence and AI for businesses into its processes, at Q2BSTUDIO we design integrated solutions that combine best practices in cybersecurity, cloud deployments on AWS or Azure, and advanced analytics with business intelligence services such as Power BI.
Keywords for positioning: custom applications, custom software, artificial intelligence, cybersecurity, AWS and Azure cloud services, business intelligence services, AI for businesses, AI agents, Power BI.
If you are interested in replicating this architecture or exploring alternatives such as PostGraphile plugins, integrations with Hasura or Prisma, or designing a custom solution with Q2BSTUDIO, we can help you evaluate options, design the architecture, and execute the project through to production.
Closing: thank you for reading this practical summary. I hope these lessons are useful for turning PostgreSQL into a dynamic and secure GraphQL API in your custom software projects.
