SQL over REST

SQL over REST

Submit MySQL-dialect SQL to /sql/<database> and PostgreSQL-dialect SQL to /psql/<database>. The HTTP interface is useful for scripts, jobs, dashboards, and services that do not need a MySQL driver. It uses the same parser, planner, permissions, storage engine, and transaction machinery as the other SQL frontend.

POST keeps credentials and SQL out of the URL and avoids URL-length and escaping problems:

<syntaxhighlight lang="bash"> curl --fail-with-body -u root:strong-password \

 -H 'Content-Type: text/plain; charset=utf-8' \
 --data-binary 'SELECT id, name FROM users ORDER BY id LIMIT 10' \
 http://localhost:4321/sql/myapp

</syntaxhighlight>

GET with URL-encoded SQL after the database name remains a compatibility convenience. Prefer POST for applications and logs.

JSONL results and errors

Row-producing queries return newline-delimited JSON (NDJSON/JSONL): one object per row. Clients can therefore process a large result incrementally instead of waiting for one enclosing array.

<syntaxhighlight lang="json"> {"id": 1, "name": "Ada"} {"id": 2, "name": "Grace"} </syntaxhighlight>

A successful write that emits no result rows returns an affected_rows object. Authentication failures use HTTP 401; SQL failures use a non-success status and a text error body. Always check both status and body. A fast 401 or SQL error is not successful query throughput.

Parameters

URL query parameters are placed into the SQL session and can be referenced as named variables such as @user_id. Keep the SQL text in the POST body and URL-encode values:

<syntaxhighlight lang="bash"> curl --fail-with-body -u root:strong-password \

 --data-binary 'SELECT id, name FROM users WHERE id = @user_id' \
 'http://localhost:4321/sql/myapp?user_id=42'

</syntaxhighlight>

This avoids SQL string concatenation, but applications must still validate type, range, and authorization. Never use a parameter supplied by the caller to choose an unrestricted schema, table, or column name.

PostgreSQL syntax

Use the /psql/<database> endpoint for PostgreSQL-style syntax and JSON operators:

<syntaxhighlight lang="bash"> curl --fail-with-body -u root:strong-password \

 --data-binary "SELECT payload->>'name' FROM events LIMIT 10" \
 http://localhost:4321/psql/myapp

</syntaxhighlight>

This is a syntax frontend, not the PostgreSQL wire protocol and not a promise of complete PostgreSQL compatibility. See Supported SQL and JSON.

Transactions across HTTP requests

Without an explicit session identifier, each request uses its request-local session and automatic transaction handling. To retain transaction state across multiple HTTP requests, send the same unpredictable X-Session-Id header on each request. Treat it like a credential, prevent sharing between users, and always finish with COMMIT or ROLLBACK. For conventional long-lived database sessions, the MySQL protocol is often the simpler interface.

Security and custom APIs

HTTP Basic authentication uses MemCP users and grants. Never expose the development default password, and terminate TLS at a trusted proxy or restrict the network before sending credentials. General SQL access is powerful; browsers and public clients should normally receive a narrowly scoped application endpoint instead.

For custom routing, response formats, authentication, or WebSockets, see In-Database WebApps and REST Services. Also see Security and Authentication, Database Tools compatibility with MemCP, and Performance Measurement.