3

I want my users to be able to upload reports (PDFs). For this purpose, I have created a table of "reports" and thought that I can reference the object stored there. Therefore I created a column "file" with the foreign key reference to the object store.

This has worked so far, however, I do not get a query to work. Example:

const { data } = await supabase
      .from<definitions["reports"]>("reports")
      .throwOnError()
      .select(`id,title,file(id)`);

The above example gives me this error:

{
   "code":"PGRST200",
   "details":null,
   "hint":"Verify that 'reports' and 'file' exist in the schema 'public' and that there is a foreign key relationship between them. If a new relationship was created, try reloading the schema cache.",
   "message":"Could not find a relationship between 'reports' and 'file' in the schema cache"
}

I'm only recently working with Postgres and so I would imagine that a reference across the tables and the files may simply not be possible. Anyway, I would like to know how else I could implement this use case.

Many thanks

AAV
  • 798
  • 1
  • 6
  • 23
K. D.
  • 4,041
  • 9
  • 48
  • 72

1 Answers1

1

In your case you don't have a relation between two tables.

The solution can be like this:

Create a table - report

id,title,file_id,created_by + maybe extra data.

You need to generate file_id as a unique key (e.g. use package uuid).

final file_id = uuid.v4();

Upload a new report to the Storage, but use our generated file_id as filename.

{bucket_id}/{user_id}/{file_id}.pdf

After that you will have all data in one table (report) and if it's needed to download a file from Storage.

igdmitrov
  • 476
  • 1
  • 3
  • 10