About

Postgrest-Table is an in-browser data-exploration tool for PostgREST.

Key Features

  • Minimal setup: You only need to setup PostgREST, which you may already do if you are a customer of some hosted Postgres service.
  • No dependencies required: Everything runs in-browser, configurations are stored in your browser, eliminating the need for server-side configuration.
  • Auto-refresh feature: Watch changes occur in front of your eyes.
  • AJAX capabilities: Performs AJAX calls to save time on curl-fu.
  • Customizable knobs: Offers user-tweakable controls for fine-grained customization.
  • Graphs and analytics: Supports graphs to visualize data distributions and relationships, with support for inspecting items and drilling-down.
  • Request a feature: You can champion a feature you miss dearly: contact me.

Use Cases

  • Development: Utilize Postgrest-Table daily for exploratory data analysis and troubleshooting on your local Postgres instance.
  • Troubleshooting: Postgrest-Table let’s you quickly find and compare data-points.
  • Dashboards for you: Configure dashboards for one-off rollouts or daily scenarios with ease.
  • Dashboards for curated datasets: If your product is an access to some curated dataset, you can use Postgrest-Table offer a discoverable preview in no time and no effort.
  • Unlock Enhanced User Experience in your product: Are you looking to revolutionize the way your users interact with your application? By embedding Postgrest-Table in your product (contact-me), you can provide users with a personalized analytics dashboard tailored to their specific needs.

Concepts

Postgrest-Table works by issuing browser-requests to running PostgREST instances. PostgREST has an elaborate query-params scheme to filter, order, limit the returned subset of resources, or embed other resources. Oftentimes, when developing or when troubleshooting you need to query different datasets or the same datasets but with different parameters. We call these perspectives Postgrest-Table lets you edit multiple perspectives simultaneously. The dataset in the perspective updates as you edit your query, so you can iteratively refine your dataset. Datasets need not be large to be difficult to comprehend. Thus Postgrest-Table allows you to explore datasets with some summarizing charts and table subsets, which we call projections. Finally Postgrest-Table allows you to organize all these projections, their input in dashboards for quick access to different use-cases. A dedicated expression language (we call it Minilang) allows extra interactivity by supporting dynamic inputs and even knobs for minimal parametric dashboards.

Configuration

