Custom SQL Tutorial

This tutorial will guide you step-by-step on how to configure and use custom SQL queries on the Lovrabet platform, completing a full data query feature from scratch.

Learning Objectives

Through this tutorial, you will learn:

• Create and configure custom SQL on the platform
• Call custom SQL using the SDK
• Handle query results and errors
• Implement common business scenarios

📋 Prerequisites

Before starting, please ensure:

• ✅ Registered Lovrabet platform account
• ✅ Created application and obtained AppCode
• ✅ Installed @lovrabet/sdk (>= 1.1.19)
• ✅ Obtained AccessKey (server-side) or logged in to platform (browser)

If you haven't completed the above preparations, please refer to the Quick Start Guide first.

---

Step 1: Create Custom SQL on Platform

1.1 Enter SQL Management Page

1. Log in to Lovrabet Platform
2. Select your application
3. Navigate to SQL management page: 【Application Configuration】 → 【Application Assets】 → 【Custom SQL Management】

---

1.2 Create New SQL Query

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

Basic Information

Field	Description	Example
SQL Name	Query name (required)	Query Active User Statistics
SQL Description	Query purpose (optional)	Statistics of login and action counts for active users in the last 7 days
SQL Group	For management (optional)	User Statistics

SQL Statement

Enter your SQL statement in the SQL Editor.

Beginner Example:

-- Simple query: Query all active users
SELECT
  id,
  name,
  email,
  created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100

Advanced Example:

-- Complex statistics: Query active user statistics for the last 7 days
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 Guidelines

Basic Standards:

• ✅ Specify field names: Avoid SELECT *, specify required fields explicitly
• ✅ Add comments: Use -- comment to explain SQL purpose
• ✅ Use aliases: Add meaningful aliases for tables and fields (AS)
• ✅ Format code: Each clause on a separate line, maintain indentation

Performance Considerations:

• ✅ Add LIMIT: Limit returned quantity, avoid returning large amounts of data at once
• ✅ WHERE conditions: Properly use indexed fields in filter conditions
• ✅ Avoid subqueries: Use JOIN instead of subqueries whenever possible
• ✅ Use EXPLAIN: Analyze query performance with EXPLAIN on platform

Security Recommendations:

• ✅ Use parameters: Use #{paramName} for dynamic values instead of concatenation
• ✅ Permission control: Only query data users have permission to access
• ❌ Avoid deletion: Don't write DELETE statements (use Dataset API)
• ❌ Avoid modification: UPDATE operations should be cautious (recommend using Dataset API)

Common SQL Patterns:

Scenario	SQL Key Points
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) ...
Group By	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, you can use parameterized queries:

SQL Statement:

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:

Define the following parameters in the platform's parameter configuration area:

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

Parameter Syntax Specification

• Use #{paramName} format to mark parameters in SQL
• Parameter names can only contain letters, numbers, and underscores
• Parameter names are case-sensitive
• Recommend using camelCase: startDate, userId, orderStatus
• Parameters are automatically type-converted and protected against SQL injection

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] }

Advantages of Parameterized Queries:

• ✅ Security: Automatically prevent SQL injection attacks
• ✅ Flexibility: Same SQL can be used for different parameter scenarios
• ✅ Maintainability: Parameters separated from SQL logic
• ✅ Type safety: Platform performs type checking and conversion

Example Comparison:

-- ❌ Unsafe: Vulnerable to SQL injection attacks
SELECT * FROM users WHERE id = 123

-- ✅ Safe: Using parameterized query
SELECT * FROM users WHERE id = #{userId}

SQL Writing Best Practices

Performance Optimization:

1. Use specific field names: Avoid SELECT *, specify required fields explicitly
2. Add appropriate indexes: Add indexes for fields in WHERE and JOIN conditions
3. Limit returned quantity: Use LIMIT to avoid returning too much data
4. Avoid subqueries: Use JOIN instead of subqueries whenever possible

Readability:

1. Use aliases: Add clear aliases for tables and fields
2. Multi-line writing: Each clause on a separate line
3. Add comments: Add -- comment for complex logic
4. Consistent case: Recommend uppercase keywords, lowercase field names

Example:

-- Query user order statistics (last 30 days)
SELECT
  u.id AS user_id,
  u.name AS user_name,
  u.email,
  COUNT(DISTINCT o.id) AS order_count,
  COALESCE(SUM(o.amount), 0) AS total_amount,
  AVG(o.amount) AS avg_amount,
  MAX(o.created_at) AS last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
  AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND o.status IN ('paid', 'completed')
