Custom SQL Tutorial

This tutorial walks you through configuring and using a custom SQL query on the Lovrabet platform — from scratch, all the way to a complete data query feature.

💡 What you'll learn By the end of this tutorial, you will know how to:

📋 Prerequisites

Before you start, make sure you have:

✅ A registered Lovrabet platform account
✅ An app created, with its AppCode
✅ @lovrabet/sdk installed (>= 1.1.19)
✅ An AccessKey (server side) or an active platform login (browser)

If any of these are missing, see the quick start guide first.

Step 1: Create a Custom SQL on the Platform

1.1 Open the SQL Management Page

Sign in to the Lovrabet platform
Select your app
Open SQL management: [App Configuration] → [App Assets] → [Custom SQL Management]

1.2 Create a New SQL Query

Click the "New SQL" button in the top-right corner and fill in the following:

Basic Information

Field	Description	Example
SQL name	Name of the query (required)	`Active user statistics`
SQL description	What the query is for (optional)	`Login and action counts for active users over the last 7 days`
SQL group	For easier management (optional)	`User statistics`

SQL Statement

Enter your SQL in the SQL editor.

Beginner example:

SQL

-- 简单查询：查询所有活跃用户
SELECT
  id,
  name,
  email,
  created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100

Advanced example:

SQL

-- 复杂统计：查询最近7天活跃用户统计
SELECT
  u.id,
  u.name,
  u.email,
  COUNT(DISTINCT l.id) AS login_count,
  COUNT(DISTINCT a.id) AS action_count,
  MAX(l.login_time) AS last_login_time
FROM users u
LEFT JOIN user_logins l ON u.id = l.user_id
  AND l.login_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
LEFT JOIN user_actions a ON u.id = a.user_id
  AND a.action_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
WHERE u.status = 'active'
GROUP BY u.id, u.name, u.email
ORDER BY login_count DESC
LIMIT 100

