Back to Details

cap-apps-sql-query

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

@domoinc/toolkit - SqlClient

@domoinc/toolkit - SqlClient

When to use this skill

何时使用该技能

Apply when executing SQL queries against Domo datasets, using AI-generated SQL from 
AIClient.text_to_sql
, or any time you need to run raw SQL against a dataset alias. Use 
SqlClient
from 
@domoinc/toolkit
instead of 
domo.post('/sql/v1/')
.
当你需要针对Domo数据集执行SQL查询、使用
AIClient.text_to_sql
生成的AI SQL，或者任何需要针对数据集别名执行原生SQL的场景时，都可以使用该技能。请使用
@domoinc/toolkit
中的
SqlClient
，而非
domo.post('/sql/v1/')
。

Overview

概述

SqlClient
is the toolkit wrapper for Domo's SQL API. Use it to execute SQL queries against datasets mapped in 
manifest.json
.
SqlClient
是Domo SQL API的工具包封装。你可以使用它对
manifest.json
中映射的数据集执行SQL查询。

Import and instantiation

导入与实例化

typescript
import { SqlClient } from '@domoinc/toolkit';

const sqlClient = new SqlClient();
typescript
import { SqlClient } from '@domoinc/toolkit';

const sqlClient = new SqlClient();

Methods

方法

get(alias, query)

get(alias, query)

Executes a SQL query against a dataset.
typescript
const result = await sqlClient.get('datasetAlias', 'SELECT * FROM datasetAlias');
Parameters:
  • alias
    (
    string
    ): dataset alias from 
    manifest.json
    mappings
  • query
    (
    string
    ): SQL query string
Returns: 
Promise<Response<SqlResponse>>
针对数据集执行SQL查询。
typescript
const result = await sqlClient.get('datasetAlias', 'SELECT * FROM datasetAlias');
参数：
  • alias
    （
    string
    类型）：来自
    manifest.json
    映射的数据集别名
  • query
    （
    string
    类型）：SQL查询字符串
返回值：
Promise<Response<SqlResponse>>

parsePageFilters(datasets, produceClauses?)

parsePageFilters(datasets, produceClauses?)

Transforms Domo page filters into SQL predicates.
typescript
// Get predicates as objects
const predicates = sqlClient.parsePageFilters(['datasetAlias']);

// Get predicates as WHERE/HAVING clause strings
const clauses = sqlClient.parsePageFilters(['datasetAlias'], true);
将Domo页面过滤器转换为SQL谓词。
typescript
// 获取对象形式的谓词
const predicates = sqlClient.parsePageFilters(['datasetAlias']);

// 获取WHERE/HAVING子句字符串形式的谓词
const clauses = sqlClient.parsePageFilters(['datasetAlias'], true);

Response format

响应格式

CRITICAL: SQL API returns a columnar format, not an array of row objects.
typescript
// Response shape:
{
  columns: ['vendor', 'Total Spend'],
  rows: [
    ['Sysco Utah', 1880794.74],
    ['Intermountain Meats', 809389.15]
  ],
  metadata: [...],
  numRows: 8,
  numColumns: 2,
  datasource: 'dataset-uuid',
  fromcache: true
}
You must zip 
columns
+ 
rows
into objects for UI rendering:
typescript
const result = await sqlClient.get('myAlias', sql);
const res = result?.body || result?.data || result;
const colNames: string[] = res?.columns || [];
const rawRows: unknown[][] = res?.rows || [];

const rows = rawRows.map((row) => {
  const obj: Record<string, unknown> = {};
  colNames.forEach((col, i) => {
    obj[col] = row[i];
  });
  return obj;
});
// rows => [{ vendor: 'Sysco Utah', 'Total Spend': 1880794.74 }, ...]
重要提示：SQL API返回的是列格式数据，而非行对象数组。
typescript
// 响应结构：
{
  columns: ['vendor', 'Total Spend'],
  rows: [
    ['Sysco Utah', 1880794.74],
    ['Intermountain Meats', 809389.15]
  ],
  metadata: [...],
  numRows: 8,
  numColumns: 2,
  datasource: 'dataset-uuid',
  fromcache: true
}
你需要将
columns
和
rows
合并为对象，以便用于UI渲染：
typescript
const result = await sqlClient.get('myAlias', sql);
const res = result?.body || result?.data || result;
const colNames: string[] = res?.columns || [];
const rawRows: unknown[][] = res?.rows || [];

