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.

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.

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 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.

Inputs

Response

Every call answers with the JSON envelope 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 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 explains every status code a call can answer with.