How to use Datasource data in MODLR

Once a Datasource has been added to MODLR, a Process can read and write data from it using MODLR's scripting functions.

Querying data

datasource.client is the preferred way to run queries - it streams rows instead of loading an entire result set into memory, and reuses a connection pool rather than opening a new connection per query.

const client = datasource.client("Internal Datastore");

// Fetch a single row
const account = client.first(
    "SELECT account_code, account_name FROM performance_management.accounts WHERE account_code = ?",
    ["REV"]
);

if (account) {
    console.log(account.account_name);
}

// Stream multiple rows - preferred for large result sets
const rows = client.select(
    "SELECT account_code, account_name, account_type FROM performance_management.accounts WHERE account_type = ?",
    ["REV"]
);

for (const row of rows) {
    console.log(row.account_code, row.account_name);
}

// Or load everything into memory at once, if the result set is small
const allRevAccounts = client.selectAll(
    "SELECT account_code, account_name FROM performance_management.accounts WHERE account_type = ?",
    ["REV"]
);
console.log(`Found ${allRevAccounts.length} revenue accounts.`);

Writing data

Use client.execute for INSERT, UPDATE, DELETE and DDL statements. It returns the number of affected rows, and the generated key for an INSERT where the datasource supports it.

const client = datasource.client("Internal Datastore");

const update = client.execute(
    "UPDATE performance_management.accounts SET account_name = ? WHERE account_code = ?",
    ["Revenue - Subscription Sales", "REV"]
);
console.log(`Rows updated: ${update.affected}`);

const insert = client.execute(
    "INSERT INTO performance_management.accounts (account_code, account_name, account_type) VALUES (?, ?, ?)",
    ["REV2", "Revenue - Services", "REV"]
);
console.log(`New row ID: ${insert.generatedKey}`);

Bulk loading data

When loading a large number of rows - for example, from an API response in an Advanced Integration - use datasource.createBatch rather than calling execute per row. It groups inserts together, dramatically reducing the number of round-trips to the database.

const batch = datasource.createBatch(
    "Internal Datastore",
    "performance_management.accounts",
    ["account_code", "account_name", "account_type"]
);

for (const account of accounts) {
    batch.insert([account.code, account.name, account.type]);
    // batch.insert() automatically flushes once the batch size is reached
}

// Flush any remaining rows that didn't reach the batch size threshold
batch.flush();

createBatch also supports INSERT_OR_UPDATE and INSERT_OR_IGNORE modes for handling duplicate keys - see the datasource.createBatch reference for details.

Legacy functions

Older processes may use datasource.select, datasource.insert and datasource.update. These are deprecated in favour of datasource.client, and shouldn't be used in new processes, but are still documented for reference.

Working with files

If your Datasource is a File-System Datasource rather than a database, see Flat File Load for reading files with datasource.readFile and related functions.