---
url: /technical/netsuite-examples.md
description: >-
  Two ways to pull data from NetSuite into MODLR - via the SuiteTalk REST API,
  or as a JDBC Datasource.
---

# NetSuite Examples

NetSuite can be connected to MODLR in two different ways:

* **[Process Example](#process-example)** - an [Advanced Integration](/technical/integrations.html#advanced-integrations) using NetSuite's SuiteTalk REST API (SuiteQL), authenticated with OAuth 1.0 signed requests from a Process.
* **[Datasource Example](#datasource-example)** - connecting directly to NetSuite as a [Datasource](/technical/datasources.html) using NetSuite's own JDBC driver (SuiteAnalytics Connect), and querying it with SQL like any other database.

Which one to use depends on what you need: the REST API is a better fit for targeted, scripted pulls of specific records, while the JDBC Datasource is often simpler if you just want to run SQL/SuiteQL queries against NetSuite directly.

## Process Example

The code below is an example of a process connecting to NetSuite's SuiteTalk REST API. NetSuite authenticates using OAuth 1.0 (HMAC-SHA256 signed requests) rather than MODLR's built-in OAuth 2.0 flow, so this is an [Advanced Integration](/technical/integrations.html#advanced-integrations). It queries the `account` table via SuiteQL, paging through results.

::: info
This process requires NetSuite OAuth 1.0 credentials (consumer key/secret and token id/secret), stored as [Secrets](/process-functions/security-getsecret.html), and your NetSuite account ID and SuiteTalk base URL, stored as [Model Variables](/process-functions/script-variableget.html).
:::

### Library Code

Handles OAuth 1.0 request signing, and wraps the SuiteQL query and metadata endpoints. Save this as a [Process Library](/technical/process-libraries.html) (e.g. `NetsuiteConnector.js`) and load it with [`script.library`](/process-functions/script-library.html).

```js
/**
 * OAuthClient provides methods to generate OAuth 1.0 headers.
 */
class OAuthClient {
    /**
     * Constructs a new OAuthClient instance with the provided credentials.
     * @param {Object} credentials - An object containing OAuth credentials.
     * @param {string} credentials.account_id - The NetSuite account ID.
     * @param {string} credentials.consumer_key - The OAuth consumer key.
     * @param {string} credentials.consumer_secret - The OAuth consumer secret.
     * @param {string} credentials.token_id - The OAuth token ID.
     * @param {string} credentials.token_secret - The OAuth token secret.
     */
    constructor(credentials) {
        this.credentials = credentials;
    }

    /**
     * Generates a random nonce string of the specified length.
     */
    _generateNonce(length = 11) {
        const charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        let result = "";
        for (let i = 0; i < length; i++) {
            const randomIndex = Math.floor(Math.random() * charset.length);
            result += charset[randomIndex];
        }
        return result;
    }

    /**
     * Generates a timestamp representing the current time in seconds.
     */
    _generateTimestamp() {
        return Math.floor(Date.now() / 1000);
    }

    /**
     * Generates OAuth 1.0 headers for making authenticated requests.
     */
    generateOAuthHeaders(method, url) {
        const nonce = this._generateNonce();
        const timestamp = this._generateTimestamp();

        let baseParams = {
            oauth_consumer_key: this.credentials.consumer_key,
            oauth_token: this.credentials.token_id,
            oauth_signature_method: "HMAC-SHA256",
            oauth_timestamp: timestamp,
            oauth_nonce: nonce,
            oauth_version: "1.0"
        };

        const queryStringStart = url.indexOf("?");
        const queryParams = {};
        if (queryStringStart !== -1) {
            const queryString = url.substring(queryStringStart + 1);
            const queryItems = queryString.split("&");
            for (const item of queryItems) {
                const [key, value] = item.split("=");
                queryParams[key] = decodeURIComponent(value);
            }
        }

        const params = { ...baseParams, ...queryParams };
        const sortedParams = Object.keys(params)
            .sort()
            .map((key) => `${encodeURIComponent(key)}=${encodeURIComponent(params[key])}`)
            .join("&");

        const baseString = `${method.toUpperCase()}&${encodeURIComponent(url.split("?")[0])}&${encodeURIComponent(sortedParams)}`;
        const signingKey = `${encodeURIComponent(this.credentials.consumer_secret)}&${encodeURIComponent(this.credentials.token_secret)}`;
        const signature = security.sha256hmac(signingKey, baseString);

        const oauthHeader = `OAuth realm="${this.credentials.account_id}",oauth_consumer_key="${encodeURIComponent(this.credentials.consumer_key)}",oauth_token="${encodeURIComponent(this.credentials.token_id)}",oauth_signature_method="HMAC-SHA256",oauth_timestamp="${encodeURIComponent(timestamp)}",oauth_nonce="${encodeURIComponent(nonce)}",oauth_version="1.0",oauth_signature="${encodeURIComponent(signature)}"`;

        return {
            Authorization: oauthHeader,
        };
    }
}

const OAUTH_CREDENTIALS = {
    account_id: script.variableGet("netsuite.accountId"),
    consumer_key: security.getSecret("NETSUITE_CONSUMER_KEY"),
    consumer_secret: security.getSecret("NETSUITE_CONSUMER_SECRET"),
    token_id: security.getSecret("NETSUITE_TOKEN_ID"),
    token_secret: security.getSecret("NETSUITE_TOKEN_SECRET")
};

const baseUrl = script.variableGet("netsuite.baseUrl"); // e.g. "https://<account-id>.suitetalk.api.netsuite.com"
const oauthClient = new OAuthClient(OAUTH_CREDENTIALS);

function netsuiteQuery(query, limit = 10000, offset = 0) {
    let url = `${baseUrl}/services/rest/query/v1/suiteql?limit=${limit}&offset=${offset}`;
    const headers = oauthClient.generateOAuthHeaders("POST", url);

    let response = web.post(
        url,
        {
            ...headers,
            "Content-Type": "application/json",
            "prefer": "transient"
        },
        {
            q: query
        }
    );

    if (response.status != 200) {
        console.log(response);
        return null;
    }

    return JSON.parse(response.body);
}

function netsuiteMetadataEndpoint(route) {
    let url = `${baseUrl}/${route}`;
    const headers = oauthClient.generateOAuthHeaders("GET", url);

    let response = web.get(
        url,
        {
            ...headers,
            "Content-Type": "application/json",
            "prefer": "transient",
            "Accept": "*.*"
        }
    );

    if (response.status != 200) {
        return null;
    }

    return JSON.parse(response.body);
}

function netsuiteMetadata() {
    let url = `${baseUrl}/services/rest/record/v1/metadata-catalog`;
    const headers = oauthClient.generateOAuthHeaders("GET", url);

    let response = web.get(
        url,
        {
            ...headers,
            "Content-Type": "application/json",
            "prefer": "transient",
            "Accept": "*.*"
        }
    );

    if (response.status != 200) {
        return null;
    }

    return JSON.parse(response.body);
}
```

### Process Code

```js
script.library("NetsuiteConnector.js");

var sql = "SELECT account.* FROM account";

function pre() {
    // This function is called once before the process is executed.
    // Use this to setup prompts.
    script.log('process pre-execution parameters parsed.');
}

function begin() {
    // This function is called once at the start of the process
    script.log('process execution started.');

    let count = 0;
    let total = 0;

    do {
        let result = netsuiteQuery(sql, 1000, count);
        if (!result) {
            console.log("NetSuite query failed.");
            return;
        }

        total = result.totalResults;
        console.log(`Fetched ${result.items.length} of ${total} accounts.`);

        for (const item of result.items) {
            console.log(item);
            count++;

            if (script.IsCancelled()) {
                return;
            }
        }
    } while (count < total);
}

function data(record) {
    // This function is called once for each line of data on the second cycle
    // Use this to build dimensions and push data into cubes
}

function end() {
    // This function is called once at the end of the process
    script.log('process execution finished.');
}
```

## Datasource Example

NetSuite can also be connected as a [Datasource](/technical/datasources.html), using NetSuite's own JDBC driver (distributed as part of **SuiteAnalytics Connect**). Once connected this way, a Process can query NetSuite directly with SQL/SuiteQL, the same as [any other Datasource](/technical/using-datasource-data.html) - no OAuth signing or custom Process code required.

### Getting the JDBC driver

The driver is downloaded from within your NetSuite account, not from MODLR:

1. You'll need the **SuiteAnalytics Connect** permission, and the Connect Service feature enabled on your NetSuite account.
2. From the NetSuite home page, find the **Settings** portlet and click **Set Up SuiteAnalytics Connect**.
3. On the driver download page, select your operating system, then note the values shown under **Your Configuration** (service host, account ID and role ID) - you'll need these for the connection URL below.
4. Under **Installation Bundles and Drivers**, click **Download** next to the **JDBC** driver.

See Oracle's [Downloading and Installing Connect Drivers](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/article_1202121851.html) documentation for the full, up to date steps.

### Adding the driver to MODLR

Follow the general steps in [Adding a JDBC Driver](/technical/datasources-jdbc.html) to upload the driver you just downloaded. NetSuite's driver class name is:

```
com.netsuite.jdbc.openaccess.OpenAccessDriver
```

### Connection URL

NetSuite's JDBC connection URL follows this template:

```
jdbc:ns://<service_host>:1708;ServerDataSource=NetSuite2.com;encrypted=1;NegotiateSSLClose=false;CustomProperties=(AccountID=<account_id>;RoleID=<role_id>)
```

Replace `<service_host>`, `<account_id>` and `<role_id>` with the values from the **Your Configuration** panel on the SuiteAnalytics Connect Driver Download page.

### Adding the Datasource

Follow [Adding a Datasource](/technical/adding-a-datasource.html) to create the new Datasource, selecting the NetSuite driver you just uploaded and using the connection URL above. For the username and password, use a NetSuite login (a dedicated integration/role-restricted user is recommended) that has access to the role specified by `RoleID`.

Once saved and tested, the Datasource can be queried like any other - see [How to use Datasource data in MODLR](/technical/using-datasource-data.html).