SQL over REST - Revision history

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

2026-08-28T10:14:03Z

Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

← Older revision		Revision as of 12:14, 28 August 2026
Line 7:		Line 7:
	POST keeps credentials and SQL out of the URL and avoids URL-length and escaping problems:		POST keeps credentials and SQL out of the URL and avoids URL-length and escaping problems:

	<~~syntaxhighlight lang="bash"~~>		<pre>
	curl --fail-with-body -u root:strong-password \		curl --fail-with-body -u root:strong-password \
	-H 'Content-Type: text/plain; charset=utf-8' \		-H 'Content-Type: text/plain; charset=utf-8' \
	--data-binary 'SELECT id, name FROM users ORDER BY id LIMIT 10' \		--data-binary 'SELECT id, name FROM users ORDER BY id LIMIT 10' \
	http://localhost:4321/sql/myapp		http://localhost:4321/sql/myapp
	</~~syntaxhighlight~~>		</pre>

	GET with URL-encoded SQL after the database name remains a compatibility convenience. Prefer POST for applications and logs.		GET with URL-encoded SQL after the database name remains a compatibility convenience. Prefer POST for applications and logs.
Line 20:		Line 20:
	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.		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"~~>		<pre>
	{"id": 1, "name": "Ada"}		{"id": 1, "name": "Ada"}
	{"id": 2, "name": "Grace"}		{"id": 2, "name": "Grace"}
	</~~syntaxhighlight~~>		</pre>

	A successful write that emits no result rows returns an <code>affected_rows</code> 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.		A successful write that emits no result rows returns an <code>affected_rows</code> 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.
Line 31:		Line 31:
	URL query parameters are placed into the SQL session and can be referenced as named variables such as <code>@user_id</code>. Keep the SQL text in the POST body and URL-encode values:		URL query parameters are placed into the SQL session and can be referenced as named variables such as <code>@user_id</code>. Keep the SQL text in the POST body and URL-encode values:

	<~~syntaxhighlight lang="bash"~~>		<pre>
	curl --fail-with-body -u root:strong-password \		curl --fail-with-body -u root:strong-password \
	--data-binary 'SELECT id, name FROM users WHERE id = @user_id' \		--data-binary 'SELECT id, name FROM users WHERE id = @user_id' \
	'http://localhost:4321/sql/myapp?user_id=42'		'http://localhost:4321/sql/myapp?user_id=42'
	</~~syntaxhighlight~~>		</pre>

	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.		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.
Line 43:		Line 43:
	Use the <code>/psql/<database></code> endpoint for PostgreSQL-style syntax and JSON operators:		Use the <code>/psql/<database></code> endpoint for PostgreSQL-style syntax and JSON operators:

	<~~syntaxhighlight lang="bash"~~>		<pre>
	curl --fail-with-body -u root:strong-password \		curl --fail-with-body -u root:strong-password \
	--data-binary "SELECT payload->>'name' FROM events LIMIT 10" \		--data-binary "SELECT payload->>'name' FROM events LIMIT 10" \
	http://localhost:4321/psql/myapp		http://localhost:4321/psql/myapp
	</~~syntaxhighlight~~>		</pre>

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

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

2026-08-28T09:59:36Z

Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

← Older revision		Revision as of 11:59, 28 August 2026
Line 1:		Line 1:
	~~There are two methods to do~~ SQL over REST:		<!-- Copyright (C) 2026 Carl-Philip Haensch -->
			<!-- SPDX-License-Identifier: GPL-3.0-or-later -->
			= SQL over REST =

	~~a) GET method:~~ <code>~~curl <nowiki>http://root:admin@localhost:4321~~/sql/</~~nowiki~~>~~[SCHEMA]~~/~~[SQL_QUERY]~~</code>		Submit MySQL-dialect SQL to <code>/sql/<database></code> and PostgreSQL-dialect SQL to <code>/psql/<database></code>. 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.

	b) POST ~~method: <code>curl~~ -~~X POST -d '[SQL_QUERY]' <nowiki>http://root:admin@localhost~~:~~4321/sql/</nowiki>[SCHEMA]</code>~~		POST keeps credentials and SQL out of the URL and avoids URL-length and escaping problems:

	=~~= JSONL results ==~~		<syntaxhighlight lang="bash">
	~~The result is a <code>jsonl<~~/~~code> document containing the result lines:~~		curl --fail-with-body -u root:strong-password \
	~~curl~~ -~~X POST~~ -d '~~select a~~, ~~sum(b) as sum_b from a group by a~~' ~~<nowiki>~~http://~~root:admin@~~localhost:4321/sql/~~test~~</~~nowiki~~>		-H 'Content-Type: text/plain; charset=utf-8' \
	~~returns~~		--data-binary 'SELECT id, name FROM users ORDER BY id LIMIT 10' \
	~~{"a": 1, "sum_b": 7}~~		http://localhost:4321/sql/myapp
	~~{"a": 2, "sum_b": 6}~~		</syntaxhighlight>
	~~{"a": 6, "sum_b": null}~~

	~~== PostgreSQL Syntax Mode ==~~		GET with URL-encoded SQL after the database name remains a compatibility convenience. Prefer POST for applications and logs.
	~~To use~~ the ~~postgresql syntax mode, just use other REST endpoints:~~

	~~a) GET method: <code>curl <nowiki>http://root:admin@localhost:4321/psql/</nowiki>[SCHEMA]/[SQL_QUERY]</code>~~		== JSONL results and errors ==

	~~b) POST method: <code>curl~~ -~~X POST~~ -~~d '[SQL_QUERY]' <nowiki>http:~~/~~/root:admin@localhost~~:~~4321/psql/</nowiki>[SCHEMA]</code>~~		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.

	== ~~Custom REST interfaces directly~~ in ~~MemCP~~ ==		<syntaxhighlight lang="json">
	To ~~create your own~~ custom ~~REST endpoints~~, ~~consider reading about~~ [[In-Database WebApps]]		{"id": 1, "name": "Ada"}
			{"id": 2, "name": "Grace"}
			</syntaxhighlight>

			A successful write that emits no result rows returns an <code>affected_rows</code> 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 <code>@user_id</code>. 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 <code>/psql/<database></code> 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 <code>X-Session-Id</code> 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]].

