SQL Operations

When the data model SDK cannot meet complex query needs, you can directly use SQL statements to perform database operations.

Tip

SQL Operations are supported only for server-side calls.

Supported SQL Syntax

Currently supported SQL statement types:

SELECT - Data query
INSERT - Data insertion
UPDATE - Data update
DELETE - Data deletion

⚠️ Note: Advanced database features such as transactions (Transaction), stored procedures, and triggers are not supported.

The following describes performing SQL operations using @cloudbase/node-sdk:

Initialize the SDK

Install dependencies

npm install @cloudbase/node-sdk --save

Initialization code

import cloudbase from "@cloudbase/node-sdk"

// Use the require method in the Cloud Function environment
// const cloudbase = require('@cloudbase/node-sdk')

// Initialize the application
const app = cloudbase.init({
    env: env: "your-env-id" // Replace with your environment ID
})

// Obtain the data model instance
const models = app.models

💡 Note: For detailed initialization configurations, please refer to the Initialize the SDK documentation

Query Methods

MySQL data model provides two SQL query methods:

Method	Description	Security	Recommendation
`$runSQL`	Precompiled mode, parameterized queries	High (prevents SQL injection)	⭐⭐⭐
`$runSQLRaw`	Raw mode, directly executes SQL	Low (requires manual safeguards)	⭐

⚠️ Note: Only supported for server-side calls (Cloud Functions, CloudBase, etc.). It is recommended to prioritize using the $runSQL precompiled mode.

Precompiled Mode $runSQL

Using parameterized queries with Mustache syntax ({{ }}) to bind parameters effectively prevents SQL injection.

SELECT Query Example

// Conditional Query
const result = await models.$runSQL(
    "SELECT * FROM `table_name` WHERE title = {{title}} LIMIT 10", 
    { title: "hello" }
);

// Value Comparison
const result = await models.$runSQL(
    "SELECT * FROM `table_name` WHERE read_num > {{num}}", 
    { num: 1000 }
);

// Time Range Query
const result = await models.$runSQL(
    "SELECT * FROM `table_name` WHERE updatedAt > UNIX_TIMESTAMP({{timestamp}}) * 1000", 
    { timestamp: "2024-06-01 00:00:00" }
);

// LIKE Fuzzy Search
const result = await models.$runSQL(
    "SELECT * FROM `table_name` WHERE author_tel LIKE {{tel}}", 
    { tel: "1858%" }
);

// Aggregation
const result = await models.$runSQL(
    "SELECT COUNT(*) as total FROM `table_name` WHERE is_published = {{isPublished}}", 
    { isPublished: true }
);

// Specify fields to query
const result = await models.$runSQL(
    "SELECT read_num, title FROM `table_name` ORDER BY read_num DESC"
);

INSERT/UPDATE/DELETE Example

⚠️ Note: During INSERT operations, the unique identifier _id field must be passed; otherwise, the data will lack a unique identifier, which may cause issues with subsequent query and update operations.

// Insert data (must include the _id unique identifier)
const insertResult = await models.$runSQL(
    "INSERT INTO `table_name` (_id, title, content, author) VALUES ({{id}}, {{title}}, {{content}}, {{author}})",
    { 
        id: id: "article_" + Date.now() + "_" + Math.random().toString(36).substr(2, 9), // Generate a unique ID
        title: title: "New Article", 
        content: content: "Article content", 
        author: // Author's name 
    }
);

// Batch insert data
const batchInsertResult = await models.$runSQL(
    "INSERT INTO `table_name` (_id, title, status) VALUES ({{id1}}, {{title1}}, {{status1}}), ({{id2}}, {{title2}}, {{status2}})",
    { 
        id1: "article_" + Date.now() + "_001",
        title1: title1: "Article 1",
        status1: "published",
        id2: "article_" + Date.now() + "_002", 
        title2: title2: "Article 2",
        status2: "draft"
    }
);

