Backend Function (III): Standalone Endpoint - SQL Complex Queries

This is the final article in the Backend Function series. The previous article covered calling custom SQL for report queries in the frontend. However, some scenarios require backend processing - such as batch updating data, using SQL results as input for subsequent business logic, or mixing SQL and dataset APIs within transactions.

This article introduces how to execute custom SQL in Backend Function.

What You Will Learn in This Section

How to call SQL in Backend Function
Differences between SQL return values in Frontend SDK and BFF
Using SQL in transactions
Combining SQL results with dataset API

Requirements

Use custom SQL in Backend Function to cover these scenarios:

Complex report statistics (multi-table joins, grouped aggregations)
Batch data updates (conditionally batch modify status)
Special queries (using database-specific functions)
SQL results + dataset API combined usage

Core Concepts

Frontend vs Backend Function SQL Calls

Feature	Frontend SDK	Backend Function
Call Method	`client.sql.execute()`	`context.client.sql.execute()`
Return Structure	`{ execSuccess, execResult }`	Returns array directly
Error Handling	Check `execSuccess`	`try-catch` to catch exceptions
Use Case	Query data for display	Business logic processing

Implementation Steps

Step 1: Prepare Custom SQL

Create custom SQL on the Lovrabet platform:

SQL Name: getUserStats

SELECT
  u.id,
  u.username,
  u.email,
  COUNT(o.id) as order_count,
  COALESCE(SUM(o.total_amount), 0) as total_amount
FROM dataset_users u
LEFT JOIN dataset_orders o ON u.id = o.user_id
WHERE u.status = 'active'
  <if test="userId">
    AND u.id = #{userId}
  </if>
GROUP BY u.id, u.username, u.email

Step 2: Call SQL in Backend Function

Query Statistical Data

/**
 * Get User Statistics - Backend Function Standalone Endpoint
 *
 * [API Path] POST /api/{appCode}/endpoint/getUserStats
 *
 * [Platform Configuration] https://app.lovrabet.com/app/{appCode}/data/backend-function
 * Note: appCode is the application code, you can configure this standalone endpoint on the platform after creation
 *
 * [HTTP Request Body Parameters]
 * { "userId": "User ID (optional)" }
 *
 * [Return Data Structure]
 * ENDPOINT: Returns business data object
 * {
 *   "success": true,
 *   "data": [...],  // User statistics array
 *   "total": 10     // Total count
 * }
 *
 * @param {Object} params - Request parameters
 * @param {string} params.userId - User ID (optional)
 * @param {Object} context - Execution context (automatically injected by platform, no need to pass when calling)
 * @param {Object} context.userInfo - Current user information
 * @param {Object} context.client - Database operation entry point
 * @returns {Object} Returns statistical results
 */
export default async function getUserStats(params, context) {
  const { userId } = params;

  // Returns array directly (in BFF, sql.execute returns result array directly)
  const rows = await context.client.sql.execute({
    sqlCode: "getUserStats",
    params: { userId },
  });

  // If userId is not specified, return statistics for all users
  // If userId is specified, return statistics for a single user

  if (userId) {
    // Single user: return object or null
    return rows.length > 0 ? rows[0] : null;
  } else {
    // All users: return array
    return {
      success: true,
      data: rows,
      total: rows.length,
    };
  }
}

Frontend Call:

import { lovrabetClient } from "./api/client";

// Get all user statistics
const result = await lovrabetClient.bff.execute({
  scriptName: "getUserStats",
  params: {},
});

console.log(result.data); // User statistics array

// Get single user statistics
const userStats = await lovrabetClient.bff.execute({
  scriptName: "getUserStats",
  params: { userId: "123" },
});

console.log(userStats.orderCount); // Order count

Batch Update Data

SQL Name: batchUpdateStatus

UPDATE dataset_orders
SET status = #{newStatus},
    update_time = NOW()
WHERE status = #{oldStatus}
  <if test="startDate">
    AND create_time >= #{startDate}
  </if>
  <if test="endDate">
    AND create_time &lt;= #{endDate}
  </if>

