Server Side Scripts

Utilize modern Javascript (ES2020) to enhance the capabilities of a DataSource. These scripts execute within a secure sandbox environment. Employ standard javascript APIs to augment your business functionality.

Edit DataSource UI

Server-side scripts empower developers to control data querying and updating in the database entirely. Incorporate business validations before and after inserting, editing, or deleting data. The CloudIO Platform makes it easy to interact with the database through the DataSource while keeping the underlying database connection secure. Auditing tracks all changes made through the DataSource, including who made them and when. Perform all changes in compliance with the security protocols defined by the user's access level.

Ensure to await the completion of all asynchronous function calls. Unawaited promises may only execute partially.

Variables Available In Scripts

Pre Query Script

const db: DB;
const query: Query<DataSourceType>;
const session: Session;
let rows: Row<DataSourceType>[];
let skipQuery: boolean;

Post Query Script

const db: DB;
const query: Query<DataSourceType>;
const session: Session;
let rows: Row<DataSourceType>[];

Before Insert Script

const db: DB;
const session: Session;
let rows: Row<DataSourceType>[];
let skipDML: boolean;

After Insert Script

const db: DB;
const session: Session;
let rows: Row<DataSourceType>[];

Before Update Script

const db: DB;
const session: Session;
let rows: Row<DataSourceType>[];
let skipDML: boolean;

After Update Script

const db: DB;
const session: Session;
let rows: Row<DataSourceType>[];

Before Delete Script

const db: DB;
const session: Session;
let rows: Row<DataSourceType>[];
let skipDML: boolean;

After Delete Script

const db: DB;
const session: Session;
let rows: Row<DataSourceType>[];

Typescript Types

type DataSourceName = keyof AllDatasources;

type DataType<T> = T;

type NewRow<T> = {
  [K in keyof T]?: T[K] extends 'Date' | 'DateTime' | undefined
    ? Date
    : DataType<T[K]>;
} & {
  _ca?: string;
  _cid?: string;
  _id?: string;
  _orig?: Partial<T>;
  _newKeys?: string[];
  _ov?: unknown;
  _rs?: 'N' | 'I';
};

type DBRow<T> = {
  [K in keyof T]: T[K] extends 'Date' | 'DateTime' | undefined
    ? string
    : DataType<T[K]>;
} & {
  _ca?: string;
  _cid?: string;
  _id?: string;
  _orig?: Partial<T>;
  _newKeys?: string[];
  _ov?: unknown;
  _rs: 'Q' | 'U' | 'D' | 'V';
};

type Row<T> = NewRow<T> | DBRow<T>;

export type YN = 'Y' | 'N';

const FieldTypes = {
  Attachment: 'Attachment',
  // Address: 'Address',
  // Binary: 'Binary',
  Boolean: 'Boolean',
  // Currency: 'Currency',
  Date: 'Date',
  DateTime: 'DateTime',
  Decimal: 'Decimal',
  Double: 'Double',
  Email: 'Email',
  EncryptedString: 'EncryptedString',
  Integer: 'Integer',
  // IntegerArray: 'IntegerArray',
  // Location: 'Location',
  JSON: 'JSON',
  // Password: 'Password',
  Percent: 'Percent',
  Phone: 'Phone',
  Reference: 'Reference',
  String: 'String',
  // StringArray: 'StringArray',
  Textarea: 'Textarea',
  // Time: 'Time',
  URL: 'URL',
  UserID: 'UserID',
  YN: 'YN',
} as const;

type FieldType = keyof typeof FieldTypes;

const FieldTypeArray = Object.keys(FieldTypes) as FieldType[];

type ISODateString = string;

export interface SubscriptionEvent {
  appUid: string;
  ctx: string;
  id: string;
  ts: string;
  data: unknown;
}

export interface Email {
  to: string;
  subject: string;
  text: string;
  html?: string;
}

interface BaseWorkflowInfo {
  appUid: string;
  executionId: string;
  nodeId: string;
  version: number;
  wfInstUid: string;
  wfUid: string;
  requestId: string;
}

interface ApprovedWorkflowInfo extends BaseWorkflowInfo {
  approvalStatus: 'Approved';
  nodeType: 'Approval' | 'MultiApproval';
}

interface RejectedWorkflowInfo extends BaseWorkflowInfo {
  approvalStatus: 'Rejected';
  nodeType: 'Approval' | 'MultiApproval';
  rejectReason: string;
}

type WorkflowInfo = ApprovedWorkflowInfo | RejectedWorkflowInfo;

export type Param = string | number | null;

export interface Options {
  appUid?: string;
}

