JSON DSL Reference

Every Pulsabase SDK (TypeScript, Dart) compiles method chains into a structured JSON DSL sent to POST /v1/query. Understanding this format is useful when:

Calling the API directly with cURL or HTTP clients like Insomnia/Postman
Building a custom client in an unsupported language
Debugging what the SDK sends on the wire

Inspecting the DSL from the SDK

You don’t need to write DSL by hand to understand it. Every QueryBuilder chain exposes .toPayload() which returns the exact JSON the SDK would send — no request is made:

// See exactly what DSL your query compiles to
const payload = pb.from(User)
  .where('status', 'active')
  .orderBy('created_at', 'desc')
  .limit(10)
  .toPayload();

console.log(JSON.stringify(payload, null, 2));
// {
//   "action": "find",
//   "table": "users",
//   "query": { "status": "active" },
//   "sort": [{ "field": "created_at", "order": "desc" }],
//   "limit": 10
// }

This is useful for debugging, logging, or porting a query to a plain cURL call.

Base Request Format

curl -X POST https://api.yourproject.pulsabase.io/v1/query \
  -H "X-Api-Key: pk_live_your_api_key" \
  -H "Authorization: Bearer <user_access_token>" \
  -H "Content-Type: application/json" \
  -d '<JSON DSL payload>'

The Authorization: Bearer header is optional — required only when the table has RLS policies that depend on the authenticated user.

DSL Structure

{
  "action":  "<action>",
  "table":   "table_name",
  "query":   {},
  "select":  [],
  "sort":    [],
  "join":    [],
  "with":    [],
  "limit":   100,
  "offset":  0,
  "payload": {},
  "distinct": false,
  "returning": [],
  "having": {}
}

Only action and table are required. All other fields are optional.

Actions Reference

Action	SDK Equivalent	Description
`find`	`.find()`	SELECT multiple rows
`findOne`	`.findOne()`	SELECT single row
`insert`	`.insert(payload)`	INSERT one or many rows
`update`	`.update(payload)`	UPDATE rows matching `query`
`delete`	`.delete()`	DELETE rows matching `query`
`upsert`	`.upsert(payload, onConflict)`	INSERT … ON CONFLICT DO UPDATE
`bulkUpdate`	`.bulkUpdate(instructions)`	Multiple targeted updates
`bulkDelete`	`.bulkDelete(instructions)`	Multiple targeted deletes
`insertWith`	`.insertWith(payload, relations)`	Nested insert with relations
`transaction`	`pb.transaction([...])`	Atomic multi-operation batch
`fts`	`.whereFts(fields, query)`	PostgreSQL full-text search
`spatial`	`.whereSpatial(...)`	PostGIS spatial queries
`vectorSearch`	`.whereVector(...)`	pgvector similarity search
`relationAttach`	`.attach(ids)`	M2M pivot attach
`relationDetach`	`.detach(ids)`	M2M pivot detach
`relationSync`	`.sync(ids)`	M2M pivot sync
`relationUpdatePivot`	`.updatePivot(id, data)`	Update pivot columns
`relationToggle`	`.toggle(ids)`	M2M toggle

`query` (WHERE filters)

The query object maps column names to filter values.

Equality (default)

{ "status": "active" }

Equivalent to WHERE status = 'active'

Comparison Operators

{
  "age": { "$gt": 18 },
  "price": { "$lte": 100 },
  "score": { "$between": [80, 100] }
}

Operator	SQL
`$eq`	`=`
`$ne`	`!=`
`$gt`	`>`
`$gte`	`>=`
`$lt`	`<`
`$lte`	`<=`
`$between`	`BETWEEN x AND y`

String Matching

{ "name": { "$ilike": "%john%" } }

Operator	SQL
`$like`	`LIKE '%x%'` (case-sensitive)
`$ilike`	`ILIKE '%x%'` (case-insensitive)

List Operators

{
  "role": { "$in": ["admin", "moderator"] },
  "status": { "$nin": ["banned", "deleted"] }
}

Null Checks

{ "deleted_at": null }

Results in WHERE deleted_at IS NULL

OR Conditions

{
  "$or": [
    { "status": "active" },
    { "role": "admin" }
  ]
}

Multiple AND Conditions (default when not using $or)

{
  "status": "active",
  "age": { "$gte": 18 }
}

Results in WHERE status = 'active' AND age >= 18

`select` (Column Selection)

{ "select": ["id", "name", "email"] }

Aggregation Functions

{
  "select": [
    "status",
    { "Function": "COUNT", "Field": "*", "As": "total" },
    { "Function": "AVG",   "Field": "price", "As": "avg_price" }
  ]
}

Supported functions: COUNT, SUM, AVG, MIN, MAX, UPPER, LOWER, CONCAT, COALESCE, NOW, ROUND, LENGTH, TRIM.

`sort`

{
  "sort": [
    { "field": "created_at", "order": "desc" },
    { "field": "name", "order": "asc", "nulls": "last" }
  ]
}

nulls options: "first" or "last" (PostgreSQL NULLS FIRST / NULLS LAST).

`join`

Structured SQL JOINs (no raw SQL — all validated):

{
  "action": "find",
  "table": "users",
  "join": [
    {
      "table": "orders",
      "type": "LEFT",
      "on": [
        {
          "left":  { "table": "users",  "column": "id" },
          "op":    "=",
          "right": { "table": "orders", "column": "user_id" }
        }
      ]
    }
  ],
  "select": ["users.name", "orders.total"]
}