Backend Function:

/**
 * Batch Update Order Status - Backend Function Standalone Endpoint
 *
 * [API Path] POST /api/{appCode}/endpoint/batchUpdateOrderStatus
 *
 * [Platform Configuration] https://app.lovrabet.com/app/{appCode}/data/backend-function
 *
 * [HTTP Request Body Parameters]
 * {
 *   "oldStatus": "Original status",
 *   "newStatus": "New status",
 *   "startDate": "Start date (optional)",
 *   "endDate": "End date (optional)"
 * }
 *
 * [Return Data Structure]
 * { "success": true, "message": "...", "affectedRows": 10 }
 *
 * @param {Object} params - Request parameters
 * @param {string} params.oldStatus - Original status
 * @param {string} params.newStatus - New status
 * @param {string} params.startDate - Start date (optional)
 * @param {string} params.endDate - End date (optional)
 * @param {Object} context - Execution context (automatically injected by platform)
 * @returns {Object} Returns update result
 */
export default async function batchUpdateOrderStatus(params, context) {
  const { oldStatus, newStatus, startDate, endDate } = params;

  // Parameter validation
  if (!oldStatus || !newStatus) {
    throw new Error("oldStatus and newStatus cannot be empty");
  }

  if (oldStatus === newStatus) {
    throw new Error("New status cannot be the same as original status");
  }

  // Execute batch update SQL
  const affectedRows = await context.client.sql.execute({
    sqlCode: "batchUpdateStatus",
    params: {
      oldStatus,
      newStatus,
      startDate,
      endDate,
    },
  });

  // Return update result (affectedRows is typically an array containing the number of affected rows)
  const rowCount = affectedRows[0]?.affected_rows || 0;

  return {
    success: true,
    message: `Successfully updated ${rowCount} orders`,
    affectedRows: rowCount,
  };
}

SQL Results + Dataset Operations

SQL Name: getPendingOrders

SELECT
  o.id,
  o.order_no,
  o.total_amount,
  c.customer_name,
  c.phone
FROM dataset_orders o
JOIN dataset_customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
ORDER BY o.create_time DESC
LIMIT 50

Backend Function:

/**
 * Get Pending Orders and Calculate Discounts - Backend Function Standalone Endpoint
 *
 * [API Path] POST /api/{appCode}/endpoint/getPendingOrdersWithDiscount
 *
 * [Platform Configuration] https://app.lovrabet.com/app/{appCode}/data/backend-function
 *
 * [HTTP Request Body Parameters]
 * {}
 *
 * [Return Data Structure]
 * { "success": true, "orders": [...], "summary": {...} }
 *
 * @param {Object} params - Request parameters
 * @param {Object} context - Execution context (automatically injected by platform)
 * @returns {Object} Returns order list and summary
 */
export default async function getPendingOrdersWithDiscount(params, context) {
  // Dataset code mapping table
  const TABLES = {
    customers: "dataset_XXXXXXXXXX", // Dataset: Customers | Table: customers (replace with actual 32-bit code)
  };
  const models = context.client.models;

  // 1. Call custom SQL to get pending orders
  const orders = await context.client.sql.execute({
    sqlCode: "getPendingOrders",
    params: {},
  });

  // Collect all customer IDs for batch query
  const customerIds = [
    ...new Set(orders.map((o) => o.customer_id).filter(Boolean)),
  ];

  // Batch query customer information
  let customerMap = new Map();
  if (customerIds.length > 0) {
    const customers = await models[TABLES.customers].filter({
      where: { id: { $in: customerIds } },
    });
    customerMap = new Map(customers.tableData.map((c) => [c.id, c]));
  }

  // 2. Process each order, calculate discount
  const result = orders.map((order) => {
    // Get customer's membership level (from batch query results)
    const customer = customerMap.get(order.customer_id);

    let discount = 0;
    if (customer?.level === "VIP") {
      discount = 0.1; // VIP 10% off
    } else if (customer?.level === "SVIP") {
      discount = 0.15; // SVIP 15% off
    }

    const discountAmount = order.total_amount * discount;
    const finalAmount = order.total_amount - discountAmount;

    return {
      ...order,
      discount: discount * 100, // Convert to percentage
      discountAmount,
      finalAmount,
    };
  });

  // 3. Calculate summary information
  const totalOriginal = result.reduce((sum, o) => sum + o.total_amount, 0);
  const totalDiscount = result.reduce((sum, o) => sum + o.discountAmount, 0);
  const totalFinal = result.reduce((sum, o) => sum + o.finalAmount, 0);

  return {
    success: true,
    orders: result,
    summary: {
      totalOriginal,
      totalDiscount,
      totalFinal,
    },
  };
}

