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.

An unknown instance answers 404 : Unable to get instance with name 'x'.

Access, headers and errors¶

Over HTTP, a system API answers only once its settings open it : TOKEN_ACCESS (the token of an API user whose group grants this system API) or IS_PUBLIC. Without settings it is NO_ACCESS : only your code (g.sys) and the admin panel reach it.
The settings can also keep the answer in the cache, or ask for the token of a person (authProviders). Pre and post hooks run around it, as around any API.
It takes the request headers and answers the envelope. 401 : no valid token, or the API is NO_ACCESS. 403 : no group grants it. 400 : the body is wrong, and the message names the key.