Query Microsoft Dataverse Data from Node.js

The CData API Server, when paired with the Microsoft Dataverse connector (or any of over 300 supported data sources), exposes Microsoft Dataverse data as an OData web service that can be queried from Node.js using simple HTTP requests. This article walks you through setting up the CData API Server and demonstrates the process to retrieve JSON-formatted Microsoft Dataverse data in Node.js.

Getting Started

---

Setting Up the API Server

If you haven't already, download and install the CData API Server. After installation, run the application, download the Microsoft Dataverse connector from within the API Server, and configure your Microsoft Dataverse data connection. Then, configure API Server to generate OData feeds for any tables you want to access in your single-page application (SPA).

Enable CORS

AngularJS requires servers to have CORS (Cross-Origin Resource Sharing) enabled. To enable CORS, go to the Server tab under the Settings section in the API Server and adjust the following settings:

• Click the checkbox to Enable cross-origin resource sharing (CORS).
• Either click the checkbox to Allow all domains without '*' or specify the domain(s) that are allowed to connect in Access-Control-Allow-Origin.
• Set Access-Control-Allow-Methods to "GET, PUT, POST, OPTIONS".
• Set Access-Control-Allow-Headers to "Content-Type, Authorization".
• Click Save to save the changes made.

Connect to Microsoft Dataverse

To work with Microsoft Dataverse data using AngularJS, we start by creating and configuring a Microsoft Dataverse connection. Follow the steps below to configure the API Server to connect to Microsoft Dataverse data:

1. First, navigate to the Connections page.
2. Click Add Connection and then search for and select the Microsoft Dataverse connection. 👁 Selecting a data source (SQLite is shown)
   
3. Enter the necessary authentication properties to connect to Microsoft Dataverse. 👁 Connecting to a datasource (SQLite is shown)
   
4. After configuring the connection, click Save & Test to confirm a successful connection.

Configure API Server Users

Next, create a user to access your Microsoft Dataverse data through the API Server. You can add and configure users on the Users page. Follow the steps below to configure and create a user:

1. On the Users page, click Add User to open the Add User dialog.
2. Next, set the Role, Username, and Privileges properties and then click Add User. 👁 Configure a new user.
   
3. An Authtoken is then generated for the user. You can find the Authtoken and other information for each user on the Users page: 👁 API Server user settings.
   

Creating API Endpoints for Microsoft Dataverse

Having created a user, you are ready to create API endpoints for the Microsoft Dataverse tables:

1. First, navigate to the API page and then click Add Table . 👁 Add tables
   
2. Select the connection you wish to access and click Next. 👁 Select the connection (SQLite is shown)
   
3. With the connection selected, create endpoints by selecting each table and then clicking Confirm. 👁 Adding tables from the connection (SQLite is shown)
   

Gather the OData URL

Having configured a connection to Microsoft Dataverse data, created a user, and added resources to the API Server, you now have an easily accessible REST API based on the OData protocol for those resources. From the API page in API Server, you can view and copy the API Endpoints for the API:

👁 API Endpoints

Just like with standard OData feeds, you can limit the fields returned by adding a $select parameter to the query. You can also include other standard OData URL parameters, such as $filter, $orderby, $skip, and $top. Refer to the help documentation for more details on supported OData queries.

Save the Authtoken

Select the user you created, then copy and save the authtoken for future use. You can also toggle on 'Token Expiration' and set the number of days based on your use case. Click Save to save the details.

👁 Copy and save authtoken

Consume Microsoft Dataverse OData feeds using Node.js

OData feeds provide a standardized way to access data and can be easily consumed in Node.js. Node.js scripts use the built-in http module to make requests to the CData API Server, which acts as an intermediary to expose Microsoft Dataverse data as OData. The process involves constructing HTTP GET requests to the API Server's OData endpoints, handling the responses, and parsing the JSON data.

To get started, we'll create a new Node.js project and install the necessary packages to run it.

Create a Node.js Application

