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

The previous article covered Post-validation Hooks. This is the final article in the Backend Function series, introducing how to execute custom SQL in Backend Functions.

Calling SQL from the frontend is suitable for data display reports, but 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.

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

AI-Assisted Development (Recommended)

Why Can AI Help You Call SQL in BFF?

Without rabetbase, executing SQL on the server side means building a backend service. Backend developers pick a framework (Spring Boot? Laravel? Express?), configure database connection pooling (connection count? timeout?), write SQL execution logic, add authentication middleware, handle parameter binding, error handling, logging... The SQL itself is 10 lines, the surrounding code is 200 lines. Then deploy to a server: buy a machine, set up the environment, configure nginx reverse proxy, add SSL. Integration debugging is even more painful: the frontend calls an API that returns { code: 200, data: { success: true, result: [...] } } which doesn't match expectations -- two extra layers of wrapping. Parameter passing: the frontend sends JSON, the backend's SQL parameter binding uses ? placeholders, and converting JSON to SQL parameters requires another pile of middleware code. Want to query SQL first and then update data in the same request? Transaction management is all manual: connection.beginTransaction() -> query SQL -> process results -> UPDATE -> commit -> rollback -> finally release connection. Once forgot connection.release(), ran overnight with a connection leak, and the entire system went down the next day. A single "query SQL then update" took 3 days from setting up the service to getting it working.

With rabetbase: no backend service to build. The BFF runtime has built-in SQL execution and dataset API, and both can be mixed within the same transaction. The CLI's sql save saves SQL, bff new --type ENDPOINT creates endpoint scaffolds (transaction API ready), and Skills guide AI to distinguish between frontend and BFF calling conventions and correctly mix SQL and dataset API in transactions. No server to build, no connection pool to configure, no guessing API formats.

All You Need to Tell AI

In Claude Code, enter:

Use rabetbase CLI to help me create a custom SQL getUserStats that queries user statistics (order count, total amount), supporting filtering by userId. Then create a BFF function of ENDPOINT type called getUserStats that calls this SQL and returns statistics for a single user or all users.

What AI Will Do

AI will use rabetbase CLI to automatically complete the following:

Query user and order dataset structures (rabetbase dataset detail)
Create and save custom SQL (rabetbase sql validate + rabetbase sql save)
Create BFF endpoint scaffold (rabetbase bff new --type ENDPOINT)
Write business logic (SQL call + result processing)
Push to the platform to take effect (rabetbase bff push)

Once complete, the frontend can call it via bff.execute(). Below is the complete code generated by AI.

Manual Operation (Alternative)

Method 1: Manual CLI

# Create SQL file
mkdir -p .rabetbase/sql
# Edit .rabetbase/sql/getUserStats.sql (refer to code below)

# Validate and save
rabetbase sql validate --file .rabetbase/sql/getUserStats.sql --format json
rabetbase sql save --file .rabetbase/sql/getUserStats.sql --db <dbId> --format json

# Create BFF endpoint
rabetbase bff new --type ENDPOINT --name getUserStats
# Edit the generated file (refer to code below)
rabetbase bff push --yes --type ENDPOINT --name getUserStats

Method 2: Platform UI

Create custom SQL: https://app.lovrabet.com/app/{appCode}/data/custom-sql
Create BFF standalone endpoint: https://app.lovrabet.com/app/{appCode}/data/backend-function

Step 1: SQL Code Reference

.rabetbase/sql/getUserStats.sql:

-- @lovrabet sqlName=getUserStats description=User statistics query
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: BFF Endpoint Code Reference

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. After creation, this standalone endpoint can be configured on the platform
 *
 * [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,
    };
  }
}

After editing, push to the platform:

rabetbase bff status --format json
rabetbase bff push --yes --type ENDPOINT --name getUserStats

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

Create local SQL file .rabetbase/sql/batchUpdateStatus.sql:

-- @lovrabet sqlName=batchUpdateStatus description=Batch update order status
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>

Validate and save SQL:

rabetbase sql validate --file .rabetbase/sql/batchUpdateStatus.sql --format json
rabetbase sql save --file .rabetbase/sql/batchUpdateStatus.sql --db <dbId> --format json

Backend Function (created with rabetbase bff new --type ENDPOINT --name batchUpdateOrderStatus, then edit):

/**
 * 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

-- @lovrabet sqlName=getPendingOrders description=Get pending orders
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

Validate and save SQL:

rabetbase sql validate --file .rabetbase/sql/batchUpdateStatus.sql --format json
rabetbase sql save --file .rabetbase/sql/batchUpdateStatus.sql --db <dbId> --format json

Backend Function (created with rabetbase bff new --type ENDPOINT --name batchUpdateOrderStatus, then edit):

/**
 * 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-character 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

-- @lovrabet sqlName=decreaseStock description=Deduct stock (optimistic locking)
UPDATE dataset_products
SET stock = stock - #{quantity},
    update_time = NOW(),
    version = version + 1
WHERE id = #{productId}
  AND stock >= #{quantity}
  AND version = #{version}

Validate and save SQL:

rabetbase sql validate --file .rabetbase/sql/batchUpdateStatus.sql --format json
rabetbase sql save --file .rabetbase/sql/batchUpdateStatus.sql --db <dbId> --format json

Backend Function (created with rabetbase bff new --type ENDPOINT --name batchUpdateOrderStatus, then edit):

/**
 * 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-character 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) {
  // ...
}

FAQ

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 all development tutorials! Key knowledge points:

Knowledge Point	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 Development Phase!

You have mastered all core skills for Lovrabet development. Next is Build & Deploy to bring your work online!

←上一节

Post-Save Data Processing

Build & Deploy

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 endpoint for complex business logic

Difficulty Level: L2 | Estimated Time: 30 minutes

Requirements​

Core Concepts​

Frontend vs Backend Function SQL Calls​

Implementation Steps​

AI-Assisted Development (Recommended)​

All You Need to Tell AI​

What AI Will Do​

Method 1: Manual CLI​

Method 2: Platform UI​

Step 1: SQL Code Reference​

Step 2: BFF Endpoint Code Reference​

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​

FAQ​

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 Development Phase!​

Related Reading​

Core Documentation​

Advanced Topics​

Requirements

Core Concepts

Frontend vs Backend Function SQL Calls

Implementation Steps

AI-Assisted Development (Recommended)

All You Need to Tell AI

What AI Will Do

Method 1: Manual CLI

Method 2: Platform UI

Step 1: SQL Code Reference

Step 2: BFF Endpoint Code Reference

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

FAQ

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 Development Phase!

Related Reading

Core Documentation

Advanced Topics