#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

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

#Permissions

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

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

• 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

#save options

#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:

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

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)

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

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

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

#Save template and reuse

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

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

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.

#Related

• ctx.resource: data resource; SQLResource uses flowSql internally
• ctx.initResource(): initialize SQLResource for paginated lists
• ctx.request(): generic HTTP requests