Expose PostgreSQL databases as instant REST APIs

Bier

Alpha. Bier is in its first stage. Expect bugs and possibly security
flaws - it is not ready for production use.

Bier is an Elixir library that serves a RESTful API generated on the fly from
PostgreSQL introspection: point it at a database and it inspects the tables,
views, functions, and foreign keys and exposes them over HTTP - no controllers,
no route files, no schema definitions to write. It is heavily inspired by
PostgREST, and tracks PostgREST's request/response behavior closely (see
Conformance).

How it works, in one paragraph

Each Bier instance is a supervision tree the host application starts. On boot it
opens a Postgrex connection pool, introspects the configured schemas, builds
a Plug.Router module at runtime, and starts a Bandit web server with it.
Every incoming request is resolved to a {schema, relation} at request time and
compiled into one parameterized SQL statement that returns its result set as
JSON, which is then rendered in the negotiated media type.

Installation

Add bier to your dependencies:

def deps do
  [
    {:bier, "~> 0.2"}
  ]
end

To track unreleased work on main, use a git dependency instead:

def deps do
  [
    {:bier, github: "milmazz/bier"}
  ]
end

Requires Elixir ~> 1.18 (developed against Elixir 1.20 / OTP 29) and a
reachable PostgreSQL instance. Bier pulls in Bandit, Plug, Postgrex,
DBConnection, NimbleOptions, JOSE (JWT verification), and
telemetry as runtime dependencies.

Usage

Add a Bier child to your application's supervision tree. Each child is one
named instance with its own config, connection pool, and web server;
multiple instances coexist by passing distinct :name values.

children = [
  {Bier,
   name: MyApp.Bier,
   router: [port: 4040, scheme: :http],
   database: "my_app_dev",
   username: "postgres",
   password: "postgres",
   db_schemas: ["api"]}
]

Supervisor.start_link(children, strategy: :one_for_one)

Once it is up, the database is reachable over HTTP, e.g.:

# read rows, filter, select columns, order, paginate
curl "http://localhost:4040/items?select=id,name&age=gte.18&order=name.asc&limit=10"

# insert and get the row back
curl -X POST "http://localhost:4040/items" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"name": "Ada"}'

# call a database function
curl "http://localhost:4040/rpc/add?a=1&b=2"

Documentation

New to Bier? Start with the tutorials, then reach for the reference guides.

Tutorials (learn by building a brewery catalog API)

• Getting Started - create the database, boot Bier, and make your first requests.
• Authentication - add roles and JWTs so only members can post.
• Realtime - push new rows to the browser with a trigger and the SSE endpoint.

Reference

• API reference - reading, filtering, ordering, pagination, embedding, mutations, RPC, time zones, and content negotiation.
• Configuration - every option, the PGRST_* environment variables, and standalone/Docker/CLI operation.
• Observability - telemetry events, Server-Timing, health endpoints, and the error envelope.
• Realtime events - the SSE endpoint: channels, auth, delivery semantics, telemetry, and - with events_publication - a WAL change feed with typed row events and Last-Event-ID resume.
• Injection safety - what is bound vs. escaped in the generated SQL, and why.

Configuration

Options are validated by a NimbleOptions schema. Their defaults are sourced
from application env, so you can also set them under config :bier, … instead
of passing them to start_link/1. The main keys (named after their PostgREST
equivalents):

The configuration guide documents every option

• type, default, PGRST_* variable, and the validators that can reject a
  configuration at boot.

Pluggable JSON

Bier.json_library/0 returns the configured encoder (the stdlib JSON module by
default, which requires Elixir 1.18+). Override it with:

config :bier, :json_library, Jason

Schema-cache reload

Bier introspects the database at boot and serves from that snapshot. After a
DDL change (new table, column, FK), reload the cache without restarting -
exactly like PostgREST:

NOTIFY pgrst, 'reload schema';

