tutorial

How to Query CloudQuery with PostGraphile

Yevgeny Pats • August 15, 2021

In this how-to guide, we will walk you through how to set up CloudQuery to build your cloud asset inventory in PostgreSQL and build a GraphQL API query layer with PostGraphile on top of it. this can be used to build different use cases on from search to security, cost and infrastructure automation.

General Architecture #

ETL (Extract-Transform-Load) ingestion layer: CloudQuery
Datastore: PostgreSQL
API Access Layer: PostGraphile and GraphiQL

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.

Walkthrough #

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 .

Step 2: Install PostGraphile #

For full details, check out the PostGraphile 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 on how to create a unified AWS resource view (or GCP View). And just like that you can now query and search all your resources by arn, tags, name with GraphQL!

Step 5: Deploying in production #

If you want to expose PostGraphile publicly please see PostGraphile Security or expose it privately and use either a bastion host or something like Tailscale on Kubernetes together with our helm charts.

Summary #

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 as the API layer to expose the data for your internal team/users or any other downstream processing in the most convenient/preferred way.

On this page