💡 SQL writing tips
Basics:
Performance considerations:
Security advice:
Common SQL patterns:
Scenario SQL
Pagination LIMIT #{limit} OFFSET #{offset}
Fuzzy search WHERE name LIKE CONCAT('%', #{keyword}, '%')
Date range WHERE date >= #{startDate} AND date <= #{endDate}
IN query WHERE id IN (#{ids})
Aggregation SELECT COUNT(*), SUM(amount), AVG(score) ...
Grouping GROUP BY ... HAVING COUNT(*) > 10
Sorting ORDER BY create_time DESC, name ASC

Scenario	SQL
Pagination	`LIMIT #{limit} OFFSET #{offset}`
Fuzzy search	`WHERE name LIKE CONCAT('%', #{keyword}, '%')`
Date range	`WHERE date >= #{startDate} AND date <= #{endDate}`
IN query	`WHERE id IN (#{ids})`
Aggregation	`SELECT COUNT(*), SUM(amount), AVG(score) ...`
Grouping	`GROUP BY ... HAVING COUNT(*) > 10`
Sorting	`ORDER BY create_time DESC, name ASC`

1.3 Configure SQL Parameters (optional but recommended)

If your SQL needs dynamic parameters, use a parameterized query:

SQL statement:

SQL

SELECT
  u.id,
  u.name,
  COUNT(o.id) as order_count,
  SUM(o.amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
  AND o.order_date >= #{startDate}
  AND o.order_date <= #{endDate}
WHERE u.status = #{userStatus}
GROUP BY u.id, u.name
ORDER BY total_amount DESC

Parameter configuration:

In the platform's parameter configuration area, define the following parameters:

Parameter	Type	Default	Description
`startDate`	Date	`2025-01-01`	Start date
`endDate`	Date	`2025-12-31`	End date
`userStatus`	String	`active`	User status

⚠️ Parameter syntax

Supported parameter types:

Type	SQL example	Call example
String	`WHERE name = #{userName}`	`{ userName: 'Alice' }`
Number	`WHERE age > #{minAge}`	`{ minAge: 18 }`
Date	`WHERE created_at >= #{startDate}`	`{ startDate: '2025-01-01' }`
Boolean	`WHERE is_active = #{active}`	`{ active: true }`
Array	`WHERE id IN (#{userIds})`	`{ userIds: [1, 2, 3] }`

Why parameterized queries win:

✅ Security: automatic protection against SQL injection
✅ Flexibility: one SQL serves many parameter scenarios
✅ Maintainability: parameters stay separate from SQL logic
✅ Type safety: the platform validates and converts types

Comparison:

SQL

-- ❌ 不安全：容易受到 SQL 注入攻击
SELECT * FROM users WHERE id = 123

-- ✅ 安全：使用参数化查询
SELECT * FROM users WHERE id = #{userId}

💡 SQL writing best practices
Performance optimization:
Readability:
Example:

1.4 Test the SQL

Before saving, always test that the SQL executes correctly:

Click the "Test Run" button
If the SQL has parameters, fill in test values
Review the results and execution time
Confirm the returned data shape is correct

Sample test result:

JSON

{
  "success": true,
  "data": [
    {
      "id": 1001,
      "name": "张三",
      "email": "zhangsan@example.com",
      "login_count": 15,
      "action_count": 127,
      "last_login_time": "2025-11-12 10:30:00"
    },
    {
      "id": 1002,
      "name": "李四",
      "email": "lisi@example.com",
      "login_count": 12,
      "action_count": 98,
      "last_login_time": "2025-11-11 16:45:00"
    }
  ],
  "executeTime": "245ms"
}

💡 Testing tips

1.5 Save and Get the SQL Code

Click the "Save" button
On success, the system generates a unique SQL Code
Copy and keep this SQL Code — you'll need it to call the query later

SQL Code format: xxxxx-xxxxx (for example, 12345-67890)

Step 2: Call the SQL from Code

2.1 Install and Configure the SDK

Install the SDK:

Bash

npm install @lovrabet/sdk

Configure the client:

TypeScript

import { LovrabetClient, AuthMode } from "@lovrabet/sdk";

// 方式一：服务端（Node.js）- 使用 AccessKey
const client = new LovrabetClient({
  authMode: AuthMode.AccessKey,
  accessKey: process.env.LOVRABET_ACCESS_KEY,
  appCode: "your-app-code",
  env: "production", // 或 'dev'
});

// 方式二：浏览器 - 使用 Cookie（用户已登录平台）
const client = new LovrabetClient({
  authMode: AuthMode.Cookie,
  appCode: "your-app-code",
});

// 方式三：浏览器 - 使用 Token
const client = new LovrabetClient({
  authMode: AuthMode.Token,
  token: "your-token",
  timestamp: 1678901234567,
  appCode: "your-app-code",
});

2.2 Run a Basic Query

The simplest call:

TypeScript

// 执行 SQL（不带参数）
const result = await client.sql.execute("12345-67890");

// 检查执行结果
if (result.execSuccess && result.execResult) {
  console.log(`查询成功，返回 ${result.execResult.length} 条数据`);

  // 处理数据
  result.execResult.forEach((row) => {
    console.log(row);
  });
} else {
  console.error("SQL 执行失败");
}

Sample output:

Plain

查询成功，返回 2 条数据
{
  id: 1001,
  name: '张三',
  email: 'zhangsan@example.com',
  login_count: 15,
  action_count: 127,
  last_login_time: '2025-11-12 10:30:00'
}
{
  id: 1002,
  name: '李四',
  email: 'lisi@example.com',
  login_count: 12,
  action_count: 98,
  last_login_time: '2025-11-11 16:45:00'
}

2.3 Queries with Parameters

Pass parameters:

TypeScript

const result = await client.sql.execute("12345-67890", {
  startDate: "2025-01-01",
  endDate: "2025-01-31",
  userStatus: "active",
});

if (result.execSuccess && result.execResult) {
  console.log("查询结果:", result.execResult);
}

Dynamic parameters example:

TypeScript

// 从用户输入获取参数
function getDateRange() {
  const today = new Date();
  const sevenDaysAgo = new Date(today);
  sevenDaysAgo.setDate(today.getDate() - 7);

  return {
    startDate: sevenDaysAgo.toISOString().split("T")[0],
    endDate: today.toISOString().split("T")[0],
  };
}

const { startDate, endDate } = getDateRange();

const result = await client.sql.execute("12345-67890", {
  startDate,
  endDate,
  status: "active",
});

2.4 Add Type Definitions (TypeScript)

Define the result type:

TypeScript

// 定义查询结果的数据结构
interface ActiveUserStat {
  id: number;
  name: string;
  email: string;
  login_count: number;
  action_count: number;
  last_login_time: string;
}

// 使用泛型获得类型提示
const result = await client.sql.execute<ActiveUserStat>("12345-67890");

if (result.execSuccess && result.execResult) {
  result.execResult.forEach((user) => {
    // TypeScript 会自动提示字段
    console.log(`用户: ${user.name}`);
    console.log(`登录次数: ${user.login_count}`);
    console.log(`操作次数: ${user.action_count}`);
  });
}

Step 3: Process the Results

3.1 Basic Data Handling

Iterate the results:

TypeScript

const result = await client.sql.execute<ActiveUserStat>("12345-67890");

if (result.execSuccess && result.execResult) {
  const users = result.execResult;

  // 遍历所有数据
  users.forEach((user) => {
    console.log(`${user.name} - 登录${user.login_count}次`);
  });

  // 使用 map 转换数据
  const userNames = users.map((user) => user.name);
  console.log("用户列表:", userNames);

  // 过滤数据
  const activeUsers = users.filter((user) => user.login_count > 10);
  console.log(`活跃用户数: ${activeUsers.length}`);
}

3.2 Aggregating Statistics

TypeScript

interface UserOrderStat {
  user_id: number;
  order_count: number;
  total_amount: number;
}

const result = await client.sql.execute<UserOrderStat>("12345-67890");

if (result.execSuccess && result.execResult) {
  const stats = result.execResult;

  // 计算总订单数
  const totalOrders = stats.reduce((sum, stat) => sum + stat.order_count, 0);
  console.log(`总订单数: ${totalOrders}`);

  // 计算总金额
  const totalRevenue = stats.reduce((sum, stat) => sum + stat.total_amount, 0);
  console.log(`总销售额: ${totalRevenue.toFixed(2)}`);

  // 找出最大值
  const topUser = stats.reduce((max, stat) =>
    stat.total_amount > max.total_amount ? stat : max
  );
  console.log(`销售冠军: 用户${topUser.user_id}, 金额${topUser.total_amount}`);

  // 计算平均值
  const avgAmount = totalRevenue / stats.length;
  console.log(`平均销售额: ${avgAmount.toFixed(2)}`);
}

3.3 Transforming and Formatting Data

TypeScript

interface RawOrderData {
  order_date: string;
  product_name: string;
  quantity: number;
  price: number;
}

const result = await client.sql.execute<RawOrderData>("12345-67890");

if (result.execSuccess && result.execResult) {
  // 转换为前端需要的格式
  const formattedData = result.execResult.map((order) => ({
    日期: order.order_date,
    产品: order.product_name,
    数量: order.quantity,
    单价: `¥${order.price.toFixed(2)}`,
    小计: `¥${(order.quantity * order.price).toFixed(2)}`,
  }));

  console.table(formattedData);
}

Sample output:

Plain

┌─────────┬────────────┬────────────┬────────┬─────────┬──────────┐
│ (index) │    日期    │    产品    │  数量  │  单价   │   小计   │
├─────────┼────────────┼────────────┼────────┼─────────┼──────────┤
│    0    │ 2025-01-15 │  iPhone 15 │   2    │ ¥5999.00│ ¥11998.00│
│    1    │ 2025-01-16 │  iPad Pro  │   1    │ ¥7999.00│ ¥7999.00 │
└─────────┴────────────┴────────────┴────────┴─────────┴──────────┘

3.4 Export to CSV

TypeScript

interface ReportData {
  customer_name: string;
  order_date: string;
  product_name: string;
  quantity: number;
  amount: number;
}

async function exportToCSV(sqlCode: string) {
  const result = await client.sql.execute<ReportData>(sqlCode);

  if (!result.execSuccess || !result.execResult) {
    throw new Error("查询失败");
  }

  // 生成 CSV 表头
  const headers = ["客户名称", "订单日期", "产品名称", "数量", "金额"];
  const csvHeader = headers.join(",") + "\n";

  // 生成 CSV 行
  const csvRows = result.execResult
    .map(
      (row) =>
        `${row.customer_name},<equation>{row.order_date},</equation>{row.product_name},<equation>{row.quantity},</equation>{row.amount}`
    )
    .join("\n");

  const csvContent = csvHeader + csvRows;

  // 下载文件
  const blob = new Blob([csvContent], { type: "text/csv;charset=utf-8;" });
  const link = document.createElement("a");
  link.href = URL.createObjectURL(blob);
  link.download = `report_${Date.now()}.csv`;
  link.click();

  console.log("导出成功");
}

// 使用
await exportToCSV("12345-67890");

Step 4: Error Handling

4.1 Complete Error Handling

TypeScript

import { LovrabetError } from "@lovrabet/sdk";

async function fetchUserStats(sqlCode: string, params?: Record<string, any>) {
  try {
    // 执行 SQL 查询
    const result = await client.sql.execute(sqlCode, params);

    // 检查业务逻辑是否成功
    if (!result.execSuccess) {
      console.error("SQL 执行失败");
      return null;
    }

    // 检查是否有结果
    if (!result.execResult || result.execResult.length === 0) {
      console.warn("查询结果为空");
      return [];
    }

    // 返回成功结果
    return result.execResult;
  } catch (error) {
    // 处理 HTTP 请求错误
    if (error instanceof LovrabetError) {
      console.error("API 请求失败:", error.message);
      console.error("错误代码:", error.code);

      // 根据错误码做不同处理
      if (error.code === 401) {
        console.error("认证失败，请检查 AccessKey 或重新登录");
      } else if (error.code === 404) {
        console.error("SQL 不存在，请检查 SQL Code");
      } else if (error.code === 500) {
        console.error("服务器错误，请稍后重试");
      }
    } else {
      console.error("未知错误:", error);
    }

    return null;
  }
}

// 使用
const stats = await fetchUserStats("12345-67890", { status: "active" });

if (stats) {
  console.log("查询成功:", stats);
} else {
  console.log("查询失败，请查看错误日志");
}

4.2 Handling Common Errors

Error reference table:

Scenario	Symptom	Fix
SQL not found	HTTP 404	Check that the SQL Code is correct
Authentication failed	HTTP 401	Check your AccessKey or log in again
Insufficient permissions	HTTP 403	Ask an administrator to grant access
SQL syntax error	`execSuccess: false`	Test the SQL on the platform
Missing parameter	`execSuccess: false`	Check that parameter names and values match
Database error	`execSuccess: false`	Check that the tables/columns exist
Timeout	Network timeout	Optimize the SQL or raise the timeout

4.3 User-Friendly Error Messages

TypeScript

async function showUserStatsWithError(sqlCode: string) {
  try {
    const result = await client.sql.execute<UserStat>(sqlCode);

    if (!result.execSuccess) {
      // 业务逻辑错误
      showNotification({
        type: "error",
        title: "查询失败",
        message: "SQL 执行出错，请联系管理员或稍后重试",
      });
      return;
    }

    if (!result.execResult || result.execResult.length === 0) {
      // 空结果
      showNotification({
        type: "info",
        title: "无数据",
        message: "当前没有符合条件的数据",
      });
      return;
    }

    // 成功
    showNotification({
      type: "success",
      title: "查询成功",
      message: `查询到 ${result.execResult.length} 条数据`,
    });

    displayData(result.execResult);
  } catch (error) {
    if (error instanceof LovrabetError) {
      // HTTP 错误
      if (error.code === 401) {
        showNotification({
          type: "error",
          title: "认证失败",
          message: "请重新登录后再试",
          action: "去登录",
        });
      } else {
        showNotification({
          type: "error",
          title: "网络错误",
          message: "请检查网络连接后重试",
        });
      }
    }
  }
}

Step 5: Real-World Examples

Example 1: Activity Statistics Dashboard

Goal: build a dashboard showing user activity over the last 7 days

1. Create the SQL on the platform:

SQL

-- 用户活跃度统计
-- 参数：days - 统计天数
SELECT
  DATE(l.login_time) as login_date,
  COUNT(DISTINCT l.user_id) as active_users,
  COUNT(l.id) as total_logins,
  AVG(TIMESTAMPDIFF(MINUTE, l.login_time, l.logout_time)) as avg_duration
FROM user_logins l
WHERE l.login_time >= DATE_SUB(CURDATE(), INTERVAL #{days} DAY)
GROUP BY DATE(l.login_time)
ORDER BY login_date DESC

2. Implement the frontend:

TypeScript

interface DailyActivity {
  login_date: string;
  active_users: number;
  total_logins: number;
  avg_duration: number;
}

async function loadActivityDashboard() {
  const result = await client.sql.execute<DailyActivity>("12345-67890", {
    days: 7,
  });

  if (!result.execSuccess || !result.execResult) {
    console.error("加载失败");
    return;
  }

  const data = result.execResult;

  // 渲染图表
  renderChart({
    labels: data.map((d) => d.login_date),
    datasets: [
      {
        label: "活跃用户数",
        data: data.map((d) => d.active_users),
        borderColor: "rgb(75, 192, 192)",
      },
      {
        label: "登录次数",
        data: data.map((d) => d.total_logins),
        borderColor: "rgb(255, 99, 132)",
      },
    ],
  });

  // 显示统计
  const totalUsers = data.reduce((sum, d) => sum + d.active_users, 0);
  const avgDuration =
    data.reduce((sum, d) => sum + d.avg_duration, 0) / data.length;

  console.log(`7天内活跃用户总数: ${totalUsers}`);
  console.log(`平均在线时长: ${avgDuration.toFixed(1)} 分钟`);
}

Example 2: Sales Report Generation

Goal: generate a sales report for a given date range

1. Platform SQL:

SQL

-- 销售订单报表
-- 参数：startDate, endDate - 日期范围
-- 参数：status - 订单状态（'all' 表示所有状态）
SELECT
  o.order_date,
  o.order_no,
  c.customer_name,
  p.product_name,
  o.quantity,
  o.unit_price,
  (o.quantity * o.unit_price) AS subtotal,
  o.status
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= #{startDate}
  AND o.order_date <= #{endDate}
  AND (#{status} = 'all' OR o.status = #{status})
ORDER BY o.order_date DESC, o.order_no

2. Implement the export:

TypeScript

interface OrderReport {
  order_date: string;
  order_no: string;
  customer_name: string;
  product_name: string;
  quantity: number;
  unit_price: number;
  subtotal: number;
  status: string;
}

async function generateSalesReport(
  startDate: string,
  endDate: string,
  status: string = "all"
) {
  // 查询数据
  const result = await client.sql.execute<OrderReport>("12345-67890", {
    startDate,
    endDate,
    status,
  });

  if (!result.execSuccess || !result.execResult) {
    throw new Error("查询失败");
  }

  const orders = result.execResult;

  // 计算汇总
  const summary = {
    totalOrders: orders.length,
    totalQuantity: orders.reduce((sum, o) => sum + o.quantity, 0),
    totalAmount: orders.reduce((sum, o) => sum + o.subtotal, 0),
    avgOrderAmount: 0,
  };
  summary.avgOrderAmount = summary.totalAmount / summary.totalOrders;

  // 生成 Excel 数据
  const excelData = [
    // 表头
    ["日期", "订单号", "客户", "产品", "数量", "单价", "小计", "状态"],
    // 数据行
    ...orders.map((o) => [
      o.order_date,
      o.order_no,
      o.customer_name,
      o.product_name,
      o.quantity,
      o.unit_price,
      o.subtotal,
      o.status,
    ]),
    // 汇总行
    [],
    ["汇总", "", "", "", summary.totalQuantity, "", summary.totalAmount, ""],
    ["订单数", summary.totalOrders],
    ["平均金额", summary.avgOrderAmount.toFixed(2)],
  ];

  // 导出（使用 SheetJS 等库）
  exportToExcel(excelData, `销售报表_<equation>{startDate}_</equation>{endDate}.xlsx`);

  return summary;
}

// 使用
const summary = await generateSalesReport(
  "2025-01-01",
  "2025-01-31",
  "completed"
);
console.log("报表生成成功:", summary);

Example 3: Live Search Suggestions

Goal: show search suggestions as the user types

1. Platform SQL:

SQL

-- 用户搜索建议
-- 参数：keyword - 搜索关键词
SELECT
  id,
  name,
  email,
  avatar,
  department
FROM users
WHERE status = 'active'
  AND (
    name LIKE CONCAT('%', #{keyword}, '%')
    OR email LIKE CONCAT('%', #{keyword}, '%')
  )
ORDER BY
  CASE
    WHEN name LIKE CONCAT(#{keyword}, '%') THEN 1    -- 名称前缀匹配优先
    WHEN email LIKE CONCAT(#{keyword}, '%') THEN 2   -- 邮箱前缀匹配次之
    ELSE 3                                            -- 其他模糊匹配
  END,
  name
LIMIT 10

2. Implement the search component:

TypeScript

import { debounce } from "lodash";

interface UserSuggestion {
  id: number;
  name: string;
  email: string;
  avatar: string;
  department: string;
}

// 防抖搜索函数
const searchUsers = debounce(
  async (keyword: string, callback: (users: UserSuggestion[]) => void) => {
    if (keyword.length < 2) {
      callback([]);
      return;
    }

    try {
      const result = await client.sql.execute<UserSuggestion>(
        "12345-67890",
        { keyword }
      );

      if (result.execSuccess && result.execResult) {
        callback(result.execResult);
      } else {
        callback([]);
      }
    } catch (error) {
      console.error("搜索失败:", error);
      callback([]);
    }
  },
  300
); // 300ms 防抖

// React 组件示例
function UserSearchInput() {
  const [suggestions, setSuggestions] = useState<UserSuggestion[]>([]);
  const [loading, setLoading] = useState(false);

  const handleSearch = (keyword: string) => {
    setLoading(true);
    searchUsers(keyword, (users) => {
      setSuggestions(users);
      setLoading(false);
    });
  };

  return (
    <div className="search-container">
      <input
        type="text"
        placeholder="搜索用户..."
        onChange={(e) => handleSearch(e.target.value)}
      />

      {loading && <div>搜索中...</div>}

      {suggestions.length > 0 && (
        <ul className="suggestions">
          {suggestions.map((user) => (
            <li key={user.id}>
              <img src={user.avatar} alt={user.name} />
              <div>
                <div className="name">{user.name}</div>
                <div className="email">{user.email}</div>
                <div className="department">{user.department}</div>
              </div>
            </li>
          ))}
        </ul>
      )}
    </div>
  );
}

Step 6: Performance Optimization

6.1 Use Caching

TypeScript

class SQLCache {
  private cache = new Map<string, { data: any; expiry: number }>();
  private client: LovrabetClient;

  constructor(client: LovrabetClient) {
    this.client = client;
  }

  async execute<T>(
    sqlCode: string,
    params?: Record<string, any>,
    cacheDuration: number = 5 * 60 * 1000 // 默认 5 分钟
  ) {
    const cacheKey = `${sqlCode}:${JSON.stringify(params)}`;
    const cached = this.cache.get(cacheKey);

    // 缓存命中且未过期
    if (cached && cached.expiry > Date.now()) {
      console.log("从缓存读取数据");
      return cached.data as T[];
    }

    // 执行查询
    const result = await this.client.sql.execute<T>(sqlCode, params);

    if (result.execSuccess && result.execResult) {
      // 存入缓存
      this.cache.set(cacheKey, {
        data: result.execResult,
        expiry: Date.now() + cacheDuration,
      });

      return result.execResult;
    }

    return null;
  }

  // 清除缓存
  clear(sqlCode?: string) {
    if (sqlCode) {
      // 清除特定 SQL 的缓存
      for (const key of this.cache.keys()) {
        if (key.startsWith(sqlCode)) {
          this.cache.delete(key);
        }
      }
    } else {
      // 清除所有缓存
      this.cache.clear();
    }
  }
}

// 使用
const cache = new SQLCache(client);

// 第一次查询（从服务器）
const data1 = await cache.execute<UserStat>("12345-67890", {
  status: "active",
});

// 第二次查询（从缓存）
const data2 = await cache.execute<UserStat>("12345-67890", {
  status: "active",
});

// 清除缓存
cache.clear("12345-67890");

6.2 Concurrent Queries

TypeScript

// 多个独立查询可以并发执行
async function loadDashboardData() {
  const [userStats, orderStats, revenueStats] = await Promise.all([
    client.sql.execute<UserStat>("sql-code-1"),
    client.sql.execute<OrderStat>("sql-code-2"),
    client.sql.execute<RevenueStat>("sql-code-3"),
  ]);

  // 处理各个结果
  if (userStats.execSuccess && userStats.execResult) {
    displayUserStats(userStats.execResult);
  }

  if (orderStats.execSuccess && orderStats.execResult) {
    displayOrderStats(orderStats.execResult);
  }

  if (revenueStats.execSuccess && revenueStats.execResult) {
    displayRevenueStats(revenueStats.execResult);
  }
}

6.3 Paginated Queries

TypeScript

interface PagedResult<T> {
  data: T[];
  total: number;
  hasMore: boolean;
}

async function fetchPagedData<T>(
  sqlCode: string,
  page: number,
  pageSize: number
): Promise<PagedResult<T>> {
  const result = await client.sql.execute<T>(sqlCode, {
    offset: (page - 1) * pageSize,
    limit: pageSize,
  });

  if (!result.execSuccess || !result.execResult) {
    return { data: [], total: 0, hasMore: false };
  }

  const data = result.execResult;
  const hasMore = data.length === pageSize;

  return {
    data,
    total: data.length,
    hasMore,
  };
}

// 使用
let currentPage = 1;
const pageSize = 20;

async function loadNextPage() {
  const result = await fetchPagedData<UserData>(
    "12345-67890",
    currentPage,
    pageSize
  );

  displayData(result.data);

  if (result.hasMore) {
    currentPage++;
    console.log("还有更多数据");
  } else {
    console.log("已加载所有数据");
  }
}

FAQ

Q1: Where do I find the SQL Code?

A: In the platform's Custom SQL management page. Every SQL has a unique code in the format xxxxx-xxxxx. You can:

View it in the SQL list
Copy it from the SQL detail page
See it when running a test

Q2: How do I debug SQL execution issues?

A: Work through these steps:

Test on the platform
- Click "Test Run" on the platform's SQL management page first
- Confirm the SQL executes correctly
Enable SDK debug mode
Inspect the returned result
Inspect network requests
- Open browser DevTools → Network tab
- Review the request and response details

Q3: Why is execSuccess false?

A: execSuccess: false means the SQL failed at the database. Common causes:

SQL syntax error: check that the statement is correct
Table or column missing: verify the names and spelling
Parameter issues: check that parameter names and types match
Database connection issues: ask an administrator to check the database

Steps to resolve:

Re-test the SQL on the platform
Check the SQL statement and parameters
Check the platform's error logs
Contact technical support

Q4: How do I optimize slow queries?

A: Suggestions:

Add indexes
Limit the number of rows returned
Avoid SELECT *
Use proper WHERE conditions
Paginate queries

Q5: Are write operations (INSERT/UPDATE/DELETE) supported?

A: executeSql is mainly for queries (SELECT) today.

✅ Supported: SELECT queries
⚠️ Partially supported: some platform configurations allow UPDATE/DELETE (requires administrator permissions)
❌ Not recommended: running writes directly from the frontend (security risk)

Recommended approach:

Reads: use executeSql
Writes: use the Dataset API (create, update, delete)

Best Practices

✅ Do

Always check execSuccess
Use parameterized queries
Define TypeScript types
Handle errors and empty results
Use caching to improve performance
Test the SQL on the platform first

❌ Avoid

Skipping the execSuccess check
Hardcoding sensitive information
Running queries inside loops
Returning large datasets without pagination

Next Steps

Congratulations — you've completed the full custom SQL tutorial!

What's next:

📚 Read the full SQL API documentation
🔐 Learn about authentication
📖 Learn the Dataset API
💡 Browse more examples
❓ Check the FAQ

Last updated: 2025-11-12

Custom SQL Tutorial ​

📋 Prerequisites ​

Step 1: Create a Custom SQL on the Platform ​

1.1 Open the SQL Management Page ​

1.2 Create a New SQL Query ​

Basic Information ​

SQL Statement ​

1.3 Configure SQL Parameters (optional but recommended) ​

1.4 Test the SQL ​

1.5 Save and Get the SQL Code ​

Step 2: Call the SQL from Code ​

2.1 Install and Configure the SDK ​

2.2 Run a Basic Query ​

2.3 Queries with Parameters ​

2.4 Add Type Definitions (TypeScript) ​

Step 3: Process the Results ​

3.1 Basic Data Handling ​

3.2 Aggregating Statistics ​

3.3 Transforming and Formatting Data ​

3.4 Export to CSV ​

Step 4: Error Handling ​

4.1 Complete Error Handling ​

4.2 Handling Common Errors ​

4.3 User-Friendly Error Messages ​

Step 5: Real-World Examples ​

Example 1: Activity Statistics Dashboard ​

Example 2: Sales Report Generation ​

Example 3: Live Search Suggestions ​

Step 6: Performance Optimization ​

6.1 Use Caching ​

6.2 Concurrent Queries ​

6.3 Paginated Queries ​

FAQ ​

Q1: Where do I find the SQL Code? ​

Q2: How do I debug SQL execution issues? ​

Q3: Why is execSuccess false? ​

Q4: How do I optimize slow queries? ​

Q5: Are write operations (INSERT/UPDATE/DELETE) supported? ​

Best Practices ​

✅ Do ​

❌ Avoid ​

Next Steps ​

Custom SQL Tutorial

📋 Prerequisites

Step 1: Create a Custom SQL on the Platform

1.1 Open the SQL Management Page

1.2 Create a New SQL Query

Basic Information

SQL Statement

1.3 Configure SQL Parameters (optional but recommended)

1.4 Test the SQL

1.5 Save and Get the SQL Code

Step 2: Call the SQL from Code

2.1 Install and Configure the SDK

2.2 Run a Basic Query

2.3 Queries with Parameters

2.4 Add Type Definitions (TypeScript)

Step 3: Process the Results

3.1 Basic Data Handling

3.2 Aggregating Statistics

3.3 Transforming and Formatting Data

3.4 Export to CSV

Step 4: Error Handling

4.1 Complete Error Handling

4.2 Handling Common Errors

4.3 User-Friendly Error Messages

Step 5: Real-World Examples

Example 1: Activity Statistics Dashboard

Example 2: Sales Report Generation

Example 3: Live Search Suggestions

Step 6: Performance Optimization

6.1 Use Caching

6.2 Concurrent Queries

6.3 Paginated Queries

FAQ

Q1: Where do I find the SQL Code?

Q2: How do I debug SQL execution issues?

Q3: Why is execSuccess false?

Q4: How do I optimize slow queries?

Q5: Are write operations (INSERT/UPDATE/DELETE) supported?

Best Practices

✅ Do

❌ Avoid

Next Steps