Every instance listens on the db_channel channel (default "pgrst") with a
dedicated connection; set db_channel_enabled: false to opt out and save the
connection. From Elixir, Bier.reload_schema_cache(MyApp.Bier) does the same
on demand (PostgREST's SIGUSR1 equivalent). A failed reload keeps the
previous snapshot serving. 'reload config' is accepted and logged, but a
no-op: the host application owns Bier's configuration.

To reload automatically on every DDL change, install PostgREST's event
trigger:

CREATE OR REPLACE FUNCTION public.pgrst_watch() RETURNS event_trigger
  LANGUAGE plpgsql
  AS $$
BEGIN
  NOTIFY pgrst, 'reload schema';
END;
$$;

CREATE EVENT TRIGGER pgrst_watch
  ON ddl_command_end
  EXECUTE PROCEDURE public.pgrst_watch();

Running standalone

Bier is primarily a library you embed (see Usage), but it can also run
as a standalone server - no host application required - configured entirely
from PostgREST-compatible PGRST_* environment variables. This is a config-level
drop-in for PostgREST for the settings Bier implements.

Docker

docker build -t bier .

docker run --rm -p 3000:3000 \
  -e PGRST_DB_URI="postgresql://authenticator:secret@db:5432/app" \
  -e PGRST_DB_SCHEMAS="api" \
  -e PGRST_DB_ANON_ROLE="web_anon" \
  bier

The image runs bin/bier start, which boots one instance bound to
PGRST_SERVER_PORT (default 3000). A fatal config problem (e.g. a JWT secret
shorter than 32 characters) is printed to stderr and aborts startup.

Release

MIX_ENV=prod mix release builds a self-contained release under
_build/prod/rel/bier:

MIX_ENV=prod mix release

BIER_STANDALONE=1 \
PGRST_DB_URI="postgresql://authenticator:secret@localhost:5432/app" \
PGRST_DB_SCHEMAS="api" \
PGRST_DB_ANON_ROLE="web_anon" \
_build/prod/rel/bier/bin/bier start

BIER_STANDALONE=1 is what tells Bier.Application to boot an instance from the
environment; it is baked into the Docker image. Without it (the default), the
application starts only its registry, so embedding Bier in a host app is
unaffected.

Inspecting configuration

The bier escript (mix escript.build) resolves and prints the effective
config without starting a server - useful for debugging a deployment's env:

PGRST_DB_SCHEMAS=api ./bier --dump-config
./bier --help

Supported PGRST_* keys mirror the Configuration table
(PGRST_DB_URI, PGRST_DB_SCHEMAS, PGRST_SERVER_PORT, PGRST_JWT_SECRET,
PGRST_LOG_LEVEL, …) plus their deprecated PostgREST aliases. A handful of
PostgREST keys are accepted and echoed without having an effect, so an existing
PostgREST config can be pointed at Bier unedited; anything outside that set is
rejected. The configuration guide has the full
list, along with the in-database (ALTER ROLE … SET pgrst.*) configuration
source, which outranks both the environment and the config file.

Architecture

There are two supervisors with different jobs. Bier.Application (the OTP
mod:) starts only node-wide infrastructure: Bier.Registry, the process
registry every Bier instance registers through, and Bier.Events.Registry, the
pub/sub registry behind the SSE endpoint. It does not start a web server -
except under BIER_STANDALONE=1, where it additionally boots one instance from
the environment (see Running standalone). Bier itself
is the per-instance Supervisor the host application starts; each instance
owns its config, its Postgrex pool, a DynamicSupervisor, and a dynamically
generated router module.

Boot flow

sequenceDiagram
    participant A as MyApp.Application
    participant B as Bier (Supervisor)
    participant C as Bier.Config
    participant P as Postgrex pool
    participant E as Bier.HttpServerStarter
    participant I as Bier.Introspection
    participant F as Bier.RouterBuilder
    participant G as Bandit
    A->>+B: start_link(name:, router:, …)
    B->>+C: new!/2 (validate opts, defaults from app env)
    C->>-B: %Bier.Config{}
    B->>P: start per-instance pool (via Bier.Registry)
    B->>+E: start_link(config)
    E->>+I: run / functions / media_handlers(pool, db_schemas)
    I->>P: query pg_catalog
    I->>-E: relations, functions, media handlers
    E->>E: stash introspection in :persistent_term
    E->>+F: build(config, relations)
    F->>-E: <name>.Router module
    E->>+G: start Bandit (plug: Router) under the DynamicSupervisor
    G->>-E: listening
    E->>-B: {:ok, state}
    B->>-A: ready

Bier.RouterBuilder.build/2 creates the router with Module.create/3 at runtime,
named <name>.Router. It is a thin catch-all: every request flows through a
fixed plug pipeline (:match → assign_instance → Bier.Plugs.Cors →
Bier.Plugs.Vary → Bier.Plugs.Warning → Bier.Plugs.Observability →
Bier.Plugs.ReadBody → :dispatch) and is then
forwarded to Bier.Plugs.ActionController. Because the router is regenerated on
every boot it is not checked in, and grepping for routes will not find them - edit
the quoted block in RouterBuilder instead.

After HttpServerStarter, the supervisor also starts Bier.SchemaCacheListener
(unless db_channel_enabled: false), which LISTENs on db_channel and swaps
the Bier.SchemaCache snapshot on NOTIFY … 'reload schema'.

Request flow

sequenceDiagram
    participant C as Client
    participant G as Bandit
    participant R as <name>.Router
    participant AC as Bier.Plugs.ActionController
    participant AU as Bier.Auth
    participant QP as Bier.QueryParser
    participant QE as Bier.QueryExecutor
    participant RN as Bier.Response / Render
    participant FC as Bier.Plugs.FallbackController
    C->>+G: HTTP request
    G->>+R: catch-all match
    R->>R: :match → assign_instance → Cors → Vary → Warning → Observability → ReadBody → :dispatch
    R->>+AC: call/2
    AC->>AC: resolve {schema, relation} from path + Accept/Content-Profile
    opt schema requires auth
        AC->>+AU: resolve (JWT verify, SET LOCAL ROLE, request.* GUCs)
        AU->>-AC: auth context
    end
    alt GET / HEAD
        AC->>+QP: parse_request(query_string)
        QP->>-AC: plan (select / filter / order / limit / embed)
        AC->>+QE: run(pool, relation, plan) → one SQL → JSON
        QE->>-AC: {body, count}
        AC->>+RN: render (JSON / CSV / singular / nulls-stripped, Content-Range)
        RN->>-AC: conn
    else POST / PATCH / PUT / DELETE
        AC->>AC: Bier.Mutation.handle (INSERT/UPDATE/DELETE/upsert RETURNING)
    else /rpc/<fn>
        AC->>AC: Bier.Rpc.dispatch (scalar / setof / composite / void)
    end
    alt success
        AC->>-G: %Plug.Conn{}
    else error
        AC->>FC: FallbackController.call (PGRST error envelope)
        FC->>G: %Plug.Conn{}
    end
    G->>-C: response

ActionController resolves the target and method, runs the read/mutation/RPC
path, and lets any non-Plug.Conn return value fall through to
Bier.Plugs.FallbackController, which maps internal reasons and Postgres
SQLSTATEs to HTTP statuses and PostgREST's {code, message, details, hint}
error envelope.

Every request runs as one parameterized SQL statement; the injection-safety
model (what is bound vs. escaped, and why) is in docs/injection_safety.md.

Content negotiation

Responses are rendered in the client's negotiated media type: application/json
(default), text/csv, and application/geo+json. geo+json is offered on
relation reads, on mutations sent with Prefer: return=representation, and on
/rpc/* calls, whenever the PostGIS extension is installed (a target relation
without a geometry column errors with SQLSTATE 22023, mirroring PostgREST).
ST_AsGeoJSON is emitted unqualified and resolves via the session
search_path (matching PostgREST) - a PostGIS installed outside the
search path fails at execution.

Advertised server version

Every response carries Server: bier/<version> - Bier's own mix.exs version
(Bier.version/0), which is also what the OpenAPI document reports as
info.version. It is not configurable: the header is written from a
before_send callback in Bier.Plugs.Observability, so it also reaches the
responses the error funnel builds.

The dialect - which PostgREST release this build is wire-conformant with -
is a separate question, answered by the OpenAPI document's externalDocs URL
(https://postgrest.org/en/v16/…) and by Bier.postgrest_version/0. That
split is deliberate; see the divergence note below.

The query parser

Bier.QueryParser is a generated, dependency-free module built from its
lib/bier/query_parser.ex.exs template via mix gen.parsers (which runs
mix nimble_parsec.compile). nimble_parsec is a dev/test-only dependency -
the shipped code does not depend on it at runtime. Edit the .ex.exs template
and regenerate; never edit the generated .ex directly.

Conformance

Bier reproduces the request/response behavior of PostgREST v16.0, and is
developed against a frozen conformance suite derived from it: 805 cases across
17 areas - URL grammar, operators, select/embedding, filters, ordering,
pagination, representations, mutations, RPC, auth, errors, headers, content
negotiation, OpenAPI, config, observability, and domain representations.
PostgREST is the ground truth - each case cites the exact upstream source line,
and a difference from upstream is treated as a Bier bug.

All 800 active cases pass. Five are excluded: three assert the HTTP reason
phrase, which the test client cannot read (#42), and two pin upstream
behavior Bier deliberately answers differently (see below).

The suite and the behavior models it is built from live under spec/
in the repository, and docs/CONFORMANCE_IMPL.md documents
how it is wired. Neither ships in the package.

Deliberate divergences from PostgREST

PostgREST is the ground truth, and every divergence from it is a bug - with the
short list of exceptions below, where matching upstream would mean reproducing
a defect or misrepresenting what this server is. Each is recorded here so it is
not mistaken for drift.

Server: bier/<version>. Upstream sets Server: postgrest/<version>, and
conformance case 1771 pins that prefix - the one case Bier is knowingly
exempted from. A Server header names the software that built the response,
and wearing another project's product token would route Bier's bugs to
PostgREST's issue tracker. The same reasoning applies to the OpenAPI document's
info.version. What a client can actually act on - which dialect it is
speaking - is still advertised, through externalDocs, which points at the
PostgREST release this build reproduces. The exemption is declared in the
conformance harness rather than by editing the case, so spec/ keeps recording
what PostgREST really does. See
#122.

Unbalanced parentheses in select. Upstream parses the select parameter
with pFieldForest and no eof terminator (QueryParams.hs), so Parsec keeps
the longest valid prefix and silently discards whatever input follows it -
select=name,...processes(process:name,...process_costs(cost))) is accepted
with the stray ) simply never read, and conformance case 11125 pins that
tolerance. Bier answers 400 PGRST100 instead. Matching upstream would mean
accepting arbitrary trailing garbage after a well-formed tree, so a select
truncated by a stray token would come back silently short with a 200. The
same select without the stray ) already returns exactly the body the case
expects, so only the handling of malformed input differs. See
#138.

Vary: Origin on CORS responses. Bier.Plugs.Cors echoes the request's
Origin into Access-Control-Allow-Origin rather than sending *, and a
response whose headers depend on a request header must name that header in
Vary (RFC 9111 §4.1). PostgREST builds its CORS policy with
corsVaryOrigin = False (Cors.hs), so it names nothing: a shared cache may
serve a response stamped Access-Control-Allow-Origin: https://a.example to a
request from https://b.example. Bier emits the union -
Vary: Accept, Prefer, Range, Origin - appended inside the Bier.Plugs.Vary
funnel so the v16 default is not suppressed. A wildcard
Access-Control-Allow-Origin: * is not an echo and stays bare, and CORS
preflight responses are consciously left alone: upstream answers them in the
wai-cors middleware, before the funnel that appends Vary runs at all, so
changing them would be inventing behavior rather than correcting it. See
#98.

CSV quoting. Bier's CSV writer is RFC 4180. Upstream builds CSV bodies from
PostgreSQL's record_out text with the parentheses stripped (asCsvF), which
backslash-escapes and leaves embedded newlines unquoted - a value containing
either yields malformed CSV. Bier renders the cells in SQL (so column order
and numeric text are PostgreSQL's) but keeps its own quoting. See
#110.

Benchmarks

bench/http/ contains a k6 harness that benchmarks Bier against PostgREST
head-to-head: both servers run natively against the same local PostgreSQL under
matched configuration (pool size, schema, anon role, no JWT, no compression,
HTTP/1.1 keep-alive) across four scenarios - single-row read by primary key,
filtered 25-row page, insert, and update by primary key.

The published numbers were measured against PostgREST v16.1 (2026-08),
with the full per-request auth context (role switch + request.* GUCs)
applied on both sides, and with server-side prepared statements enabled on
both sides (PostgREST's db-prepared-statements default; Bier's statement
cache).

On our reference machine (Apple M1 Max, PostgreSQL 17), Bier sustains
2.7-4.2x PostgREST's max throughput depending on the scenario, holds
median latency at parity (within ±7% of PostgREST in every scenario, ahead
on the reads), wins p90 on the read scenarios (and ties on the writes), and
holds a much tighter tail: Bier wins p99 in all four scenarios, staying
under 15 ms in every round, while PostgREST's read-side p99 reached tens of
milliseconds.

Latency is measured open-loop (k6 constant-arrival-rate, immune to
coordinated omission) at a shared arrival rate both servers sustain with zero
dropped iterations, so the comparison is apples-to-apples. These numbers are
a snapshot of one machine, not a universal claim - see
bench/http/REPORT.md
for the full tables and environment, and bench/http/run.sh to reproduce
them.

Development

Development happens in a git checkout - the conformance suite and its fixtures
are not part of the published package.

mix deps.get
mix compile
mix test            # boots a local Postgres fixture DB, then runs the suite
mix format
mix gen.parsers     # regenerate the parser modules after editing a *.ex.exs template

Run every CI gate before pushing with:

mix precommit

which chains, in order: mix deps.unlock --check-unused,
mix format --check-formatted, mix hex.audit,
mix compile --warnings-as-errors, mix credo --strict,
mix docs --warnings-as-errors, and mix test. (CI runs the same steps
individually so each gate reports separately.)

The test suite runs the spec/ submodule's numbered fixture chain
(spec/fixtures/01_roles.sql through 07_analyze.sql) into a local
bier_test database; docs/CONFORMANCE_IMPL.md covers
the database wiring, and CONTRIBUTING.md is the full
contributor guide.

Why "Bier"?

A friend asked what this side project was. I told him it's "like an urn" 🏺 -
a bier is the stand a coffin rests on. He was not amused. The name stuck. The
real motivation is more cheerful: Elixir is my favorite language, I keep falling
deeper into PostgreSQL, and serving a REST API straight from database
introspection is a great excuse to explore both - plus Bandit, Plug,
runtime module generation, and a parser built with NimbleParsec.

Happy hacking!