At the start, you define endpoints in the corresponding tab. An endpoint corresponds to PostgREST base endpoints (for instance if you configured PostgREST to serve and show some blog post contents at https://example.com/api/prest/posts the endpoint is https://example.com/api/prest/ or https://example.com/api/prest).

A endpoint may need headers, namely authorization JWTs for PostgREST if the anon-role is not what you want. We recommend you setup a read-only role as Postgrest Table only ever reads data. Sometimes an apikey to identify your application in hosted PostgREST services.

These configurations are saved in local-storage and never leave your computer except for accessing PostgREST itself.

Postgrest-Table then lists accessible resources as inputs (for example “posts”, “categories”, and what have you).

You can then create perspectives for resources, which correspond to a particular subset of the exposed resource.

Postgrest-Table offers a variety of mechanism to build such URL-defined queries.

  • by filtering on a value in a table
  • by filtering in a “filter” header
  • by writing a Minilang expression (advanced)

Projections

Projections answer the need to look at a given dataset with various questions in mind. As you troubleshoot something, you need to first locate the data you are interested by drilling-down from a large dataset.

Pie-Charts

Use them for small number of categories.

Pie Charts are most useful for:

  • finding whether there is a single or multiple values in a given column
  • coarse-grain drilling-down by filtering out lots of data

Bar-Charts

Use them for small to moderate categories. Bar-Charts support breakdowns.

Bar Charts are most useful for:

  • understanding what are the most represented values
  • coarse-grain drilling-down by filtering out lots of data
  • fine-grain drill-down with breakdowns

ECDF-Charts

Use them for somewhat scientific understanding of data distributions in some numerical axis. ECDF-Charts support breakdowns to compare two or more ensemble of data.

ECDT charts are most useful for:

  • spotting uniformity or heterogeneity of data
  • spotting outliers and then filtering them-out or zooming on them
  • comparing classes of datapoints (e.g., subscribed vs. freemium users)

Scatter-Charts

Use them to see how dimensions in your data influence each other. Scatter-Charts support breakdowns.

Scatter charts are most useful for:

  • spot at trends over time (e.g., version vs. time during a rollout)
  • fine-grained drill-down by zooming on a very-narrow subset of items

Tables and expressions

Sometimes you want to display. Tables allow you to pick and re-organize some columns. Tables also allow you to generate new columns based on existing values, these expression are written in Minilang, a simplistic expression language.

Minilang allows you to create values with formatting information and visual clues:

  • links to pages
  • images based on a (possibly-concatenated from other fields) URL
  • color-dot and progress bars
  • AJAX-call to perform browser requests (e.g., to your local dev server, to enqueue some task etc.)

Tables provide a whole-new dimension of interactivity, it’s not no-code, but low-code for a number of cases where scripting a procedure to run over many data rows is cumbersome.

Knobs (user inputs)

In many cases, a dashboard can benefit from parametrization - specifically, focusing on a particular user, entity, or timeframe. Such a parametrization exists in Postgrest-Table under the monicker knob. One particularly useful application of knobs is to tie multiple perspectives together under a single parameter, making it easier to manage and analyze complex data sets.

Minilang

Minilang is a small expression language (like you would find in spreadsheet) to go further than simply looking at raw data with CTRL-F. Many Postgrest-Table fields accept either a PostgREST column name or an expression. For instance, when configuring a BarChart, sometimes you want to display or breakdown something which is more complicated than a combination of columns as given by PostgREST (e.g., distribution of number of comments in a blog-posts, color differently points with the most recent version).

  • accessing columns $col."some_column_name"
  • accessing input knobs $inputs."some_column_name"
  • making some color dot colordot("blue")
    • supports CSS colors like “rgba(50,50,50,0.32)”
  • making some progressbar progressbar(42)
  • making some link to a page link("https://example.com/")
  • concatenating strings: concat("https://example.com/api/users/", $col."user_id")
  • converting values to strings: stringify(42.3)
  • converting numbers: round(42.3), ceiling(42.3)
  • cardinality of arrays: card([1,2,3])
  • accessing json-object key-values: object-lookup($col."json_blob", "name", "fallback-value-when-name-is-missing")
  • accessing json-arrays index-values: array-lookup($col."json_ary", 3, "fallback-value-when-item-3-is-missing")
  • branching on some condition
    • you’ll need a predicate pred(:eq, "value-1", "value-2") (reduces to a boolean)
      • predicates are eq, gt, gte, lt, lte, neq
    • you’ll need a multi-branch branch("default-value", ...some-predicate-or-boolean-column..., value1, ...some-other-predicate..., value2)
      • alternates between a test-boolean and a value, the default-value
  • ajax-calls: ajax({"method":"POST", "url": concat("https://example.com/api/",..some-built-url-chunks..)})
  • logical conjunction: conj(true,true,false,...)
  • logical disjunction: disj(true,true,...)
  • sum of numbers: sum($col."age",100,...)
  • product of numbers: sum($col."qty",$col."price")
  • logical disjunction: disj(true,true,...)
  • accessing columns $col."some_column_name"
  • function definition/calling:
    • fun(...arglist..., ..expression..)
    • call(..some function..., ...arglist...)
    • e.g., call(fun(["a"],$arg.a),[42])
  • accessing the whole dataset: $json-src
  • accessing the row number: $row-number

And more (to be documented).

How-To

We mainly cover advanced topics in this How-To.

Editing a projection with an input

For a good dashboard experience, you want to bind the dataset of many perspectives to an input (for example, to list the sales of products in a user-selected category).

Add a Minilang predicate expression to each perspective:

pred(:eq, $col."category_name", $input."selected-category")

Credits

  • by Lucas DiCioccio.
  • written in Purescript Halogen
  • charts powered by by Apache ECharts
  • style powered by BulmaCSS