Visualizing a Postgres database from the inspector

A story of moldable development

Databases are pervasive in software development, but more often than not, exploring databases is perceived to be different than typical development and it happens outside of the development environment. We think that this is not reasonable and that it should change.

A while ago we showed how we can accommodate a database exploration right into the Glamorous Toolkit inspection workflow. This also shows nicely that working with the database is not special in any way.

So what?

Let’s take the case we used previously with a database including cities with populations and life expectancies around the world. Let’s say we want to learn in which continents are the largest cities and how are their life expectancies.

First, we put the data together. We open a connection directly from the Playground and then shoot an SQL query at it.

select 
 city.countrycode,
 city.name as city,
 city.population ,
 country.lifeexpectancy as life,
 country.continent 
  from city left join country
  on city.countrycode=country.code

Opening a connection to the database and evaluating the SQL query gets us a result that looks like a table.

So, there. Now, we have a result that is shown like a table. But, for our purpose, a table is not a great representation. We’d need some visualization. Now, granted there are quite a few ways to visualize this data, but let’s say that we are developers and want to stick to basics. A bar chart would do. So, we open a Playground in the context of the result and here we go:

countries := self recordsAsDictionaries 
 sorted: [ :x :y | (x at: #population) > (y at: #population) ].
data := GtPlotterDataGroup new values: countries.
data := data labelled: [ :each | each at: #city ].
data := data colored: [:each | Color black ].
GtPlotterHorizontalBarChart new
 barLabelWidth: 185;
 barWidthScale: (GtPlotterLinearScale new 
  domainFrom: 0 to: (countries max: [:each| each at: #population]));
 barWidthData: [ :each | each at: #population ];
 barHeightScale: (GtPlotterLinearScale new 
  rangeFrom: 0 to: 20;
  domainFrom: 0 to: (countries max: [:each| each at: #life ]));
 barHeightData: [ :each | each at: #life ];
 amountOfTicks: 2;
 with: data.

The database query result can be manipulated through the same tools as any other object in the system.

Not bad. But, our geography is not that great. So, not to appear ignorant we color the bars distinctly for each continent.

countries := self recordsAsDictionaries 
 sorted: [ :x :y | (x at: #population) > (y at: #population) ].
colors := {'Asia' -> 'cf7b5d' . 'Europe' -> 'ff1c55' . 'North America' -> '3a9371' . 'South America' -> '75eab6' . 'Africa' -> 'bae342' . 'Oceania' -> 'aaaaaa'} asDictionary.
data := GtPlotterDataGroup new values: countries.
data := data labelled: [:each | each at: #city ].
data := data colored: [:each | (colors at: (each at: #continent)) asHexColor alpha: 0.5].
GtPlotterHorizontalBarChart new
 barLabelWidth: 185;
 barWidthScale: (GtPlotterLinearScale new 
  domainFrom: 0 to: (countries max: [:each| each at: #population]));
 barWidthData: [ :each | each at: #population ];
 barHeightScale: (GtPlotterLinearScale new 
  rangeFrom: 0 to: 20;
  domainFrom: 0 to: (countries max: [:each| each at: #life]));
 barHeightData: [:each | each at: #life ];
 amountOfTicks: 2;
 with: data.

Tweaking the visualization in place.

Much better!

The whole process can be measured in minutes. Ok, you might say, but you can use a notebook for this. Or a dedicated data manipulation environment. Indeed, you can. Still, we show that we can bring the data exploration experience to the regular development, rather than having to switch to where the experience is. In our case, it all happens in the same environment, with the same workflow, and relying on the same muscle memory.

This is a typical example of moldable development and reinforces the idea that a one time investment in learning how to think through moldable tools can then be applied over and over in many different contexts.