// Update data.
const updateResult = await models.$runSQL(
    "UPDATE `table_name` SET read_num = read_num + 1 WHERE _id = {{id}}",
    { id: "article_id_123" }
);

// Delete data.
const deleteResult = await models.$runSQL(
    "DELETE FROM `table_name` WHERE status = {{status}} AND updatedAt < {{date}}",
    { 
        status: "draft", 
        date: "2024-01-01 00:00:00" 
    }
);

💡 Note: It is recommended to generate a unique identifier for the _id field using the following method:
Timestamp + random string: "prefix_" + Date.now() + "_" + Math.random().toString(36).substr(2, 9)
UUID library: Use the uuid package to generate standard UUIDs.
Business-related ID: Generate a meaningful unique identifier based on business logic.

Result Format

// SELECT Query Return Example
{
    "data": {
        "total": 1,
        "executeResultList": [{
            "_id": "9JXU7BWFZJ",
            "title": "hello",
            "read_num": 997,
            "createdAt": "2024-01-01T00:00:00.000Z",
            "updatedAt": "2024-01-02T00:00:00.000Z"
        }],
        "backendExecute": "27"
    },
    "requestId": "16244844-19fe-4946-8924-d35408ced576"
}

// INSERT/UPDATE/DELETE Operation Return Example
{
    "data": {
        "total": 1,           // number of affected rows
        "executeResultList": [],
        "backendExecute": "15"
    },
    "requestId": "16244844-19fe-4946-8924-d35408ced576"
}

Raw Mode $runSQLRaw

For special scenarios such as dynamic table names, manual handling of SQL injection prevention is required.

Basic Usage

// Execute SQL statement directly
const result = await models.$runSQLRaw(
    "SELECT * FROM `table_name` WHERE title = 'hello' LIMIT 10"
);

// Dynamic table name scenario (ensure table name source safety)
const tableName = getValidTableName(); // Table name security must be verified
const result = await models.$runSQLRaw(
    `SELECT * FROM \`${tableName}\` WHERE status = 'active'`
);

// Complex Query Example
const result = await models.$runSQLRaw(`
    SELECT t1.title, t2.category_name, COUNT(t3.comment_id) as comment_count
    FROM articles t1 
    LEFT JOIN categories t2 ON t1.category_id = t2._id
    LEFT JOIN comments t3 ON t1._id = t3.article_id
    WHERE t1.status = 'published'
    GROUP BY t1._id, t2.category_name
    ORDER BY comment_count DESC
    LIMIT 20
`);

Security Notes

Precompiled Mode Security Recommendations

Prefer: Unless necessary, always use the $runSQL precompiled mode.
Parameter Binding: All user inputs are passed through parameter binding to avoid direct concatenation.
Type Validation: Ensure parameter types match database field types.

Raw Mode Security Requirements

When using $runSQLRaw, the following must be strictly followed:

Input Validation: Strictly validate and filter all user inputs.
Allowlist Mechanism: Only allow predefined safe values (such as table names, field names)
Special Character Escaping: Single quotes, backticks, and other SQL special characters must be properly handled.
Error Handling: Avoid exposing detailed database error information to clients
Permission Control: Ensure the account executing SQL has the minimum necessary permissions.

// ❌ Dangerous example - directly concatenating user input
const userInput = req.body.title;
const result = await models.$runSQLRaw(
    `SELECT * FROM articles WHERE title = '${userInput}'`
);

// ✅ Safe example - using precompiled mode
const result = await models.$runSQL(
    "SELECT * FROM articles WHERE title = {{title}}",
    { title: req.body.title }
);

SQL Operations

Initialize the SDK​

Install dependencies​

Initialization code​

Query Methods​

Precompiled Mode $runSQL​

SELECT Query Example​

INSERT/UPDATE/DELETE Example​

Result Format​

Raw Mode $runSQLRaw​

Basic Usage​

Security Notes​

Precompiled Mode Security Recommendations​

Raw Mode Security Requirements​