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 section lists what Nano supports today.

Prerequisites

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

1. Start the Server

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.

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

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

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

Select specific columns

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

Order by

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

Limit + offset

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”

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

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

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)

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).

# 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}'

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

# 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}:

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

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 for the embedded API.

The auth endpoints issue JWTs today:

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 for the full auth flow including OAuth2.

10. Pagination

For deep pagination, prefer keyset over offset — see 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.

# 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

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

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 in a browser. The OpenAPI spec is generated live from your schema — every table, every column, every operator. See 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
`Content-Range` header missing	`Prefer: count=*` not sent	Send `Prefer: count=exact` (or `planned`, `estimated`)

Where Next

AUTH_AND_OAUTH — full auth flow, OAuth2 PKCE.
REALTIME_WEBSOCKET — subscribe to row-level changes.
RLS_POLICY_MANAGEMENT — policies through the embedded API.
SWAGGER_UI_QUICKSTART — explore your auto-generated API.
NODEJS_BAAS_APP — full app combining REST + Realtime in TypeScript.