SQL templates

Download
Focus Mode
Font Size
Last updated: 2026-05-13 11:37:44
What Is an SQL Template
SQL templates provide developers with a way to run SQL commands directly on MySQL-type data models. Using SQL templates, developers can more easily query and manipulate databases with SQL commands. When model methods are insufficient for complex queries, multi-table joins, and similar scenarios, SQL templates can be used to address these issues.
Additionally, SQL templates can be invoked from mini-program clients and Web clients because they are parameterized and include permission conditions by default at runtime. Compared to direct SQL commands, SQL templates are applicable to more scenarios and are more convenient to use.
Usage
The current SQL template is associated with a data model. Consequently, the primary table for related SQL operations is the table corresponding to the associated data model.
On the data model management page, you can go to the SQL template page to create and manage SQL templates.
Creating
When creating an SQL template, you need to enter the template name and SQL command. You can also enter a description to record template-related information.
Template name: It can contain English letters, numbers, and underscores. The template name will be used for subsequent invocations and must be globally unique.
SQL commands: The system supports SELECT, INSERT INTO, UPDATE, DELETE, and REPLACE INTO commands.
Description: It is used to record and describe information related to the SQL template.
Test
You can test-run an SQL template either during or after its creation. During test runs, you need to enter test parameter values according to the parameter format specified in the SQL command.
Custom parameters in SQL statements must be populated before they can be executed.
During testing, select the environment for SQL execution: trial data and production data. If your environment supports distinguishing between trial and production data, you can choose to test on trial data or production data during testing. Testing will directly operate on and affect the data in database tables. Please exercise caution when selecting to run the test.
After a test run, you can view the actual execution results on the results page.
Management
On the SQL template page, you can query the SQL templates corresponding to the current model. You can then modify, test, or delete these SQL templates.
Modifying or deleting an SQL template may affect page components, page queries, code, or other elements that invoke the SQL template. Please proceed with caution.
Invocation
You can invoke the SQL templates under a model by using the model methods in the SDK.
Before using the methods, you must initialize the SDK. For more details about the SDK, you can refer to the Data Model SDK.
Console SQL Template Definition and Execution Example:
﻿﻿
-- SQL corresponding to student_select2
﻿
SELECT * 
FROM `{{ model.tableName() }}` 
WHERE  `start_date` > {{ d_start }}  AND `end_date` < {{ d_end }} 
﻿
Execute the corresponding template using the SDK:
const res = await app.models.student.runSQLTemplate({
      templateName: 'student_select2',
      envType: "pre",  
      params: {
        "d_start": "2025-01-01",
        "d_end": "2026-01-01"
      }
    })
