BaaS REST API Tutorial

BaaS REST API Tutorial

Available since: v3.8.0 (2026-04-02) 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 ships a PostgREST-compatible REST API in the same binary. Point your client at /rest/v1/{table}, use 20 URL-style filter operators (eq, gt, lte, like, cs, fts, or, …), and reads / writes are RLS-aware via JWT claims out of the box. No SQL on the client. No second service. No drift between your DB and your API.

If you’ve used Supabase or PostgREST, the surface is identical — you can swap the host and the existing client SDKs work unchanged.

---

Prerequisites

• HeliosDB Nano v3.8+ binary (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. The 20 Filter Operators

Filters take the shape ?column=op.value. Combine as many as you like; they AND together.

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)
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
not	NOT	?category=not.eq.tools
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. RLS — Row-Level Security via JWT

The REST endpoints honour Row-Level Security policies defined in SQL. Claims from the bearer JWT populate the session context so policies like tenant_id = current_setting('jwt.tenant_id') evaluate correctly.

Set up a policy

psql "postgresql://postgres:s3cret@127.0.0.1:5432/postgres" <<'SQL'
CREATE TABLE notes (
  id      SERIAL PRIMARY KEY,
  owner   TEXT NOT NULL,
  body    TEXT
);

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY notes_owner ON notes
  USING (owner = current_setting('jwt.email', true));

INSERT INTO notes (owner, body) VALUES
  ('alice@example.com', 'Alice''s note'),
  ('bob@example.com',   'Bob''s note');
SQL

Sign up + token

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)

Fetch with the token — Alice only sees her own row

curl -s "http://localhost:8080/rest/v1/notes" \
  -H "Authorization: Bearer $ALICE_TOKEN" | jq
# [{"id":1,"owner":"alice@example.com","body":"Alice's note"}]

Without the token, the request returns 401 Unauthorized (or empty if the policy is permissive). RLS enforcement happens server-side — the client cannot opt out.

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

0-7/8

# 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)
[] returned but the row is in the DB	RLS policy excludes it for your user	Check pg_rls_policies and current_setting('jwt.*')
Filter operator silently ignored	Typo (?price=gtt.10)	Server doesn’t recognise the prefix; only the 20 above are valid
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 — write production policies.
• SWAGGER_UI_QUICKSTART — explore your auto-generated API.
• NODEJS_BAAS_APP — full app combining REST + Realtime in TypeScript.