How to Query CloudQuery with PostGraphile
In this how-to guide, we will walk you through how to set up CloudQuery (opens in a new tab) to build your cloud asset inventory in PostgreSQL and build a GraphQL API query layer with PostGraphile (opens in a new tab) on top of it. this can be used to build different use cases on from search to security, cost and infrastructure automation.
- ETL (Extract-Transform-Load) ingestion layer: CloudQuery (opens in a new tab)
- Datastore: PostgreSQL
- API Access Layer: PostGraphile (opens in a new tab) and GraphiQL (opens in a new tab)
- Raw SQL access to all your cloud asset inventory to create views or explore any questions or connections between resources.
- Multi-Cloud Asset Inventory: Ingest configuration from all your clouds to a single datastore with a unified structure.
- GraphQL Endpoint to access and query all your cloud configurations.
If it’s your first time using CloudQuery we suggest you first run it locally to get familiar with the tool. Take a look at our quickstart guide.
If you are already familiar with CloudQuery, take a look at how to deploy it to AWS on RDS Aurora and EKS at github.com/cloudquery/terraform-aws-cloudquery (opens in a new tab) .
For full details, check out the PostGraphile (opens in a new tab) docs. If you are running locally will need Node.js and you can install PostGraphile globally via
npm i -g postgraphile or (
brew install PostGraphile)
To run PostGraphile locally all you need to do is the following (adjust the Postgres URL accordingly):
postgraphile -c "postgres://postgres:pass@localhost:5432/postgres" --enhance-graphiql --skip-plugins graphile-build:NodePlugin --simple-collections only -p 6060
That’s it! The output of a successful run is presented below:
PostGraphile v4.12.9 server listening on port 6060 🚀
‣ GraphQL API: http://localhost:6060/graphql
‣ GraphiQL GUI/IDE: http://localhost:6060/graphiql
‣ Postgres connection: postgres://postgres:[SECRET]@localhost/postgres
‣ Postgres schema(s): public
‣ Documentation: https://graphile.org/postgraphile/introduction/
‣ Node.js version: v18.3.0 on darwin arm64
‣ Join PostHog in supporting PostGraphile development: https://graphile.org/sponsor/
* * *
Open the browser with the
http://localhost:6060/graphiql endpoint to see the GraphiQL UI where you can compose any query you want interactively:
By default PostGraphile exposes all tables and relationships of the existing tables but let’s say you want to create a new view. All you need to do is to create a new view and PostGraphile will automatically generate the model for that. For example, check out this blog on how to create a unified AWS resource view (opens in a new tab) (or GCP View (opens in a new tab)). And just like that you can now query and search all your resources by
name with GraphQL!
If you want to expose PostGraphile publicly please see PostGraphile (opens in a new tab) Security or expose it privately and use either a bastion host or something like Tailscale on Kubernetes (opens in a new tab) together with our helm charts. (opens in a new tab)
In this post we showed you how to build an open-source cloud asset inventory with CloudQuery as the ETL (Extract-Transform-Load) / data-ingestion layer and PostGraphile (opens in a new tab) as the API layer to expose the data for your internal team/users or any other downstream processing in the most convenient/preferred way.