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

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')