Would it be possible to implement an Ecto adapter for PostgREST?

zoedsoupe

Hello community! I’m starting to implement an all-in-one library/SDK for Supabase services for Elixir and the roadmap can be seen on the project repository. I already implemented the Storage service and the next one would be the Database.

Given that introduction, the Supabase’s Database service uses under the hood the PostgREST implementation. So I would like to implement an Ecto adapter for this service but I having some troubles and doubts.

1. Is that even possible?

PostgREST doesn’t allow to make direct queries on the server, so I need to translate a

from u from “user”, where: u.first_name =~ “John"

into a HTTP request, for the hipotetical URL:

https://<supabase-base-url>/rest/v1/user?ilike.first_name=“Jhon”

So how I could start a new “connection”, manage it, close it but also send this custom “queries”?

I know Ecto adapters behaviour have prepare and execute callbacks that seems to fit like a charm to this issue, but then I realise other problems

2. I would need to implement a new driver?

I know that there is a EctoAdapters.SQL behaviour that manage connections pool and some other cool stuff, but it needs a driver, for example for Postgres would be postgrex. That’s awesome but… How it would perform within the PostgREST adapter? PostgrREST doesn’t even allows migrations or custom queries and thier response are always JSON.

What is the difference about an Ecto adapter and a database Driver? In this situation of PostgREST it seems to be the same thing.

3. So, without an Ecto Adapter

I could rewrite the unmaintained repository that implement some functionalities from PostgREST called postgrestex but it doesn’t aims to implement an Ecto support. And also I would need to perform a extensible “query” validations and building ala Ecto.Query. Would this be an idiomatic way to implement this, ala postgrest-js

Conclusion

So these are my doubts and questions! I really appreciate any response! For more additional content I recommend to follow the postgrest-js repo link and check their implementation.

Also, a insertion could be invoked in JS like this:


const { error } = await supabase
  .from('countries')
  .insert({ id: 1, name: 'Denmark' })

And a filtering API example would be:


const { data, error } = await supabase
  .from('cities')
  .select('name, country_id')
  .eq('name', 'The Shire')    // Correct

const { data, error } = await supabase
  .from('cities')
  .eq('name', 'The Shire')    // Incorrect
  .select('name, country_id')

13 comments

#ecto #postgres #ecto_sql #ecto_adapter

1 1305 13

2023-10-11 11:48:34 UTC

Showing Posts 1 to 10

Show Best Posts
Show All Posts (oldest first)
Show All Posts (newest first)

kwando

You are allowed to connect to supabase with the regular Ecto/Postgres adapter in case you missed that

Post #1

zoedsoupe

I don’t think so.. How you would do it? How it would translate the queries to PostgREST? You mean connecting directly to the Postgres instance without using the API? If so, good to know! But think there different are use cases for PostgREST

Post #2

v0idpwn

Yes, you’d just have to write an Ecto adapter.

Post #3

kwando

Yes, connecting to the Postgres instance directly. You can get the connection string under project “Project Settings / Database”.

To me it seems a bit backwards to make an Ecto adapter for this when you already have a connection to the database, maybe just a API client would be enough?
I have no idea how much work it is to create an Ecto adapter, but it is probably non trivial.

Wish you the best of luck with this.

Post #4

zachdaniel

Creator of Ash

It is a significant lift to create an ecto adapter

Post #5

zoedsoupe

ok, but i really need to say that isn’t clear on how i could achieve this as @kwando already says that is possible to connect directly to the postgres instance. Implement an Adapter for the PostgREST API will demand a lot o limitations and Ecto.Query transformations, as it doesn’t support where clauses, for example, only eq(column, value), neq(column, value) and etc

it would be awesome to use Ecto for this solution but now it doesn’t seems to be a relevant integration. Implementing in the way postgrest-js and postgrest-rs implements would be a better way, although some funciton names would need to be renamed like not and in filters.

Post #6

linusdm

Maybe I don’t understand, but why would I choose to go through an SDK that uses an http protocol for accessing a postgres database, if there is a perfectly normal way to access that same database through an sql connection, without all the intermediate translations?
That just doesn’t make sense to me. I’m sure many phoenix webapps are set up like that, pointing their connection string to supabase.

If I’m understanding incorrectly, please provide some more details or context so we’re all on the same page.

Post #7

dimitarvp

Feel free to make me look very stupid but… why do you need this? What’s wrong with just pointing your own Ecto Repo to Supabase’s Postgres server?

I too don’t get what’s the desired result here.

Furthermore, have you looked at Supabase’s GitHub page? They have various projects that might achieve part or all of what you need, Elixir ones included.

Post #8

zoedsoupe

I mean, is just an alternative. The postgREST aims to be used on envirenments where isn’t possible to achieve a connection with a database, mostly used in frontend only applications like on the javascript ecosystem. For Elixir is obviously most interesting to use the connection string directly and use Ecto. Does that mean the SDk shouldn’t implement the postgrest interface? only the ones that make sense for the Elixir ecosystem like Phoenix Live View components, storage and realmente services? I really want to extend this discussion to the path of “what is the limit of the SDK?” and “does taht make sense to be available to Elixir applications?”

Post #9