bier

# Bier

[![CI](https://github.com/milmazz/bier/actions/workflows/elixir.yml/badge.svg)](https://github.com/milmazz/bier/actions/workflows/elixir.yml)
[![Hex.pm](https://img.shields.io/hexpm/v/bier.svg)](https://hex.pm/packages/bier)
[![Documentation](https://img.shields.io/badge/hex-docs-blue.svg)](https://hexdocs.pm/bier)
[![License](https://img.shields.io/hexpm/l/bier.svg)](https://github.com/milmazz/bier/blob/main/LICENSE)

> **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](#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:

```elixir
def deps do
  [
    {:bier, "~> 0.1"}
  ]
end
```

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

```elixir
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.

```elixir
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.:

```sh
# 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](docs/tutorials/getting-started.md) — create the database, boot Bier, and make your first requests.
- [Authentication](docs/tutorials/authentication.md) — add roles and JWTs so only members can post.
- [Realtime](docs/tutorials/realtime.md) — push new rows to the browser with a trigger and the SSE endpoint.

**Reference**

- [API reference](docs/guides/api.md) — reading, filtering, ordering, pagination, embedding, mutations, RPC, time zones, and content negotiation.
- [Configuration](docs/guides/configuration.md) — every option, the `PGRST_*` environment variables, and standalone/Docker/CLI operation.
- [Observability](docs/guides/observability.md) — telemetry events, Server-Timing, health endpoints, and the error envelope.
- [Realtime events](docs/guides/realtime_events.md) — the SSE endpoint: channels, auth, delivery semantics, telemetry.
- [Injection safety](docs/injection_safety.md) — 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):

| Key | Default | Purpose |
|-----|---------|---------|
| `name` | `Bier` | Instance name; also the registry key and `<name>.Router` module. |
| `router` | `[port: 4040, scheme: :http]` | Bandit web-endpoint options. |
| `hostname` / `port` / `database` / `username` / `password` | `localhost` / `5432` / `bier` / — / — | Postgres connection. |
| `pool_size` | `10` | Per-instance Postgrex pool size. |
| `db_schemas` | `["public"]` | Ordered list of exposed schemas; the first is the default. |
| `db_anon_role` | `nil` | Role assumed for unauthenticated requests. |
| `db_extra_search_path` | `["public"]` | Extra schemas appended to the search path. |
| `db_max_rows` | `nil` | Cap on rows returned per request. |
| `db_tx_end` | `:commit` | End each request's transaction with `:commit` or `:rollback`. |
| `db_pre_request` | `nil` | Function run inside every request transaction before the main query. |
| `jwt_secret` / `jwt_aud` | `nil` | JWT verification secret (an HMAC string, or a JWK/JWK Set for RS/ES/PS/EdDSA) and expected audience. |
| `jwt_role_claim_key` | `"$.role"` | RFC 9535 JSON Path to the role inside the JWT claims. |
| `client_error_verbosity` | `"verbose"` | Error envelope shape; `"minimal"` drops `details`/`hint`. |
| `url_use_legacy_target_names` | `true` | Allow filters/orders to address an aliased embed by its relation name (deprecated, warns). |
| `server_cors_allowed_origins` | `nil` | Comma-separated CORS allow-list. |
| `server_timing_enabled` | `false` | Emit a `Server-Timing` header. |
| `server_trace_header` | `nil` | Request header (e.g. `X-Request-Id`) echoed on the response. |
| `log_level` | `:error` | Access-log verbosity. |
| `openapi_mode` | `"follow-privileges"` | How the root OpenAPI document is served; under `follow-privileges`, per-role privilege filtering is cached and refreshes on schema-cache reload. |
| `openapi_version` | `"2.0"` | OpenAPI document version; `"3.0"` emits OpenAPI 3.0.3 (a Bier extension; PostgREST/postgrest#932). |
| `openapi_security_active` | `false` | Advertise JWT security definitions in the OpenAPI document. |

The [configuration guide](docs/guides/configuration.md) 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:

```elixir
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:

```sql
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:

```sql
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](#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

```sh
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`:

```sh
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:

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

Supported `PGRST_*` keys mirror the [Configuration](#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](docs/guides/configuration.md) 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](#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

```mermaid
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

```mermaid
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
`SQLSTATE`s 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](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: 762 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 758 active cases pass.** Four are excluded: three assert the HTTP reason
phrase, which the test client cannot read ([#42][]), and one pins the `Server`
header's product token, which Bier deliberately answers differently (see
below).

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

[#42]: https://github.com/milmazz/bier/issues/42
[spec]: https://github.com/milmazz/bier/tree/main/spec
[conformance-impl]: https://github.com/milmazz/bier/blob/main/docs/CONFORMANCE_IMPL.md

### 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](https://github.com/milmazz/bier/issues/122).

**`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](https://github.com/milmazz/bier/issues/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](https://github.com/milmazz/bier/issues/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.

On our reference machine (Apple M1 Max, PostgreSQL 17), Bier sustains
**1.9–2.5x PostgREST's max throughput** depending on the scenario, wins
median latency in three of the four scenarios (and ties the fourth), trades
p90 (PostgREST is ~1.2–1.35x ahead on three, Bier >2x ahead on the filtered
page), and holds a much tighter tail: Bier's p99 stayed under 4 ms in every
round of every scenario, 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`](https://github.com/milmazz/bier/blob/main/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.

```sh
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:

```sh
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 loads `spec/conformance/fixtures.sql` into a local `bier_test`
database; [`docs/CONFORMANCE_IMPL.md`][conformance-impl] covers the database
wiring, and [CONTRIBUTING.md](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!

[PostgreSQL]: https://www.postgresql.org
[PostgREST]: https://postgrest.org/en/v16/
[Bandit]: https://github.com/mtrudel/bandit
[Plug]: https://hexdocs.pm/plug
[Plug.Router]: https://hexdocs.pm/plug/Plug.Router.html
[Postgrex]: https://hexdocs.pm/postgrex/readme.html
[DBConnection]: https://hexdocs.pm/db_connection
[JOSE]: https://hexdocs.pm/jose
[telemetry]: https://hexdocs.pm/telemetry
[NimbleOptions]: https://hexdocs.pm/nimble_options
[NimbleParsec]: https://hexdocs.pm/nimble_parsec/NimbleParsec.html