Writing a CXone Studio Script That Does a Database Lookup via the DBConnector Action

How-To-Articles
1

Writing a CXone Studio Script That Does a Database Lookup via the DBConnector Action

What You Will Build

  • A NICE CXone Studio script that performs a synchronous database lookup against an external PostgreSQL database using the DBConnector action.
  • The script retrieves customer data based on a phone number passed from the IVR and sets variables for downstream routing or personalization.
  • The implementation uses the NICE CXone Studio JSON definition format and the REST API to deploy the script.

Prerequisites

  • OAuth Client Type: Service Account or User Account with Script:Write and Script:Read scopes.
  • SDK/API Version: NICE CXone API v2 (Studio Scripts).
  • Language/Runtime: JavaScript (Node.js 18+) for the deployment script; NICE CXone Studio JSON for the logic.
  • External Dependencies:
    • axios for HTTP requests.
    • A configured DB Connector in the NICE CXone Administration console (Administration > Integrations > Database Connectors). You must have already created a connector named CustomerDB pointing to your PostgreSQL instance.
    • A database table customers with columns phone_number (VARCHAR) and customer_name (VARCHAR).

Authentication Setup

NICE CXone APIs use OAuth 2.0. For programmatic deployment, use the Client Credentials flow.

// auth.js
const axios = require('axios');

const CXONE_BASE_URL = 'https://api.custhelp.com';
const CLIENT_ID = process.env.CXONE_CLIENT_ID;
const CLIENT_SECRET = process.env.CXONE_CLIENT_SECRET;

/**
 * Retrieves an OAuth access token from NICE CXone.
 * @returns {Promise<string>} The access token.
 */
async function getAccessToken() {
    const url = `${CXONE_BASE_URL}/oauth/token`;
    const params = new URLSearchParams();
    params.append('grant_type', 'client_credentials');
    params.append('client_id', CLIENT_ID);
    params.append('client_secret', CLIENT_SECRET);

    try {
        const response = await axios.post(url, params, {
            headers: {
                'Content-Type': 'application/x-www-form-urlencoded',
                'Accept': 'application/json'
            }
        });
        return response.data.access_token;
    } catch (error) {
        console.error('Failed to authenticate:', error.response ? error.response.data : error.message);
        throw error;
    }
}

module.exports = { getAccessToken };

Implementation

Step 1: Define the DBConnector Action in Studio JSON

The core of this tutorial is the Studio Script definition. NICE CXone Studio scripts are defined as JSON objects. The DBConnector action allows you to execute SQL queries against pre-configured database connectors.

Key Concepts:

  • Connector Name: Must match the name defined in the CXone Administration console (e.g., CustomerDB).
  • Query Type: SELECT for read operations.
  • Parameters: Use {{variable_name}} syntax to inject runtime variables.
  • Result Handling: The action returns a result set. You must map the first row to script variables.

Here is the JSON structure for the script logic:

{
  "name": "Customer Lookup Script",
  "description": "Looks up customer name by phone number using DBConnector",
  "version": "1.0",
  "language": "en-US",
  "flow": {
    "startNode": "Init",
    "nodes": [
      {
        "id": "Init",
        "type": "start",
        "nextNodeId": "DBLookup",
        "variables": [
          {
            "name": "PhoneNumber",
            "value": "{{inbound.phoneNumber}}",
            "type": "string"
          }
        ]
      },
      {
        "id": "DBLookup",
        "type": "dbConnector",
        "connectorName": "CustomerDB",
        "queryType": "SELECT",
        "sql": "SELECT customer_name FROM customers WHERE phone_number = '{{PhoneNumber}}' LIMIT 1",
        "nextNodeId": "HandleResult",
        "onErrorNodeId": "HandleError",
        "resultMapping": {
          "customerName": "customer_name"
        }
      },
      {
        "id": "HandleResult",
        "type": "logic",
        "nextNodeId": "EndSuccess",
        "onFalseNodeId": "EndNotFound",
        "condition": "{{customerName}} != null"
      },
      {
        "id": "EndSuccess",
        "type": "end",
        "result": "Customer found: {{customerName}}"
      },
      {
        "id": "EndNotFound",
        "type": "end",
        "result": "Customer not found."
      },
      {
        "id": "HandleError",
        "type": "end",
        "result": "Database error occurred."
      }
    ]
  }
}

Step 2: Deploy the Script via the REST API

While you can use the CXone Studio UI to create scripts, automation requires the REST API. The endpoint for creating a script is POST /api/v2/scripting/scripts.

OAuth Scope Required: Script:Write

