# Server Side Scripts

Utilize modern Javascript ([ES2020](https://tc39.es/ecma262/2020/)) 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](/files/-MavoyyRrxxMS1YRGnuS)

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.

{% hint style="warning" %}
Ensure to await the completion of all asynchronous function calls. Unawaited promises may only execute partially.
{% endhint %}

## Variables Available In Scripts

### Pre Query Script

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

### Post Query Script

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

### Before Insert Script

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

### After Insert Script

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

### Before Update Script

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

### After Update Script

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

### Before Delete Script

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

### After Delete Script

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

### Typescript Types

{% code overflow="wrap" lineNumbers="true" %}

```typescript
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',
};
```

{% endcode %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://next-docs.cloudio.io/datasource/scripts.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.