Skip to main content

SQL API Reference

v1.1.19+

The SQL API allows you to execute custom SQL queries configured on the Lovrabet platform.

Version Requirement

This feature is supported starting from SDK v1.1.19.

Beginner's Guide

If you are using custom SQL for the first time, we recommend reading the Custom SQL Usage Tutorial first. This document serves only as an API reference.

When to Use the SQL API?​

ScenarioRecommended API
Simple CRUD operationsDataset API
Cross-table join queriesSQL API
Complex aggregate statisticsSQL API
Custom reportsSQL API

API Methods​

execute() - Recommended Method v1.2.0+​

Execute SQL queries using the client.sql namespace.

Method Signature​

client.sql.execute<T>({ sqlCode, params }: SqlExecuteParams): Promise<SqlExecuteResult<T>>
client.sql.execute<T>(sqlCode: string | number, params?: Record<string, any>): Promise<SqlExecuteResult<T>>

Parameters​

ParameterTypeRequiredDescription
sqlCodestring | numberYesSQL code, format: "xxxxx-xxxxx" or numeric ID
paramsRecord<string, any>NoSQL parameter object for parameterized queries

Return Value​

interface SqlExecuteResult<T> {
  execSuccess: boolean; // Whether SQL execution was successful
  execResult?: T[]; // Query result array (only exists on success)
}
Important

The SDK returns an object containing execSuccess and execResult, not a direct array.

Your application must check execSuccess before using execResult.

executeSql() - Compatible Method​

Uses the client.api namespace (backward compatible, not recommended).

client.api.executeSql<T>(sqlCode: string | number, params?: Record<string, any>): Promise<SqlExecuteResult<T>>

Quick Start​

import { createClient, sqlSafe } from "@lovrabet/sdk";

// 1. Create client
const client = createClient({
  accessKey: process.env.LOVRABET_ACCESS_KEY,
  appCode: "your-app-code",
});

// 2. Execute SQL - using sqlSafe syntax sugar (recommended)
const { data, error } = await sqlSafe(() =>
  client.sql.execute({ sqlCode: "xxxxx-xxxxx" })
);

// 3. Handle result (single check)
if (error) {
  console.error("Query failed:", error.message);
  return;
}

// data is directly the query result array
console.log(`Query successful, returned ${data.length} records`);
data.forEach((row) => console.log(row));
// Without sqlSafe: need to manually check execSuccess
const result = await client.sql.execute({ sqlCode: "xxxxx-xxxxx" });

if (result.execSuccess && result.execResult) {
  console.log(`Query successful, returned ${result.execResult.length} records`);
  result.execResult.forEach((row) => console.log(row));
} else {
  console.error("SQL execution failed");
}

Parameterized Query​

// Use #{paramName} in SQL to define parameters
// Example: SELECT * FROM users WHERE id = #{userId} AND status = #{status}

// Recommended: object parameters
const result = await client.sql.execute({
  sqlCode: "xxxxx-xxxxx",
  params: {
    userId: 123,
    status: "active",
  }
});

// Or pass parameters directly
const result = await client.sql.execute("xxxxx-xxxxx", {
  userId: 123,
  status: "active",
});

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

TypeScript Type Support​

// Define result type
interface UserStat {
  id: number;
  name: string;
  login_count: number;
}

// Use sqlSafe + generics
const { data, error } = await sqlSafe<UserStat>(() =>
  client.sql.execute({ sqlCode: "xxxxx-xxxxx" })
);

if (error) {
  console.error("Query failed:", error.message);
  return;
}

// data is UserStat[], type-safe
data.forEach((user) => {
  console.log(`${user.name}: ${user.login_count} logins`);
});

Error Handling​

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

// Single check handles all errors
const { data, error } = await sqlSafe(() =>
  client.sql.execute({ sqlCode: "xxxxx-xxxxx" })
);

