Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Execute plain query

Runs what the generated APIs can not : a SQL statement as text on a SQL instance, or a database command on MongoDB, and answers what the driver returns. It is the tool of migration scripts and of the few custom APIs which need a join, a stored procedure or a DDL statement.
Method POST
URL /api/system-api/admin/execute-plain-query : admin is the user path of your account
Body { instance, database?, collection?, query }
Answer data : the result of the driver (rows, or the result of the command)
From code g.sys.system.executeQuery

The call

Body : SQL
{ "instance": "mysql8", "query": "SELECT status, COUNT(*) AS orders FROM inventory.orders GROUP BY status" }
Answer
{ "success": true, "statusCode": 200, "data": [ { "status": "PAID", "orders": 128 }, { "status": "NEW", "orders": 7 } ] }
Body : a MongoDB command
{ "instance": "mongodb", "database": "shop", "query": { "dbStats": 1 } }
Key Meaning
instance Required. crm::acme runs on the tenant acme of a multi-tenant instance ; in a call made for a tenant, the plain name is enough.
database Required for PostgreSQL (one connection per database) and MongoDB. Other SQL instances name the database in the statement.
collection MongoDB, for commands which need it.
query The SQL text, or the command object of MongoDB (anything db.command() accepts).

Per database

Database Example
MySQL, MariaDB, TiDB, Percona SELECT * FROM inventory.employees;
SQL Server SELECT * FROM inventory.dbo.employees; · USE inventory; exec dbo.procedureName;
PostgreSQL database: "e_commerce" and SELECT * FROM "public"."customers";
Oracle SELECT * FROM "INVENTORY"."employees" : no semicolon at the end, backticks around the statement in code
MongoDB { "listIndexes": "orders" }, { "createIndexes": "orders", "indexes": [ … ] }, { "dbStats": 1 }
  • Several statements in one call work on the SQL databases whose connection string allows it (multipleStatements=true on MySQL).
  • The code example shows DDL, DML, joins, stored procedures and MongoDB indexes.

Good to know

  • The statement runs with the user of the connection string : it can create, alter and drop. The APIs Security Report lists this API with the sensitive system APIs ; grant it to the groups which really need it.
  • Values from a request must be escaped by you, or better, passed through the generated APIs. For indexes, prefer create indexes, which works the same on every database.

Access and settings

  • Over HTTP, a system API answers once its settings give it apiAccessType: TOKEN_ACCESS (the token of an API user whose group grants this system API, in x-am-authorization) or IS_PUBLIC. Without settings it is NO_ACCESS : your code calls it through g.sys, the admin panel tests it, and an HTTP call is refused.
  • The settings can also cache the answer or require person tokens (authProviders) ; pre and post hooks run around it like around any API.
  • The request headers apply : x-am-response-case, x-am-content-type-response, x-am-internationalization, x-am-tenant-username…

Errors

Code When
400 The body is wrong : the message names the missing or invalid key, Unable to get instance with name … for an unknown instance.
401 No valid API user token, or the API is NO_ACCESS : You are not authorized to access this API.
403 No group grants this system API.