Using SQL in Transactions

SQL Name: decreaseStock

UPDATE dataset_products
SET stock = stock - #{quantity},
    update_time = NOW(),
    version = version + 1
WHERE id = #{productId}
  AND stock >= #{quantity}
  AND version = #{version}

Backend Function:

/**
 * Submit Order (Deduct Stock) - Backend Function Standalone Endpoint
 *
 * [API Path] POST /api/{appCode}/endpoint/submitOrderWithStock
 *
 * [Platform Configuration] https://app.lovrabet.com/app/{appCode}/data/backend-function
 *
 * [HTTP Request Body Parameters]
 * {
 *   "customerName": "Customer name",
 *   "customerPhone": "Customer phone",
 *   "items": [
 *     { "productId": "Product ID", "quantity": 1, "price": 100 }
 *   ]
 * }
 *
 * [Return Data Structure]
 * { "success": true, "message": "...", "orderId": "...", "totalAmount": 0 }
 *
 * @param {Object} params - Request parameters
 * @param {string} params.customerName - Customer name
 * @param {string} params.customerPhone - Customer phone
 * @param {Array} params.items - Order items
 * @param {Object} context - Execution context (automatically injected by platform)
 * @returns {Object} Returns submission result
 */
export default async function submitOrderWithStock(params, context) {
  const { items } = params; // items = [{ productId, quantity, price }, ...]

  // Dataset code mapping table
  const TABLES = {
    orders: "dataset_XXXXXXXXXX", // Dataset: Orders | Table: orders (replace with actual 32-bit code)
    orderItems: "dataset_YYYYYYYYYY", // Dataset: Order Items | Table: order_items
  };
  const models = context.client.models;

  // Use transaction to ensure atomicity of stock deduction and order creation
  const result = await context.client.db.transaction(async (tx) => {
    let totalAmount = 0;

    // 1. Check stock and deduct (using SQL)
    for (const item of items) {
      // Get current stock and version number
      const products = await tx.sql.execute({
        sqlCode: "getProductStock",
        params: { productId: item.productId },
      });

      if (products.length === 0) {
        throw new Error(`Product ${item.productId} does not exist`);
      }

      const product = products[0];
      const currentStock = product.stock;
      const currentVersion = product.version;

      if (currentStock < item.quantity) {
        throw new Error(
          `Product ${product.name} has insufficient stock, current stock: ${currentStock}`
        );
      }

      // Deduct stock (using version for optimistic locking)
      await tx.sql.execute({
        sqlCode: "decreaseStock",
        params: {
          productId: item.productId,
          quantity: item.quantity,
          version: currentVersion,
        },
      });

      totalAmount += product.price * item.quantity;
    }

    // 2. Create order (using models instead of tx.models)
    const orderId = await models[TABLES.orders].create({
      customerName: params.customerName,
      customerPhone: params.customerPhone,
      totalAmount,
      status: "paid",
    });

    // 3. Create order items
    for (const item of items) {
      await models[TABLES.orderItems].create({
        orderId: orderId,
        productId: item.productId,
        quantity: item.quantity,
        price: item.price,
        subtotal: item.price * item.quantity,
      });
    }

    return { orderId, totalAmount };
  });

  return {
    success: true,
    message: "Order submitted successfully",
    ...result,
  };
}

Key Concepts Summary

SQL Execution Syntax in Backend Function