console.log(res)
Method Description:
Invoke the method: models.<modelName>.runSqlTemplate. Replace modelName with the specific data model identifier.
Parameter: templateName. Enter the SQL template name.
Parameter: params. Provide the mapping between variable names and their corresponding values in key-value format. The variable names correspond to the parameters written in the SQL template. The variable values are automatically populated when the SQL template is executed.
Parameter: envType. This parameter indicates whether to operate on trial data or production data. It is optional, with a default value of prod. The options are prod and pre.
SQL Statement
Supported Commands
The current SQL template supports only the following data manipulation commands. It does not support table or database operation commands:
SELECT.
INSERT INTO.
REPLACE INTO.
UPDATE.
DELETE.
Conventions
An SQL template can contain only one SQL command. It does not support multiple SQL commands separated by a semicolon.
The primary table in an SQL template must be the primary table of the current model. Cross-model access is not allowed. However, the child table in a join operation can be any table within the same database. For example, when the current model identifier is student, you can use SELECT * FROM student, but you cannot use SELECT * FROM user. The same rule applies to other commands such as INSERT.
When inserting data (INSERT / REPLACE), you must write the system fields owner and _openid to ensure data security. You can assign values using function expressions (user.userId(), user.openId()).
Parameters
In SQL statements, you can introduce variable parameters using the {{param}} format. Variable parameters are used to pass variable values when the SQL template is actually invoked and executed.
Enclose parameters using the {{ }} notation.
Built-in Functions
The system provides built-in functions to enable the use of SQL templates in conjunction with the system's inherent logic. This allows users to implement part of the model's inherent logic when using SQL templates, such as: row-level permissions, data id generation, and user identification.
Function Category
Function expressions
Description
Model
model.tableName()
Obtains the table name corresponding to the model to which the SQL template belongs, automatically identifying the experience and production environments.
Model
model.tableName('my_model')
Obtains the table name corresponding to the specified model.
Model
model.dataId()
Automatically generates a system-style data ID.
User
user.userId()
Obtains the user identifier of the currently logged-in user at runtime.
User
user.openId()
Obtains the openId of the currently logged-in user at runtime. If the user is logged in via a mini program, the openId corresponds to the WeChat openId.
Permission
auth.rowPermission()
Obtains the model row permissions for the role corresponding to the currently logged-in user at runtime, where the model is the one bound to the SQL template.
Permission
auth.rowPermission('my_model')
Obtains the model row permissions for the role corresponding to the currently logged-in user at runtime, where the model is specified by the user.
System
system.currentEpoch()
Obtains the current 10-digit second-level timestamp.
System
system.currentEpochMillis()
Obtains the current 13-digit millisecond-level timestamp.
The preceding are system-level functions provided by the platform. In addition, you can choose to use MySQL native functions. Refer to MYSQL Built-in Functions.
Model Functions
model.tableName().
model.tableName('my_model_name').
Note:
Obtains the table name corresponding to the model and automatically identifies the trial and production environments.
When the model identifier to which the SQL template belongs is xyz, model.tableName() is equivalent to model.tableName('xyz').
Background:
1. For MySQL-type data models, MySQL tables are used as the underlying storage medium. During the evolution of data models, the naming rules for the tables corresponding to models have version differences. Users can view the model table names in TCB > Database > Model > Basic Information and use them directly in SQL. Alternatively, users can ignore the table naming rules and directly operate on the model tables through functions.
2. Data models incorporate the staging concept, which refers to the trial and production environments. Data between these two environments is isolated at the table level, with different tables used for different environments. Using the functions described above, you can dynamically select the corresponding table based on the execution environment. This allows users to focus only on the type of execution environment without needing to understand the mapping relationship between the environment and the model table names, as the system handles this automatically.
Example:
-- Use table names directly
SELECT * 
FROM `student-preview`;
﻿
-- Use the current model data bound to the SQL template
SELECT * 
FROM {{ model.tableName() }};
﻿
-- Use the joined table data of the student and class models, with the student model currently bound.
SELECT student.*,  class.*
FROM {{ model.tableName() }} as student
INNER JOIN {{ model.tableName('class_info') }} as class
ON class.student_id = student._id;
﻿
model.dataId().
Description: Automatically generates a system-style data ID.
Background: Each model has a primary key identifier named "_id" (a system field). When a user operates a model to create data, the system automatically assigns a string primary key to "_id", which is a globally unique identifier. When writing data in an SQL template, you can manually specify the data identifier or use this function to let the system create it automatically.
Example:
-- Manually write the data identifier
INSERT INTO `my_model_table` (`_id`, `name`, `age`)
VALUES ('xyz', 'Zhang San', 18);
﻿
-- SELECT * FROM `my_model_table`
-- ('xyz', 'Zhang San', 18)
﻿
-- Function writes the data identifier
INSERT INTO `my_model_table` (`_id`, `name`, `age`)
VALUES ({{ model.dataId() }}, 'Zhang San', 18);
﻿
-- SELECT * FROM `my_model_table`
-- ('B3PVJN4U2C', 'Zhang San', 18)
﻿
Permission Functions
auth.rowPermission().
auth.rowPermission('my_model_name').
Note:
At runtime, the model row permissions corresponding to the role of the current logged-in user are obtained. You can specify the model's row permissions or, by default, use the model row permissions to which the SQL template belongs.
When the model identifier to which the SQL template belongs is xyz, auth.rowPermission() is equivalent to auth.rowPermission('xyz').
Background: Models support the configuration of read/write row permissions based on the logged-in user's role, which is used to constrain the user's access behavior to the model.
Example: First, set appropriate model permissions, and then use row permissions through the SQL template.
﻿﻿
Read permissions.
﻿
-- Single-table read permissions
SELECT * 
FROM 'student' 
WHERE `price` > 100 AND {{ auth.rowPermission() }}
-- auth.rowPermission() represents the row permissions of the default model 'student'.
﻿
﻿
-- Multi-table join read permissions
SELECT * 
FROM 'model1_table' AS table1 
LEFT JOIN 'model2_table' AS table2 
ON table2.relate_id = table1._id AND {{ auth.rowPermission('model2') }}
WHERE table1.`price` > 100 AND {{ auth.rowPermission() }}
-- auth.rowPermission() represents the row permissions of the default model 'model1'.
-- auth.rowPermission('model2') represents the row permissions of the specified model 'model2'.
﻿
Write permissions.
-- Only delete data for which the currently logged-in user has write permissions.
DELETE FROM 'student' 
WHERE `age` > 10 AND {{ auth.rowPermission() }}
﻿
-- Only update data for which the currently logged-in user has write permissions.
UPDATE 'student' 
SET `name` = 'Zhang San' 
WHERE {{ auth.rowPermission() }}
﻿
User Functions
user.userId().
user.openId().
Description: This section describes how to obtain the user identifier of the currently logged-in user. Both userId and openId are user identifiers, and they are used in different scenarios. In simple terms:
1. When the logged-in user is from a mini program, openId refers to the openId within that mini program. The openId differs from the userId, where userId represents the system user identifier.
2. When the logged-in user is not from a mini program, userId and openId typically have the same value, which represents the system user identifier.
3. In the system fields of a model, the data for owner, createBy, and updateBy is derived from user.userId(), while the _openid data is derived from user.openId().
Example:
﻿
-- Convention: Write owner and _openId when inserting data.
INSERT INTO 'my_model_table' (`_id`,`name`,`age`, `owner`, `_openid`)
VALUES ('xyz', 'Zhang San', 18, {{ user.userId() }}, {{ user.openId() }})
﻿
System Functions
system.currentEpoch().
system.currentEpochMillis().
Description: Returns a 10-digit second-level timestamp and a 13-digit millisecond-level timestamp.
Background: In MySQL built-in functions, the 10-digit second-level timestamp is represented by UNIX_TIMESTAMP(NOW()), and the 13-digit millisecond-level timestamp is represented by UNIX_TIMESTAMP(NOW(3)) * 1000.
In the system, the 13-digit millisecond-level timestamp is used in most cases, and the model system fields createdAt and updatedAt are stored in this way. To simplify operations, you can use system functions to automate this process.
Example:
﻿
UPDATE 'student' 
SET `name` = 'Zhang San', `updatedAt` = {{ system.currentEpochMillis() }}
WHERE `_id` = 'xyz';
﻿
Best Practices
Recommended Usage
1. Use function expressions for table names: If you are using the table of the model to which the SQL template belongs, use the function model.tableName(). If you are using other tables in the current environment (for joins), use the function model.tableName('your_model_name').
﻿
SELECT * FROM {{ model.tableName() }} as t1
LEFT JOIN {{ model.tableName('other_model_name') }} as t2
ON t2.relate_id = t1._id;
﻿
2. When creating data:
You can manually assign a value to the primary key _id. It is recommended to use the function model.dataId() for assignment.
For the required system fields owner and _openid, assign values using the functions user.userId() and user.openId().
Optionally fill in the system fields createBy and updateBy (using the function user.userId()) and createdAt and updatedAt (using the function system.currentEpochMillis()) based on business needs.
﻿
INSERT INTO {{ model.tableName() }} 
(`_id`, `owner`, `_openid`, `createBy`, `updateBy`, `createdAt`, `updatedAt`)
VALUES
({{ model.dataId() }}, {{ user.userId() }}, {{ user.openId() }}, {{ user.userId() }}, {{ user.userId() }}, {{ system.currentEpochMillis() }}, {{ system.currentEpochMillis() }})
﻿
3. When updating or deleting, record the modification time updatedAt using the function system.currentEpochMillis().
﻿
UPDATE 'student'
SET `name` = 'Zhang San', `updatedAt` = {{ system.currentEpochMillis() }}
WHERE `_id` = 'xyz';
﻿
4. To read and write data with row-level permissions, use the variable auth.rowPermission() or auth.rowPermission('model_name').
Write permissions only constrain updates and deletions. Creation is not constrained.
﻿
-- Write permissions
UPDATE 'student' 
SET `name` = 'Zhang San' 
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}
﻿
﻿
-- Read permissions
SELECT * FROM {{ model.tableName() }}
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}
﻿
Case Studies
Next, we will demonstrate how to perform database operations using SQL templates by combining them with specific scenarios.
Assume the following model exists:
Model identifier: teacher_info
Field
Description
Type
System Fields
name
Teacher name
Text
No
age
Age
Digits
No
is_head_teacher
Whether head teacher
Boolean
No
gender
Gender
Text
No
class_id
Class
Text
No
owner
Owner
Association relationship
Yes
_openid
Record creator
Text
Yes
createBy
Creator
Association relationship
Yes
updateBy
Modifier
Association relationship
Yes
createdAt
Creation time
Date and time
Yes
updatedAt
Update time
Date and time
Yes
Creating Data
Creating Teacher Information
Define an SQL template with the identifier: insert_teacher_info_01.
﻿
INSERT INTO {{ model.tableName() }} 
(`_id`, `name`, `age`, `is_head_teacher`, `gender`, `owner`, `_openid`, `createBy`, `updateBy`, `createdAt`, `updatedAt`)
VALUES
({{ model.dataId() }}, {{ name }}, {{ age }}, {{ is_head_teacher }}, {{ gender }}, {{ user.userId() }}, {{ user.openId() }}, {{ user.userId() }}, {{ user.userId() }}, {{ system.currentEpochMillis() }}, {{ system.currentEpochMillis() }})
﻿
SDK Calls
Use the model identifier teacher_info and the SQL template identifier insert_teacher_info_01 to provide custom variable input parameters.
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'insert_teacher_info_01',
    envType: "pre",
    params: {
      "name": "Teacher Zhang",
      "age": 30,
      "is_head_teacher": true,
      "gender": "male"
    }
  })
