postgrest-table

because PostgREST is awesome

Everything happens on your browser

PostgREST not only exposes data over REST entities, it also provides an OpenAPI description.

Postgrest-table lets your browser load the OpenAPI description of a PostgREST service. Plus, this page let your browser understands further the selector syntax that PostgREST expects.

All interactions between this page and your PosgtREST instances are done via your browser. Hence, this service does not intercept any data.

Three families of columns

  1. PostgREST exposes mainentities from PostgreSQL tables and views.

  2. PostgREST provides some support for embedding via auto-detected foreign-keys.

  3. Finally, Postgrest-Table (this page) allows you to compute extra columns directly on your browser with a limited expression language.

Each family of column comes with its own set of limitations:

Only main and embedded data are part of CSV exports as PostgREST is in charge of formatting the CSV.

Computed data can only reference main columns or previously-defined computed columns (a naive way to avoid circular definitions).

Computed columns

We provide a limited language. Heavier queries should be turned into SQL views and exposed via PostgREST.

Primitives

"hello"
42.0
["a", "b", "c"]
{"name":"just", "lastname":"leblanc"}

References

$row-number
$field:"firstname"
$computed-field:"fullname"

Json Extraction

object-lookup($field:"some-json-object", "key", "default-value")
array-lookup($field:"some-json-array", 0, "default-value")

Strings and html-generation

concat($field:"firstname", " ", $field:"lastname")
concat("hello ", $computed-field:"fullname")
stringify(1.42)
stringify(true)
image(concat("https://somesite/", $field:"icon", ".png")
link(concat("https://somesite/", $field:"uuid", "/", stringify(round($field:"id")))
progressbar(42.0)
colordot($computed-field:"some_css_color")

Computation

negate(1.2)
inverse(100.0)
product(1.2, 3.0, $field:"factor")
sum(1.2, product(1.2, 3.0))
round(1.2)
ceiling(1.2)

Logic and branching

conj(true, true)
disj(false, true)
predicate(:eq, 1.0, 1.3)
predicate(:gte, 1.0, 1.3)
predicate(:gt, 1.0, 1.3)
predicate(:lt, 1.0, 1.3)
predicate(:lte, 1.0, 1.3)
branch("some-fallback", predicate(:eq, $field:"xyz", 1.3), "xyz-is-1.3")

Charts

We leverage Vega-Lite to generate charts. Features implementation and the styling are still rough.

A chart can display computed columns, however if you end-up doing that often you should consider working on your SQL views.

Bookmarklets

This page will auto-load a PostgREST spec if provided a ?postgrest=$url query param in the URL.

As a result, you can create a smart-booklet to load Postgrest-Table in your browser by bookmarking the following code: 

javascript:location.href='https://postgrest-table.netlify.app?postgrest='+document.location.href;

Troubleshooting

Netlify, which hosts this page, enables HTTPS. Hence, if you point to a plain HTTP PostgREST instance, your browser will detect an insecure connection attempt from. You can bypass this limitation by deactivating mixed-content blocking (often clicking a shield/lock icon after a failed connection attempt). Of course, it is better to secure your PostgREST endpoints.

Roadmap

Albeit best effort, you can poke around and observe when we provide the following:

  • more primitives for computed computations
  • more charting-interactions
  • more FKs-interactions
  • more and polished filters for more types (dates, jsonb, arrays, fts)