query/batch.md


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63


Batch Queries
back to query /// home
Run multiple queries of the same type in one request. All should share the same action and table.
Request
{
  "action": "insert",
  "table": "items",
  "queries": [
    { "data": { "name": "Widget A", "price": 10 } },
    { "data": { "name": "Widget B", "price": 20 } },
    { "data": { "name": "Widget C", "price": 30 } }
  ],
  "rollback_on_error": true
}


Fields


Field
Type
Required
Notes


action
string
yes
shared across all sub queries


table
string
yes
shared across all sub queries


queries
array
yes
array of individual query bodies (without action and table)


rollback_on_error
boolean
no
default false. if true, any failure rolls back everything


Each item in queries can have the same fields as a normal single query (columns, data, where, filter, limit, offset, order_by) just without repeating action and table.
Permission
Batch operations should require the allow_batch_operations permission for your power level (on jde_groups). Without it you get a 403. 
Someday better granularity of this permission might be introduced but for now this should suffice.
How it should work

everything runs in a single transaction
for INSERT batches the server should ideally be smart about it and combine them into one multi row INSERT statement instead of doing N individual inserts (way faster)
for SELECT and COUNT each sub query runs individually within the transaction
if rollback_on_error is true and any sub query fails the entire batch should be rolled back and nothing committed
if rollback_on_error is false (default) failures are reported but successful queries still commit

Response
Success looks the same as the individual action responses but with a results array for partial success/failure scenarios.
When rollback_on_error is true and something fails:
{
  "success": false,
  "error": "Batch operation failed, all changes rolled back [request_id: abc123]",
  "results": [
    { "success": true, "rows_affected": 1 },
    { "success": false, "error": "duplicate key" },
    { "success": true, "rows_affected": 1 }
  ]
}