// Returns array directly, no need to check execSuccess
const rows = await context.client.sql.execute({
  sqlCode: "yourSqlCode",
  params: {
    /* parameters */
  },
});

// rows is the result array
rows.forEach((row) => console.log(row));

Difference from Frontend SDK:

Environment	Call Method	Return Value	Data Access
Frontend	`client.sql.execute()`	`{ execSuccess, execResult }`	`result.execResult`
BFF	`context.client.sql.execute()`	Returns array directly	Use `rows` directly

// ❌ Wrong: No execResult in BFF
const result = await context.client.sql.execute({ sqlCode: "xxx" });
const rows = result.execResult; // undefined!

// ✅ Correct: BFF return value is the array directly
const rows = await context.client.sql.execute({ sqlCode: "xxx" });
const firstRow = rows[0];

Calling SQL in Transactions

const TABLES = {
  orders: "dataset_XXXXXXXXXX", // Dataset: Orders | Table: orders
};
const models = context.client.models;

await context.client.db.transaction(async (tx) => {
  // Use tx.sql.execute() to ensure execution within transaction
  await tx.sql.execute({
    sqlCode: "yourSqlCode",
    params: {
      /* ... */
    },
  });

  // Use models[TABLES.xxx] for dataset operations
  await models[TABLES.orders].create({
    /* ... */
  });
});

SQL Parameter Passing

// Use #{paramName} in SQL
const rows = await context.client.sql.execute({
  sqlCode: "yourSqlCode",
  params: {
    userId: 123,
    startDate: "2024-01-01",
    status: "active",
  },
});

Best Practices

Parameter Validation

export default async function myFunction(params, context) {
  const { userId, startDate, endDate } = params;

  // Required parameter validation
  if (!userId) {
    throw new Error("userId cannot be empty");
  }

  // Business rule validation
  if (startDate && endDate && startDate > endDate) {
    throw new Error("Start date cannot be greater than end date");
  }

  // Business logic...
}

Error Handling

export default async function myFunction(params, context) {
  try {
    const rows = await context.client.sql.execute({
      sqlCode: "yourSqlCode",
      params: {},
    });

    return {
      success: true,
      data: rows,
    };
  } catch (error) {
    // Log error
    console.error("SQL execution failed:", error);

    return {
      success: false,
      message: error.message || "Query failed",
    };
  }
}

Adding JSDoc Comments

Add complete JSDoc comments to Backend Functions for easier future maintenance:

/**
 * Get User Statistics - Backend Function Standalone Endpoint
 *
 * [API Path] POST /api/{appCode}/endpoint/getUserStats
 *
 * [Platform Configuration] https://app.lovrabet.com/app/{appCode}/data/backend-function
 *
 * [HTTP Request Body Parameters]
 * { "userId": "User ID (optional)" }
 *
 * [Return Data Structure]
 * { "success": true, "data": [...], "total": 10 }
 *
 * @param {Object} params - Request parameters
 * @param {string} params.userId - User ID (optional)
 * @param {Object} context - Execution context (automatically injected by platform)
 * @returns {Promise<Object>} Returns statistical results
 */
export default async function getUserStats(params, context) {
  // ...
}

Frequently Asked Questions

Q: How to debug SQL in Backend Function?

A: Use console.log to output debug information:

/**
 * Backend Function Example - Debug Logs
 *
 * [API Path] POST /api/{appCode}/endpoint/myFunction
 *
 * [Platform Configuration] https://app.lovrabet.com/app/{appCode}/data/backend-function
 */
export default async function myFunction(params, context) {
  console.log("Input parameters:", JSON.stringify(params));
  console.log("Current user:", context.userInfo?.username);

  const rows = await context.client.sql.execute({
    sqlCode: "yourSqlCode",
    params: {},
  });

  console.log("Query result row count:", rows.length);

  return rows;
}

Then check the Backend Function logs on the platform.

Q: What to do if SQL execution times out?

Optimize SQL queries, add indexes
Use LIMIT to restrict the number of returned rows
Reduce complex multi-table joins
Check for deadlocks