const rows = rawRows.map((row) => {
  const obj: Record<string, unknown> = {};
  colNames.forEach((col, i) => {
    obj[col] = row[i];
  });
  return obj;
});
// rows => [{ vendor: 'Sysco Utah', 'Total Spend': 1880794.74 }, ...]

Common pattern: AI text-to-SQL + SqlClient

常见模式：AI文本转SQL + SqlClient

Use 
AIClient
to generate SQL, then 
SqlClient
to execute it:
typescript
import { AIClient, SqlClient } from '@domoinc/toolkit';

// 1) Generate SQL from natural language
const aiResponse = await AIClient.text_to_sql(question, [
  {
    dataSourceName: 'myAlias',
    description: 'Description of the dataset',
    columns: [
      { name: 'vendor', type: 'string' },
      { name: 'amount', type: 'number' }
    ]
  }
]);
const responseBody = aiResponse.data || aiResponse.body || aiResponse;
const sql = responseBody.output || responseBody.choices?.[0]?.output;

// 2) Execute SQL
const sqlClient = new SqlClient();
const result = await sqlClient.get('myAlias', sql);

// 3) Parse columnar response into row objects
const res = result?.body || result?.data || result;
const colNames: string[] = res?.columns || [];
const rawRows: unknown[][] = res?.rows || [];
const rows = rawRows.map((row) => {
  const obj: Record<string, unknown> = {};
  colNames.forEach((col, i) => {
    obj[col] = row[i];
  });
  return obj;
});
使用
AIClient
生成SQL，再用
SqlClient
执行：
typescript
import { AIClient, SqlClient } from '@domoinc/toolkit';

// 1) 从自然语言生成SQL
const aiResponse = await AIClient.text_to_sql(question, [
  {
    dataSourceName: 'myAlias',
    description: 'Description of the dataset',
    columns: [
      { name: 'vendor', type: 'string' },
      { name: 'amount', type: 'number' }
    ]
  }
]);
const responseBody = aiResponse.data || aiResponse.body || aiResponse;
const sql = responseBody.output || responseBody.choices?.[0]?.output;

// 2) 执行SQL
const sqlClient = new SqlClient();
const result = await sqlClient.get('myAlias', sql);

// 3) 将列格式响应解析为行对象
const res = result?.body || result?.data || result;
const colNames: string[] = res?.columns || [];
const rawRows: unknown[][] = res?.rows || [];
const rows = rawRows.map((row) => {
  const obj: Record<string, unknown> = {};
  colNames.forEach((col, i) => {
    obj[col] = row[i];
  });
  return obj;
});

Important notes

重要注意事项

  • Use 
    SqlClient
    instead of 
    domo.post('/sql/v1/')
    .
  • SQL endpoint ignores page filters; use 
    parsePageFilters()
    to inject filters manually when needed.
  • Dataset alias in SQL 
    FROM
    must match manifest alias (example: 
    SELECT * FROM vendorPayments
    ).
  • Response is always columnar (
    columns
    + 
    rows
    ), never a flat array of objects.
  • Response may be in 
    .body
    , 
    .data
    , or directly on result; parse defensively.
  • 请使用
    SqlClient
    而非
    domo.post('/sql/v1/')
    。
  • SQL端点会忽略页面过滤器；如果需要，可使用
    parsePageFilters()
    手动注入过滤器。
  • SQL语句
    FROM
    子句中的数据集别名必须与manifest中的别名匹配（例如：
    SELECT * FROM vendorPayments
    ）。
  • 响应始终为列格式（
    columns
    + 
    rows
    ），绝不会是扁平化的对象数组。
  • 响应数据可能存储在
    .body
    、
    .data
    或直接在结果对象上；请进行防御性解析。