Data API builder — local Postgres setup

This directory runs Microsoft Data API builder (DAB) in a Docker container and exposes a local PostgreSQL table as both a REST and a GraphQL API.

It is configured to serve a table actors in the public schema of a database named filerating, with columns id (primary key), firstname, lastname, and rating_id.

The container connects to a PostgreSQL instance running on the host machine at port 5432. No database connection is made until you start the container with real credentials.

Files

File	Purpose
dab-config.json	DAB configuration: data source, runtime, and entities.
compose.yaml	Runs DAB via Docker Compose, mounting the config.
.env.example	Template for the database connection string.
Dockerfile	Optional: bake the config into a custom image instead.

Prerequisites

• Docker with the Compose plugin (docker compose version).
• A PostgreSQL server running on the host at localhost:5432 with a filerating database and a public.actors table.

DAB container images are x86-64 (amd64) only — ARM64 is not supported.

1. Configure the connection

The connection string is kept out of dab-config.json and injected through an environment variable. The config references it with @env(...):

"connection-string": "@env('DATABASE_CONNECTION_STRING')"

Create your .env from the template and fill in real credentials:

cp .env.example .env

Then edit .env:

DATABASE_CONNECTION_STRING=Host=host.docker.internal;Port=5432;Database=filerating;Username=youruser;Password=yourpassword;

This is an Npgsql (the .NET PostgreSQL driver) connection string, not a libpq URL. Key points:

• Host=host.docker.internal — from inside the container this resolves to the host machine. On Linux this only works because compose.yaml maps it via extra_hosts: host.docker.internal:host-gateway.
• Port=5432 — the host's Postgres port.
• Database=filerating — the database DAB queries.
• Add SSL Mode=Disable; if your local Postgres rejects SSL, or SSL Mode=Require;Trust Server Certificate=true; if it requires it.

Make sure Postgres accepts the connection

Because the container connects over the Docker bridge (not localhost), local Postgres must accept that. On Arch the standard package keeps both files in the data directory (/var/lib/postgres/data/), owned by the postgres user.

• postgresql.conf: listen_addresses = '*'. Default is localhost only, which the container cannot reach. See the exposure note below before using '*'.
• pg_hba.conf: a line permitting the container's source address. Docker Compose runs containers on its own user-defined network (e.g. 172.21.0.0/16), not the default bridge network — so docker network inspect bridge is misleading here. Use the broad Docker range 172.16.0.0/12, which covers all Docker networks. For local dev with trust auth: host all all 172.16.0.0/12 trust. For password auth scoped to one DB/user instead: host filerating youruser 172.16.0.0/12 scram-sha-256. To pin tightly to the compose subnet instead, read it from docker network inspect data-api-builder_default --format '{{range .IPAM.Config}}{{.Subnet}}{{end}}' — but compose may renumber it on recreate, so /12 is more robust.
• Changing listen_addresses needs a restart, not just a reload: sudo systemctl restart postgresql.
• Verify it is listening on the bridge, not only localhost: ss -tlnp | grep 5432 should show 0.0.0.0:5432 (or *:5432).

Exposure note: listen_addresses = '*' binds Postgres to every interface, including your LAN IP — not just the Docker bridge. The port is then reachable by any host that can route to this machine on 5432. pg_hba.conf is what actually gates access: with only the 127.0.0.1, ::1, and 172.16.0.0/12 lines above, a connection from any other address finds no matching rule and is rejected. So outside systems cannot authenticate — but the open port is still visible to them. To avoid exposing it on the LAN at all, bind only the addresses you need instead of '*', e.g. listen_addresses = 'localhost,172.17.0.1' (localhost plus the docker0 gateway), or firewall port 5432 so only the Docker bridge can reach it. Note trust auth means anything on the bridge connects with no password.

2. Run the container

Run all of these from this directory (where compose.yaml lives), with your .env already in place.

Start

docker compose up -d

-d runs it in the background (detached). A healthy start logs that it loaded the config and is listening on port 5000. The container publishes HTTP on http://localhost:5000.

Watch the logs

docker compose logs -f dab      # follow live; Ctrl-C to stop following
docker compose logs dab         # just dump what's there so far

Check status

docker compose ps

Shows whether dab is running. If it exited, the logs above explain why (most often a bad config or a database it cannot reach).

Restart

Needed after editing dab-config.json, since the file is mounted into the running container:

docker compose restart dab

Stop

docker compose stop            # stop the container, keep it around
docker compose down            # stop AND remove the container + network

Use stop if you just want to pause and start it again later; use down for a clean teardown. Neither touches your Postgres data — that lives on the host, not in the container.

docker compose start           # start a container previously `stop`ped

3. Check the APIs

DAB runs in development mode (runtime.host.mode), so the interactive UIs and Swagger are enabled, and the anonymous role has read access — no auth token required.