interface DB {
  find<T extends DataSourceName>(
    ds: T,
    request: DBQuery<AllDatasources[T]>,
    options?: Options,
  ): Promise<DBRow<AllDatasources[T]>[]>;
  executeQuery(
    sql: string,
    params?: Param[],
    options?: Options,
  ): Promise<Record<string, any>[]>;
  queryString(
    sql: string,
    params?: Param[],
    options?: Options,
  ): Promise<string | undefined>;
  queryNumber(
    sql: string,
    params?: Param[],
    options?: Options,
  ): Promise<number | undefined>;
  executeUpdate(
    sql: string,
    params?: Param[],
    options?: Options,
  ): Promise<void>;
  executeUpdateMany(
    sql: string,
    paramsArray?: Param[][],
    options?: Options,
  ): Promise<void>;
  insertMany<T extends DataSourceName>(
    ds: T,
    rows: NewRow<AllDatasources[T]>[],
    options?: Options,
  ): Promise<DBRow<AllDatasources[T]>[]>;
  updateMany<T extends DataSourceName>(
    ds: T,
    rows: DBRow<AllDatasources[T]>[],
    options?: Options,
  ): Promise<DBRow<AllDatasources[T]>[]>;
  deleteMany<T extends DataSourceName>(
    ds: T,
    rows: DBRow<AllDatasources[T]>[],
    options?: Options,
  ): Promise<DBRow<AllDatasources[T]>[]>;
  findOne<T extends DataSourceName>(
    ds: T,
    request: DBQuery<AllDatasources[T]>,
    options?: Options,
  ): Promise<DBRow<AllDatasources[T]> | null>;
  insertOne<T extends DataSourceName>(
    ds: T,
    row: NewRow<AllDatasources[T]>,
    options?: Options,
  ): Promise<DBRow<AllDatasources[T]>>;
  updateOne<T extends DataSourceName>(
    ds: T,
    row: DBRow<AllDatasources[T]>,
    options?: Options,
  ): Promise<DBRow<AllDatasources[T]>>;
  deleteOne<T extends DataSourceName>(
    ds: T,
    row: DBRow<AllDatasources[T]>,
    options?: Options,
  ): Promise<DBRow<AllDatasources[T]>>;
  startWorkflow(
    wfUid: string,
    version: number,
    description: string,
    state?: Record<string, unknown>,
  ): Promise<string>;
  retryWorkflowNode(
    wfUid: string,
    version: number,
    wfInstUid: string,
    nodeId: string,
    executionId: string,
  ): Promise<void>;
  completeWorkflowNode(data: WorkflowInfo): Promise<void>;
  completeWaitForWorkflowNode(
    $data: string,
    $state: Record<string, unknown>,
  ): Promise<void>;
  publish(ctx: string, id: string, data: unknown): Promise<void>;
  publishEvent(event: SubscriptionEvent): Promise<void>;
  processMustacheTemplate(
    template: string,
    data: Record<string, unknown>,
  ): Promise<string>;
  sendEmail(email: Email): Promise<void>;
  fetch(
    resource: string,
    init?: {
      insecure?: boolean;
      method?: 'GET' | 'POST' | 'PUT' | 'OPTIONS' | 'DELETE' | 'HEAD';
      body?: string;
      headers?: Record<string, string>;
    },
  ): Promise<FetchResponse>;
  getProfile(profileCode: string): Promise<string>;
  getUserInfo(
    userName: string,
  ): Promise<{ emailAddress: string; displayName: string } | null>;
  getUserSettings(userName?: string): Promise<UserSettings>;
  sleep(seconds: number): Promise<void>;
  runTests(
    testUids: string[],
    options: { appUid: string; username: string; password: string },
  ): Promise<{ status: 'OK' | 'ERROR'; message?: string }>;
  screenshot(
    uri: string,
    options: { username: string; password: string },
  ): Promise<{ status: 'OK' | 'ERROR'; message?: string; png_data?: string }>;
}

interface WF {
  getValue(key: string): Promise<any>;
  putValue(key: string, value: any): Promise<void>;
  getInstanceValue(key: string): Promise<any>;
  putInstanceValue(key: string, value: any): Promise<void>;
  incrementAndGet(key: string): Promise<number>;
  instanceIncrementAndGet(key: string): Promise<number>;
  pageUrlForApproval(
    appUid: string,
    page: string,
    params?: Record<string, unknown>,
  ): Promise<string>;
}

interface WF {
  getValue(key: string): Promise<any>;
  putValue(key: string, value: any): Promise<void>;
  getInstanceValue(key: string): Promise<any>;
  putInstanceValue(key: string, value: any): Promise<void>;
  incrementAndGet(key: string): Promise<number>;
  instanceIncrementAndGet(key: string): Promise<number>;
  pageUrlForApproval(
    appUid: string,
    page: string,
    params?: Record<string, unknown>,
  ): Promise<string>;
}

