Docs
Contents

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)

ModeEntryAuthenticationCapabilities
User HTTPPOST {endpoint}/sql (endpoint contains /v1)User Session / JWT / cookieLimited single-table queries, controlled two-table INNER JOIN, single-level forward IN, simple single-table DML
Server HTTPSame pathProject API KeyMetadata, queries, controlled DML/DDL (still subject to scope + project isolation)
MySQL wireMySQL protocol endpoint (username=Project ID, password=project API Key)Project API KeyAPI Key capabilities + prepared statements + wire cursors; TLS enforced
Console SQLConsole current project SQL terminalConsole identityInteractive 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

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

  2. SDK path (Console SDK only): point the Client at the application project, then call sql.query / sql.execute, and use params to bind ? parameter markers as needed.

  3. HTTP path: POST {endpoint}/sql, with the body carrying database / sql / params / mode, plus X-Appwrite-Project (and X-Appwrite-Key for the Server mode).

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

  5. Validate the response's truncated / affectedRows; use APPBASE SECURITY (...) for permission-management DDL with all values bound via ?.

SDK category differences (2.6.0 actual surface)

Key parameters and returns

Errors and boundaries

SDK Category Differences

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.

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);

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

const write = await sql.execute({
    databaseId: 'main',
    query: 'UPDATE orders SET status = ? WHERE id = ?',
    params: ['paid', 'order-1']
});
console.log(write.affectedRows, write.truncated);

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

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 SQL

MySQL 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 connection

Example Guide