---
title: "BaaS REST API Tutorial"
---

# BaaS REST API Tutorial

**Build:** default — no feature flag required
**Endpoints:** `/rest/v1/{table}` (GET/POST/PATCH/DELETE), `/rest/v1/rpc/{function}` (POST)

---

## UVP

Most embedded databases force you to write a server tier just to expose data over HTTP. HeliosDB Nano includes a PostgREST-style REST API in the same binary. Point your client at `/rest/v1/{table}` and filter with URL-style operators (`eq`, `gt`, `lte`, `like`, `in`, …). **No SQL on the client. No second service.**

If you've used Supabase or PostgREST, the URL shape is the same; the [filter operators](#4-filter-operators) section lists what Nano supports today.

:::caution[Known issue]
The `/rest/v1` table routes are a known issue in the current release; see
[Known Limitations](https://www.heliosdb.com/docs/nano/reference/compatibility/known-limitations/#planned).
Until it is fixed, use the PostgreSQL wire protocol (port 5432) for table
access. Row-level security is not applied to REST requests (planned; see
[section 9](#9-row-level-security-planned-for-rest)).
:::

---

## Prerequisites

- HeliosDB Nano binary, current release (`heliosdb-nano --version`)
- A terminal with `curl` and `jq`
- About 10 minutes

---

## 1. Start the Server

```bash
heliosdb-nano start --memory --auth scram-sha-256 --password s3cret
```

Three protocols come up on one process:

| Protocol | Default port | What we'll use it for |
|----------|-------------:|-----------------------|
| PostgreSQL wire | 5432 | Set up the schema |
| MySQL wire | 3306 | (not used in this tutorial) |
| REST / HTTP | 8080 | All the curl examples below |

Open a second terminal for the client commands.

---

## 2. Create the Schema

We'll model a simple e-commerce table. Use `psql` (any PG client works) to set it up — schema management still goes through SQL.

```bash
psql "postgresql://postgres:s3cret@127.0.0.1:5432/postgres" <<'SQL'
CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  category    TEXT,
  price       NUMERIC(10, 2),
  in_stock    BOOLEAN DEFAULT true,
  tags        TEXT[],
  created_at  TIMESTAMP DEFAULT now()
);

INSERT INTO products (name, category, price, tags) VALUES
  ('Widget',  'tools',     9.99,  ARRAY['featured', 'new']),
  ('Gadget',  'tools',     19.99, ARRAY['new']),
  ('Gizmo',   'tools',     29.99, ARRAY['featured']),
  ('Sprocket','machinery', 49.99, ARRAY['popular']),
  ('Bolt',    'hardware',   1.99, ARRAY['featured']),
  ('Nut',     'hardware',   0.49, ARRAY['budget']);
SQL
```

---

## 3. Read Data — `GET /rest/v1/products`

### Select all

```bash
curl -s "http://localhost:8080/rest/v1/products" | jq
```

```json
[
  {"id":1,"name":"Widget","category":"tools","price":"9.99","in_stock":true, ...},
  ...
]
```

### Select specific columns

```bash
curl -s "http://localhost:8080/rest/v1/products?select=id,name,price" | jq
```

### Order by

```bash
curl -s "http://localhost:8080/rest/v1/products?order=price.desc" | jq '.[].name'
```

### Limit + offset

```bash
curl -s "http://localhost:8080/rest/v1/products?order=price.asc&limit=2&offset=1"
```

---

## 4. Filter Operators

Filters take the shape `?column=op.value`. Combine as many as you like; they AND together. Nano's REST filters support `eq`, `neq`, `gt`, `gte`, `lt`, `lte`, `like`, `ilike`, `is` and `in`, with `not.` negation; `cs`, `cd`, `ov`, full-text and `or`/`and` filters are planned.

| Op | SQL | Example |
|----|-----|---------|
| `eq` | `=` | `?category=eq.tools` |
| `neq` | `!=` | `?category=neq.tools` |
| `gt` | `>` | `?price=gt.10` |
| `gte` | `>=` | `?price=gte.10` |
| `lt` | `<` | `?price=lt.50` |
| `lte` | `<=` | `?price=lte.50` |
| `like` | `LIKE` | `?name=like.Wi*` |
| `ilike` | `ILIKE` | `?name=ilike.wi*` |
| `is` | `IS` | `?in_stock=is.true` |
| `in` | `IN` | `?category=in.(tools,hardware)` |
| `not.` prefix | NOT | `?category=not.eq.tools` |

Planned operators (not accepted by the current release):

| Op | SQL | Example |
|----|-----|---------|
| `cs` | `@>` (contains) | `?tags=cs.{featured}` |
| `cd` | `<@` (contained by) | `?tags=cd.{featured,new,popular}` |
| `ov` | `&&` (overlaps) | `?tags=ov.{featured,popular}` |
| `fts` | `@@` to_tsquery | `?description=fts.heliosdb` |
| `plfts` | `@@` plainto_tsquery | `?description=plfts.helios database` |
| `phfts` | `@@` phraseto_tsquery | `?description=phfts.embedded database` |
| `wfts` | `@@` websearch_to_tsquery | `?description=wfts.helios OR cool` |
| `or` | OR | `?or=(price.lt.5,category.eq.machinery)` |
| `and` | AND | `?and=(price.gt.10,price.lt.30)` |

### Compound example

> "All in-stock featured tools or machinery, sorted by price, top 5, returning name + price"

```bash
curl -s "http://localhost:8080/rest/v1/products?\
in_stock=is.true&\
tags=cs.{featured}&\
or=(category.eq.tools,category.eq.machinery)&\
order=price.desc&\
limit=5&\
select=name,price" | jq
```

---

## 5. Insert — `POST /rest/v1/products`

### Single row

```bash
curl -s -X POST "http://localhost:8080/rest/v1/products" \
  -H "Content-Type: application/json" \
  -d '{"name":"Wrench","category":"tools","price":14.99}'
```

### Bulk insert

```bash
curl -s -X POST "http://localhost:8080/rest/v1/products" \
  -H "Content-Type: application/json" \
  -d '[
    {"name":"Hammer","category":"tools","price":12.50},
    {"name":"Drill","category":"tools","price":89.00}
  ]'
```

### Return the inserted row(s)

```bash
curl -s -X POST "http://localhost:8080/rest/v1/products" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"name":"Saw","category":"tools","price":34.99}' | jq
```

`Prefer: return=representation` mirrors PostgREST behaviour — without it the server returns the new resource location only.

---

## 6. Update — `PATCH /rest/v1/products`

Updates always require a filter; otherwise the request is rejected (no accidental whole-table writes).

```bash
# Mark all hardware items as out of stock
curl -s -X PATCH "http://localhost:8080/rest/v1/products?category=eq.hardware" \
  -H "Content-Type: application/json" \
  -d '{"in_stock":false}'
```

```bash
# Bulk price increase
curl -s -X PATCH "http://localhost:8080/rest/v1/products?category=eq.tools" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"price":99.99}' | jq
```

---

## 7. Delete — `DELETE /rest/v1/products`

```bash
# Delete budget hardware
curl -s -X DELETE "http://localhost:8080/rest/v1/products?\
category=eq.hardware&\
tags=cs.{budget}"
```

Same rule: a filter is required.

---

## 8. RPC — Call SQL Functions Over HTTP

Stored procedures and functions are exposed at `/rest/v1/rpc/{function}`:

```sql
CREATE FUNCTION discount(amount NUMERIC, pct NUMERIC) RETURNS NUMERIC
LANGUAGE SQL AS $$
  SELECT amount * (1 - pct / 100);
$$;
```

```bash
curl -s -X POST "http://localhost:8080/rest/v1/rpc/discount" \
  -H "Content-Type: application/json" \
  -d '{"amount": 100, "pct": 15}'
# 85
```

---

## 9. Row-Level Security (planned for REST)

In the stock Nano server, row-level security is enforced through the embedded API and the REPL; SQL `CREATE POLICY` and enforcement for REST and wire clients are planned. The REST API does not apply RLS policies, so enforce per-user access in your service layer. JWT-based RLS on REST is planned; see [RLS Quick Start](https://www.heliosdb.com/docs/nano/guides/rls_quickstart/) for the embedded API.

The auth endpoints issue JWTs today:

### Sign up + token

```bash
curl -s -X POST http://localhost:8080/auth/v1/signup \
  -H 'Content-Type: application/json' \
  -d '{"email":"alice@example.com","password":"s3cret"}'

ALICE_TOKEN=$(curl -s -X POST http://localhost:8080/auth/v1/token \
  -H 'Content-Type: application/json' \
  -d '{"email":"alice@example.com","password":"s3cret"}' | jq -r .access_token)
```

See [AUTH_AND_OAUTH](https://www.heliosdb.com/docs/nano/guides/tutorials/auth_and_oauth_tutorial/) for the full auth flow including OAuth2.

---

## 10. Pagination

For deep pagination, prefer keyset over `offset` — see [KEYSET_PAGINATION_QUICKREF](https://www.heliosdb.com/docs/nano/guides/quickstart/keyset_pagination_quickref/). The REST surface accepts `?order=...&limit=N` and the storage engine will use the constant-time path automatically when the order matches an index.

```bash
# Page 1
curl -s "http://localhost:8080/rest/v1/products?order=id.asc&limit=20" | jq

# Page 2 (keyset — pass the last id seen)
curl -s "http://localhost:8080/rest/v1/products?id=gt.20&order=id.asc&limit=20" | jq
```

---

## 11. Count Modes

```bash
# Exact count via the Prefer header
curl -s -I "http://localhost:8080/rest/v1/products" \
  -H "Prefer: count=exact" | grep -i content-range
# Content-Range: 0-7/8
```

Three modes: `exact` (slowest, accurate), `planned` (planner estimate, fast), `estimated` (cheap heuristic). Pick the one your UI tolerates.

---

## 12. Swagger UI

Open [http://localhost:8080/docs](http://localhost:8080/docs) in a browser. The OpenAPI spec is generated live from your schema — every table, every column, every operator. See [SWAGGER_UI_QUICKSTART](https://www.heliosdb.com/docs/nano/guides/quickstart/swagger_ui_quickstart/).

---

## Troubleshooting

| Symptom | Cause | Fix |
|---------|-------|-----|
| `401 Unauthorized` on a request that worked yesterday | JWT expired | Refresh with `/auth/v1/token?grant_type=refresh_token` |
| `400 Bad Request: PATCH/DELETE without filter` | Whole-table mutation guard | Add a filter parameter (use `?id=neq.0` if you really want everything) |
| Error on a filter | Unknown or planned operator (`?price=gtt.10`, `?tags=cs.{x}`) | Use one of the supported operators in [section 4](#4-filter-operators) |
| `Content-Range` header missing | `Prefer: count=*` not sent | Send `Prefer: count=exact` (or `planned`, `estimated`) |

---

## Where Next

- [AUTH_AND_OAUTH](https://www.heliosdb.com/docs/nano/guides/tutorials/auth_and_oauth_tutorial/) — full auth flow, OAuth2 PKCE.
- [REALTIME_WEBSOCKET](https://www.heliosdb.com/docs/nano/guides/tutorials/realtime_websocket_tutorial/) — subscribe to row-level changes.
- [RLS_POLICY_MANAGEMENT](https://www.heliosdb.com/docs/nano/guides/rls_policy_management_tutorial/) — policies through the embedded API.
- [SWAGGER_UI_QUICKSTART](https://www.heliosdb.com/docs/nano/guides/quickstart/swagger_ui_quickstart/) — explore your auto-generated API.
- [NODEJS_BAAS_APP](https://www.heliosdb.com/docs/nano/guides/tutorials/sdk/nodejs_baas_app/) — full app combining REST + Realtime in TypeScript.