Portable Data Dialect

The portable data dialect lets a workflow express a database query or mutation once — in a backend-neutral, JSONLogic-shaped envelope — and run it unchanged against PostgreSQL, MySQL, SQLite, MongoDB, or Elasticsearch. Switching backend is a connector change, not a rewrite.

Two functions implement it:

• data_query — reads: filter, project, sort, paginate, and include related records.
• data_write — writes: insert, update, delete, and upsert.

The raw functions (db_read, db_write, mongo_read) remain the escape hatch for anything outside the portable vocabulary — hand-written SQL, CTEs, multi-table statements, aggregations.

{
  "name": "data_query",
  "input": {
    "connector": "orders_db",
    "query": {
      "source": "users",
      "filter": { "and": [
        { ">":  [{ "field": "age" }, 25] },
        { "in": [{ "field": "status" }, ["active", "trial"]] }
      ] },
      "fields": ["id", "name", "age"],
      "sort": [{ "age": "asc" }],
      "limit": 50
    },
    "output": "data.users"
  }
}

Point connector at Postgres and this renders parameterized SQL via sea-query; at MongoDB and it renders a find filter document; at Elasticsearch and it renders a Query DSL search body. The results come back as the same JSON row array either way.

The token model

Three tokens appear inside envelopes, and they never collide:

params is the single point where the message touches a query or mutation — it produces literal values, never SQL or query text:

{
  "filter": { "==": [{ "field": "id" }, { "param": "id" }] },
  "params": { "id": { "var": "data.req.id" } }
}

Every resolved value is a bound parameter (SQL) or a document value (Mongo/ES). No user value is ever string-interpolated — the dialect is injection-safe by construction.

Query envelope (data_query)

Operator vocabulary

Anything outside this vocabulary is rejected with a located error naming the operator and position — never silently ignored.

Write envelope (data_write)

One task per operation:

{ "name": "data_write", "input": {
    "connector": "orders_db", "op": "insert", "target": "users",
    "values": [ { "name": "Ada", "status": "active" }, { "name": "Grace", "status": "active" } ],
    "returning": ["id"], "output": "data.created" } }

{ "name": "data_write", "input": {
    "connector": "orders_db", "op": "update", "target": "users",
    "set": { "status": "inactive" },
    "filter": { "==": [{ "field": "id" }, { "param": "id" }] },
    "params": { "id": { "var": "data.req.id" } },
    "output": "data.updated" } }

{ "name": "data_write", "input": {
    "connector": "orders_db", "op": "upsert", "target": "users",
    "values": { "email": "ada@x.io", "name": "Ada" },
    "on_conflict": { "target": ["email"], "action": "update" },
    "output": "data.upserted" } }

Safety guards

• Unfiltered mutations are rejected. An update/delete with no filter would rewrite or truncate the whole table. It fails unless the envelope carries "all": true and the server enables write.allow_unfiltered (default false) — a deliberate double opt-in.
• Bulk inserts are capped. A values array longer than write.max_rows (default 1000) is rejected — never silently truncated.
• Connector operation gates. A connector’s config can disable operation types entirely (operations: { "delete": false }) — see Connector operation gates.

Backend mapping

The filter of an update/delete goes through the exact same rendering path as a query’s WHERE — including relation predicates (some → SQL EXISTS).

Parity or error

The dialect’s governing rule: match the reference semantics where a backend can; raise a precise, located capability error where it cannot; never approximate silently. The notable divergences:

Elasticsearch notes

• _id is explicit. ES keys documents on the metadata _id, which lives outside the document source. There is no implicit id → _id mapping — declare it in the schema ({"columns": {"id": {"name": "_id"}}}). A physical _id column is then lifted into the bulk action / URL path on insert and upsert; mutating _id in set is rejected.
• Read-your-writes. Every ES write requests a refresh (wait_for, or true on the by-query endpoints), so a data_query later in the same pipeline sees the write — parity with SQL/Mongo visibility, at a throughput cost.
• _bulk is ordered but non-transactional. Any item error fails the call, though earlier items may already be applied. Version conflicts on _update_by_query/_delete_by_query surface as errors (conflicts=abort).

Result shapes

Written to the task’s output path:

The schema registry

Both functions accept an optional inline schema — privileged configuration authored alongside the workflow, never built from request input. Without one, the dialect runs in identity mode: names pass through as-is.

"schema": {
  "entities": {
    "users": {
      "table": "app_users",
      "columns": {
        "id":     { "name": "user_id", "type": "int" },
        "email":  { "type": "string" },
        "secret": { "queryable": false, "writable": false }
      },
      "relations": {
        "orders": { "to": "orders", "kind": "has_many", "local": "id", "foreign": "user_id" }
      }
    }
  },
  "unmapped": "reject"
}

• Renames — logical entity/column names map to physical tables/columns (id → user_id, or id → _id for Elasticsearch).
• Types — drive value coercion where a backend needs the hint.
• Allowlist — with "unmapped": "reject", only declared entities/columns are usable; queryable: false hides a column from reads, writable: false protects it from writes (generated/identity columns).
• Relations — declare has_one / has_many / many_to_many (the latter via through) so some/all/none predicates and include work.

Relations and includes

With relations declared, a filter can quantify over related records:

{ "some": [{ "field": "orders" }, { ">": [{ "field": "total" }, 100] }] }

renders as a correlated EXISTS (SQL), $elemMatch over embedded documents (Mongo), or a nested/has_child query (ES). include fetches the related records themselves, hydrated with one child query per relation:

"include": { "orders": { "fields": ["id", "total"], "limit": 10 } }

Connector operation gates

A db or es connector’s config can en/disable operation types, regardless of what workflows ask for. Everything defaults to allowed:

{
  "name": "orders-db-readonly",
  "connector_type": "db",
  "config": {
    "type": "db",
    "connection_string": "postgres://…",
    "operations": {
      "read": true,
      "insert": true,
      "update": false,
      "delete": false,
      "upsert": false,
      "raw_write": false
    }
  }
}

A gated call fails with a validation error naming the operation and connector (operation 'delete' is disabled on connector 'orders-db-readonly'). Because raw SQL cannot be classified per-statement, db_write has its own raw_write gate — to make a connector fully delete-proof, disable both delete and raw_write.

Configuration

[query]                    # Page-size bounds for data_query
# default_limit = 100      # Page size when a query omits `limit`
# max_limit = 1000         # Hard cap; a query asking for more is rejected

[write]                    # Safety bounds for data_write
# max_rows = 1000          # Cap on rows per bulk insert/upsert (over → rejected)
# allow_unfiltered = false # Permit unfiltered update/delete (still needs per-call "all": true)

Both sections are overridable via environment variables (ORION_QUERY__MAX_LIMIT, ORION_WRITE__MAX_ROWS, …).