WHERE u.status = #{userStatus}
  AND u.created_at >= #{startDate}
GROUP BY u.id, u.name, u.email
HAVING order_count > #{minOrderCount}
ORDER BY total_amount DESC
LIMIT #{limit};

---

1.4 Test SQL

Before saving, make sure to test whether the SQL can execute properly:

1. Click "Test Run" button
2. If there are parameters, fill in test parameter values
3. Check execution results and time
4. Confirm returned data format is correct

Test Result Example:

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

Testing Key Points

• ✅ Check if returned field names meet expectations
• ✅ Check if data types are correct
• ✅ Check if execution time is within acceptable range (recommend < 3 seconds)
• ✅ Test edge cases (empty results, large data volumes, etc.)

---

1.5 Save and Get SQL Code

1. Click "Save" button
2. After successful save, the system will generate a unique SQL Code
3. Copy and save this SQL Code, it will be needed for subsequent calls

SQL Code Format: xxxxx-xxxxx (e.g., 12345-67890)

---

Step 2: Call SQL in Code

2.1 Install and Configure SDK

Install SDK:

npm install @lovrabet/sdk

Configure Client:

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

// Method 1: Server-side (Node.js) - Using AccessKey
const client = new LovrabetClient({
  authMode: AuthMode.AccessKey,
  accessKey: process.env.LOVRABET_ACCESS_KEY,
  appCode: "your-app-code",
  env: "production", // or 'dev'
});

// Method 2: Browser - Using Cookie (user already logged in to platform)
const client = new LovrabetClient({
  authMode: AuthMode.Cookie,
  appCode: "your-app-code",
});

// Method 3: Browser - Using Token
const client = new LovrabetClient({
  authMode: AuthMode.Token,
  token: "your-token",
  timestamp: 1678901234567,
  appCode: "your-app-code",
});

---

2.2 Execute Basic Query

Simplest Call:

// Execute SQL (without parameters)
const result = await client.api.executeSql("12345-67890");

// Check execution result
if (result.execSuccess && result.execResult) {
  console.log(`Query successful, returned ${result.execResult.length} records`);

  // Process data
  result.execResult.forEach((row) => {
    console.log(row);
  });
} else {
  console.error("SQL execution failed");
}

Output Example:

Query successful, returned 2 records
{
  id: 1001,
  name: 'Zhang San',
  email: 'zhangsan@example.com',
  login_count: 15,
  action_count: 127,
  last_login_time: '2025-11-12 10:30:00'
}
{
  id: 1002,
  name: 'Li Si',
  email: 'lisi@example.com',
  login_count: 12,
  action_count: 98,
  last_login_time: '2025-11-11 16:45:00'
}

---

2.3 Query with Parameters

Passing Parameters:

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

if (result.execSuccess && result.execResult) {
  console.log("Query results:", result.execResult);
}

Dynamic Parameters Example:

// Get parameters from user input
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.api.executeSql("12345-67890", {
  startDate,
  endDate,
  status: "active",
});

---

2.4 Add Type Definitions (TypeScript)

Define Result Type:

// Define query result data structure
interface ActiveUserStat {
  id: number;
  name: string;
  email: string;
  login_count: number;
  action_count: number;
  last_login_time: string;
}

// Use generics to get type hints
const result = await client.api.executeSql<ActiveUserStat>("12345-67890");

if (result.execSuccess && result.execResult) {
  result.execResult.forEach((user) => {
    // TypeScript will auto-suggest fields
    console.log(`User: ${user.name}`);
    console.log(`Login count: ${user.login_count}`);
    console.log(`Action count: ${user.action_count}`);
  });
}

---

Step 3: Process Query Results

3.1 Basic Data Processing

Iterate Results:

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

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

  // Iterate all data
  users.forEach((user) => {
    console.log(`${user.name} - logged in ${user.login_count} times`);
  });

  // Use map to transform data
  const userNames = users.map((user) => user.name);
  console.log("User list:", userNames);

  // Filter data
  const activeUsers = users.filter((user) => user.login_count > 10);
  console.log(`Active users: ${activeUsers.length}`);
}

---

3.2 Data Aggregation Statistics

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

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

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

  // Calculate total orders
  const totalOrders = stats.reduce((sum, stat) => sum + stat.order_count, 0);
  console.log(`Total orders: ${totalOrders}`);

  // Calculate total amount
  const totalRevenue = stats.reduce((sum, stat) => sum + stat.total_amount, 0);
  console.log(`Total sales: ${totalRevenue.toFixed(2)}`);

  // Find maximum value
  const topUser = stats.reduce((max, stat) =>
    stat.total_amount > max.total_amount ? stat : max
  );
  console.log(`Sales champion: User ${topUser.user_id}, Amount ${topUser.total_amount}`);

  // Calculate average
  const avgAmount = totalRevenue / stats.length;
  console.log(`Average sales: ${avgAmount.toFixed(2)}`);
}