1. Initialize a Node.js project on your system (e.g., D: drive) using a Terminal or Command Prompt. Make sure to run it as an Administrator.

   cd D:\
   mkdir my-odata-app
   cd my-odata-app
   npm init -y
   

2. Now, install the required packages.

   npm install http
   

Fetching data by ID

In this section, we'll work on retrieving specific records from a Microsoft Dataverse entity by filtering based on a unique identifier using the app_data.js script.

• Constructing the filtered request: The http.get() method is used to send a GET request to the CData API Server. The path option includes an OData $filter clause: /api.rsc/DEMO_DB_CRM_Account?$filter= + encodeURIComponent("Id eq '" + id + "'"). This filter tells the API Server to only return the record where the Id field matches the provided id value. The encodeURIComponent() function ensures that the filter expression is properly encoded for inclusion in the URL, handling any special characters. The auth option provides authentication credentials to the API Server.
• Processing the response: The script sets up event listeners on the response object (res). The data event listener accumulates the response body in the body variable. Once the entire response is received (the end event), the body is parsed as JSON using JSON.parse(), and the resulting JavaScript object (jsonData) is then logged to the console. Error handling is included to catch and log any errors during the HTTP request.

app_data.js

var http = require('http');

// Function to fetch data based on a given Id
function fetchDataById(id) {
 http.get({
 protocol: "http:",
 hostname: "localhost",
 port: 8080,
 path: "/api.rsc/DEMO_DB_CRM_Account?$filter=" + encodeURIComponent("Id eq '" + id + "'"), // Updated URL with filter
 auth: 'your_username:your_authtoken' // Enter your username and authtoken/password
 }, function (res) {
 var body = '';

 res.on('data', function (chunk) {
 body += chunk;
 });

 res.on('end', function () {
 var jsonData = JSON.parse(body);
 console.log(jsonData);
 });
 }).on('error', function (e) {
 console.log("Error: ", e);
 });
}

// Example usage: Fetch data for Id '11'
fetchDataById('11');

Retrieve the specific records for an unique identifier from the Microsoft Dataverse entity, using the following command in the Terminal or Command Prompt:

node app_data.js

Retrieving all data from a Microsoft Dataverse entity

The app_table.js script focuses on fetching all records from a specified Microsoft Dataverse entity.

• Making the unfiltered request: Similar to app_data.js, http.get() is used, but this time the path option simply specifies the entity's endpoint: /api.rsc/DEMO_DB_CRM_Account. By omitting the $filter parameter, the API Server returns all available data from that entity. Again, the auth option is used for authentication.
• Handling and Displaying the Data: The response is handled in the same way as in app_data.js: the script listens for data events to build the response body, parses the JSON response in the end event, and logs the parsed JSON data (jsonData) to the console. The raw body is also logged before parsing. Error handling is included here as well.

app_table.js

var http = require('http');

http.get({
 protocol: "http:",
 hostname: "localhost",
 port: 8080,
 path: "/api.rsc/DEMO_DB_CRM_Account", // Updated URL to fetch data from DEMO_DB_CRM_Account
 auth: 'your_username:your_password' // Enter your username and password/authtoken
}, function (res) {
 var body = '';

 res.on('data', function (chunk) {
 body += chunk;
 });

 res.on('end', function () {
 console.log(body);
 var jsonData = JSON.parse(body);
 console.log(jsonData);
 });
}).on('error', function (e) {
 console.log("Error: ", e);
});

Retrieve all the records from the Microsoft Dataverse entity, using the following command:

node app_table.js

Free Trial & More Information

Accessing Microsoft Dataverse data from Node.js can be greatly simplified by leveraging an OData API. CData API Server exposes Microsoft Dataverse data as an OData service, enabling you to perform "Read" operations using standard HTTP requests. This allows you to work with your Microsoft Dataverse data in a more structured and standardized way.

To see how API Server can simplify your data access, download a free, 30-day trial of CData API Server today! For more general information on our API Server and to see what other data sources we support, refer to our API Server page.