# execute_sql

Source: https://modulify.ai/docs/api/database/execute-sql

Runs a SQL statement against a site database and returns the rows.

- Title: Run SQL on the site database
- Scope: `data:sql`
- Access: Destructive
- Endpoint: `POST /v1/execute_sql`

This is the tool for anything the collection tools cannot express: a join, an aggregate, a migration, a bulk update, adding a column, creating an index. It runs whatever you send. There is no read-only mode and nothing blocks `DROP`, `TRUNCATE` or a `DELETE` without a `WHERE`, so a careless statement destroys live content with no undo.

It needs the **Delete projects** permission in the workspace for any statement, so an ordinary member cannot use it, and it sits behind its own `data:sql` scope, which is unticked by default. The tool tells the client to read the shape first with `get_collection_schema`, say back to you exactly what it is about to run, and prefer a `SELECT` to confirm what a destructive statement would touch before running it.

Before a migration, a bulk update or any statement that drops a table or deletes rows, the tool tells the client to read `eligibility` from `list_database_backups`. While that reads `ready` and `canBackup` is true, the client takes a backup with `create_database_backup`, when it has that tool, and waits until the backup reads `ready`. On `unsupported` or `no-database`, no backup of these rows exists or ever will.

## Request

Call it with a `POST` to `https://api.modulify.ai/v1/execute_sql`, sending the inputs below as a JSON object. The token needs the `data:sql` scope.

> **Warning**
>
> This method is marked destructive: it deletes or overwrites data. Check the inputs before you call it, and send an [Idempotency-Key](https://modulify.ai/docs/api/idempotency) header whenever you might retry it.

```bash
curl -X POST https://api.modulify.ai/v1/execute_sql \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"projectId":"PROJECT_ID","sql":"SQL"}'
```

Over MCP, the same method is the [execute_sql tool](https://modulify.ai/docs/mcp/database/execute-sql).

## Write for the engine

The tool tells the client to read `get_site_database` first and write for the engine it reports. The newer database is SQLite and the older one is Postgres, and the two disagree on far more than syntax: SQLite has no `uuid`, `jsonb`, `timestamptz`, array or enum types, no `information_schema`, no plpgsql and no `ADD COLUMN IF NOT EXISTS`, and its catalog is `sqlite_master` with `pragma_table_info` and `pragma_foreign_key_list`.

Postgres syntax sent to the newer database is not reliably refused. `ADD COLUMN IF NOT EXISTS` fails the whole statement, while a Postgres type name such as `uuid` or `jsonb` is accepted quietly and leaves a column that stores the wrong thing, so the table looks created and half works.

On a site on the newer database, a virtual table, such as a full text search table, makes every [database backup](https://modulify.ai/docs/editor/backups#what-cannot-be-backed-up) of that site fail until it is dropped, and stops the site being cloned or duplicated. The tool tells the client not to create one unless you asked for full text search and were told that first.

## Values, caps and results

Pass values through `params` as `$1`, `$2` rather than pasting them into the string, both to avoid quoting bugs and because a value taken from site content is untrusted. Number them from `$1` in the order they appear and use each once: on the newer database they are rewritten positionally, and a reused or transposed number is refused.

Statements are capped at 20,000 characters. The older database stops a statement after 15 seconds, while the newer database has no per statement timeout, so a runaway statement is not stopped for you. At most 200 rows come back, with `truncated` set to true when there were more, and that cap is applied after the database has returned everything, so put your own `LIMIT` and `OFFSET` in the statement.

Several statements separated by semicolons report only the last result, so run them one at a time when you need to know what each did, and on the older database they run only when you pass no `params`. A site with no database yet is refused with a `409` and `This site has no database yet. Create one first, then run the statement again!` See [Running SQL](https://modulify.ai/docs/data/databases#running-sql).

## Inputs

| Input | Type | Required | Description |
| --- | --- | --- | --- |
| `projectId` | string | Yes | The site id. |
| `sql` | string | Yes | The statement to run, at most 20,000 characters. Use `$1`, `$2` placeholders for values rather than interpolating them. |
| `params` | array | No | The values for the `$1`, `$2` placeholders, in order. |

## Response

Every call answers with the [JSON envelope](https://modulify.ai/docs/api/requests-and-responses#the-response) of `success`, `message`, `data`, `code` and `version`. `data` holds the result described above, and on a method that returns a total, `count` carries it. The [response headers](https://modulify.ai/docs/api/requests-and-responses#headers-on-every-method-call) carry the call's `X-Request-Id` and what is left of your per-minute budget in `X-RateLimit-Limit`, `X-RateLimit-Remaining` and `X-RateLimit-Reset`. [Errors](https://modulify.ai/docs/api/errors) explains every status code a call can answer with.