HATEOAS Resource Embeddings (_self link)

[mkleczek]

Problem

It would be really useful for PostgREST to generate links to embedded resources. That would make it easier to implement UIs allowing navigation between related resources.

Right now clients have to embed the knowledge of unique/primary/foreign keys which is duplication of metadata already known to PostgREST.

Solution

Provide _self syntax, eg:

http://localhost:3000/films?select=title,director:directors(last_name,_self) - provide last_name and URI to full representation (eg. http://localhost:3000/directors?id=eq.2)
http://localhost:3000/films?select=title,_self - provide title and URI to full representation (eg. http://localhost:3000/films?id=eq.432)
http://localhost:3000/films?select=title,director:directors(last_name,_self(films)) - provide last_name and URI to retrieve director's films (eg. http://localhost:3000/directors?id=eq.2&select=films)

The problem has been earlier discussed in several issues: https://github.com/PostgREST/postgrest/issues?q=hateoas - but it seems like no solution has been found. Not really sure if _self link syntax is the right solution: if it is general enough and/or compliant to some standard, but I think it might be good enough for many scenarios. The nice thing about it is that it is opt-in.

[steve-chavez]

http://localhost:3000/films?select=title,director:directors(last_name,_self) - provide last_name and URI to full representation (eg. http://localhost:3000/directors?id=eq.2)

Q: Where does the URI fit into? It would be a part of the response JSON?

[wolfgangwalther]

I wonder how much of this can already be implemented via computed columns / computed relationships - and which pieces are missing to fully do it.

[mkleczek]

http://localhost:3000/films?select=title,director:directors(last_name,_self) - provide last_name and URI to full representation (eg. http://localhost:3000/directors?id=eq.2)

Q: Where does the URI fit into? It would be a part of the response JSON?

Yes, I think so. It should be treated as any other column/attribute. It should also support aliasing syntax, eg: myuri:_self.

(whether it should be a full URI with scheme://host:port is disputable, maybe path would be enough).

[mkleczek]

I wonder how much of this can already be implemented via computed columns / computed relationships - and which pieces are missing to fully do it.

Correct me if I'm wrong but:

1. I think right now computed columns can not be generic (ie. you have to provide a separate function per relation)
2. Generic function requires knowledge of primary key columns and its values so it would need a catalog lookup.

PostgREST already has PK information in the schema cache so it could pass it as part of the query in an expression that calculates URI for each row. I am quite certain translation from query expression to appropriate SQL fragment would be better done in Haskell.

[wolfgangwalther]

1. I think right now computed columns can not be generic (ie. you have to provide a separate function per relation)

Nope, you can do stuff like CREATE FUNCTION _self(any) or so. There might even be a more adequate pseudo-type available, too.

2. Generic function requires knowledge of primary key columns and its values so it would need a catalog lookup.

You can either write a generic function + catalog lookup - or you can create specialized functions at schema creation time.

For example, I use psql to run all my SQL code, bundle it into an extension and then install that extension on the prod server. I can easily do meta-programming similar to this:

SELECT FORMAT($$ CREATE FUNCTION _self(%s) .... %s ... $$, table_type, pk_column) FROM (.. query to list all my endpoints and the relevant PKs ...) \gexec

This way, I write a generic solution that spits out specialized functions for each endpoint. The generic part / catalog lookups are only done at build-time, not run-time.

TLDR: This is just a question of having the right tooling.

[mkleczek]

1. I think right now computed columns can not be generic (ie. you have to provide a separate function per relation)

Nope, you can do stuff like CREATE FUNCTION _self(any) or so. There might even be a more adequate pseudo-type available, too.

OK, didn't know PostgREST would pick it up for all relations.

2. Generic function requires knowledge of primary key columns and its values so it would need a catalog lookup.

You can either write a generic function + catalog lookup - or you can create specialized functions at schema creation time.

For example, I use psql to run all my SQL code, bundle it into an extension and then install that extension on the prod server. I can easily do meta-programming similar to this:

SELECT FORMAT($$ CREATE FUNCTION _self(%s) .... %s ... $$, table_type, pk_column) FROM (.. query to list all my endpoints and the relevant PKs ...) \gexec
This way, I write a generic solution that spits out specialized functions for each endpoint. The generic part / catalog lookups are only done at build-time, not run-time.

1. It seems to me such functionality available OOTB in PostgREST would be much more preferable to code generation based on string templates during install time. Especially that these functions need to be updated any time PK definition is changed.
2. How would you handle _self(col1,col2)?

[wolfgangwalther]

2. How would you handle _self(col1,col2)?

Not sure what that means. Do you mean a multi-column PK?

Or is this a query string syntax similar to _self(films)?

---

Thinking about the latter case, I don't think the last example would work with computed relationships:

http://localhost:3000/films?select=title,director:directors(last_name,_self(films)) - provide last_name and URI to retrieve director's films (eg. http://localhost:3000/directors?id=eq.2&select=films)

This syntax seems to suggest that _self(...) should be able to contain an arbitrary select expression, which surely can't be mapped to a single computed relationship / function call.

[mkleczek]

2. How would you handle _self(col1,col2)?

Not sure what that means. Do you mean a multi-column PK?

No. Sorry for confusion.

Or is this a query string syntax similar to _self(films)?

Yep, this one.

Thinking about the latter case, I don't think the last example would work with computed relationships:

http://localhost:3000/films?select=title,director:directors(last_name,_self(films)) - provide last_name and URI to retrieve director's films (eg. http://localhost:3000/directors?id=eq.2&select=films)

This syntax seems to suggest that _self(...) should be able to contain an arbitrary select expression, which surely can't be mapped to a single computed relationship / function call.

Why not? _self expression produces just URI - where is the problem with it?

EDIT: I think we both agree it cannot be done with computed relationships. (misunderstood your answer)