SQL Proxy (controlled SQL access)
Overview and use cases
SQL Proxy is a controlled SQL gateway: requests go through parse → capability/permission checks → logical identifier rewriting → tenant condition injection → execution. It is not a transparent MySQL proxy and cannot bypass the TablesDB permission model. Suitable for: querying and controlled DML/DDL with SQL syntax, BI tool / command-line integration, pointing existing MySQL clients at project tables.
Prerequisites (access modes and authentication)
| Mode | Entry | Authentication | Capabilities |
|---|---|---|---|
| User HTTP | POST {endpoint}/sql (endpoint contains /v1) | User Session / JWT / cookie | Limited single-table queries, controlled two-table INNER JOIN, single-level forward IN, simple single-table DML |
| Server HTTP | Same path | Project API Key | Metadata, queries, controlled DML/DDL (still subject to scope + project isolation) |
| MySQL wire | MySQL protocol endpoint (username=Project ID, password=project API Key) | Project API Key | API Key capabilities + prepared statements + wire cursors; TLS enforced |
| Console SQL | Console current project SQL terminal | Console identity | Interactive queries, structure viewing, controlled management |
Starting with 2.6.0, the Console SQL page no longer displays the MySQL wire host, CA, connection string, or auto-created direct-connection password; project members use the in-page SQL terminal. The wire host/CA/Key must be obtained through a server-side process approved by the instance administrator; do not infer them from frontend or build variables.
Usage workflow
Choose the access mode and load the corresponding identity: browsers use User HTTP (user Session/JWT); server side uses Server HTTP or MySQL wire (project API Key); interactive troubleshooting uses the Console SQL terminal.
SDK path (Console SDK only): point the Client at the application project, then call
sql.query/sql.execute, and useparamsto bind?parameter markers as needed.HTTP path:
POST {endpoint}/sql, with the body carryingdatabase/sql/params/mode, plusX-Appwrite-Project(andX-Appwrite-Keyfor the Server mode).MySQL wire: obtain host/CA/Key from the instance administrator-approved process, connect with TLS enforced, then execute via prepared statements; cursors are used only within the same connection.
Validate the response's
truncated/affectedRows; useAPPBASE SECURITY (...)for permission-management DDL with all values bound via?.
SDK category differences (2.6.0 actual surface)
Only the Console SDK exports the
Sqlservice (the client must point at the application project):sql.query({ databaseId, query, params })(paramsoptional) —— fixedmode=query, returns a row set;sql.execute({ databaseId, query, params })(paramsoptional) —— fixedmode=execute, returns the affected row count;?parameter markers bind theparamsarray in left-to-right order; returnsSqlResponse(containingcolumns/rows/rowCount/affectedRows/truncated).
Neither the Node SDK nor the Web SDK has the
Sqlservice; neither does the Java Console SDK. Web user scenarios can use the underlying HTTP (POST {endpoint}/sqlcarries the user Session; in JWT scenarios dosetJWTfirst).HTTP body:
database(required),sql(required, single statement, multiple statements forbidden),params(optional,?array: JSON scalars / role array / boolean),mode(query|execute, default query, must match statement type). Request headersX-Appwrite-Project/X-Appwrite-Key.
Key parameters and returns
Stable response fields:
columns[{name, type_name}],rows,rowCount,affectedRows(execute),truncated.Row count / response size limits are instance-dependent: when triggered,
truncated=true, and results must not be treated as a complete set; the HTTP shape has no wire cursor, so full batch processing should paginate by business key or use the MySQL wire instead.Permission management SQL (only standard project API Keys with a stable key id):
CREATE TABLE ... APPBASE SECURITY (READ=?, CREATE=?, UPDATE=?, DELETE=?, ROW_SECURITY=?, ENABLED=?)Values must be
?bound (role array / native JSON boolean); literals / expressions / duplicate fields / multiple statements are forbidden. Row actions are only READ / UPDATE / DELETE (no row-level CREATE ACL); actions absent from a PATCH keep their original value, and an explicit[]clears them. Responses may includecommitStatus/operationReference/retrySql;commitStatus=committed_degradedmeans committed but post-effects unknown——recordoperationReference, never retry the original SQL.
Errors and boundaries
Transaction-type statements (BEGIN / COMMIT / ROLLBACK / SET TRANSACTION / autocommit) are
transaction_not_supportedwithout exception——do not treat them as empty-result successes.Not supported: stored procedures, triggers, views, foreign keys, partitioning, user management, GRANT/REVOKE, arbitrary MySQL extensions.
unsupported_capability: entry identity / SQL shape / table mode / security matrix are not published (the database being able to execute ≠ Proxy support).authorization_denied: first verify entry identity, project, table/row permission, and Key scope; do not use a higher-privilege Key to mask User path problems.User RLS row-level semantics: SELECT requires table read permission, or under
rowSecurity=trueonly returns rows readable by the current role; INSERT must hit table create/write; UPDATE/DELETE require table actions or row-level security constraints. Aggregates, GROUP BY, CTEs, UNION, most JOINs/subqueries, and wire cursor are not part of the User path; when permission metadata is unknown, fail closed——the same SQL may return different results for different users.API Key scope mapping: Metadata=
tables.read; SELECT=table read +rows.read; ordinary DML=table read +rows.write; CREATE TABLE=tables.write. Create a dedicated minimal-scope Key for SQL.Enablement status / actual endpoint / TLS / capabilities follow the instance's
/ready(/healthonly reflects process liveness).
SDK Category Differences
Console SDK (console-sdk/nodejs.md): the only
Sqlservice carrier.Server SDK / client SDK (server-sdk/nodejs.md / client-sdk/web.md): no
Sqlservice; use the HTTP shape as needed.The Java Console SDK has no
Sql: use the HTTP API or the JS Console SDK.
Examples by language
The following provides examples by language, item by item along the usage workflow; each card can switch languages (synced across the page).
Query
For the four entry points, first choose the shape and load the identity: browser User HTTP (user Session; in JWT scenarios first setJWT); server-side uses Server HTTP or MySQL wire (project API Key); interactive troubleshooting uses the Console SQL terminal. Among SDKs, only the Console SDK (Node) exposes the Sql service (the Java Console SDK has no Sql; this page provides no per-method Python / Go examples)——this card's tabs cover the Console / Web / REST subset.
- Console (Node.js)
- Web (user session)
- REST (curl)
import { Client, Sql } from 'agentbase-console-sdk';
const client = new Client()
.setEndpoint(process.env.APPBASE_ENDPOINT!) // Must include /v1 and point to the app project
.setProject(process.env.APPBASE_PROJECT_ID!)
.setKey(process.env.APPBASE_API_KEY!);
const sql = new Sql(client);
const result = await sql.query({
databaseId: 'main',
query: 'SELECT id, status FROM orders WHERE status = ? ORDER BY id LIMIT 100',
params: ['pending'] // ? Bind in left-to-right order; do not concatenate user input into SQL
});
console.log(result.rows, result.rowCount, result.truncated);import { Client } from 'agentbase-web-sdk'; // Prerequisite: the user is logged in; for JWT scenarios call client.setJWT(...) first
const endpoint = import.meta.env.VITE_APPBASE_ENDPOINT;
const client = new Client()
.setEndpoint(endpoint)
.setProject(import.meta.env.VITE_APPBASE_PROJECT_ID);
// Web SDK has no Sql service: the underlying client.call carries the current user's Session via the User HTTP path
const result = await client.call(
'POST',
new URL(`${endpoint.replace(/\/+$/, '')}/sql`),
{ 'content-type': 'application/json' },
{ database: 'main', sql: 'SELECT id, status FROM orders WHERE id = ?', params: ['order-1'], mode: 'query' }
);
console.log(result.rows, result.rowCount, result.truncated); // Same output validation as the Console side: truncated=true must not be treated as a complete setcurl -sS -X POST "$APPBASE_ENDPOINT/sql" \
-H 'Content-Type: application/json' \
-H "X-Appwrite-Project: $APPBASE_PROJECT_ID" \
-H "X-Appwrite-Key: $APPBASE_API_KEY" \
-d '{"database":"main","sql":"SELECT id, status FROM orders WHERE status = ? LIMIT 100","params":["pending"],"mode":"query"}'
# Stable response fields: columns / rows / rowCount / affectedRows / truncated; truncated=true must not be treated as a complete setControlled writes (execute)
mode=execute returns the number of affected rows (affectedRows) and also validates truncated——the HTTP shape has no wire cursor, so truncated=true results must not be treated as a complete set. The User path only exposes simple single-table DML; DDL / permission management SQL are Server shape only (project API Key).
- Console (Node.js)
- Web (user session)
- REST (curl)
const write = await sql.execute({
databaseId: 'main',
query: 'UPDATE orders SET status = ? WHERE id = ?',
params: ['paid', 'order-1']
});
console.log(write.affectedRows, write.truncated);// Web SDK has no Sql service: the underlying client.call carries the current user's Session; the User path only supports simple single-table DML
const result = await client.call(
'POST',
new URL(`${endpoint.replace(/\/+$/, '')}/sql`),
{ 'content-type': 'application/json' },
{ database: 'main', sql: 'UPDATE orders SET status = ? WHERE id = ?', params: ['paid', 'order-1'], mode: 'execute' }
);
console.log(result.affectedRows, result.truncated); // execute validates the affected row count; transactional statements always return transaction_not_supported# mode must match the statement type
curl -sS -X POST "$APPBASE_ENDPOINT/sql" \
-H 'Content-Type: application/json' \
-H "X-Appwrite-Project: $APPBASE_PROJECT_ID" \
-H "X-Appwrite-Key: $APPBASE_API_KEY" \
-d '{"database":"main","sql":"UPDATE orders SET status = ? WHERE id = ?","params":["paid","order-1"],"mode":"execute"}'
# Check affectedRows and truncated; transaction statements (BEGIN / COMMIT, etc.) always return transaction_not_supportedPermission management DDL (APPBASE SECURITY)
Only Server shape entry points with a standard project API Key carrying a stable key id are open (User path is not open): permission field values must all be ? bound (role array / native JSON boolean); literals / expressions / duplicate fields / multiple statements are forbidden; actions absent from a PATCH keep their original value, and an explicit [] clears that action.
- REST (curl)
curl -sS -X POST "$APPBASE_ENDPOINT/sql" \
-H 'Content-Type: application/json' \
-H "X-Appwrite-Project: $APPBASE_PROJECT_ID" \
-H "X-Appwrite-Key: $APPBASE_API_KEY" \
-d '{"database":"main","sql":"CREATE TABLE orders (id VARCHAR(255) PRIMARY KEY, status VARCHAR(32)) APPBASE SECURITY (READ = ?, CREATE = ?, UPDATE = ?, DELETE = ?, ROW_SECURITY = ?, ENABLED = ?)","params":[["user:alice"],["users"],["team:ops"],[],true,true],"mode":"execute"}'
# The response may include commitStatus / operationReference / retrySql; commitStatus=committed_degraded means committed but post-effects unknown—record the operationReference and never retry the original SQLMySQL wire(prepared statement)
The wire's host / CA / Key must be obtained through a server-side process approved by the instance administrator; do not infer them from frontend code or build variables. TLS is enforced; username=Project ID, password=project API Key. Each language connects with a standard MySQL client/driver using prepared statements (this card is the CLI shape; there are no per-language SDK tabs).
MYSQL_ENABLE_CLEARTEXT_PLUGIN=1 mysql \
--protocol=tcp \
--enable-cleartext-plugin \
--ssl \
-h "${APPBASE_SQL_PROXY_HOST}" \
-P "${APPBASE_SQL_PROXY_PORT}" \
-u "${APPBASE_PROJECT_ID}" \
-p"${APPBASE_API_KEY}" \
main
# prepared statement parameter count is based on the user's SQL; cursors (DECLARE / OPEN / FETCH / CLOSE) may only be used within the same authenticated connectionExample Guide
JavaScript(Console SDK
sql.query/sql.execute):examples/javascript.md