Skip to main content

SQL script mode

Script mode defines APIs by writing SQL and is suitable for complex queries, dynamic conditions, batch writes and stored procedures. The platform uses MyBatis-style parameter binding and dynamic tags.

Create an API​

  1. Go to the Workbench.
  2. Click Script Mode.
  3. Select a data source and schema.
  4. Write SQL and click Parse to extract parameters and response fields.
  5. Complete basic information, request parameters and response parameters in the right panel.
  6. After tests pass, save and release.

Supported statements​

StatementDescription
SELECTQuery, with pagination support
INSERTInsert; can be combined with <foreach> for batch writes
UPDATEUpdate
DELETEDelete
CALLCall a stored procedure (depends on data source support)

For write operations, we recommend enabling Transaction in basic information to ensure consistency across multiple scripts.

Parameter placeholders​

SyntaxDescription
#{name}Prepared parameter, bound by type; recommended
${name}String interpolation with SQL injection risk; the platform restricts interpolated parameters and may return 41012

Example:

SELECT id, order_no, amount
FROM orders
WHERE order_no = #{orderNo}
  AND status = #{status}

Parameter types support String, Array, Int, Float and Object. Array parameters are usually used with <foreach> in SQL.

Dynamic tags​

MyBatis standard tags are supported:

TagPurpose
<if>Conditional fragment
<where>Automatically handles WHERE and leading AND/OR
<set>Automatically handles SET and trailing commas
<foreach>Iterates a collection, for IN queries or batch inserts
<choose> / <when> / <otherwise>Multi-branch selection
<trim>Custom prefix and suffix trimming

if + where​

SELECT id, order_no, amount, status
FROM orders
<where>
  <if test="status != null and status != ''">
    AND status = #{status}
  </if>
  <if test="startTime != null">
    AND created_at &gt;= #{startTime}
  </if>
</where>
ORDER BY created_at DESC

foreach query​

SELECT id, user_name
FROM sys_user
WHERE id IN
<foreach collection="userIds" item="id" open="(" separator="," close=")">
  #{id}
</foreach>

foreach batch insert​

INSERT INTO orders (order_no, amount, status)
VALUES
<foreach collection="orders" item="item" separator=",">
  (#{item.orderNo}, #{item.amount}, #{item.status})
</foreach>

Greater-than and less-than symbols​

When SQL uses > and < together with tags such as <if>, XML parsing conflicts occur and the characters must be escaped:

<if test="amount != null">
  AND amount &lt; #{amount}
</if>

You can also use CDATA:

<![CDATA[
AND amount < #{amount}
]]>

When only > and < are used without dynamic tags, escaping is usually not required.

Parameter parsing​

After you click Parse, the platform scans #{} parameters in the SQL and generates request parameter drafts, and also attempts to parse SELECT response fields.

  • INSERT, UPDATE and DELETE do not support automatic response field parsing; add them manually.
  • Confirm types, required flags and descriptions in the parsing results manually.
  • Custom parameters can be added directly by clicking +.
  • Table mode and proxy mode have different parameter sources; do not mix them.

Pagination​

After pagination is selected in request parameters, callers must pass pageNum and pageSize; missing values return 41010.

  • pageSize has an upper limit of 1000.
  • Pagination parameters are handled uniformly by the platform; do not repeat LIMIT in SQL.
  • If your business SQL performs its own pagination, disable platform pagination to avoid double pagination.

Transactions​

For write operations across multiple scripts, we recommend enabling transactions:

  • Multiple SQL statements in the same API execute in a transaction, and any failure rolls back.
  • Proxy and orchestration modes have different transaction semantics; do not assume cross-service transactions.
  • Transactions inside stored procedures are controlled by the database.

Non-relational data sources​

Limitations for data sources such as Elasticsearch and Hive:

  • Full standard SQL is not supported; the platform only performs common syntax conversions.
  • Dynamic tags such as <if> are not supported.
  • When table or column names contain special characters such as -, use double quotes, for example "test-a".
  • Support for pagination, aggregation and functions depends on the driver implementation.

AI assistance​

The intelligent assistant can:

  • Generate SQL from natural language.
  • Perform read-only previews and provide optimization suggestions for SQL.
  • Save results as Workbench API drafts.

Generated SQL still requires human confirmation of the data source, table names, columns and permission scope.

Common errors​

Error codeCause
40011SQL execution failed; check the syntax and table columns
40014Special characters are not wrapped in double quotes
41010Pagination is enabled but pagination parameters are missing
41011A parameter name uses a runtime reserved word
41012A disallowed SQL interpolation parameter is used