PgRest

A PostgREST and Supabase-compatible REST API powered by Elixir and Ecto.

Define Ecto schemas, get a full REST API that works with existing PostgREST clients like @supabase/postgrest-js. Queries are parsed into Ecto expressions — no raw SQL, no runtime code generation.

Why PgRest?

PostgREST and Supabase are fantastic battle-tested solutions, but they run as separate services. PgRest brings the same query language and client compatibility to Elixir — no sidecar process, no migration from Supabase, just add it to your existing Phoenix or Plug app.

• Drop-in client compatibility — Works with @supabase/postgrest-js and any PostgREST client. Same URL syntax, same operators, same response format.
• Ecto-native — Schemas define your API resources. Filters become where clauses, embeds become preload, inner joins become join. All type-safe through Ecto's query builder.
• Scoped by default — scope/2 callbacks for tenant isolation and soft deletes. Custom handle_param/4 for search, geo-queries, or any app-specific filter.
• No extra infrastructure — It's a Plug. Runs in your existing BEAM process, uses your existing Ecto repo. No separate service to deploy or manage.

Demo App

See demo/ for an interactive query explorer built with Phoenix LiveView. It runs 18 example queries live against a seeded project/task database using @supabase/postgrest-js, showing the client code, generated SQL, JSON response, and timing for each.

The repo ships a Hivemind wrapper that serves the demo at localhost:4042:

bin/pgrest start
bin/pgrest stop
bin/pgrest console

Requires a local Postgres reachable at localhost:5432 (mix ecto.setup from demo/ creates the database). Or run the demo directly:

cd demo && mix setup && mix phx.server

Prerequisites

• Elixir 1.19+
• PostgreSQL with an Ecto repository
• A Plug-based application (Phoenix, Bandit, etc.)

Installation

Add pgrest to your dependencies in mix.exs:

def deps do
  [
    {:pgrest, "~> 0.1.0"}
  ]
end

Then fetch dependencies:

mix deps.get

Quick Start

1. Define a Resource

A resource is an Ecto schema that uses PgRest.Resource:

defmodule MyApp.API.Tasks do
  use Ecto.Schema
  use PgRest.Resource
  import Ecto.Query

  schema "tasks" do
    field :title, :string
    field :status, :string
    field :priority, :string
    belongs_to :project, MyApp.API.Projects
    timestamps()
  end

  # Optional: always-applied query scope (tenant isolation, soft deletes)
  @impl PgRest.Resource
  def scope(query, _context), do: where(query, [t], is_nil(t.deleted_at))

  # Optional: custom URL params (?search=foo)
  @impl PgRest.Resource
  def handle_param("search", value, query, _context) do
    where(query, [t], ilike(t.title, ^"%#{value}%"))
  end

  def handle_param(_, _, query, _), do: query
end

2. Start the Registry

Add PgRest.Registry to your supervision tree. With :otp_app, it auto-discovers every module that has use PgRest.Resource:

children = [
  MyApp.Repo,
  {PgRest.Registry, otp_app: :my_app},
]

Or pass an explicit list:

{PgRest.Registry, modules: [MyApp.API.Tasks, MyApp.API.Projects]}

3. Mount the Plug

In your router, forward API requests to PgRest.Plug:

# Phoenix router
forward "/api", PgRest.Plug, repo: MyApp.Repo

# Plug.Router
forward "/api", to: PgRest.Plug, init_opts: [repo: MyApp.Repo]

4. Query with Any PostgREST Client

import { PostgrestClient } from '@supabase/postgrest-js'

const api = new PostgrestClient('http://localhost:4000/api')

// Filter, order, paginate
const { data } = await api
  .from('tasks')
  .select('title,status,project(name)')
  .eq('status', 'pending')
  .order('due_date', { ascending: true })
  .limit(10)

Or with plain HTTP:

GET /api/tasks?status=eq.pending&select=title,status,project(name)&order=due_date.asc&limit=10

How It Works

Every request flows through the same pipeline:

Base query (from schema)
    |
scope/2 (tenant isolation, soft deletes — always runs)
    |
URL filters (?status=eq.active&priority=eq.high)
    |
handle_param/4 (custom params: ?search=, ?within_miles=)
    |
select, order, limit, offset → Repo.all()

URL parameters are parsed into an AST, then applied as Ecto query expressions. The parser handles the full PostgREST operator set: comparison (eq, neq, gt, gte, lt, lte), pattern matching (like, ilike, match), containment (in, cs, cd, ov), full-text search (fts, plfts, phfts, wfts), logical grouping (and, or, not), and relationship embedding via select.

Using the Query Builder Directly

The parser and filter modules work independently of HTTP. You can use them to build Ecto queries from PostgREST-style parameter maps anywhere — LiveView, GenServers, background jobs:

params = %{"status" => "eq.pending", "priority" => "eq.high", "order" => "due_date.asc", "limit" => "10"}

{:ok, parsed} = PgRest.Parser.parse(params, allowed_fields: MyApp.Tasks.__schema__(:fields))
{:ok, filters} = PgRest.TypeCaster.cast_filters(parsed.filters, MyApp.Tasks)

query =
  MyApp.Tasks
  |> PgRest.Filter.apply_all(filters)
  |> PgRest.Order.apply_order(parsed.order)
  |> Ecto.Query.limit(^parsed.limit)

Repo.all(query)

The result is a composable %Ecto.Query{} — pipe it further, add your own clauses, or pass it to streams.

Plug Options

forward "/api", PgRest.Plug,
  repo: MyApp.Repo,           # Required — Ecto repo module
  json: Jason,                 # JSON encoder (default: Jason)
  max_limit: 1000,             # Max rows per request (default: nil = no limit)
  context_builder: &build/2    # Custom context from conn → map

Resource Callbacks

Supported Operations

Telemetry

PgRest emits :telemetry events for all operations:

[:pg_rest, :query, :start]    — %{resource: module, operation: atom, repo: module}
[:pg_rest, :query, :stop]     — includes duration
[:pg_rest, :query, :exception] — on failure

Testing

mix test

License

MIT