type options: "INNER", "LEFT", "RIGHT", "FULL", "CROSS".

`with` (Eager Loading)

{
  "action": "find",
  "table": "rooms",
  "with": [
    {
      "relation": "members",
      "select": ["id", "email"],
      "limit": 50
    }
  ]
}

`insert` Payload

Single record

{
  "action": "insert",
  "table": "users",
  "payload": { "name": "Jane", "email": "jane@example.com" }
}

Multiple records (bulk insert)

{
  "action": "insert",
  "table": "users",
  "payload": [
    { "name": "Jane", "email": "jane@example.com" },
    { "name": "John", "email": "john@example.com" }
  ]
}

`update` Payload

{
  "action": "update",
  "table": "users",
  "query": { "id": "uuid-123" },
  "payload": { "status": "banned" }
}

`upsert`

{
  "action": "upsert",
  "table": "user_settings",
  "payload": { "user_id": "uuid-123", "theme": "dark" },
  "on_conflict": {
    "columns": ["user_id"],
    "do": "update"
  }
}

`transaction`

Execute multiple operations atomically:

{
  "action": "transaction",
  "requests": [
    {
      "action": "update",
      "table": "accounts",
      "query": { "id": "acc-1" },
      "payload": { "balance": 50 }
    },
    {
      "action": "update",
      "table": "accounts",
      "query": { "id": "acc-2" },
      "payload": { "balance": 150 }
    },
    {
      "action": "insert",
      "table": "audit_logs",
      "payload": { "action": "transfer", "amount": 50 }
    }
  ]
}

`groupBy` + `having`

{
  "action": "find",
  "table": "orders",
  "select": ["status", { "Function": "COUNT", "Field": "*", "As": "total" }],
  "groupBy": ["status"],
  "having": { "total": { "$gt": 10 } }
}

Full-Text Search (`fts`)

{
  "action": "fts",
  "table": "articles",
  "query": {
    "$fts": {
      "fields": ["title", "body"],
      "query": "typescript tutorial",
      "language": "english"
    }
  }
}

SDK equivalent:

await pb.from(Article).whereFts(['title', 'body'], 'typescript tutorial', { language: 'english' }).find();

Vector Search (`vectorSearch`)

Requires a vector(n) column and the pgvector extension.

{
  "action": "vectorSearch",
  "table": "documents",
  "query": {
    "$vector": {
      "field": "embedding",
      "value": [0.1, 0.2, 0.3, "..."],
      "operator": "<->",
      "limit": 10,
      "threshold": 0.8
    }
  }
}

Field	Description
`field`	The `vector(n)` column to search
`value`	Query embedding — array of floats, same dimension as the column
`operator`	Distance function: `<->` (L2/Euclidean), `<#>` (negative inner product), `<=>` (cosine distance)
`limit`	Number of nearest neighbors to return
`threshold`	Max distance threshold (optional — filters out results beyond this distance)

SDK equivalent:

const embedding = await getEmbedding('search query'); // your embedding function

await pb.from(Document)
  .whereVector('embedding', embedding, { operator: '<=>', limit: 10, threshold: 0.5 })
  .select('id', 'title', 'content')
  .find();

Spatial Query (`spatial`)

Requires a geometry(...) column and the PostGIS extension.

{
  "action": "spatial",
  "table": "places",
  "query": {
    "$spatial": {
      "field": "location",
      "function": "ST_DWithin",
      "geo": "POINT(2.3488 48.8534)",
      "srid": 4326,
      "distance": 5000
    }
  }
}

Field	Description
`field`	The `geometry(...)` column
`function`	PostGIS function: `ST_DWithin`, `ST_Within`, `ST_Intersects`, `ST_Contains`
`geo`	Reference geometry in WKT format (e.g. `POINT(lng lat)`, `POLYGON(...)`)
`srid`	Spatial Reference ID (default: `4326` — WGS84)
`distance`	Distance in meters (for `ST_DWithin`)

SDK equivalent:

// Find all places within 5km of a point
await pb.from(Place)
  .whereSpatial('location', {
    function: 'ST_DWithin',
    geo: 'POINT(2.3488 48.8534)',
    distance: 5000,
  })
  .select('id', 'name', 'location')
  .find();

Spatial SELECT — distance or transformed geometry:

{
  "action": "find",
  "table": "places",
  "select": [
    "id",
    "name",
    { "SpatialFunction": "ST_Distance", "Field": "location", "Ref": "POINT(2.3488 48.8534)", "As": "distance_m" },
    { "SpatialFunction": "ST_AsGeoJSON", "Field": "location", "As": "geojson" }
  ],
  "sort": [{ "field": "distance_m", "order": "asc" }]
}

JSON DSL Reference

Inspecting the DSL from the SDK

Base Request Format

DSL Structure

Actions Reference

query (WHERE filters)

Equality (default)

Comparison Operators

String Matching

List Operators

Null Checks

OR Conditions

Multiple AND Conditions (default when not using $or)

select (Column Selection)

Aggregation Functions

sort

join

with (Eager Loading)

insert Payload

Single record

Multiple records (bulk insert)

update Payload

upsert

transaction

groupBy + having

Full-Text Search (fts)

Vector Search (vectorSearch)

Spatial Query (spatial)

`query` (WHERE filters)

`select` (Column Selection)

`sort`

`join`

`with` (Eager Loading)

`insert` Payload

`update` Payload

`upsert`

`transaction`

`groupBy` + `having`

Full-Text Search (`fts`)

Vector Search (`vectorSearch`)

Spatial Query (`spatial`)