﻿
Creating or Updating Data
Update teacher information. If the teacher does not exist, create a new record. (Based on whether a primary key or unique key exists, choose to create or update.)
Note:
 When a primary key or unique field exists, the MySQL execution mechanism performs a delete-then-create operation.
Define an SQL template with the identifier: replace_teacher_info_01.
﻿
REPLACE INTO {{ model.tableName() }} 
(`_id`, `name`, `age`, `is_head_teacher`, `gender`, `owner`, `_openid`, `createBy`, `updateBy`, `createdAt`, `updatedAt`)
VALUES
({{ id }}, {{ name }}, {{ age }}, {{ is_head_teacher }}, {{ gender }}, {{ user.userId() }}, {{ user.openId() }}, {{ user.userId() }}, {{ user.userId() }}, {{ system.currentEpochMillis() }}, {{ system.currentEpochMillis() }})
﻿
SDK Calls
Use the model identifier teacher_info and the SQL template identifier replace_teacher_info_01 to provide custom variable input parameters.
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'replace_teacher_info_01',
    envType: "pre",
    params: {
      "id": "123456", // Update if the id data exists; otherwise, create a new record.
      "name": "Teacher Zhang",
      "age": 28,
      "is_head_teacher": false,
      "gender": "male"
    }
  })
﻿
Syntax
When there are too many input parameters for data insertion, you can use syntactic sugar to simplify the definition of SQL template input parameters by using a single variable array type.
Define an SQL template with the identifier: insert_teacher_info_02.
﻿
INSERT INTO {{ model.tableName() }} 
(`_id`, `name`, `age`, `is_head_teacher`, `gender`, `owner`, `_openid`, `createBy`, `updateBy`, `createdAt`, `updatedAt`)
VALUES
({{ model.dataId() }}, {{ myParam }}, {{ user.userId() }}, {{ user.openId() }}, {{ user.userId() }}, {{ user.userId() }}, {{ system.currentEpochMillis() }}, {{ system.currentEpochMillis() }})
﻿
Call the SDK.
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'insert_teacher_info_02',
    envType: "pre",
    params: {
       "myParam": ["Teacher Wang", 31, true, "female"]
    }
  })
