---
url: /technical/using-datasource-data.md
description: >-
  How to read and write data from a Datasource within a Process, using
  datasource.client and related functions.
---

# How to use Datasource data in MODLR

Once a [Datasource](/technical/datasources.html) has been [added to MODLR](/technical/adding-a-datasource.html), a [Process](/technical/creating-processes.html) can read and write data from it using MODLR's scripting functions.

## Querying data

[`datasource.client`](/process-functions/datasource-client.html) 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.

```js
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.

```js
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](/technical/integrations.html#advanced-integrations) - use [`datasource.createBatch`](/process-functions/datasource-createBatch.html) rather than calling `execute` per row. It groups inserts together, dramatically reducing the number of round-trips to the database.

```js
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`](/process-functions/datasource-createBatch.html) reference for details.

## Legacy functions

Older processes may use [`datasource.select`](/process-functions/datasource-select.html), [`datasource.insert`](/process-functions/datasource-insert.html) and [`datasource.update`](/process-functions/datasource-update.html). 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](/technical/flat-file-load.html) for reading files with `datasource.readFile` and related functions.