---

3.3 Data Transformation and Formatting

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

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

if (result.execSuccess && result.execResult) {
  // Transform to frontend format
  const formattedData = result.execResult.map((order) => ({
    Date: order.order_date,
    Product: order.product_name,
    Quantity: order.quantity,
    Price: `$${order.price.toFixed(2)}`,
    Subtotal: `$${(order.quantity * order.price).toFixed(2)}`,
  }));

  console.table(formattedData);
}

Output Example:

┌─────────┬────────────┬────────────┬────────┬─────────┬──────────┐
│ (index) │    Date    │   Product  │Quantity│  Price  │ Subtotal │
├─────────┼────────────┼────────────┼────────┼─────────┼──────────┤
│    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

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

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

  if (!result.execSuccess || !result.execResult) {
    throw new Error("Query failed");
  }

  // Generate CSV header
  const headers = ["Customer Name", "Order Date", "Product Name", "Quantity", "Amount"];
  const csvHeader = headers.join(",") + "\n";

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

  const csvContent = csvHeader + csvRows;

  // Download file
  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("Export successful");
}

// Usage
await exportToCSV("12345-67890");

---

Step 4: Error Handling

4.1 Complete Error Handling

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

async function fetchUserStats(sqlCode: string, params?: Record<string, any>) {
  try {
    // Execute SQL query
    const result = await client.api.executeSql(sqlCode, params);

    // Check if business logic succeeded
    if (!result.execSuccess) {
      console.error("SQL execution failed");
      return null;
    }

    // Check if there are results
    if (!result.execResult || result.execResult.length === 0) {
      console.warn("Query result is empty");
      return [];
    }

    // Return successful result
    return result.execResult;
  } catch (error) {
    // Handle HTTP request errors
    if (error instanceof LovrabetError) {
      console.error("API request failed:", error.message);
      console.error("Error code:", error.code);

      // Handle different error codes
      if (error.code === 401) {
        console.error("Authentication failed, please check AccessKey or log in again");
      } else if (error.code === 404) {
        console.error("SQL does not exist, please check SQL Code");
      } else if (error.code === 500) {
        console.error("Server error, please try again later");
      }
    } else {
      console.error("Unknown error:", error);
    }

    return null;
  }
}

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

if (stats) {
  console.log("Query successful:", stats);
} else {
  console.log("Query failed, please check error logs");
}

---

4.2 Handle Common Errors

Error Type Reference Table:

Error Scenario	Manifestation	Solution
SQL not found	HTTP 404	Check if SQL Code is correct
Authentication failed	HTTP 401	Check AccessKey or log in again
Insufficient permissions	HTTP 403	Contact administrator for permissions
SQL syntax error	execSuccess: false	Test SQL on platform
Missing parameters	execSuccess: false	Check if parameter names and values match
Database error	execSuccess: false	Check if table/fields exist
Timeout	Network timeout	Optimize SQL or increase timeout

---

4.3 User-Friendly Error Messages

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

    if (!result.execSuccess) {
      // Business logic error
      showNotification({
        type: "error",
        title: "Query Failed",
        message: "SQL execution error, please contact administrator or try again later",
      });
      return;
    }

    if (!result.execResult || result.execResult.length === 0) {
      // Empty result
      showNotification({
        type: "info",
        title: "No Data",
        message: "No data matching the criteria",
      });
      return;
    }

    // Success
    showNotification({
      type: "success",
      title: "Query Successful",
      message: `Found ${result.execResult.length} records`,
    });

    displayData(result.execResult);
  } catch (error) {
    if (error instanceof LovrabetError) {
      // HTTP error
      if (error.code === 401) {
        showNotification({
          type: "error",
          title: "Authentication Failed",
          message: "Please log in and try again",
          action: "Go to Login",
        });
      } else {
        showNotification({
          type: "error",
          title: "Network Error",
          message: "Please check network connection and try again",
        });
      }
    }
  }
}

---

Step 5: Practical Cases

Case 1: Data Statistics Dashboard

Requirement: Implement a dashboard showing user activity for the last 7 days

1. Create SQL on Platform:

-- User activity statistics
-- Parameter: days - number of days to count
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 Frontend Code:

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

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

  if (!result.execSuccess || !result.execResult) {
    console.error("Loading failed");
    return;
  }

  const data = result.execResult;

  // Render chart
  renderChart({
    labels: data.map((d) => d.login_date),
    datasets: [
      {
        label: "Active Users",
        data: data.map((d) => d.active_users),
        borderColor: "rgb(75, 192, 192)",
      },
      {
        label: "Login Count",
        data: data.map((d) => d.total_logins),
        borderColor: "rgb(255, 99, 132)",
      },
    ],
  });

  // Display statistics
  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(`Total active users in 7 days: ${totalUsers}`);
  console.log(`Average online duration: ${avgDuration.toFixed(1)} minutes`);
}

---

Case 2: Order Report Generation

Requirement: Generate sales report for specified date range

1. Platform SQL:

-- Sales order report
-- Parameters: startDate, endDate - date range
-- Parameter: status - order status ('all' means all statuses)
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 Export Function:

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"
) {
  // Query data
  const result = await client.api.executeSql<OrderReport>("12345-67890", {
    startDate,
    endDate,
    status,
  });

  if (!result.execSuccess || !result.execResult) {
    throw new Error("Query failed");
  }

  const orders = result.execResult;

  // Calculate summary
  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;

  // Generate Excel data
  const excelData = [
    // Header
    ["Date", "Order No", "Customer", "Product", "Quantity", "Unit Price", "Subtotal", "Status"],
    // Data rows
    ...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 row
    [],
    ["Summary", "", "", "", summary.totalQuantity, "", summary.totalAmount, ""],
    ["Order Count", summary.totalOrders],
    ["Average Amount", summary.avgOrderAmount.toFixed(2)],
  ];

  // Export (using SheetJS or similar library)
  exportToExcel(excelData, `Sales_Report_${startDate}_${endDate}.xlsx`);

  return summary;
}

// Usage
const summary = await generateSalesReport(
  "2025-01-01",
  "2025-01-31",
  "completed"
);
console.log("Report generated successfully:", summary);

---

Case 3: Real-time Search Suggestions

Requirement: Display real-time search suggestions as user types

1. Platform SQL:

-- User search suggestions
-- Parameter: keyword - search 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    -- Name prefix match priority
    WHEN email LIKE CONCAT(#{keyword}, '%') THEN 2   -- Email prefix match second
    ELSE 3                                            -- Other fuzzy matches
  END,
  name
LIMIT 10

2. Implement Search Component:

import { debounce } from "lodash";

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

// Debounced search function
const searchUsers = debounce(
  async (keyword: string, callback: (users: UserSuggestion[]) => void) => {
    if (keyword.length < 2) {
      callback([]);
      return;
    }

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

      if (result.execSuccess && result.execResult) {
        callback(result.execResult);
      } else {
        callback([]);
      }
    } catch (error) {
      console.error("Search failed:", error);
      callback([]);
    }
  },
  300
); // 300ms debounce

// React component example
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="Search users..."
        onChange={(e) => handleSearch(e.target.value)}
      />

      {loading && <div>Searching...</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 Using Cache

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 // Default 5 minutes
  ) {
    const cacheKey = `${sqlCode}:${JSON.stringify(params)}`;
    const cached = this.cache.get(cacheKey);

    // Cache hit and not expired
    if (cached && cached.expiry > Date.now()) {
      console.log("Reading data from cache");
      return cached.data as T[];
    }

    // Execute query
    const result = await this.client.api.executeSql<T>(sqlCode, params);

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

      return result.execResult;
    }

    return null;
  }

  // Clear cache
  clear(sqlCode?: string) {
    if (sqlCode) {
      // Clear cache for specific SQL
      for (const key of this.cache.keys()) {
        if (key.startsWith(sqlCode)) {
          this.cache.delete(key);
        }
      }
    } else {
      // Clear all cache
      this.cache.clear();
    }
  }
}

// Usage
const cache = new SQLCache(client);

// First query (from server)
const data1 = await cache.execute<UserStat>("12345-67890", {
  status: "active",
});

// Second query (from cache)
const data2 = await cache.execute<UserStat>("12345-67890", {
  status: "active",
});

// Clear cache
cache.clear("12345-67890");

---

6.2 Concurrent Queries

// Multiple independent queries can execute concurrently
async function loadDashboardData() {
  const [userStats, orderStats, revenueStats] = await Promise.all([
    client.api.executeSql<UserStat>("sql-code-1"),
    client.api.executeSql<OrderStat>("sql-code-2"),
    client.api.executeSql<RevenueStat>("sql-code-3"),
  ]);

  // Process each result
  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 Pagination Query

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.api.executeSql<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,
  };
}