Note: For syntactic sugar input parameters, the array length must match the number of fields in the SQL template, and values must be written in the order of the insertion fields.
In this case, the variable {{ myParam }} in the SQL template is equivalent to {{ name }}, {{ age }}, {{ is_head_teacher }}, {{ gender }}.
Updating Data
Note:
Modify teacher information (writable only when the currently logged-in user has permission).
Define an SQL template with the identifier: update_teacher_info_01.
﻿
UPDATE {{ model.tableName() }} 
SET `name` = {{ name }}, `age` = {{ age }}, `updatedAt` = {{ system.currentEpochMillis() }}
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}
﻿
Call the SDK.
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'update_teacher_info_01',
    envType: "pre",
    params: {
      "name": "Teacher Li",
      "age": 30,
      "id": "123456"
    }
  })
Deleting Data
Note:
Delete teacher information (writable only when the currently logged-in user has permission).
Define an SQL template with the identifier: delete_teacher_info_01.
﻿
DELETE FROM {{ model.tableName() }} 
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}
﻿
Call the SDK.
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'delete_teacher_info_01',
    envType: "pre",
    params: {
      "id": "123456"
    }
  })
Single table queries
Note:
Query a single piece of data by the primary key _id (visible only when the currently logged-in user has permission).
Define an SQL template with the identifier: get_teacher_info_01.
﻿
SELECT * 
FROM {{ model.tableName() }} 
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}
﻿
Call the SDK.
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'get_teacher_info_01',
    envType: "pre",
    params: {
      "id": "123456"
    }
  })