Test in this order — each step isolates a different layer, so the first one that fails tells you where the problem is.

Step 1 — is DAB itself up?

curl http://localhost:5000/health

A healthy JSON response means the container started and loaded the config. This does not prove it can reach Postgres — it only confirms DAB is listening on 5000. If this hangs or refuses, check docker compose ps and docker compose logs dab.

Step 2 — can DAB reach the database? (REST)

This is the real connectivity test: it makes DAB query Postgres. The entity Actor is published at /api/actor (REST base path /api + entity path /actor).

# All actors — this query round-trips to Postgres
curl http://localhost:5000/api/actor

REST responses are wrapped in a value array:

{ "value": [ { "id": 1, "firstname": "...", "lastname": "...", "rating_id": 3 } ] }

Interpreting the result:

• A value array (even empty {"value":[]}) → the full path works: container → Docker bridge → host Postgres → filerating.public.actors.
• An error, or DAB exits on startup → it can't reach the DB. Check docker compose logs dab. The most common cause on Linux is a missing pg_hba.conf entry for the container's source address (no pg_hba.conf entry for host "172.x.x.x"); see step 1 of the connection setup.

Step 3 — exercise the query options

Once step 2 returns rows:

# A single actor by primary key (id)
curl http://localhost:5000/api/actor/id/1

# Filter, select, and sort (OData-style query params)
curl "http://localhost:5000/api/actor?\$filter=rating_id eq 3"
curl "http://localhost:5000/api/actor?\$select=firstname,lastname"
curl "http://localhost:5000/api/actor?\$orderby=lastname desc&\$first=5"

Swagger / OpenAPI (development mode only): http://localhost:5000/swagger

GraphQL

The GraphQL endpoint is at http://localhost:5000/graphql. Open that URL in a browser to get the interactive Nitro (Banana Cake Pop) explorer, or query it directly:

curl -X POST http://localhost:5000/graphql \
  -H "Content-Type: application/json" \
  -d '{"query":"{ actors { items { id firstname lastname rating_id } } }"}'

The entity exposes:

• actors — a paginated list query (returns items, endCursor, hasNextPage).
• actor_by_pk(id: 1) — fetch one row by primary key.

Example with filtering:

{
  actors(filter: { rating_id: { eq: 3 } }) {
    items {
      id
      firstname
      lastname
    }
  }
}

4. Configuring and extending

All changes are made in dab-config.json. Because the file is mounted as a volume, restart the container to pick up edits:

docker compose restart dab

Allow writes (create / update / delete)

The entity is read-only. To allow mutations for the anonymous role, expand its actions:

"permissions": [
  { "role": "anonymous", "actions": ["create", "read", "update", "delete"] }
]

"actions": ["*"] grants everything. REST then accepts POST /api/actor, PATCH/PUT /api/actor/id/{id}, and DELETE /api/actor/id/{id}; GraphQL exposes createActor, updateActor, and deleteActor mutations.

Add another table

Add a sibling entry under entities. For example, a ratings table:

"Rating": {
  "source": { "object": "public.ratings", "type": "table" },
  "permissions": [ { "role": "anonymous", "actions": ["read"] } ]
}

You can also define a relationships block on Actor to let GraphQL traverse actor -> rating via the rating_id foreign key. See the entities reference.

Rename or hide columns

Use the entity mappings block to expose friendlier field names, e.g. map firstname to first_name in the API surface. See the mappings docs.

Production note

This setup is for local development: anonymous read access, dev UIs enabled, and HTTP only. For anything beyond local use, switch runtime.host.mode to production (disables Swagger/Nitro), add authentication, and terminate TLS at a reverse proxy — the DAB container only serves HTTP unless you supply your own certificate.

Alternative: baked image instead of a volume mount

compose.yaml mounts dab-config.json at runtime, which is convenient for editing. If you prefer an immutable image with the config baked in, use the provided Dockerfile:

docker build -t dab-local:1 .
docker run --name dab --publish 5000:5000 \
  --add-host host.docker.internal:host-gateway \
  --env DATABASE_CONNECTION_STRING="Host=host.docker.internal;Port=5432;Database=filerating;Username=youruser;Password=yourpassword;" \
  --detach dab-local:1

Troubleshooting

Symptom	Likely cause / fix
Container exits immediately	Invalid config JSON. Run docker compose logs dab.
Connection refused / timeout to DB	Postgres not listening on the bridge, or pg_hba.conf blocks the container. See step 1.
password authentication failed	Wrong credentials in .env, or wrong pg_hba.conf auth method.
REST/GraphQL returns empty value	Table empty, or public.actors doesn't exist / wrong schema.
host.docker.internal not resolving	Ensure the extra_hosts entry is present (it is in compose.yaml).