🟩🚀 Draxlr SQL query/dashboard builder on YugabyteDB managed

This tweet by Jitendra Nirnejak caught my attention as it was mentioning YugabyteDB:

Let's test it. You can do the same.

YugabyteDB free database service

I start by creating a free YugabyteDB managed database on https://cloud.yugabyte.com this is really easy: Create Cluster -> Sandbox -> Cloud Provider -> Region -> Credentials -> Create Cluster

While the cluster is creating (this takes a few minutes because it runs on a dedicated cloud instance with your choice of cloud provider (AWS or GCP currently) and region (all regions to be closer to your application). If you don't have a preference, choose AWS us-east-1 and you will be close to the Draxlr servers.

Draxlr register

Setting a Draxlr account is also easy: https://app.draxlr.com/register/ you can register with a Google Account (like you can for YugabyteDB). They ask How did you hear about us? (my name is Franck Pachot 😂) and you can choose the Manual Setup. You can usePostgreSQL because YugabyteDB is compatible but you want a nice 🚀 logo, right?

In Connect to Data Source, you will have to provide your credentials. Before going to the YugabyteDB portal, first take a note of the Draxlr public IP ("Our IP")

YugabyteDB connection

You need to add this IP to the list of allowed IP to connect to your cluster. On the YugabyteDB portal, once the cluster is created, click on "Add IP Allow List"

This is where you enter the IP from the previous step so that an inbound rule is added for it:

Now you can click on the "Connect" button, "Connect to your Application", "YSQL" (the PostgreSQL compatible API), "Parameter" and get the host name

Draxlr connect info

You got the hostname from the previous step, the port is 5433, the user is admin, the password is the one you entered or generated when creating the database. If you don't remember, look in your Downloads as it was mandatory to download it. The database name is yugabyte. Of course, you can create other PostgreSQL database and users and you can use the Cloud Shell for that in the YugabyteDB portal.

For the moment, you have no tables. We will create some from the YugabyteDB tutorial

YugabyteDB tutorial

In the YugabyteDB portal "Connect" you can "Launch Cloud Shell". It starts a container where you will be connected on ysqlsh the equivalent of psql. It asks for your admin password (remember, it may be in your Downloads) that you can paste (mouse right click). You can run any SQL command here, but on the left, you can also open the tutorial

I suggest to do the Step 1 and Step 2 that will create two tables emp and dept and insert sample data. And then you can query them from Draxlr

Draxlr query

If you refresh the screen which were showing "no table found" you will see the two tables and Start Exploring

By simply clicking on emp I see the data

By choosing job on Group By the result is reduced to the list of jobs:

By choosing sal and Average on Summarize I can see the average salary per job:

I can add a descending Sort on this salary average and display it as a graph:

Clicking on Join directly proposes an inner join to dept and I can then Group By the location of the department:

This has generated the following query and, of course, you can edit the SQL directly:

select
  "dept"."loc",
  avg("emp"."sal") as "emp__sal__avg"
from
  "public"."emp"
  inner join "public"."dept" on "emp"."deptno" = "dept"."deptno"
group by
  "dept"."loc"
order by
  "emp__sal__avg" desc

All queries can be put on a dashboard to display text and graphs, and share