postgrest
postgrest copied to clipboard
PostgREST
Use `Prefer: schema=x` as an alternative to Accept-Profile/Content-Profile
Problem
Changing from Accept-Profile to Content-Profile depending on the HTTP request method can be confusing. See https://github.com/PostgREST/postgrest/issues/2890#issuecomment-1665169758 and https://github.com/PostgREST/postgrest/issues/3231.
We don't really need the flexibility of saying insert into into schema.users but give me the results of another_schema.users on the same request. This can be achieved in theory with Content-Profile from Accept-Profile.
Solution
Use Prefer: schema=x instead and apply it to all HTTP methods.
This would be great, and help clean up some Caddy Config code I've had to implement in order to abstract and "fix" this.
I've seen many end users struggle with this too and make the common mistake of using the wrong header.
If we really want to make working with multiple schemas easy, we should reconsider just doing that via the request's path, like /schema1/tableA, /schema2/viewB etc.
like /schema1/tableA, /schema2/viewB
That should be in its own issue but I'd be against it because:
- it makes the url brittle (a schema is an implementation detail and a rename would break the url)
- would make impossible to do resource embedding across different schemas (if we ever want to)
- will make custom urls harder to implement later on (https://github.com/PostgREST/postgrest/issues/1086#issuecomment-1513910590)
it makes the url brittle (a schema is an implementation detail and a rename would break the url)
That's not true. A schema is not an implementation detail as soon as you use multiple of them. Right now, you need to pass the schema in the -Profile headers. That makes it part of the API.
Whether to change a header or the path in the URL if you change the schema name - does not make a difference. You need to change it anyway.
would make impossible to do resource embedding across different schemas (if we ever want to)
Not true, either. You just need syntax. you could do /schema1/table1?select=*,schema2/table2(*), for example. That's actually quite natural and much easier to deal with name conflicts compared to the header approach.
will make custom urls harder to implement later on
And of course I don't agree here as well. If done right, this could actually be a step towards custom URLs.
@wolfgangwalther I've opened a new issue for the schema path idea: https://github.com/PostgREST/postgrest/issues/2956
I guess we can have both? I don't see why one would exclude the other. This issue is mostly about improving the current header way.
Now that we have Prefer: handling=strict, we can use Prefer: handling=strict; schema=xx instead. No need for creating a new header.
Prefer is not the solution to everything. Especially not this. When you do GET /xyz with a different schema... it's an entirely different endpoint / resource. This is not what Prefer is about. I am very much :-1: on this.
Especially not this. When you do GET /xyz with a different schema... it's an entirely different endpoint / resource
That doesn't look right according to REST definition of resource.
https://ics.uci.edu/~fielding/pubs/dissertation/rest_arch_style.htm
A resource is a conceptual mapping to a set of entities This abstract definition of a resource enables key features of the Web architecture. First, it provides generality by encompassing many sources of information without artificially distinguishing them by type or implementation Finally, it allows an author to reference the concept rather than some singular representation of that concept, thus removing the need to change all existing links whenever the representation changes
A schema is an implementation detail. The "concept" (say "authors") can move schemas and it'll still be the same. Having the schema on the path results in brittle URLs.
This way will also give us the flexibility of doing cross-schema resource embedding if the need arises. Using paths for schemas will overcomplicate the syntax /schema1/projects?select=schema2/projects?
Besides that Prefer is just an improvement over the existing Accept/Content-Profile headers so not a new feature. I don't see why not improve things.
Prefer is not the solution to everything.
It's the only header we can use without inventing an X-Something, which is much more likely to give trouble. If you have an alternative header, I'm all ears..
Especially not this. When you do GET /xyz with a different schema... it's an entirely different endpoint / resource
That doesn't look right according to REST definition of resource.
How so? I think we are just talking about two different things.
Your scenario is more like versioning for example. Assume a v1 and a v2 schema. Both have a view authors pointing to the same table. The v2.authors view exposes an additional column.
I fully agree with you - this is not a different "thing".
But this is also just a special case. Imagine two schemas, with two tables, both named items. Entirely different tables with no connection whatsoever. Those are two different "things" - and you shouldn't need to make a difference via header. That's just not correct. The only sane way to map this to http is to have two different endpoints.
A schema is an implementation detail. The "concept" (say "authors") can move schemas and it'll still be the same. Having the schema on the path results in brittle URLs.
In the versioning example it might be (although the api version is hardly an implementation detail, but the fact that this is represented as a schema is). In the other example, it might very well not be an implementation detail. It might give you a much required context to actually be able to tell what "kind of items" you are dealing with.
This brittle URL thing.. I really don't get it. If you use schemas in the URL - of course the URL will change when you change the schema. But the URL will also change when you rename the table or view. Why is that not an implementation detail? Where do you draw the line?
This way will also give us the flexibility of doing cross-schema resource embedding if the need arises. Using paths for schemas will overcomplicate the syntax
/schema1/projects?select=schema2/projects?
Uhm, how exactly do you put the fact that two tables in the embedding spec are in different schemas in the header? I don't get it. This seems much more complicated - because you suddenly need to draw a connection between the header and the select= embedding. The url-only syntax you proposed... is really simple and nice!
Besides that
Preferis just an improvement over the existing Accept/Content-Profile headers so not a new feature. I don't see why not improve things.
Well, the Accept/Content-Profile headers turned out to be... not a good idea, imho. And not only because of the different header names for GET and POST - but in general.
But this is also just a special case. Imagine two schemas, with two tables, both named items. Entirely different tables with no connection whatsoever. Those are two different "things" - and you shouldn't need to make a difference via header. That's just not correct. The only sane way to map this to http is to have two different endpoints.
From a conceptual point of view (the REST one), those items you refer to should be different concepts. The correct db design for that is to have only one parent table items then use table inheritance for childs items_x and items_y. Those would be different endpoints then.
But the URL will also change when you rename the table or view. Why is that not an implementation detail? Where do you draw the line?
IMO at the relation level. Databases and schemas are boxes of relations. If you rename the table/view then it is a different relation thus a different concept.
Well, the Accept/Content-Profile headers turned out to be... not a good idea, imho. And not only because of the different header names for GET and POST - but in general.
Hm, I don't see why. IME besides the different headers that has been working just fine. Simple in interface, implementation and maintenance.
We had another discussion about the Host header pointing to different databases. Following your URL argument here, should we now use /db/schema/table? (Like prest does?) Typing that.. looks inconvenient for users.
Maybe that's the real win we have with the header approach. We're able to provide default schemas/databases for our endpoints and keep them simpler.
From a conceptual point of view (the REST one), those
itemsyou refer to should be different concepts. The correct db design for that is to have only one parent tableitemsthen use table inheritance for childsitems_xanditems_y. Those would be different endpoints then.
I get the idea, although I don't think this would be respected in practice. Name collisions could also happen in homographs too.
Since I was working on bulding the OpenAPI spec, I noticed that it's quite difficult to work with the Accept-Profile header, since the response, filters and permissions may change when that header is present and the endpoint is the same. Separating it into a different endpoint instead of a header helps a lot in simplifying this. Of course, we shouldn't build our API around OpenAPI, but I think it's good to know that the issue is there.
OpenAPI is a valid point. So in general is harder to provide a static spec with the header approach.
In that case I guess we should also ditch the Host idea for mapping an endpoint to another database relation.
Let's move forward with the schema in URL then. Will open a new issue for that.
We had another discussion about the
Hostheader pointing to different databases. Following your URL argument here, should we now use/db/schema/table? (Like prest does?) Typing that.. looks inconvenient for users.
The fact that the host is transferred as a header is mostly an implementation detail of http - because nobody uses it like that. Everybody uses it as part of the URL, so there is no reason to change my proposal.
Let's move forward with the schema in URL then. Will open a new issue for that.
We already have #2157.