search

ontodev / sprocket

BSD 3-Clause "New" or "Revised" License
0 stars 1 forks source link

Prototype PostgREST backend #11

Closed jamesaoverton closed 2 years ago

jamesaoverton commented 3 years ago

It would be useful if we could point Sprocket at a PostgREST endpoint such as https://www.cmi-pb.org/api/v2 (Swagger here https://www.cmi-pb.org/docs/api/) and then explore the data with the HTML table.

I would like our query strings to match PostgREST (because I like their design), so ideally we could just pass the query string along to PostgREST, receive JSON or CSV from PostgREST, and then display it as HTML.

One problem I see is that a normal PostgREST like https://www.cmi-pb.org/api/v2/cell_type?select=cell_type_name will not return all the column names, which limits our HTML UI.

One option is for Sprocket to read the Swagger/OpenAPI description of the endpoint.

jamesaoverton commented 2 years ago

Sorry, my issue was not as clear as I thought. This issue is about Sprocket sitting between a user and a PostgREST endpoint and providing HTML views, not about bulk fetching from PostgREST. To implement this feature, Sprocket may need to store some schema information from the endpoint, but it should not be storing the actual data from the endpoint.

PostgREST is great. I love the core design of using accepting a query URL (and some optional HTTP headers) and returning table data. But there are two main features I want that it lacks:

  1. PostgREST only supports PostgreSQL but I want to be able to use a SQLite on the backend
  2. PostgREST only outputs JSON and CSV, but I want to be able to browse tables using HTML pages -- an HTML frontend

Sprocket could be divided into two parts:

  1. backend accept an HTTP request and 1.A. if a database is provided: build a SQLAlchemy query, run the query, read the SQL rows into Python lists and dicts 1.B. if a PostgREST (OpenAPI) URL is provided: forward the request, read the response JSON into Python lists and dicts
  2. frontend convert those Python lists and dicts into JSON, CSV, or HTML

It would be inefficient for a user to configure Sprocket with a PostgREST backend (1.B) then request JSON or CSV format. We could support that use case by just redirecting (HTTP 301) from the Sprocket URL to the PostgREST URL.

beckyjackson commented 2 years ago

OK, I will need to rework Sprocket to handle this. I wasn't able to find a schema that tells us all the columns in a table, though. Do you know if we can get this data anywhere?

jamesaoverton commented 2 years ago

I thought the Swagger JSON included all the columns but now I see that it does not. Sorry.

Another option is to query the table with limit 1 and no other parameters (especially no 'select' parameters). That would require an extra request. We could cache that information, but then we should also have a way to reset that cache.