Extensibility through HsLua

[steve-chavez]

Proposing HsLua to solve:

• Split off openapi into postgrest-contrib #1698
• Provide a way to customize the URL structure #1909
• Support for PGroonga operators #2028
• Support for geometry operators #2088

The main point against providing extensibility through SQL functions(for example for OpenAPI) is:

What I really like about PostgREST is that it's very simple to use and non-invasive concerning the database. There's no special configuration needed on the database side for simple features.

PostgREST is stateless in the sense that it does not have to build up any database state.

(From #1511 (comment))

[steve-chavez]

I think the tasks for this one would be(in order of increasing difficulty):

• Support extending operators. For now we could include PostGIS operators in postgrest-contrib, and leave pgroonga as a how-to in the docs.

• (Optional, for discussion) Support extending our "body query fragment". This would allow to do Add PostGIS GeoJSON support #1564 off-core. Not sure if really worth it, it doesn't seem like it could cover many use cases unlike custom operators, doesn't offer enough flexibility for more complex use cases.

• Hooking into our schema cache to allow OpenAPI and other specs. We'd need to offer OpenAPI in core to maintain backwards compatibility.

• Customize the URL structure. Looks it would require a big refactor. It would be the harder one and with less priority I'd say.

[steve-chavez]

Operators and query fragments can actually be done with static config, so Lua wouldn't be really needed here.

Customizing the URL structure and the OpenAPI generation does require compute and dynamic config, so here Lua would be a must.

By using pllua, this process could be made transparent to the database as well.

[wolfgangwalther]

The main point against providing extensibility through SQL functions(for example for OpenAPI) is:

What I really like about PostgREST is that it's very simple to use and non-invasive concerning the database. There's no special configuration needed on the database side for simple features.

As long as we keep "simple features" working without special SQL all is good. But having to resort to yet another scripting language and yet another place to put those scripts for advanced use cases is actually a step back.

We are long past the point, we'd just write arbitrary SQL and then hope for PostgREST to expose that nicely magically. We do write the exposed schemas according to how PostgREST works with them to achieve a certain effect.

We went a long way by including a lot of the configuration options inside the database, because we now have a single source of truth.

If we were to extend PostgREST with lua scripts, suddenly we'd have two sources again, SQL code and lua code. And we would need to make sure to always deploy them together, which is a real challenge for deployments.

Optionally allowing advanced configurations via SQL seems much more useful.

[steve-chavez]

I had more ideas about this. If we forget about the idea that hitting / must not touch the database and has to use the schema cache then we could provide true REST Code on Demand through a pg extension that does OpenAPI, RAML, Typescript types, etc. This extension would basically run the same metadata queries we do for the schema cache and process it through a db function, its signature would be like metadata queries -> OpenAPI, etc.

Optionally allowing advanced configurations via SQL seems much more useful.
What I really like about PostgREST is that it's very simple to use and non-invasive concerning the database. There's no special configuration needed on the database side for simple features.

And if that db function is written in pllua, and since Haskell can understand Lua via HSLua, we could provide the two options: run the function in the database or run it in PostgREST(for users that don't want this concern on the db), for the latter function the signature would be Schema cache -> OpenAPI,etc. It would be sort of an "isomorphic Lua".

Edit: closing as this is too complex and fuzzy.