Q: How to get the number of affected rows?

A: This depends on the specific database:

const rows = await context.client.sql.execute({
  sqlCode: "batchUpdate",
  params: {},
});

// MySQL: Returns { affected_rows: number }
const affectedRows = rows[0]?.affected_rows || 0;

// PostgreSQL: Use RETURNING
// SQL: UPDATE ... RETURNING *
// rows is directly the updated row data
const affectedRows = rows.length;

Q: Will SQL in a transaction rollback on failure?

A: Yes, any exception in a transaction will automatically rollback:

await context.client.db.transaction(async (tx) => {
  // Execute SQL
  await tx.sql.execute({ sqlCode: "update1" });

  // If an exception is thrown here, update1 above will also rollback
  if (someError) {
    throw new Error("Operation failed");
  }

  // Transaction only commits when all operations succeed
  await tx.sql.execute({ sqlCode: "update2" });
});

Section Summary

Congratulations on completing the Backend Function series! This was the final article covering backend function capabilities. Key takeaways:

Concept	Description
`context.client.sql.execute()`	Execute SQL in BFF
Direct array return	BFF SQL return value differs from frontend
`tx.sql.execute()`	Execute SQL in transaction
SQL + Dataset API	Use both methods together

Frontend vs BFF Difference

// Frontend SDK: Returns { execSuccess, execResult }
const data = await client.sql.execute({ sqlCode: "xxx" });
if (data.execSuccess) { data.execResult.forEach(...); }

// Backend Function: Returns array directly
const rows = await context.client.sql.execute({ sqlCode: "xxx" });
rows.forEach(...);

Congratulations on Completing the Backend Function Series!

You have mastered all Backend Function capabilities. Continue exploring more features in the upcoming tutorials!

←上一节

Backend Function (II): Post-validation - Data Masking and Processing

Core Documentation

BFF API Reference — Complete Backend Function usage guide
SQL API Usage Guide — Complete custom SQL query documentation
Syntax Sugar — Convenient functions like sqlSafe

Advanced Topics

Sales Reports: Custom SQL — Using SQL queries for complex statistics
Multi-table Join Queries — Comparing filter multi-table queries and SQL
Master-Detail Forms: Transaction Processing — BF standalone endpoints for complex business logic

Difficulty Level: L2 | Estimated Time: 30 minutes

Requirements​

Core Concepts​

Frontend vs Backend Function SQL Calls​

Implementation Steps​

Step 1: Prepare Custom SQL​

Step 2: Call SQL in Backend Function​

Query Statistical Data​

Batch Update Data​

SQL Results + Dataset Operations​

Using SQL in Transactions​

Key Concepts Summary​

SQL Execution Syntax in Backend Function​

Calling SQL in Transactions​

SQL Parameter Passing​

Best Practices​

Parameter Validation​

Error Handling​

Adding JSDoc Comments​

Frequently Asked Questions​

Q: How to debug SQL in Backend Function?​

Q: What to do if SQL execution times out?​

Q: How to get the number of affected rows?​

Q: Will SQL in a transaction rollback on failure?​

Section Summary​

Congratulations on Completing the Backend Function Series!​

Related Reading​

Core Documentation​

Advanced Topics​

Requirements

Core Concepts

Frontend vs Backend Function SQL Calls

Implementation Steps

Step 1: Prepare Custom SQL

Step 2: Call SQL in Backend Function

Query Statistical Data

Batch Update Data

SQL Results + Dataset Operations

Using SQL in Transactions

Key Concepts Summary

SQL Execution Syntax in Backend Function

Calling SQL in Transactions

SQL Parameter Passing

Best Practices

Parameter Validation

Error Handling

Adding JSDoc Comments

Frequently Asked Questions

Q: How to debug SQL in Backend Function?

Q: What to do if SQL execution times out?

Q: How to get the number of affected rows?

Q: Will SQL in a transaction rollback on failure?

Section Summary

Congratulations on Completing the Backend Function Series!

Related Reading

Core Documentation

Advanced Topics