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:


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


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
  .insert({ id: 1, name: 'Denmark' })

And a filtering API example would be:

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

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

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

1 Like

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

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

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.


It is a significant lift to create an ecto adapter :slight_smile:


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.

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.


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.

1 Like

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?”

So maybe I am truly misunderstanding you – if that’s the case, my apologies.

But I thought that Supabase provides various services that step on a number of PostgreSQL features and extensions (PostgREST included?).

That’s on you :stuck_out_tongue: I just answered your question: “is it possible to implement an ecto adapter for postgrest?”

1 Like

hmm I thing I dodn’t understand your point too. I mean, is more straight forward to connect directly via postgresql connection string, but postgREST has its own use cases. It will not become the “oficcial way” to connecto to database, specially if your HTTP connection have high latency, and will mostly used on frontend only applications. But I think it have its value to come to the Elixir ecosystem. Supabase indeed uses Postgres under the hood to provide their services like Auth or Realtime API. However I would like to build another layer of abstraction, like I already did for the Supabase Storage service, like a HTTP client.

Also this project is being som of my firstly contribution to open source, so I would like to contribute, learn more about supabase and maybe help anyone who cared to used supabase services from phoenix/elixir apps in a easy way :blush:

Ok, that’s fine! I’m really digging on adapters/drivers implementations and I came to a medium article that rewrites a postgresql driver and adapter, the plataformatec article that writes a mysql adapter to ecto and the ecto_mnesia library that is an adapter, but for only ecto 2.0.

Edit: I just found a ecto3_mnesia adapter, so i will dig in to it because my adapter can’t depend on a SQL driver…