if (error) {
  // Distinguish error types
  if (error.code === 'SQL_ERROR') {
    // Business logic failure (execSuccess = false)
    console.error('SQL execution failed:', error.cause);
  } else {
    // HTTP error (404, 500, etc.)
    console.error('Request failed:', error.message, error.status);
  }
  return;
}

// data is directly the query result array
console.log(`Found ${data.length} records`);

Distinguishing Error Types​

const { data, error } = await sqlSafe(() =>
  client.sql.execute({ sqlCode: 'user-stats' })
);

if (error) {
  // Business error: execSuccess = false
  if (error.code === 'SQL_ERROR') {
    const originalResult = error.cause;  // Original SqlExecuteResult
    console.error('Business failure:', originalResult);
  }
  // HTTP error: 404, 500, etc.
  else if (error.status) {
    console.error('HTTP error:', error.status, error.message);
  }
  // Other errors
  else {
    console.error('Unknown error:', error.message);
  }
  return;
}

// Use the data
data.forEach(row => console.log(row));

Common Error Codes​

Error CodeDescriptionSolution
401Authentication failedCheck AccessKey or re-login
404SQL not foundCheck if SQL Code is correct
500Server errorRetry later or contact technical support

FAQ​

What causes execSuccess to be false?​

execSuccess: false indicates that the SQL execution failed on the platform. Common causes:

  • SQL syntax error
  • Referenced table or field does not exist
  • SQL parameter passing error or type mismatch
  • Database connection issue

Solution: Test whether the SQL can execute normally on the platform's custom SQL management page first.

How to debug SQL queries?​

Enable SDK debug mode:

const client = createClient({
  appCode: "your-app",
  accessKey: process.env.ACCESS_KEY,
  options: {
    debug: true, // Enable debug logging
  },
});

How to handle large amounts of data?​

  1. Use LIMIT in SQL

  2. Use pagination parameters

  3. Use background tasks: For processing large amounts of data, it is recommended to use scheduled tasks instead of real-time queries

Which SQL operations are supported?​

  • SELECT queries: Fully supported
  • UPDATE/DELETE: Partially supported (requires admin permission configuration)
  • INSERT: Not recommended (use Dataset API instead)
  • DDL operations: Not supported (CREATE/DROP TABLE, etc.)

Differences in Backend Function​

If you use SQL in the Backend Function (BFF) on the Lovrabet platform, the return value structure differs from the frontend SDK:

Return Value Comparison​

EnvironmentCall MethodReturn ValueData Access Method
Frontend SDKclient.sql.execute(){ execSuccess, execResult }Need to check execSuccess, get data from execResult
Backend Functioncontext.client.sql.execute()Direct array [{ col: val }, ...]Use result directly, no execResult field

Backend Function Usage Example​

// In BFF environment
export default async function myEndpoint(params, context) {
  // Returns array directly, no need to check execSuccess
  const rows = await context.client.sql.execute({
    sqlCode: "user-stats",
    params: { userId: params.userId }
  });

  // rows is the query result array
  return { data: rows };
}
Note the Difference

There is no execResult field in BFF. The following pattern is incorrect:

// Incorrect: result.execResult is undefined in BFF
const result = await context.client.sql.execute({ sqlCode: 'xxx' });
const rows = result.execResult;  // undefined!

// Correct: BFF return value is directly an array
const rows = await context.client.sql.execute({ sqlCode: 'xxx' });
const firstRow = rows[0];  // { col1: val1, col2: val2, ... }

Error Handling​

Frontend SDK: Check execSuccess or use sqlSafe

const result = await client.sql.execute({ sqlCode: 'xxx' });
if (!result.execSuccess) {
  throw new Error('Query failed');
}

Backend Function: Execution failure throws an exception directly, catch with try-catch

try {
  const rows = await context.client.sql.execute({ sqlCode: 'xxx' });
  return { data: rows };
} catch (error) {
  throw new Error(`Query failed: ${error.message}`);
}

Last updated: 2025-11-12