Note:
Query all teacher information for those older than 20 years of age (visible only when the currently logged-in user has permission).
Define an SQL template with the identifier: get_teacher_info_02.
﻿
SELECT * 
FROM {{ model.tableName() }} 
WHERE `age` > {{ age }} AND {{ auth.rowPermission() }}
﻿
Call the SDK.
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'get_teacher_info_02',
    envType: "pre",
    params: {
      "age": 20
    }
  })
Joining Tables
Note:
Perform a fuzzy query for teacher information with the surname Zhang and a correlated query for their corresponding class information (visible only when the currently logged-in user has permission).
Define an SQL template with the identifier: get_teacher_info_03.
﻿
SELECT teacher.* , class.*
FROM {{ model.tableName() }} AS teacher
LEFT JOIN {{ model.tableName('class_info') }} AS class
ON teacher.class_id = class._id
WHERE teacher.`name` LIKE {{ var_name }} AND {{ auth.rowPermission() }}
ORDER BY teacher.`age` DESC
LIMIT 20 OFFSET 0
﻿
Call the SDK.
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'get_teacher_info_03',
    envType: "pre",
    params: {
      "var_name": "Zhang%"
    }
  })
Supplement
In the current scenario, you cannot use the MySQL keywords "IS" and "IS NOT". Use the following syntax as an alternative.
-- Original syntax: Not supported.
select * from `your_table` WHERE your_column IS NULL
-- Alternative syntax: Supported.
select * from `your_table` WHERE your_column <=> NULL
﻿
﻿
-- Original syntax: Not supported.
select * from `your_table` WHERE your_column IS NOT NULL
-- Alternative syntax: Supported.
select * from `your_table` WHERE !(your_column <=> NULL)
﻿
﻿
Help and Support

Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback
tencent cloud

CloudBase

SQL templates

What Is an SQL Template

Usage

Creating

Test

Management

Invocation

SQL Statement

Supported Commands

Conventions

Parameters

Built-in Functions

Model Functions

Permission Functions

User Functions

System Functions

Best Practices

Recommended Usage

Case Studies

Creating Data

Creating Teacher Information

SDK Calls

Creating or Updating Data

SDK Calls

Syntax

Updating Data

Deleting Data

Single table queries

Joining Tables

Supplement

Help and Support

Function Category	Function expressions	Description
Model	model.tableName()	Obtains the table name corresponding to the model to which the SQL template belongs, automatically identifying the experience and production environments.
Model	model.tableName('my_model')	Obtains the table name corresponding to the specified model.
Model	model.dataId()	Automatically generates a system-style data ID.
User	user.userId()	Obtains the user identifier of the currently logged-in user at runtime.
User	user.openId()	Obtains the openId of the currently logged-in user at runtime. If the user is logged in via a mini program, the openId corresponds to the WeChat openId.
Permission	auth.rowPermission()	Obtains the model row permissions for the role corresponding to the currently logged-in user at runtime, where the model is the one bound to the SQL template.
Permission	auth.rowPermission('my_model')	Obtains the model row permissions for the role corresponding to the currently logged-in user at runtime, where the model is specified by the user.
System	system.currentEpoch()	Obtains the current 10-digit second-level timestamp.
System	system.currentEpochMillis()	Obtains the current 13-digit millisecond-level timestamp.
Field	Description	Type	System Fields
name	Teacher name	Text	No
age	Age	Digits	No
is_head_teacher	Whether head teacher	Boolean	No
gender	Gender	Text	No
class_id	Class	Text	No
owner	Owner	Association relationship	Yes
_openid	Record creator	Text	Yes
createBy	Creator	Association relationship	Yes
updateBy	Modifier	Association relationship	Yes
createdAt	Creation time	Date and time	Yes
updatedAt	Update time	Date and time	Yes