templating and translating endpoints to Nginx

ppKrauss commented 7 years ago

In mature frameworks (eg. Spring-boot ) we can centralize all endpoint definitions, usually in a MVC-controller. In a PostREST-Nginx solution we can centralize endpoint definitions in the /etc/nginx/sites-available/* scripts... But it is a critical script and very close to server infrastructure, with permission restrictions, etc. ... And it is an "ugly way" to express endpoint definitions.

So a simple and natural way to do it is to parse, from some simple pretty directive-script syntax to the ugly-but-reliable Nginx scripts. PS: the programmer at localhost or the master of the sever (in production systems), can use a make with scripts to setup the final Nginx configurations and restart it.

The problem

Suppose the file /etc/nginx/sites-available/api, that is a Nginx complex script for a real-world little system, without lost of PostgREST-query:

server {

       # publishing by default the HTML for API description and related files for navigation
    server_name api.mydomain;
    root /var/www/api/html;
    index index.html index.htm;

    location / {
        try_files $uri $uri/ @proxy;
    }

    location @proxy {

        # # # # # # # # # #
        #### endpoints of the ISSN-L Resolver, the real-world little system 
        rewrite # isN (ISSN) checker
          ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)/is[nN]$
          /vwint_issn_isn?select=isn&issn=eq.$1$2
          break;
        rewrite
          ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)([0-9xX])/is[nN]$
          /vwint_issn_isn?select=isn&issn=eq.$1$2&dig8=eq.$3
          break;

        rewrite # N-to-C convertion (name to canonic, so ISNN to its ISSN-L)
          ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)/[nN]2[cC]$
          /vwint_issn_n2c?select=issn-l&issn=eq.$1$2
          break;
        rewrite
          ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)([0-9xX])/[nN]2[cC]$
          /vwint_issn_n2c?select=issn-l&issn=eq.$1$2&dig8=eq.$3
          break;

        rewrite # N-to-Ns convertions (name to all names, the ISSN-L group)
          ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)/[nN]2[nN]s?$
          /vwint_issn_n2ns?select=issns&issn=eq.$1$2
          break;
        rewrite
          ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)([0-9xX])/[nN]2[nN]s?$
          /vwint_issn_n2ns?select=issns&issn=eq.$1$2&dig8=eq.$3
          break;

        # # # # # # # # # #
        #### endpoint of a second application (other system)
        rewrite # URN LEX-Diário Oficial
          ^/urn:lex-do:br\;(?:rj|rio.janeiro)\;rio\.janeiro:(?:executivo|exe|legislativo|leg|judiciario|jud):materia:([0-9]+)$ 
          /vw_content?select=urn_do,materia_id,content&materia_id=eq.$1  
          break;

        # # # # # # # # # #
        #### default and auxiliar endpoint, for PostgREST-queries
        rewrite  
          ^/(.*)$ 
          /$1 
          break;

        proxy_pass  http://127.0.0.1:3000;  # my PostREST is  here!
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        default_type  application/json;
        proxy_hide_header Content-Location;
        proxy_set_header  Connection "";
        proxy_http_version 1.1;
    }
}

You can try temporally at jats.science, eg. urn:issn:1476-4687/n2c, urn:issn:1476468/isC, urn:issn:0028083/isC, urn:issn:1476-4687/N2Ns, etc.

The complete solution

Imagine the Nginx script as an template, with placeholders at server_name and all rewrite directives. Imagine a JSON with an OpenAPI of the API spec generated by swagger.io/tools, a swagger.json file... So a simple mustache engine can convert swagger.json API specification (plus some config to template-file options) into a Nginx script... It is easy to implement a Open-API-parser tool for PostgREST.

The only addition to standard swagger.json is a field with postgrest-translation: see each "/table?select=..." part of each rewrite in the the above exemple.

A pretty and auxiliar query-solution

The good user of a framework with centralized endpoint definitions (eg. Spring-boot ), like to use some controller schema design (see domain design or usual)... Let's thinking in endpoints organization, domains as SQL-schemas and entityes as SQL-views. Summarizing:

Instead table?select...where...id.eq... URL syntax, use something as

/schema/table_or_view/{id} syntax as general use and base-URL
/schema/table_or_view/{id}/json_field syntax to offer specific data, returning only the expected JSON, without "array envelope".

...

ruslantalpa commented 7 years ago

I am not sure i understand what is the purpose of this issue and what is it proposing exactly. Why the complicated nginx config...? Is it all because you want urls in this form /schema/table_or_view/{id} ?

ppKrauss commented 7 years ago

@ruslantalpa Yes, to be agile and competitive we need "plug and play" (with no-polution scripts) tools...

About templating URI, plase ignore my "auxiliar query-solution", the main demand is a "any OpenApi" endpoint definition... so /anything/{anyPathVariable}/etc, for POST and GET (!).

ruslantalpa commented 7 years ago

Can you give a more specific example and explain why the current way is bad and does not work? What exactly can't you accomplish with the current interface? But please be specific and not link a general doc about some spec :)

ppKrauss commented 7 years ago

Hi @ruslantalpa, my exemple, "the problem" section, is a good starting point... Let's start with GET and URI-templating problems:

It is impossible to define a GET by URI templating, like /urn:issn:{id}/n2c, with PostgREST.
PS: of course, we can workaround with Nginx, as I do (see eg. /urn:issn:{id}/n2c implemented with the 3rd rewrite clause).
It is impossible to remove the "array envelope" from the PostgREST result, of a GET request.
It is impossible to call directally a function (see lib.issn_n2c() at lib.sql).
We need to workaround with an "extra VIEW" declaration, in some cases will be not elegant (ugly expurious SQL code) and in others impossible to call.
In the examples I used these extra-VIEW declarations.
... Even when all GET problems solved, there are no PostgREST plan to reuse it in POST, that is, call an endpoint by POST using URL templating, as suggested above.

begriffs commented 7 years ago

Your nginx rewrite for this works, correct? I have never seen a url convention like /urn:issn:{id} before. Doesn't mean it's the wrong thing to do necessarily, but it seems highly uncommon. Supporting these kind of urls in postgrest doesn't benefit very many people (correct me if I'm wrong here!), and nginx can do it fine.
Have you tried https://postgrest.com/en/v0.4/api.html#singular-or-plural ?
Calling functions? Like https://postgrest.com/en/v0.4/api.html#stored-procedures ? Or do you mean being able to use a GET request to call them?

ruslantalpa commented 7 years ago

How i read this is "i want this url format", can you explain why is the form of the url so important if it returns the data you want? How does your format of the url work when an object in a table is identified by a primary key composed of two columns?
sure you can, if you are requesting a single object using a key, add this header Accept: application/vnd.pgrst.object
You are missing the point of PostgREST, it's value is EXACTLY the fact it does not allow you to call any function that resides in any schema, you have to make a decision that you want to expose a particular entity/function to the http API. If users were able to call any function anywhere it would be pretty easy do DDOS a database exposed by PostgREST
i am not sure what you are talking about here and what is the whole "url templating" thing about. PostgREST does not target the usecase of allowing the user to somehow design his own urls. To me this feature request (if i understand it correctly) is like saying "i want to query table A like select * FROM A but for table B i want to query it like GIVE ME ALL FROM B"

ruslantalpa commented 7 years ago

I think this might be the case that you are trying to develop apis the same way you would do in a traditional stack and from this all the problems arise. An analogy would be trying to apply OOP concepts in a functional language.

ruslantalpa commented 7 years ago

@ppKrauss i still don't think we fully understand what you are asking/requesting with "url templating"

Is there any practical reasons beside "style and tradition/convention" why one url format works and the other does not? in the end, they are just strings saved in some var of some frontend client. Why does it matter so much for it to be /entity/{id} and not /entity/?id=eq.{id}, what is the actual benefit, what do you gain in practical terms. And the followup question is why does an api designer needs to be able to have his own special format for the url (even though the can use nginx) why can it not be the format exposed by postgrest?

ppKrauss commented 7 years ago

@ruslantalpa it is an Internet standard, exacly as you say "to be /entity/{id} and not /entity/?id=eq.{id}"... And we can add also " /entity/{id}?option={option}", etc. Hidden protocols and API must be elegant, not only page-layout and design ;-)

Is an usual requeriment in relevant REST APIs, as I commented here before,

PostgREST is not REST without GET: see OpenApi specification, that use as reference a good and mature (2012) standard, RFC 6570 (URI templates). Today we can't work without it, it is impossible to deliver professional development without it.

... Perhaps a "personal position"... but to make money we need to obey requeriments :-)

ppKrauss commented 7 years ago

@begriffs and @ruslantalpa , sorry, not see before all the posts (!). Trying to reply:

Itens pointed by @begriffs here:

the rewrite, correct. URN is a standard but not a good example, imagine usual /a/b/{id}/etc. Yes, I fix it using only Nginx... But the proposal here is to continue using rewrite and Nginx (!). See the idea of use Mustache to generate all the script.
hum... it is not a problem with "story vs stories", but the use of RFC 6570 and use infact OpenAPI. PotgREST not offers a full support for OpenAPI as we can interpret there.
is "being able to use a GET request to call them".

Itens pointed by @ruslantalpa

Yes, in part is "I want" because is not your obligation (heroic PostgREST task force) neither a statistical or well-proved demand... I try to ask better with RFC reference, etc.
Important (!), it is not a suggestion to replace URL-query, but a suggestion to offer configuration option in PostgREST.
ops, can you show how to correct my rewrite lines for it? I not see how to use other MIME there (the correct MIME for output is application/json).
hum... not missing, perhaps my English is bad... now is more precise, "being able to use a GET request to call them".
... perhaps I answered...

begriffs commented 7 years ago

Oh /entity/{id} vs /entity/?id=eq.{id}...I should make a section in the docs about this because it's the most common sophomoric critique I hear about our url format. Some people have gone so far as to say that anything except /entity/{id} is "not restful." The first thing I would argue is that these syntactical differences are immaterial to the architectural constraints of representational state transfer. Here is how I believe the current preference for /entity/{id} began:

In the beginning there were flat files and then hierarchical databases. In 1970 E. F. Codd pointed out that these systems constrain the types of queries available and unnecessarily couple performance optimizations to the grammar of queries.

Codd went on to propose the relational algebra (its descendant is SQL). This relates entities by keys. A primary key uniquely specifies a row in a table. It can vary in two ways: natural vs surrogate, and simple vs compound.

Fast forward to the early 2000s. Server side scripting languages were starting to create frameworks to host web sites. These systems, in order to compete, support various kinds of datastores, including but not limited to relational databases. The popularity of "Create a blog in 15 minutes!" style tutorials began to capture developer mindset, and the urls in these frameworks reflect their own constrained form of data access. Even now the Django ORM cannot support composite keys! It's been a registered issue on their project for literally a decade that they have not yet solved.

PostgREST is different. We begin from the relational model. The /entity/{id} thing is designed for simple, usually surrogate, keys. It doesn't express a rich enough grammar. RFC6570 that you mentioned describes a mapping from URLs to a hierarchical filesystem. The fundamental impedance is that databases are not hierarchical.

Now...we could allow /entity/{id} as a shortcut in PostgREST when we detect that tables do indeed have a simple numeric primary key. A lot of people ask about it, it might be courteous for us to offer this convenience. So far we haven't because Nginx can support the url rewriting. We think you'll want to use Nginx anyway for SSL etc so you might as well do the convenience url rewriting as well.

Also we have github issues already discussing RPC using GET requests. I agree that we ought to support it in the right circumstances, and let's continue the discussion in a more relevant github issue.

begriffs commented 7 years ago

@ppKrauss also I believe you can accomplish what you wrote about /a/b/{id}/etc by using https://postgrest.readthedocs.io/en/v0.4/api.html#resource-embedding - but happy to discuss particular examples to see how well it works for them.

begriffs commented 7 years ago

One more note, not sure what you mean by

PotgREST not offers a full support for OpenAPI

If you run a postgrest server and make a request to the root path (/) postgrest will respond with correct OpenAPI format explaining the API, output which we validate in our test suite against the spec.

ppKrauss commented 7 years ago

Thanks @begriffs (!). About OpenAPI, perhaps a mistake between "read" and "write":

we can read the PostgREST spectification expressed in OpenAPI-JSON jargon, at ??test/fixtures/openapi.json ... Is a kind of summarized documentation of PostgREST itself.
we can write an OpenAPI specification and develop a back-end system consistent with it. And we can develop using PostgRES, but we can't implement an arbitrary OpenAPI specification with pure PostgREST: the limitations that I showed fall in this type.

PS: the best way to show an OpenAPI specification is converting in HTML documentation, it seems like this. Do you have something (to read) like that for PostgREST?

ppKrauss commented 7 years ago

Thanks a lot @ruslantalpa and @begriffs, for all the attention and discussion. Now I understand better how you are working and organizing PostgREST... I am transforming it into code (!!), that is not easy... First draft: https://github.com/ppKrauss/PostgREST-writeAPI

Is a proposal for PostgREST extension... What do you think?

So we can close the issue.

ruslantalpa commented 7 years ago

I have no experience with a process where you get a "spec" like "the urls have to look like this and that's final" so i am not sure if this is needed or not. As i mentioned i think the value of having the urls in a specific form is overstated.

One other thing i am not sure is if you can automate this process, i mean you might be able to get better results if you manually create a few rewrite rules rather then having an automated tool create one for each endpoint.

The idea in general is ok, it does not have any conflicts with PostgREST way of doing things, in fact, using nginx rewrite rules is the thing we recommend to have the urls the way you like them.

Maybe it will be a good idea not to discuss this as a general concept but have a spec for a specific project that describe how all urls should look like, then based on that create an openapi spec and then you tool can be used to create the nginx rewrite rules.

I'll add "watch" to your project and when you want to ask something, tag me in one of your issues

ppKrauss commented 7 years ago

Thanks again @ruslantalpa (!),

... I've done my homework, discovering the usual name and similar use cases... The usual name of this web-server controller (also a reverse proxy pattern) is "API Gateway at server-side", and there are many tools (and non-standard solutions) to do similar thing, for plug-and-play your API specifications (that "have to look like this" as you say), mainlly in the context of microservices orchestration... So the OpenAPI community was also receptive (!), and an usable tool can be emerge from the Swagger.io community: they approved!

see https://github.com/swagger-api/swagger-codegen/issues/5034

PS: it is not a replacement to PostgREST-writeAPI, I will maintain the focus on PostgREST there. But it is a good alternative to it, using a big community and infrastuture (the swagger-codegen) to develop a generic tool. In a far future we can also submmit the use of x- properties as OpenAPI standard properties.

begriffs commented 7 years ago

@ppKrauss, sure I'll add a link in the Extensions section to your project. It's a cool experiment, and could be helpful for people who want their URLs in a very specific format.

PostgREST / postgrest