Sample Scripts

Logging

Use Logger instead of the console. Log messages will appear in the Debug Console under the Server Logs tab.

Logger.log(...);
Logger.info(...);
Logger.debug(...);
Logger.warn(...);
Logger.error(...);

Debug Console must be open in order for the messages to appear.

Fetching Multiple Records from a DataSource

const records = await db.find("DataSourceName", {
  filter: [
    { name: { sw: "Steve" } },
    { joinDate: { after: new Date().toISOString() } },
    { amount: { eq: 123 } },
  ],
  limit: 20,
});

You can pass a different appUid as an option to fetch data from that app. See line no. 8 in the below example

const records = await db.find("DataSourceName", {
  filter: [
    { name: { sw: "Steve" } },
    { joinDate: { after: new Date().toISOString() } },
    { amount: { eq: 123 } },
  ],
  limit: 20,
  {appUid: 'my-other-app'}
});

Fetching a Single Record from a DataSource

const record = await db.findOne("DataSourceName", {
  filter: [{ empId: { eq: 101 } }],
});

Inserting a Record

const newRow = {...};
const insertedRow = await db.insertOne("DataSourceName", newRow);

You can pass a different appUid as an option to fetch data from that app. See line no. 4 in the below example

const insertedRow = await db.insertOne(
    "DataSourceName", 
    newRow,
    {appUid: 'my-other-app'
);

Inserting Multiple Records

const rows = [{...}, {...}];
const insertedRows = await db.insertMany("DataSourceName", rows);

Similarly use updateOne, updateMany, deleteOne, deleteMany to perform Update or Delete operations

Number of calls to the above functions are limited to 100 from within a single execution of a Server Side Script or Cloud Function.

If you want to insert more than 100 rows in a single execution, always use db.insertMany(...) instead of multiple db.insertOne(...) calls.

Duplicating a Record

const dbRow = await db.findOne("DataSourceName", {
  filter: [{ headerId: { eq: 101 } }],
});

const newRow = utils.toNewRow(dbRow); // this will remove all WHO columns and other internal keys
delete newRow.headerId; // remove fields that are auto populated by the platform
const insertedRow = await db.insertOne("DataSourceName", newRow);
Logger.log(JSON.stringify(insertedRow));

Throwing a Functional Error

rows.forEach((row) => {
  if (row.someField === "invalid") {
    throw new UserError(`Some Field is invalid ${row.someField}!`);
  }
});

Calling an external REST Service

const resp = await db.fetch('https://httpbin.org/post', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ message: 'Hi there!' })
});
const jsonResponse = resp.json();

Executing SQL Queries

const result = await db.executeQuery( // returns Record<string, unknown>[]
                 `SELECT * FROM SOME_TABLE
                   WHERE SOME_COLUMN = ?`,
                 [session.userName()]
               );

const value = await db.queryString( // returns string | undefined
                 `SELECT 'Some string value'`,
                 []
               );

await db.executeUpdate(
                 `UPDATE SOME_TABLE SET SOME_COLUMN = ?
                   WHERE SOME_COLUMN = ?`,
                 ['X', 'Y']
               );

You can pass true (boolean) as the 3rd parameter to query against the cloudio schema instead of your application schema

Awaiting for all Promises to completion

Wrong Way ❌

rows.forEach(async (row) => {
    // make async calls for each row
    await db.someAsyncFn(...);
});
// this code will exit even before all the above promises are resolved

Right Way ✅

const collectedResponse = await Promise.all(rows.map(async (row) => {
    // make async calls for each row
    await db.someAsyncFn(...);
    return "someThing";
}));
// this code will wait for all the promises to be resolved before exiting

Sending User Notification

const roleCode = "myRole";
const empId = 123;
const subject = 'Hi there!';
// notification body can be formatted with Markdown or basic HTML tags
const body = `#### h4
##### h5
###### h6

A sample paragraph...

Don't put tabs or spaces in front of your paragraphs.

First line with two spaces after.  
And the next line.

First line with the HTML tag after.<br>
And the next line.

> Quote

Some **bold** *italic* ***bold italic*** text...

Alerts: alert_warning, alert_info, alert_success, alert_error

\`\`\`alert_warning
A warning alert...!
\`\`\`

Unordered list

- one
- two
- three

Ordered list

1. one
1. two
1. three

Internal link [click here](<#/howto/edit-employee?empId=${empId}&role=${roleCode}&some=new %thing>) to navigate to the employee edit page within the current tab.

Enclose the URL within angular braces <...> to URL encode space, % etc.

External link [cloudio.io](https://cloudio.io). Opens in a new tab.

Render Image

![image alt](https://dev.cloudio.io/images/io_icon.png)

Use HTML

<a href="https://www.example.com/my great page">link</a>

<img src="https://dev.cloudio.io/images/io_icon.png"/>
`;

// send the notification

// to a user e.g. admin
await db.notifyUser('admin', subject, body);

// to a set of users e.g. admin, username2
await db.notifyUser(['admin', 'username2'], subject, body);

// to all users assigned to a role
await db.notifyRole('my-app', 'developer', subject, body);

// to all users assigned to a set of roles
await db.notifyRole('my-app', ['administrator', 'developer'], subject, body);

Sending Email

// Text Email
const email = {
                to: '[e-mail]', 
                subject: 'Hi there!', 
                text: 'email body',
               };
await db.sendEmail(email);

// HTML Email
const htmlBody = await db.processMustacheTemplate(templateString, dataObject):
const email = {
                to: '[e-mail]', 
                subject: 'Hi there!', 
                text: 'HTML Only Email',
                html: htmlBody
               };
await db.sendEmail(email);

Triggering Workflow

const wfUid = "..."; // Workflow UID that can be optained from Edit Workflow Panel
const version = 1; // Workflow Version from Edit Workflow Panel
const state = {}; // Any JSON object to set the initial state of the Workflow

await db.startWorkflow(wfUid, version, "Some description", state);

Process Mustache Template String

const output = await db.processMustacheTemplate(templateString, dataObject):

Format Date

const { format } = await import('date-fns');

const formatedDateString = format(new Date(), 'MM/dd/yyyy');