I'm excited to announce the first version of pgVis a PostgreSQL extension for building simple visualisations dashboards with SQL.

pgVis aims to make it easy to express data visualisations directly from SQL queries. Letting you quickly visualise some data for adhoc reports in psql or to share reporting dashboards in your organisation via pgvis-server . Either way pgVis is designed to be PostgreSQL centric and to fit with your existing database workflows.

The pgVis extension is a simple set of functions which leverages PostgreSQL's excellent JSON support to create a DSL (domain specfic language) for defining visualisations. A Javascript library takes care of rendering the resulting JSON visualisation definitions in any modern web browser. Visualisations can be stored in PostgreSQL and shared via a the optional pgvis-server or statically saved via psql or any other PostgreSQL client.

You can find out more on https://pgvis.org/ .

A Quick Demo 

WITH write_4k AS (
    -- The data analysis query we're going to visualise
    SELECT device, buffered, direct
    FROM benchmarks.ssd_write_thoughput
)
-- Define the visualisation
SELECT vis.visualise(
    'SSD Performance',
    -- Add a bar chart
    vis.bar_chart('SSD Write Throughput (KiB/s)',
        -- Chart dataset, serialising as a JSON array
        vis.dataset( (SELECT jsonb_agg(q.*) FROM write_4k q) ),
        -- Add two data series onto the chart
        vis.series('Buffered IO Throughput', 'device', 'buffered'),
        vis.series('Direct Throughput', 'device', 'direct')
    ),
    -- Hide the dashboard header
    vis.option('header', false)
);

Getting Started Examples

Motivations

The idea behind pgVis came from some data analysis I was doing for a project and a number of situations I've run into in the past.

Working with PostgreSQL normally means you end up playing with data fairly often. In general PostgreSQL is my go to data analysis tool, I'm much more comfortable analysing and manipulating data in SQL than I am in say a spreadsheet. However so many times I find that I want to produce a simple chart or little report to share with people and find myself having to export data into another tool.

I've also worked in plenty of teams where people were comfortable building complex reports in SQL but we lacked a simple tool to display the information to other teams and management. Of course there are other tools for building dashboards and running BI, but often they've not fitted with our workflow.

So a simple tool which could be used for visualisations one off, shared or even embedding, could be of use to other people too.

Direction

This release is very much minimum viable, it demonstrates the concept and ethos of how I want this tool to work and focus upon, while providing enough features to be useful to me and others. This initial release focuses on getting things working end to end and simplicity. The currently supported chart visualisations are simple, but there is a lot more power under the hood by constructing the right options. Likewise there has been very limited focus on supporting dynamic and filterable visualisations at the moment.

There are certainly more features I've got planned and a range of ideas in mind. In the next release I'll be aiming to have more support for dynamically updating and filtering data via the pgVis server.

Feedback

I hope that pgVis interests you enough to give it a quick try, it's pretty easy to get started with .

I'd also be very interested to hear peoples thoughts, especially features that people could see being of use to them.

Feel free to message me on Mastodon: @intrbiz@bergamot.social

I'm planning on accepting feature requests and issues on Gitlab.