// deploy-script.js
const axios = require('axios');
const { getAccessToken } = require('./auth');

// The script definition from Step 1
const scriptDefinition = {
  "name": "Customer Lookup Script",
  "description": "Looks up customer name by phone number using DBConnector",
  "version": "1.0",
  "language": "en-US",
  "flow": {
    "startNode": "Init",
    "nodes": [
      {
        "id": "Init",
        "type": "start",
        "nextNodeId": "DBLookup",
        "variables": [
          {
            "name": "PhoneNumber",
            "value": "{{inbound.phoneNumber}}",
            "type": "string"
          }
        ]
      },
      {
        "id": "DBLookup",
        "type": "dbConnector",
        "connectorName": "CustomerDB",
        "queryType": "SELECT",
        "sql": "SELECT customer_name FROM customers WHERE phone_number = '{{PhoneNumber}}' LIMIT 1",
        "nextNodeId": "HandleResult",
        "onErrorNodeId": "HandleError",
        "resultMapping": {
          "customerName": "customer_name"
        }
      },
      {
        "id": "HandleResult",
        "type": "logic",
        "nextNodeId": "EndSuccess",
        "onFalseNodeId": "EndNotFound",
        "condition": "{{customerName}} != null"
      },
      {
        "id": "EndSuccess",
        "type": "end",
        "result": "Customer found: {{customerName}}"
      },
      {
        "id": "EndNotFound",
        "type": "end",
        "result": "Customer not found."
      },
      {
        "id": "HandleError",
        "type": "end",
        "result": "Database error occurred."
      }
    ]
  }
};

async function deployScript() {
    const token = await getAccessToken();
    const url = `${process.env.CXONE_BASE_URL}/api/v2/scripting/scripts`;

    try {
        const response = await axios.post(url, scriptDefinition, {
            headers: {
                'Authorization': `Bearer ${token}`,
                'Content-Type': 'application/json',
                'Accept': 'application/json'
            }
        });

        console.log('Script deployed successfully.');
        console.log('Script ID:', response.data.id);
        console.log('Script Name:', response.data.name);
        
        // Save the ID for future updates
        return response.data.id;
    } catch (error) {
        if (error.response) {
            console.error('Deployment failed:', error.response.data);
        } else {
            console.error('Deployment error:', error.message);
        }
        throw error;
    }
}

if (require.main === module) {
    deployScript().catch(console.error);
}

module.exports = { deployScript };

Step 3: Handle Pagination and Large Result Sets

The DBConnector action in Studio is designed for single-record lookups or small sets. If your query returns more than one row, the resultMapping only captures the first row.

If you need to iterate over multiple records, you cannot do this directly in the DBConnector action. You must use a loop node in Studio. However, for a simple lookup, LIMIT 1 is critical for performance.

Optimization Tip:
Always index the column used in the WHERE clause. In this example, phone_number must be indexed in your PostgreSQL database. Unindexed queries on large tables will cause timeouts in the CXone environment, resulting in a 504 Gateway Timeout or a script error.

{
  "id": "DBLookup",
  "type": "dbConnector",
  "connectorName": "CustomerDB",
  "queryType": "SELECT",
  "sql": "SELECT customer_name FROM customers WHERE phone_number = '{{PhoneNumber}}' LIMIT 1",
  "nextNodeId": "HandleResult",
  "onErrorNodeId": "HandleError",
  "resultMapping": {
    "customerName": "customer_name"
  }
}

Step 4: Error Handling and Debugging

Database lookups can fail due to network issues, authentication failures, or SQL errors. The DBConnector action supports an onErrorNodeId.

Common Errors:

  1. Connector Not Found: The connectorName does not match the name in Administration.
  2. SQL Error: Syntax error in the sql field.
  3. Timeout: The query takes longer than 5 seconds.

In the script definition above, the HandleError node catches these exceptions. You can log the error using the log action or set a variable for debugging.

{
  "id": "HandleError",
  "type": "end",
  "result": "Database error occurred."
}

To improve debugging, you can add a log action before the end node:

{
  "id": "HandleError",
  "type": "log",
  "message": "DB Lookup Failed for {{PhoneNumber}}",
  "level": "error",
  "nextNodeId": "EndError"
},
{
  "id": "EndError",
  "type": "end",
  "result": "Database error occurred."
}

Complete Working Example

Below is the complete Node.js script to deploy the Studio script. Save this as deploy-customer-lookup.js.

// deploy-customer-lookup.js
const axios = require('axios');

// Configuration
const CXONE_BASE_URL = 'https://api.custhelp.com';
const CLIENT_ID = process.env.CXONE_CLIENT_ID;
const CLIENT_SECRET = process.env.CXONE_CLIENT_SECRET;

