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

What You Will Build

• One sentence: This tutorial builds a NICE CXone Studio flow that connects to an external SQL database, executes a parameterized query based on caller input, and returns the result to a variable for downstream logic.
• One sentence: This uses the NICE CXone Studio visual builder and the built-in DBConnector action, configured via the CXone API for programmatic deployment.
• One sentence: The programming language covered is JavaScript (Node.js) for the API integration and JSON for the Studio flow definition.

Prerequisites

• OAuth Client Type: Application (Client Credentials) or User (Authorization Code) with the studio:flow:write and studio:flow:read scopes.
• DB Connector Configuration: A pre-configured Database Connector in the CXone Admin Console. You must know the Connector ID and ensure the database user has SELECT permissions.
• SDK Version: NICE CXone Node.js SDK (@nice-cxone/sdk) version 10.0.0 or higher.
• Runtime Requirements: Node.js 18+ LTS.
• External Dependencies:
  • @nice-cxone/sdk
  • dotenv (for environment variable management)

Authentication Setup

Before interacting with the Studio API, you must authenticate. The NICE CXone Node.js SDK handles the OAuth2 client credentials flow automatically when initialized with your credentials.

Create a .env file in your project root:

CXONE_ENVIRONMENT=us1
CXONE_CLIENT_ID=your_client_id
CXONE_CLIENT_SECRET=your_client_secret
CXONE_SUBDOMAIN=your_subdomain

Initialize the SDK in your index.js:

const { PlatformClient, StudioClient } = require("@nice-cxone/sdk");
require("dotenv").config();

async function initStudioClient() {
  const platform = new PlatformClient();
  
  await platform.auth.loginClientCredentials({
    clientId: process.env.CXONE_CLIENT_ID,
    clientSecret: process.env.CXONE_CLIENT_SECRET,
    environment: process.env.CXONE_ENVIRONMENT || "us1",
    subdomain: process.env.CXONE_SUBDOMAIN,
  });

  return new StudioClient(platform);
}

module.exports = { initStudioClient };

This setup ensures that every subsequent API call includes a valid Authorization: Bearer <token> header. The SDK automatically refreshes tokens before they expire.

Implementation

Step 1: Define the Database Connector and Query Parameters

The DBConnector action in Studio relies on a pre-defined connector. You cannot create the connector via the Studio API; it must exist in the Admin Console. However, you define the query and parameters within the Studio flow definition.

The critical part of the DBConnector action is the query field. It must be a valid SQL statement. To prevent SQL injection and ensure type safety, use parameterized queries. The parameters are passed via the parameters object in the action configuration.

Assume you have a connector with ID conn-12345-abcde. You want to look up a customer record by customer_id.

The SQL query would be:

SELECT * FROM customers WHERE id = @customerId

In the Studio JSON definition, this maps to the query property. The @customerId placeholder is replaced by a Studio variable at runtime.

Step 2: Construct the Studio Flow Definition

A Studio flow is a Directed Acyclic Graph (DAG). We need three nodes:

1. Start: The entry point.
2. DB Connector: The action that performs the lookup.
3. End/Response: To handle the result.

Below is the minimal JSON structure for this flow. Note the settings object in the DBConnector node.

const flowDefinition = {
  name: "Customer Lookup via DBConnector",
  description: "Looks up customer details from SQL DB using DBConnector",
  version: 1,
  nodes: [
    {
      id: "start-node",
      type: "Start",
      settings: {
        // Initial variables can be defined here, but we will pass them via the trigger
      }
    },
    {
      id: "db-lookup-node",
      type: "DBConnector",
      settings: {
        connectorId: "conn-12345-abcde", // Replace with your actual Connector ID
        query: "SELECT first_name, last_name, email FROM customers WHERE id = @customerId",
        parameters: [
          {
            name: "customerId",
            type: "String",
            value: "${input.customerId}" // Reference the input variable from the trigger
          }
        ],
        // Optional: Limit rows to prevent large payloads
        maxRows: 1 
      },
      // Define transitions
      transitions: {
        success: "response-node",
        failure: "error-node"
      }
    },
    {
      id: "response-node",
      type: "Response",
      settings: {
        type: "Json",
        content: {
          status: "success",
          data: "${db-lookup-node.result.rows[0]}" 
          // Access the first row of the result set
        }
      }
    },
    {
      id: "error-node",
      type: "Response",
      settings: {
        type: "Json",
        content: {
          status: "error",
          message: "Database lookup failed",
          error: "${db-lookup-node.error.message}"
        }
      }
    }
  ]
};

Step 3: Create the Flow via API

Now, use the SDK to create this flow in your CXone environment.

const { initStudioClient } = require("./auth");

async function createFlow() {
  const studioClient = await initStudioClient();

  try {
    const response = await studioClient.flowsApi.postStudioFlows(flowDefinition);
    console.log("Flow created successfully with ID:", response.body.id);
    return response.body.id;
  } catch (error) {
    console.error("Failed to create flow:", error.body || error.message);
    throw error;
  }
}

createFlow();

Step 4: Invoke the Flow and Test the Lookup

To test the flow, you invoke it via the Studio API. This simulates an incoming request.

async function testFlow(flowId) {
  const studioClient = await initStudioClient();

  const inputPayload = {
    customerId: "CUST-98765" // The ID to look up
  };

  try {
    const response = await studioClient.flowsApi.postStudioFlowsIdInvoke(flowId, {
      input: inputPayload
    });
    
    console.log("Invocation Result:", JSON.stringify(response.body, null, 2));
  } catch (error) {
    console.error("Invocation failed:", error.body || error.message);
  }
}

