Database SQL Operations

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

This method only supports server-side invocation.

Applicable Scenarios

• Complex multi-table join queries
• Aggregate functions and statistical queries
• Database-specific features
• Performance optimization queries

Query Method

The MySQL data model provides two SQL query methods:

Method	Description	Security	Recommendation Level
$runSQL	Precompiled mode, parameterized queries	High (prevents SQL injection)	⭐⭐⭐
$runSQLRaw	Raw mode, directly executes SQL	Low (requires self-protection)	⭐

⚠️ Important Reminder

• Supported only for server-side invocation (Cloud Functions, Cloud Run, etc.)
• Currently supports CRUD statements
• It is recommended to prioritize using the $runSQL precompiled mode

Initialize the SDK

Please refer to the node-sdk initialization method in SDK Initialization to initialize.

1. Installation

npm install @cloudbase/node-sdk --save

2. Initialization

import cloudbase from "@cloudbase/node-sdk"

// In a cloud function environment, you need to load node-sdk using the require method
// const cloudbase = require('@cloudbase/node-sdk')

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

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

Precompiled Mode $runSQL

Use parameterized queries to bind parameters through Mustache syntax ({{ }}), effectively preventing SQL injection.

Basic Query

// 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 query
const result = await models.$runSQL(
    "SELECT * FROM `table_name` WHERE updatedAt > UNIX_TIMESTAMP({{timestamp}})", {
        timestamp: "2024-06-01 00:00:00"
    }
);

Fuzzy Query and Aggregation

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

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

// Specified fields
const result = await models.$runSQL(
    "SELECT read_num, title FROM `table_name`"
);

Response format

// Successful response example
{
    "data": {
        "total": 1,
        "executeResultList": [{
            "_id": "9JXU7BWFZJ",
            "title": "hello",
            "read_num": 997,
            // ... Other fields
        }],
        "backendExecute": "27"
    },
    "requestId": "16244844-19fe-4946-8924-d35408ced576"
}

Raw Mode $runSQLRaw

For special scenarios such as dynamic table names, it is necessary to handle SQL injection prevention on their own.

Basic Usage

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

// Dynamic table name scenario
const tableName = getTableName(); // Ensure source security
const result = await models.$runSQLRaw(
    `SELECT * FROM \`${tableName}\` WHERE status = 'active'`
);

Security Considerations

The following security measures must be observed when using $runSQLRaw:

• Prefer using precompiled mode: Unless necessary, it is recommended to use `$runSQL
• Validate User Input: Strictly validate and filter all user inputs
• Use allowlist: Only predefined safe values are allowed.
• Escape Special Characters: Pay special attention to SQL special characters like single quotes.
• Error Handling: Avoid exposing detailed database error messages