🟩🚀 Draxlr SQL query/dashboard builder on YugabyteDB managed
Tags: yugabytedb, distributed, sql, database
Published: 2022-07-25 16:08:19

This tweet by Jitendra Nirnejak caught my attention as it was mentioning YugabyteDB:
{% embed https://twitter.com/jeetnirnejak/status/1549708961200631808?s=20&t=npdEvEyoLtjf6GXy_-DC5A %}

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**
![cloud](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i9xqrdeyffrgycdmj9x9.png)

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")

![IP](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kbn1a70akkgnjhtpv0gt.png)
 

## 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"

![Allow](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/33h1hxchwy2ln8z7buzu.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/33dbij7isqkr9lmdwc2h.png)
 
Now you can click on the "Connect" button, "Connect to your Application", "YSQL" (the PostgreSQL compatible API), "Parameter" and get the host name

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kgmmtpx2zk678lpguhw8.png)
 
## 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xegrqublhkib5yuzbbpq.png)

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

## YugabyteDB tutorial


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5i9bjymtb0u4hjfxj3j9.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t61pa63bkm7ysc6sdszg.png)
 
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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o38dkjlq9tjdf1riuiwp.png)


By simply clicking on `emp` I see the data
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kxtdt5pa6fmhpsy985f3.png)


By choosing `job` on Group By the result is reduced to the list of jobs:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rbuuj9kbyfuglzvj6qmj.png)
 
By choosing `sal` and Average on Summarize I can see the average salary per job:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/06couyzya6fa0x9mbmu7.png)

I can add a descending Sort on this salary average and display it as a graph:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lejg5poott7d8kdoi9rz.png)
 
Clicking on Join directly proposes an inner join to `dept` and I can then Group By the location of the department:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q14h9ibrm6jhtxgkgi0p.png)

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

```sql
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 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kcsgfswzweb64pvotvdc.png)