// Usage:
// createFlow().then(flowId => testFlow(flowId));

Complete Working Example

Below is the full, copy-pasteable Node.js script. It combines authentication, flow creation, and invocation.

const { PlatformClient, StudioClient } = require("@nice-cxone/sdk");
require("dotenv").config();

// Configuration
const FLOW_NAME = "DevTest_DBConnector_Lookup";
const CONNECTOR_ID = process.env.CXONE_DB_CONNECTOR_ID || "conn-placeholder-id";
const TEST_CUSTOMER_ID = "CUST-12345";

// Flow Definition
const flowDefinition = {
  name: FLOW_NAME,
  description: "Automated DB Lookup Test",
  version: 1,
  nodes: [
    {
      id: "start",
      type: "Start",
      settings: {}
    },
    {
      id: "db-action",
      type: "DBConnector",
      settings: {
        connectorId: CONNECTOR_ID,
        query: "SELECT first_name, last_name FROM customers WHERE id = @custId",
        parameters: [
          {
            name: "custId",
            type: "String",
            value: "${input.customerId}"
          }
        ],
        maxRows: 1
      },
      transitions: {
        success: "success-response",
        failure: "error-response"
      }
    },
    {
      id: "success-response",
      type: "Response",
      settings: {
        type: "Json",
        content: {
          message: "Lookup Successful",
          customer: "${db-action.result.rows[0]}"
        }
      }
    },
    {
      id: "error-response",
      type: "Response",
      settings: {
        type: "Json",
        content: {
          message: "Lookup Failed",
          error: "${db-action.error.message}"
        }
      }
    }
  ]
};

async function main() {
  const platform = new PlatformClient();
  
  // 1. Authenticate
  await platform.auth.loginClientCredentials({
    clientId: process.env.CXONE_CLIENT_ID,
    clientSecret: process.env.CXONE_CLIENT_SECRET,
    environment: process.env.CXONE_ENVIRONMENT || "us1",
    subdomain: process.env.CXONE_SUBDOMAIN,
  });

  const studioClient = new StudioClient(platform);

  try {
    // 2. Create Flow
    console.log("Creating flow...");
    const createResponse = await studioClient.flowsApi.postStudioFlows(flowDefinition);
    const flowId = createResponse.body.id;
    console.log(`Flow created with ID: ${flowId}`);

    // 3. Invoke Flow
    console.log(`Invoking flow with customerId: ${TEST_CUSTOMER_ID}...`);
    const invokeResponse = await studioClient.flowsApi.postStudioFlowsIdInvoke(flowId, {
      input: {
        customerId: TEST_CUSTOMER_ID
      }
    });

    console.log("Result:", JSON.stringify(invokeResponse.body, null, 2));

  } catch (error) {
    console.error("Error:", error.body ? JSON.stringify(error.body, null, 2) : error.message);
  } finally {
    // Optional: Delete flow for cleanup
    // await studioClient.flowsApi.deleteStudioFlowsId(flowId);
  }
}

main();

Common Errors & Debugging

Error: 400 Bad Request - Invalid Connector ID

• What causes it: The connectorId in the DBConnector settings does not match an existing connector in your CXone instance.
• How to fix it: Verify the ID in the CXone Admin Console under Integrations > Database Connectors. Copy the ID exactly.
• Code showing the fix:

  // Ensure CONNECTOR_ID is fetched from env or admin console
  const CONNECTOR_ID = process.env.CXONE_DB_CONNECTOR_ID;
  if (!CONNECTOR_ID) throw new Error("CXONE_DB_CONNECTOR_ID not set");
  

Error: 400 Bad Request - Parameter Mismatch

• What causes it: The SQL query uses a placeholder (e.g., @custId) that is not defined in the parameters array, or the type is incorrect.
• How to fix it: Ensure every @paramName in the query string has a corresponding object in the parameters array with the exact same name.
• Code showing the fix:

  settings: {
    query: "SELECT * FROM t WHERE x = @myVar",
    parameters: [
      { name: "myVar", type: "String", value: "${input.myVar}" }
    ]
  }
  

Error: 429 Too Many Requests

• What causes it: You are hitting the Studio API rate limits. This is common when looping through many flow creations or invocations.
• How to fix it: Implement exponential backoff. The SDK does not automatically retry all 429s, so you must handle it.
• Code showing the fix:

  async function invokeWithRetry(client, flowId, input, retries = 3) {
    for (let i = 0; i < retries; i++) {
      try {
        return await client.flowsApi.postStudioFlowsIdInvoke(flowId, { input });
      } catch (error) {
        if (error.status === 429 && i < retries - 1) {
          const delay = Math.pow(2, i) * 1000;
          console.log(`Rate limited. Retrying in ${delay}ms...`);
          await new Promise(resolve => setTimeout(resolve, delay));
        } else {
          throw error;
        }
      }
    }
  }
  

Error: 500 Internal Server Error - Database Timeout

• What causes it: The SQL query takes longer than the CXone DB Connector timeout (typically 5-10 seconds) to return results.
• How to fix it: Optimize the SQL query. Add indexes to the database columns used in the WHERE clause. Use maxRows to limit the result set size.
• Code showing the fix:

  settings: {
    query: "SELECT first_name FROM customers WHERE id = @custId",
    maxRows: 1, // Ensure only one row is returned
    parameters: [...]
  }
  

Official References

• NICE CXone Studio API Documentation
• DBConnector Action Reference
• NICE CXone Node.js SDK on GitHub