// Usage
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("More data available");
  } else {
    console.log("All data loaded");
  }
}

---

Common Questions

Q1: Where to find SQL Code?

Answer: On the platform's Custom SQL management page, each SQL has a unique Code in the format xxxxx-xxxxx. You can:

• View in SQL list
• Copy from SQL detail page
• View during test run

---

Q2: How to debug SQL execution issues?

Answer: Follow these troubleshooting steps:

1. Test on platform

   • First click "Test Run" on the platform's SQL management page
   • Confirm SQL executes normally

2. Enable SDK debug mode

   const client = new LovrabetClient({
     // ...other config
     debug: true, // Enable debug logging
   });

3. Check returned result

   const result = await client.api.executeSql("12345-67890");
   console.log("Full result:", JSON.stringify(result, null, 2));

4. View network requests

   • Open browser DevTools → Network panel
   • Check request and response details

---

Q3: Why is execSuccess false?

Answer: execSuccess: false means SQL execution failed in the database, common reasons:

• SQL syntax error: Check if SQL statement is correct
• Table or field does not exist: Confirm table/field names are spelled correctly
• Parameter issues: Check if parameter names and types match
• Database connection issues: Contact administrator to check database status

Resolution Steps:

1. Re-test SQL on platform
2. Check SQL statement and parameters
3. View platform error logs
4. Contact technical support

---

Q4: How to optimize slow queries?

Answer: Optimization suggestions:

1. Add indexes

   -- Add indexes for commonly queried fields
   CREATE INDEX idx_user_status ON users(status);
   CREATE INDEX idx_order_date ON orders(order_date);

2. Limit returned quantity

   -- Add LIMIT clause
   SELECT * FROM large_table
   WHERE status = 'active'
   LIMIT 1000

3. Avoid SELECT *

   -- ❌ Not recommended
   SELECT * FROM users
   
   -- ✅ Recommended
   SELECT id, name, email FROM users

4. Use appropriate WHERE conditions

   -- Add time range restrictions
   WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)

5. Pagination query

   LIMIT :limit OFFSET :offset

---

Q5: Does it support write operations (INSERT/UPDATE/DELETE)?

Answer: Currently executeSql is primarily for queries (SELECT).

• ✅ Supported: SELECT queries
• ⚠️ Partial support: Some platform configurations allow UPDATE/DELETE (requires admin permissions)
• ❌ Not recommended: Execute write operations directly in frontend (security risk)

Recommended Practice:

• Read operations: Use executeSql
• Write operations: Use Dataset API (create, update, delete)

---

Best Practices

✅ Recommended Practices

1. Always check execSuccess

   if (result.execSuccess && result.execResult) {
     // Use data
   }

2. Use parameterized queries

   // ✅ Safe
   executeSql("sqlCode", { userId: "123" });
   
   // ❌ Unsafe (SQL injection risk)
   executeSql(`SELECT * FROM users WHERE id = ${userId}`);

3. Define TypeScript types

   interface User {
     id: number;
     name: string;
   }
   executeSql<User>("sqlCode");

4. Handle errors and empty results

   try {
     const result = await executeSql("sqlCode");
     if (!result.execSuccess) {
       // Handle failure
     }
     if (!result.execResult || result.execResult.length === 0) {
       // Handle empty results
     }
   } catch (error) {
     // Handle exceptions
   }

5. Use cache to optimize performance

6. Test SQL on platform first

---

❌ Avoid These Practices

1. Not checking execSuccess

   // ❌ Dangerous
   const data = await executeSql('sqlCode');
   data.execResult.forEach(...) // May throw error

2. Hardcoding sensitive information

   // ❌ Unsafe
   const accessKey = "sk_123456789"; // Don't hardcode

3. Executing queries in loops

   // ❌ Poor performance
   for (const id of userIds) {
     await executeSql("sqlCode", { userId: id });
   }
   
   // ✅ Use IN query
   await executeSql("sqlCode", { userIds: userIds.join(",") });

4. Returning large amounts of data without pagination

   -- ❌ May cause timeout
   SELECT * FROM large_table
   
   -- ✅ Use pagination
   SELECT * FROM large_table LIMIT 100

---

Next Steps

Congratulations on completing the full custom SQL tutorial!

Next you can:

• 📚 View SQL API Complete Documentation
• 🔐 Learn about Authentication Methods
• 📖 Study Dataset API
• 💡 Check More Examples
• ❓ View FAQ

---

Last Updated: 2025-11-12