ContextQMD
Back to Nocobase

ctx.sql

docs/docs/en/runjs/context/sql.md

2.0.596.3 KB
Original Source

ctx.sql

ctx.sql provides SQL execution and management, often used in RunJS (e.g. JSBlock, event flow) to access the database directly. It supports ad-hoc SQL, running saved SQL templates by ID, parameter binding, template variables ({{ctx.xxx}}), and result type control.

Use Cases

ScenarioDescription
JSBlockCustom reports, complex filtered lists, cross-table aggregation
Chart blockSaved SQL templates as chart data source
Event flow / linkageRun predefined SQL and use results in logic
SQLResourceWith ctx.initResource('SQLResource') for paginated lists, etc.

Note: ctx.sql uses the flowSql API to access the database; ensure the current user has execute permission on the target data source.

Permissions

PermissionMethodDescription
Logged-in userrunByIdRun by configured SQL template ID
SQL config permissionrun, save, destroyAd-hoc SQL, save/update/delete SQL templates

Front-end logic for normal users can use ctx.sql.runById(uid, options); for dynamic SQL or template management, the current role must have SQL config permission.

Type

ts
sql: FlowSQLRepository;

interface FlowSQLRepository {
  run<T = any>(
    sql: string,
    options?: {
      bind?: Record<string, any> | any[];
      type?: 'selectRows' | 'selectRow' | 'selectVar';
      dataSourceKey?: string;
      filter?: Record<string, any>;
    },
  ): Promise<T>;

  save(options: { uid: string; sql: string; dataSourceKey?: string }): Promise<void>;

  runById<T = any>(
    uid: string,
    options?: {
      bind?: Record<string, any> | any[];
      type?: 'selectRows' | 'selectRow' | 'selectVar';
      dataSourceKey?: string;
      filter?: Record<string, any>;
    },
  ): Promise<T>;

  destroy(uid: string): Promise<void>;
}

Common Methods

MethodDescriptionPermission
ctx.sql.run(sql, options?)Run ad-hoc SQL; supports parameter binding and template variablesSQL config
ctx.sql.save({ uid, sql, dataSourceKey? })Save/update SQL template by ID for reuseSQL config
ctx.sql.runById(uid, options?)Run saved SQL template by IDAny logged-in user
ctx.sql.destroy(uid)Delete SQL template by IDSQL config
  • run: for debugging SQL; requires config permission.
  • save, destroy: for managing SQL templates; require config permission.
  • runById: available to normal users; only runs saved templates.
  • Call save when a SQL template changes.

Options

run / runById options

OptionTypeDescription
bindRecord<string, any>Bound variables. Use $name in SQL and pass object { name: value }
type'selectRows' | 'selectRow' | 'selectVar'Result type: multiple rows, single row, single value; default selectRows
dataSourceKeystringData source key; default is main data source
filterRecord<string, any>Extra filter (if supported)

save options

OptionTypeDescription
uidstringTemplate unique id; use with runById(uid, ...)
sqlstringSQL text; supports {{ctx.xxx}} and $name placeholders
dataSourceKeystringOptional data source key

Template Variables and Parameter Binding

Template variables {{ctx.xxx}}

In SQL you can use {{ctx.xxx}} to reference context variables; they are resolved before execution:

js
// Reference ctx.user.id
const user = await ctx.sql.run(
  'SELECT * FROM users WHERE id = {{ctx.user.id}}',
  { type: 'selectRow' }
);

Variable sources are the same as for ctx.getVar() (e.g. ctx.user.*, ctx.record.*, custom ctx.defineProperty, etc.).

Parameter binding

  • Use $name in SQL and pass bind: { name: value }
js
const users = await ctx.sql.run(
  'SELECT * FROM users WHERE status = $status AND age > $minAge',
  { bind: { status: 'active', minAge: 18 }, type: 'selectRows' }
);

Examples

Ad-hoc SQL (requires SQL config permission)

js
// Multiple rows (default)
const rows = await ctx.sql.run('SELECT * FROM users LIMIT 10');

// Single row
const user = await ctx.sql.run(
  'SELECT * FROM users WHERE id = $id',
  { bind: { id: 1 }, type: 'selectRow' }
);

// Single value (e.g. COUNT, SUM)
const total = await ctx.sql.run(
  'SELECT COUNT(*) AS total FROM users',
  { type: 'selectVar' }
);

Template variables

js
ctx.defineProperty('minId', { get: () => 1 });

const rows = await ctx.sql.run(
  'SELECT * FROM users WHERE id > {{ctx.minId}}',
  { type: 'selectRows' }
);

Save template and reuse

js
// Save (requires SQL config permission)
await ctx.sql.save({
  uid: 'active-users-report',
  sql: 'SELECT * FROM users WHERE status = $status ORDER BY created_at DESC',
});

// Any logged-in user can run
const users = await ctx.sql.runById('active-users-report', {
  bind: { status: 'active' },
  type: 'selectRows',
});

// Delete template (requires SQL config permission)
await ctx.sql.destroy('active-users-report');

Paginated list (SQLResource)

js
// For pagination and filters, use SQLResource
ctx.initResource('SQLResource');
ctx.resource.setFilterByTk('saved-sql-uid');  // Saved SQL template ID
ctx.resource.setBind({ status: 'active' });
await ctx.resource.refresh();
const data = ctx.resource.getData();
const meta = ctx.resource.getMeta();  // page, pageSize, etc.

Relation to ctx.resource, ctx.request

UseRecommended
Run SQLctx.sql.run() or ctx.sql.runById()
SQL paginated list (block)ctx.initResource('SQLResource') + ctx.resource.refresh()
Generic HTTPctx.request()

ctx.sql wraps the flowSql API for SQL; ctx.request is for arbitrary API calls.

Notes

  • Use parameter binding ($name) instead of string concatenation to avoid SQL injection.
  • With type: 'selectVar' the result is a scalar (e.g. for COUNT, SUM).
  • Template variables {{ctx.xxx}} are resolved before execution; ensure the context defines them.