# Execute Plain Query API

> Run a SQL statement or a MongoDB command on any instance of the account with the execute plain query system API of API Maker - DDL, DML, joins, stored procedures, database commands - with the permissions of the caller.

Source: https://docs.apimaker.dev/v1/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html

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](/v1/docs/features/database-migration.html) 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`](/v1/examples/sys/system/executeQuery.html) |

## The call

```json title="Body : SQL"
{ "instance": "mysql8", "query": "SELECT status, COUNT(*) AS orders FROM inventory.orders GROUP BY status" }
```

```json title="Answer"
{ "success": true, "statusCode": 200, "data": [ { "status": "PAID", "orders": 128 }, { "status": "NEW", "orders": 7 } ] }
```

```json title="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](/v1/examples/sys/system/executeQuery.html) 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](/v1/docs/apis-security/api-security-report.html) 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](/v1/docs/apis-all/system-apis/system-generated-create-indexes-api.html), which works the same on every database.

## Access and settings

- Over HTTP, a system API answers once its [settings](/v1/docs/settings/systemApiSettings.html) give it `apiAccessType: TOKEN_ACCESS` (the token of an API user whose [group](/v1/docs/apis-security/api-group-permission.html) 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](/v1/docs/apis-all/hooks/preHook-api.html) run around it like around any API.
- The [request headers](/v1/docs/apis-all/header/requestHeader.html) 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. |

## Related

- [All APIs at a glance](/v1/docs/apis-all/overview.html) · [Response format](/v1/docs/apis-all/response-format.html) · [System API settings](/v1/docs/settings/systemApiSettings.html) · [System APIs from code](/v1/examples/sys/system/system.html)