interface Cache {
  setString(
    key: string,
    value: string,
    expiryInSeconds?: number,
  ): Promise<void>;
  getString(key: string): Promise<string>;
  setNumber(
    key: string,
    value: number,
    expiryInSeconds?: number,
  ): Promise<void>;
  getNumber(key: string): Promise<number>;
  setObject(
    key: string,
    value: Record<string, any>,
    expiryInSeconds?: number,
  ): Promise<void>;
  getObject(key: string): Promise<Record<string, any>>;
  incrementBy(key: string, value: number): Promise<number>;
  increment(key: string): Promise<number>;
  decrementBy(key: string, value: number): Promise<number>;
  decrement(key: string): Promise<number>;
  hasKey(key: string): Promise<boolean>;
  delete(key: string): Promise<void>;
}

interface Utils {
  toNewRow<T>(row: Row<T>): NewRow<T>;
  prepareToPost<T>(row: Row<T>): Row<T>;
  camelCase(text: string): string;
  titleCase(text: string): string;
  kebabCase(text: string): string;
  snakeCase(text: string): string;
  pascalCase(text: string): string;
  upperCamelCase(text: string): string;
  upperSnakeCase(text: string): string;
  isNull<V>(value: V | null | undefined): value is null | undefined;
  isEmpty<V>(value: V | null | undefined): value is null | undefined;
  nvl<T>(value: T | null | undefined, defaultValue: T): T;
  isNotEmpty<V>(value: V | null | undefined): value is V;
  btoa(value: string): string;
  atob(value: string): string;
  ulid(): string;
}

interface Session {
  userName(): string;
  appUid(): string;
  orgUid(): string;
  pageUid(): string;
  roleUid(): string;
  sessionUid(): string;
  displayName(): string;
  emailAddress(): string;
  accessToken(): string;
}

interface FetchResponse {
  status: number;
  statusText: string;
  text: () => string | null;
  json: () => Record<string, unknown> | null;
  headers: () => Record<string, string>;
  error: () => string | null;
}

interface FunctionRequest {
  uri: string;
  queryString?: string;
  headers: Record<string, string>;
  body?: Record<string, any>;
}

interface FunctionResponse {
  status: (status: number) => FunctionResponse;
  statusText: string;
  text: (text: string) => FunctionResponse;
  json: (value: any) => FunctionResponse;
  html: (html: string) => FunctionResponse;
  header: (key: string, value: string) => FunctionResponse;
  contentType: (contentType: string) => FunctionResponse;
  body: (text: string) => FunctionResponse;
}

interface Logger {
  debug: (...msg: any[]) => void;
  error: (...msg: any[]) => void;
  info: (...msg: any[]) => void;
  log: (...msg: any[]) => void;
  warn: (...msg: any[]) => void;
}

type Console = Logger;

export const RecordStatusLabels = {
  Q: 'Query',
  I: 'Insert',
  U: 'Update',
  D: 'Delete',
  V: 'Validate',
  N: 'New',
};

type RecordStatus = keyof typeof RecordStatusLabels;

type DataRecord = Record<string, unknown>;

interface DBQuery<T> extends BaseQuery<T> {
  filter: Filters<T>;
  limit: number;
}

interface Query<T> extends BaseQuery<T> {
  filter?: Filters<T>;
}

interface BaseQuery<T> {
  fullSQL?: string;
  offset?: number;
  limit?: number;
  params?: SchemaMemberValue<T>;
  data?: SchemaMemberValue<T>;
  pagination?: Record<string, string>;
  orderByClause?: string;
  whereClause?: string;
  whereClauseParamList?: (string | number | boolean)[];
  groupByAttributeCodeList?: (keyof T)[];
  selectAttributeCodeList?: (keyof T)[];
  fetchDistinct?: boolean;
  aggregateList?: Aggregate<T>[];
  sort?: SchemaMember<T, number>;
  projection?: SchemaMember<T, number>;
  includeRowCount?: boolean;
}

type SchemaMemberValue<T> = { [P in keyof T]?: T[P] };

type SchemaMember<T, V> = { [P in keyof T]?: V };

type AggregateFunction = 'Avg' | 'Count' | 'Max' | 'Min' | 'Sum';

interface Aggregate<T> {
  aggregateFunction: AggregateFunction;
  attributeCode: keyof T;
  intoAttributeCode: keyof T;
}

type Filters<T> = FilterEntry<T>[];

type FilterEntry<T> = SingleFilter<T> | NestedFilter<T>;

type SingleFilter<T> = {
  [K in keyof T]?: T[K] extends 'DateTime' | 'Date' | undefined
    ? DateFilter | MultipleDateFilter
    : T[K] extends string | undefined
    ? MultipleSelectFilter | MultipleStringFilter | SelectFilter | StringFilter
    : T[K] extends number | undefined
    ? NumberFilter | MultipleNumberFilter
    : T[K] extends boolean | undefined
    ? BooleanFilter
    : never;
};

