SQL (ctx.sql and ctx.device.sql)

Scripts have two SQL entry points. They are different services with different dialects and result shapes.

	ctx.sql.query	ctx.device.sql
Target	The tenant's reporting tables in CoCoCo	A database reached through a configured device
Dialect	MicroSQL, read-only	Whatever the device's database speaks
Placeholders	:name, bindings as a table keyed by name	Positional, in the device database's own syntax (for example ? or $1), bindings as an array in order
Result	{ headers, rows, rowCount }	{ columns, rows, rowsAffected?, executionTimeMs? }

In both, rows is an array of positional arrays. Match each cell to its column by position; rows are not keyed by column name.

Reporting queries: ctx.sql.query

ctx.sql.query(sql: string, bindings: table?) -> (ok, { headers, rows, rowCount })

MicroSQL is a read-only SQL subset over the tenant's reporting tables:

• qualify every column with its table (orders.status, not status);
• SELECT * is rejected;
• ORDER BY needs an explicit ASC or DESC;
• pass values as :name bindings, never by building the string;
• the tenant filter is added for you, so do not add a tenant predicate.

There is no write path through ctx.sql. To change data, call a mutation with ctx.graphql.query.

local SQL = [[
  SELECT orders.id, orders.status
  FROM orders
  WHERE orders.status = :status
  ORDER BY orders.id DESC
  LIMIT 10
]]

local reachable, result = pcall(function()
  local ok, res = ctx.sql.query(SQL, { status = "CONFIRMED" })
  if not ok then error(res) end
  return res
end)

if not reachable then
  ctx.log.warn("reporting query failed", { reason = tostring(result) })
  return { count = 0, orders = {} }
end

local orders = {}
for _, row in ipairs(result.rows) do
  table.insert(orders, { id = row[1], status = row[2] })
end

return { count = result.rowCount, orders = orders }

Errors:

• A query the engine rejects (unknown table, denied resource, syntax error) returns false, "sql.query: …". No matching rows is a success with rowCount == 0.
• A non-string query or malformed bindings raise. If the deployment has no reporting engine configured, the call raises too, which is why the example wraps it in pcall.

To check a query and see its result columns without running it, use the describeQuerySchema GraphQL query. It also suggests corrections for misspelled names:

query CheckReport {
  describeQuerySchema(input: {
    query: "SELECT orders.id, orders.status FROM orders WHERE orders.status = :status ORDER BY orders.id DESC LIMIT 10"
  }) {
    valid
    columns { name }
    errors { message }
    namedParameters
  }
}

Device databases: ctx.device.sql

ctx.device.sql(deviceIdOrName: string, { query: string, bindings: array? })
  -> (ok, { columns, rows, rowsAffected?, executionTimeMs? })

The device is addressed by its ID or its identifier. The connection details and credentials stay in the device configuration; the script never sees them. Device handles also have a sql method, but its rows are not plain values: each cell arrives as a { type, value } table. Use ctx.device.sql when you read the cells.

local ok, result = ctx.device.sql("plant-mes", {
  query = "SELECT id, name FROM parts WHERE id = ? AND active = ?",
  bindings = { 42, true },
})
if not ok then error(result) end

local parts = {}
for _, row in ipairs(result.rows) do
  table.insert(parts, { id = row[1], name = row[2] })
end

return {
  columns = result.columns,
  parts = parts,
  elapsedMs = default(result.executionTimeMs, -1),
}

• bindings is positional and fills the placeholders in order.
• Cells are plain Lua values (numbers, strings, booleans).
• rowsAffected and executionTimeMs are optional; not every device reports them.
• A connection or statement failure returns false, message. An unknown device, or a missing query, raises.

Whether a device database accepts writes depends on the device and its connection settings, not on the script. See Device for handles.