rest-to-sql for creating unit test with supabase that executes the sql against pglite?

[nurulhudaapon]

Feature request

Currently there are no easy way to test supabase queries in unit test. The pg-gateway + pglite still doesn't work with postgrest, even if it does then you have to spin up pg-gateway and postgrest server. So how about just converting the PostgREST query to SQL and then executing against in memory pglite?

This could possiblly be done building something within the Postgrest CLI.

[gregnr]

Thanks for the issue @nurulhudaapon. My main concern with a rest-to-sql lib is that we are essentially re-writing PostgREST at that point.

What would be an amazing solution though, is a WASM build of PostgREST that can accept HTTP params and output SQL. I've gone down this rabbit hole before, and Haskell (which PostgREST is written in) actually does support WASM as a compile target. But currently PostgREST has too much coupling to native PG network requests for this to work as-is. We would essentially need to route SQL calls back to JS so that they can be fulfilled by PGlite (or pg-gateway+PGlite).

If you or anyone else has experience with Haskell and willing to tackle this problem, this would be huge (and there are actually a lot of other use cases for a WASM build PostgREST too).

[nurulhudaapon]

Thanks for the issue @nurulhudaapon. My main concern with a rest-to-sql lib is that we are essentially re-writing PostgREST at that point.

What would be an amazing solution though, is a WASM build of PostgREST that can accept HTTP params and output SQL. I've gone down this rabbit hole before, and Haskell (which PostgREST is written in) actually does support WASM as a compile target. But currently PostgREST has too much coupling to native PG network requests for this to work as-is. We would essentially need to route SQL calls back to JS so that they can be fulfilled by PGlite (or pg-gateway+PGlite).

If you or anyone else has experience with Haskell and willing to tackle this problem, this would be huge (and there are actually a lot of other use cases for a WASM build PostgREST too).

Thank you @gregnr for putting your thought, I've been looking at the PostgREST implementation, currently thinking of at least adding support to the postgrest binary CLI that can emit the generated SQL.

Something like:

postgrest --rest="/users?...."
--
SELECT ... FROM public.users;

Once we have this we should be able to build to WASM target with only postgrest --rest part of the code, assuming this will be easy to abstract this out of the native PG network calls.

Note: I'm new to HS, and just learnt the basic syntax to be able to read PostgREST implantation properly as I'm very keen to tackle the first part of the solution.

[w3b6x9]

@nurulhudaapon these three files here are used to translate rest to sql: https://github.com/PostgREST/postgrest/tree/main/src/PostgREST/Query