type NestedFilter<T> = {
  [name in Combiner]?: Filters<T>;
};

type Combiner = 'allOf' | 'anyOf' | 'noneOf';

type BooleanFilter = {
  [K in keyof typeof BOOLEAN_OPS]?: boolean;
};

type YNFilter = {
  [K in keyof typeof YN_OPS]?: string;
};

type StringFilter = {
  [K in keyof typeof STRING_OPS]?: string;
};

type MultipleStringFilter = {
  [operator in keyof typeof MULTIPLE_STRING_OPS]?: string[];
};

type DateFilter = {
  [K in keyof typeof DATE_OPS]?: string;
};

type MultipleDateFilter = {
  [operator in keyof typeof MULTIPLE_DATE_OPS]?: string[];
};

type NumberFilter = { [operator in keyof typeof NUMBER_OPS]?: number };

type MultipleNumberFilter = {
  [operator in keyof typeof MULTIPLE_NUMBER_OPS]?: number[];
};

type SelectFilter = {
  [K in keyof typeof SELECT_OPS]?: string;
};

type MultipleSelectFilter = {
  [operator in keyof typeof MULTIPLE_SELECT_OPS]?: string[];
};

const BOOLEAN_OPS = {
  isTrue: 'is',
  empty: 'is empty',
  notEmpty: 'is not empty',
};

const YN_OPS = {
  is: 'is',
  empty: 'is empty',
  notEmpty: 'is not empty',
};

const STRING_OPS = {
  is: 'is',
  iIs: 'is',
  not: 'is not',
  iNot: 'is not',
  empty: 'is empty',
  notEmpty: 'is not empty',
  nct: 'does not contain',
  iNct: 'does not contain',
  like: 'contains',
  iLike: 'contains',
  sw: 'starts with',
  iSw: 'starts with',
  ew: 'ends with',
  iEw: 'ends with',
};

const MULTIPLE_STRING_OPS = {
  hasAll: 'has all the words',
  iHasAll: 'has all the words',
  hasAny: 'has any of the words',
  iHasAny: 'has any of the words',
  notAny: 'does not have any of the words',
  iNotAny: 'does not have any of the words',
  in: 'is in',
  iIn: 'is in',
  nin: 'is not in',
  iNin: 'is not in',
};

const CASE_OPPOSITE_OPERATORS: Record<
  STRING_OPS_KEY_TYPE,
  STRING_OPS_KEY_TYPE
> = {
  is: 'iIs',
  iIs: 'is',
  not: 'iNot',
  iNot: 'not',
  empty: 'empty',
  notEmpty: 'notEmpty',
  nct: 'iNct',
  iNct: 'nct',
  like: 'iLike',
  iLike: 'like',
  sw: 'iSw',
  iSw: 'sw',
  ew: 'iEw',
  iEw: 'ew',
  hasAll: 'iHasAll',
  iHasAll: 'hasAll',
  hasAny: 'iHasAny',
  iHasAny: 'hasAny',
  notAny: 'iNotAny',
  iNotAny: 'notAny',
  in: 'iIn',
  iIn: 'in',
  nin: 'iNin',
  iNin: 'nin',
};

export const caseOppositeOperator = (op: STRING_OPS_KEY_TYPE) => {
  return CASE_OPPOSITE_OPERATORS[op];
};

const NUMBER_OPS = {
  eq: 'equals',
  ne: 'not equals',
  null: 'is empty',
  notNull: 'is not empty',
  gt: 'greater than',
  gte: 'greater than or equal to',
  lt: 'less than',
  lte: 'less than or equal to',
};

const MULTIPLE_NUMBER_OPS = {
  bn: 'is between',
  in: 'is in',
  nin: 'is not in',
};

const DATE_OPS = {
  on: 'is on',
  notOn: 'is not on',
  empty: 'is empty',
  notEmpty: 'is not empty',
  after: 'is after',
  before: 'is before',
  onOrAfter: 'is on or after',
  onOrBefore: 'is on or before',
  today: 'is today',
  yesterday: 'is yesterday',
  last7days: 'since last 7 days',
  last14days: 'since last 14 days',
  last28days: 'since last 28 days',
  thisWeek: 'is this week',
  thisMonth: 'is this month',
  thisQuarter: 'is this quarter',
  thisYear: 'is this year',
};

const MULTIPLE_DATE_OPS = {
  bn: 'is between',
};

const SELECT_OPS = {
  is: 'is',
  not: 'is not',
  empty: 'is empty',
  notEmpty: 'is not empty',
};

const MULTIPLE_SELECT_OPS = {
  in: 'is in',
  nin: 'is not in',
};