// OAuth Authentication
async function getAccessToken() {
    const url = `${CXONE_BASE_URL}/oauth/token`;
    const params = new URLSearchParams();
    params.append('grant_type', 'client_credentials');
    params.append('client_id', CLIENT_ID);
    params.append('client_secret', CLIENT_SECRET);

    try {
        const response = await axios.post(url, params, {
            headers: {
                'Content-Type': 'application/x-www-form-urlencoded',
                'Accept': 'application/json'
            }
        });
        return response.data.access_token;
    } catch (error) {
        console.error('Authentication failed:', error.response ? error.response.data : error.message);
        throw error;
    }
}

// Script Definition
const scriptDefinition = {
  "name": "Customer Lookup Script",
  "description": "Looks up customer name by phone number using DBConnector",
  "version": "1.0",
  "language": "en-US",
  "flow": {
    "startNode": "Init",
    "nodes": [
      {
        "id": "Init",
        "type": "start",
        "nextNodeId": "DBLookup",
        "variables": [
          {
            "name": "PhoneNumber",
            "value": "{{inbound.phoneNumber}}",
            "type": "string"
          }
        ]
      },
      {
        "id": "DBLookup",
        "type": "dbConnector",
        "connectorName": "CustomerDB",
        "queryType": "SELECT",
        "sql": "SELECT customer_name FROM customers WHERE phone_number = '{{PhoneNumber}}' LIMIT 1",
        "nextNodeId": "HandleResult",
        "onErrorNodeId": "HandleError",
        "resultMapping": {
          "customerName": "customer_name"
        }
      },
      {
        "id": "HandleResult",
        "type": "logic",
        "nextNodeId": "EndSuccess",
        "onFalseNodeId": "EndNotFound",
        "condition": "{{customerName}} != null"
      },
      {
        "id": "EndSuccess",
        "type": "end",
        "result": "Customer found: {{customerName}}"
      },
      {
        "id": "EndNotFound",
        "type": "end",
        "result": "Customer not found."
      },
      {
        "id": "HandleError",
        "type": "log",
        "message": "DB Lookup Failed for {{PhoneNumber}}",
        "level": "error",
        "nextNodeId": "EndError"
      },
      {
        "id": "EndError",
        "type": "end",
        "result": "Database error occurred."
      }
    ]
  }
};

// Deploy Function
async function deployScript() {
    if (!CLIENT_ID || !CLIENT_SECRET) {
        throw new Error('Missing CLIENT_ID or CLIENT_SECRET environment variables.');
    }

    const token = await getAccessToken();
    const url = `${CXONE_BASE_URL}/api/v2/scripting/scripts`;

    try {
        const response = await axios.post(url, scriptDefinition, {
            headers: {
                'Authorization': `Bearer ${token}`,
                'Content-Type': 'application/json',
                'Accept': 'application/json'
            }
        });

        console.log('Script deployed successfully.');
        console.log('Script ID:', response.data.id);
        console.log('Script Name:', response.data.name);
        return response.data.id;
    } catch (error) {
        if (error.response) {
            console.error('Deployment failed with status', error.response.status);
            console.error('Response data:', JSON.stringify(error.response.data, null, 2));
        } else {
            console.error('Deployment error:', error.message);
        }
        throw error;
    }
}

// Execute
deployScript().catch(console.error);

Common Errors & Debugging

Error: 400 Bad Request - Invalid Script Definition

Cause: The JSON structure of the script definition is invalid. Common issues include:

  • Missing nextNodeId in a node.
  • Circular references in the flow.
  • Invalid SQL syntax in the sql field.

Fix: Validate the JSON structure. Ensure all nodes have a nextNodeId or are of type end. Test the SQL query in your database client first.

Error: 403 Forbidden - Insufficient Permissions

Cause: The OAuth token does not have the Script:Write scope.

Fix: Check the OAuth client configuration in CXone Administration. Ensure the client has the Script:Write scope enabled.

Error: 500 Internal Server Error - DB Connector Timeout

Cause: The database query took longer than the allowed time (typically 5 seconds).

Fix:

  1. Add an index to the phone_number column in your database.
  2. Use LIMIT 1 in your SQL query.
  3. Ensure the database server is reachable from the NICE CXone environment (check firewall rules and VPC peering if applicable).

Error: Connector Not Found

Cause: The connectorName in the script does not match the name defined in CXone Administration.

Fix: Verify the exact name of the DB Connector in Administration > Integrations > Database Connectors. The name is case-sensitive.

Official References