Postgrest: Question regarding embedding foreign entities

[matias-piin]

Hi,

I've been running into a problem regarding the topic 'Embedding Foreign Entities'.

I have the following schema:

author
-author_id
-name

book
-book_id
-name
-author_id (FK)

and I'm trying to write a query that for a given author will return the author info and all of the books they've written.

I try the following and get an error:
localhost:3000/author?author_id=eq.1&select=name,book{*}
returns
{"hint":null,"details":null,"code":"42703","message":"column author.book does not exist"}%

if I try
localhost:3000/book?book_id=eq.1&select=name,author{*}
it returns
{"hint":null,"details":null,"code":"42703","message":"column book.author does not exist"}%

I have also tried
localhost:3000/book?book_id=eq.1&select=name,author_id{*}
but it just returns
[{"book_id":1,"author_id":1}]%

I thought this would be possible from reading the documentation, but I'm not sure if I'm mis-understanding that doc or just applying it the wrong way. Any help or tips would be greatly appreciated :) Sorry if this is not the best place to ask this question.

Thank you!

[ruslantalpa]

what veersion of postgrest are you using?

[calebmer]

In PostgREST 0.2.x and down it would be localhost:3000/author?author_id=eq.1&select=name,book(*) (note the parentheses). I'm pretty sure that's the error because I've experienced the same thing the other way around 😊

[ruslantalpa]

The docs refer to last version so please use 0.3.0.1 and the requests you mentioned earlier should work

[matias-piin]

Thank you your quick replies :)

I am using version 0.3.0.1. I tried the query with parentheses and it gave me the same error:
{"hint":null,"details":null,"code":"42703","message":"column author.book does not exist"}%
Which is correct, that column does not exist, the column is book_id, but I guess it is not looking at the FK on the table to find the relevant relation.

[ruslantalpa]

the parentheses were used in 0.2, try for 0.3 {}

[matias-piin]

I originally tried with {} and got the same errors. Is there somewhere that I can check that my postgREST instance is set up correctly and can identifying my FKs?

[ruslantalpa]

you tried {} with 0.2, which is wrong, then you tried () with 0.3 which is also wrong :)

try {} with 0.3.0.1

[ruslantalpa]

you can try doing an "OPTIONS" request for each endpoint and see if you get "references" property for some of your columns that are foreign keys

[matias-piin]

I actually tried both () and {} each time, and got the same results :(

When I request OPTIONS both tables return:
{"pkey":[],"columns":[]}%
although the Primary and Foreign Keys show up in PgAdmin just fine.

[ruslantalpa]

The anonymouse role you are using does not have the right privileges to access those tables

[matias-piin]

I've added the permission to select on all tables and usage on the schema as per the docs. Still getting the same errors :(

[ruslantalpa]

can you post here your complete schema definition and role creation privileges in addition to the exact command you are using to start postgrest.
this is 99.99% privileges issue.
Notice the header in that doc ... it's deprecated so those queries are not 100% correct.

[matias-piin]

Schema:

CREATE TABLE author (
  author_id serial PRIMARY KEY,
  name text,
);

CREATE TABLE book (
  book_id serial PRIMARY KEY,
  author_id serial NOT NULL REFERENCES author (author_id),
  name text,
);

The anon user privileges:

CREATE ROLE anon NOLOGIN;
GRANT USAGE ON SCHEMA "public" TO anon;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO anon;
GRANT authenticator TO root

Thanks for all the help so far :)

[matias-piin]

Turns out curl was not encoding the URL correctly. Works well if you use the browser or a REST client to make the requests.

Thank you so much for all the help!

[WillForan]

Hit the same issue with curl. This last post saved me a few more hours of head pounding. Here's my revised and working call to curl with data-urlencode

curl 'localhost:3001/enroll' \
  -G --data-urlencode 'id=like.10931' \
  -G --data-urlencode 'select=id,person{*}'  | jq