Carli at 18:11, 20 November 2024

2024-11-20T18:11:10Z

← Older revision		Revision as of 20:11, 20 November 2024
Line 12:		Line 12:
	{"a": 2, "sum_b": 6}		{"a": 2, "sum_b": 6}
	{"a": 6, "sum_b": null}		{"a": 6, "sum_b": null}

			== PostgreSQL Syntax Mode ==
			To use the postgresql syntax mode, just use other REST endpoints:

			a) GET method: <code>curl <nowiki>http://root:admin@localhost:4321/psql/</nowiki>[SCHEMA]/[SQL_QUERY]</code>

			b) POST method: <code>curl -X POST -d '[SQL_QUERY]' <nowiki>http://root:admin@localhost:4321/psql/</nowiki>[SCHEMA]</code>

	== Custom REST interfaces directly in MemCP ==		== Custom REST interfaces directly in MemCP ==
	To create your own custom REST endpoints, consider reading about [[In-Database WebApps]]		To create your own custom REST endpoints, consider reading about [[In-Database WebApps]]

Carli at 08:49, 19 May 2024

2024-05-19T08:49:10Z

← Older revision		Revision as of 10:49, 19 May 2024
Line 12:		Line 12:
	{"a": 2, "sum_b": 6}		{"a": 2, "sum_b": 6}
	{"a": 6, "sum_b": null}		{"a": 6, "sum_b": null}

			== Custom REST interfaces directly in MemCP ==
			To create your own custom REST endpoints, consider reading about [[In-Database WebApps]]

Carli: Created page with "There are two methods to do SQL over REST: a) GET method: `curl http://root:admin@localhost:4321/sql/[SCHEMA]/[SQL_QUERY]` b) POST method: `curl -X POST -d '[SQL_QUERY]' http://root:admin@localhost:4321/sql/[SCHEMA]` == JSONL results == The result is a `jsonl` document containing the result lines: curl -X POST -d 'select a, sum(b) as sum_b from a group by a' http://root:admin@localhost:4321/sq..."

2024-05-19T08:47:48Z

Created page with "There are two methods to do SQL over REST: a) GET method: <code>curl <nowiki>http://root:admin@localhost:4321/sql/</nowiki>[SCHEMA]/[SQL_QUERY]</code> b) POST method: <code>curl -X POST -d '[SQL_QUERY]' <nowiki>http://root:admin@localhost:4321/sql/</nowiki>[SCHEMA]</code> == JSONL results == The result is a <code>jsonl</code> document containing the result lines: curl -X POST -d 'select a, sum(b) as sum_b from a group by a' <nowiki>http://root:admin@localhost:4321/sq..."

New page

There are two methods to do SQL over REST:

a) GET method: <code>curl <nowiki>http://root:admin@localhost:4321/sql/</nowiki>[SCHEMA]/[SQL_QUERY]</code>

b) POST method: <code>curl -X POST -d '[SQL_QUERY]' <nowiki>http://root:admin@localhost:4321/sql/</nowiki>[SCHEMA]</code>

== JSONL results ==
The result is a <code>jsonl</code> document containing the result lines:
curl -X POST -d 'select a, sum(b) as sum_b from a group by a' <nowiki>http://root:admin@localhost:4321/sql/test</nowiki>
returns
{"a": 1, "sum_b": 7}
{"a": 2, "sum_b": 6}
{"a": 6, "sum_b": null}