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)
What You Will Get
- 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.
Step 1: Install or Deploy CloudQuery (fetch)
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) .
Step 2: Install PostGraphile
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
Step 3: Query and Profit!
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:
Step 4: Create New Views
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 (opens in a new tab) 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!
Step 5: Deploying in production
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.