---
slug: 44-db-api
title: Your DB as a REST API
description: Expose a database as a generic REST API by introspection — sql_tables() + a whitelist, roles, and row-level security as one lambda per table. A pattern, today, with no new features.
example_ids: [db-api]
---

# Your DB as a REST API

**[Build a REST API](/en/0.6.x/43-build-api)** shows the hand-written CRUD. This page is the **generic** version: point Synsema at a database and serve every whitelisted table through the same three routes — discovery by **introspection**, access by **roles**, and per-row filtering by a **row-level-security lambda**. No framework, no codegen, no new language features.

```synsema
-- Doc example: expose your DB as a REST resource — introspection + whitelist + RLS.
-- The pattern is pure userland: sql_tables() discovers the schema, EXPOSED whitelists
-- what's public, and row-level security is one lambda per table. The serve routes on
-- top are in the page (44-db-api); everything testable without a server lives here.
intent: "doc example: DB-as-API — introspection, whitelist, row-level security"
require db(":memory:")

db_open(":memory:")
sql_exec("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
sql_exec("CREATE TABLE carts (id INTEGER PRIMARY KEY, customer_phone TEXT, status TEXT)")
sql_exec("CREATE TABLE internal_audit (id INTEGER PRIMARY KEY, note TEXT)")
sql_exec("INSERT INTO products (name, price) VALUES ('Notebook Aurora 14', 899)")
sql_exec("INSERT INTO carts (customer_phone, status) VALUES ('+549115555', 'open')")
sql_exec("INSERT INTO carts (customer_phone, status) VALUES ('+549119999', 'open')")
sql_exec("INSERT INTO internal_audit (note) VALUES ('never expose me')")

-- The whitelist IS the API surface: introspection finds tables, YOU decide what's public.
let EXPOSED be where(sql_tables(), (t) => contains(["products", "carts"], t))

-- Row-level security: one lambda per table, (row, user) => bool. Discipline, not
-- enforcement — a forgotten rls_allow is a leak, so keep ALL reads behind table_rows.
task rls_allow(t, row, user)
    when t == "carts"
        give user.role == "admin" or row.customer_phone == user.phone
    give true

task table_rows(t, user)
    when not contains(EXPOSED, t)
        give nothing
    let rows be sql("SELECT * FROM " + t)
    give where(rows, (r) => rls_allow(t, r, user))

let ADMIN be {"user": "root", "role": "admin", "phone": ""}
let MARIA be {"user": "maria", "role": "customer", "phone": "+549115555"}

print("exposed tables: " + text(EXPOSED))

test "introspection + whitelist: only public tables are exposed"
    assert(contains(EXPOSED, "products"))
    assert(contains(EXPOSED, "carts"))
    assert(not contains(EXPOSED, "internal_audit"))

test "a non-exposed table is a 404, not an error"
    assert_eq(table_rows("internal_audit", ADMIN), nothing)
    assert_eq(table_rows("no_such_table", ADMIN), nothing)

test "RLS: admin sees every cart, a customer only their own"
    assert_eq(length(table_rows("carts", ADMIN)), 2)
    let mine be table_rows("carts", MARIA)
    assert_eq(length(mine), 1)
    assert_eq((mine[0])["customer_phone"], "+549115555")

test "RLS: tables without a rule are public to any authenticated user"
    assert_eq(length(table_rows("products", MARIA)), 1)
```

## The three pieces

1. **Introspection + whitelist.** `sql_tables()` lists what exists; the whitelist decides what's public. The whitelist IS the API surface — internal tables simply don't exist to the outside:

```synsema
let EXPOSED be where(sql_tables(), (t) => contains(["products", "carts"], t))
```

2. **Row-level security = one lambda per table.** `(row, user) => bool`, applied to every read. An admin sees everything; a customer sees only their rows:

```synsema
task rls_allow(t, row, user)
    when t == "carts"
        give user.role == "admin" or row.customer_phone == user.phone
    give true
```

3. **One gate for every read.** `table_rows(t, user)` checks the whitelist, queries, filters through RLS — and every route calls it. Never query around it.

## The serve wiring

The generic routes sit on top (auth comes from `serve`'s native bearer auth — see **[Serve](/en/0.6.x/40-serve)**):

```synsema
serve on 8080
    auth with check_token

    route "GET /api" requires auth
        give EXPOSED                                  -- discoverable surface

    route "GET /api/:table" requires auth
        let rows be table_rows(params.table, user of request)
        give when rows == nothing then not_found("no such resource") otherwise rows

    route "GET /api/:table/:id" requires auth
        let rows be table_rows(params.table, user of request)
        when rows == nothing
            give not_found("no such resource")
        let hit be find_first(rows, (r) => r.id == number(params.id))
        give when hit == nothing then not_found("no such row") otherwise hit
```

Writes stay explicit — a `POST` route per writable resource, gated by role (`when not contains(WRITERS, u.role)` → `fail(403, ...)`), so the write path validates with `expect body {...}` like any hand-written API.

## This works on Postgres and MySQL, unchanged

The example uses `:memory:` SQLite so it runs offline, but nothing in the pattern is SQLite-specific: `sql()`/`sql_exec()` are engine-routed, and `sql_tables()` introspects each engine's own catalog (`sqlite_master` / `pg_catalog.pg_tables` / `information_schema.tables`). To serve your Postgres, only the connection changes:

```synsema
require db("postgres://localhost/appdb")     -- scope = canonical URL (no credentials)
db_open("postgres://user:pw@localhost:5432/appdb")
```

Everything else — whitelist, RLS, routes — is identical. (Mongo and Redis are different APIs, not SQL: for Mongo build the same pattern over `mongo_collections()` + `mongo_find`; see **[SQL, Mongo & Redis](/en/0.6.x/33-sql-mongo-redis)**.) Note the pattern uses your **application data** (`require db`) — it does not touch the agent's declared memory (`require memory`), which is a separate plane (see **[Memory & state](/en/0.6.x/61-memory)**).

> **Honest warning: this is discipline, not enforcement.** The pattern is airtight only while every read goes through `table_rows` — one route that queries the table directly is a leak. Keep the RLS gate in one task, review any new `sql(...)` in routes.

The same whitelist + RLS declaration also powers the MCP version of this API — see **[Your DB as MCP tools](/en/0.6.x/45-db-mcp)**.