salesforce

Expose External Data to Salesforce via OData in MuleSoft

Bharathvaj

03 Mar 2025 • 7 min read

What is OData?

OData (Open Data Protocol) is a protocol that allows the creation and consumption of RESTful APIs. It is designed for querying and manipulating data through HTTP-based requests and simplifies this process with features like filtering, sorting, and paging.

What is an External Object and External Data in Salesforce?

An External Object in Salesforce enables access to data stored outside Salesforce, such as in external databases or APIs, without importing the data.

External Data refers to this real-time external data that can be accessed and interacted with directly within Salesforce using integrations like OData.

Prerequisites:

Before starting the OData integration, ensure you have the following:

Anypoint Studio 7.1.4 or later
Mule Runtime Engine 4.1.1 or later
A working database or data source to expose via OData
OData Plugin

Installing OData V2 Tooling

To install the APIkit for OData v2 plugin in Anypoint Studio:

Open Anypoint Studio > Select Help > Install New Software > Add.

Navigating to Install APIkit for OData v2 Plugin in Anypoint Studio.

In Name, type APIkit for ODATA v2 Update Site.
In Location, type the URL of the site: https://studio.mulesoft.org/s4/apikit-for-odata/.
Select Add.

5. Select Anypoint ODATA Plugin and Next.

After adding the OData v2 tooling site, click '**Next**' to proceed with the installation.

6. Accept the terms and conditions of the product and select Finish.
7. Restart Studio to complete the installation.

Implementing an OData V2 API

Step 1: Creating the OData Entity Data Model (odata.raml)

The first step is to define the OData model in RAML. We’ll define the data types and properties for the Order entity, which will be exposed via the OData API.

#%RAML 1.0 Library
uses:
   odata: libraries/odataLibrary.raml
types:
  orders:
    (odata.remote): orders
    properties:
      CustomerID:
        type: string
        (odata.key): true
        (odata.nullable): false
        maxLength: 5
      orderDate:
        type: string
        (odata.nullable): true
        (odata.key): false
        maxLength: 40
        required: false
      orderID:
        type: string
        (odata.nullable): true
        (odata.key): false
        maxLength: 30
        required: false
      shippedDate:
        type: string
        (odata.nullable): true
        (odata.key): false
        maxLength: 30
        required: false

Step 2: Set Up the MuleSoft Project

Create a Mule Project in Anypoint Studio:
- Open Anypoint Studio.
- Select File > New > Mule Project.
- Name your project (e.g., order-odata-api).
- Click Finish to create the project.
Create the odata.raml File:
- Under the src/main/resources/api directory, create an odata.raml file.
- Paste the RAML code defined earlier into this file.

3. Now, right-click on the odata.raml file and select Mule > Generate OData API from RAML Types.

4. The Odata extension will download the necessary files api.raml and odataLibrary.raml file for API implementation. Generated odataLibrary.raml file will have the below contents where datatypes and query parameters are defined.

The api.raml file will contain below code.

%RAML 1.0 Library
usage: |
  Use to define an OData Entity Model
annotationTypes:
  remote:
     description: The name of the Entity in the remote data source.
     type: string
  key:
     description: Defines whether a field is a key or not.
     type: boolean
  nullable:
     description: Defines whether a field is nullable or not.
     type: boolean
  precision:
     description: Specifies the precision of a field. Only valid for date and numeric types.
     type: integer
  scale:
     description: Specifies the precision of a field. Only valid for numeric types.
     type: integer
  type:
     description: This is used to specify some OData types that are not present in RAML (Guid, Time, and DateTimeOffset).
     type: string
traits:
  orderby:
    queryParameters:
       orderby:
          description: Expression for determining what values are used to order the collection of Entries.
          type: string
          required: false
  top:
    queryParameters:
       top:
         description: Identifies a subset formed by selecting only the first N items of the set, where N is a positive integer specified by this query option.
         type: number
         required: false
  skip:
    queryParameters:
       skip:
          description: Identifies a subset defined by seeking N Entries into the Collection and selecting only the remaining Entries (starting with Entry N+1).
          type: number
          required: false
  filter:
    queryParameters:
      filter:
        description: Identifies a subset determined by selecting only the Entries that satisfy the predicate expression specified by the query option.
        type: string
        required: false
  expand:
    queryParameters:
      expand:
        description: A URI with an expand System Query Option indicates that Entries associated with the Entry or Collection of Entries identified by the Resource Path section of the URI must be represented inline.
        type: string
        required: false
  format:
    queryParameters:
      format:
        description: If the format query option is present in a request URI, it takes precedence over the value(s) specified in the Accept request header. Valid values for the $format query string option are listed in the following table.
        type: string
        required: false
  select:
    queryParameters:
      select:
        description: Specifies that a response from an OData service should return a subset of the Properties which would have been returned had the URI not included a select query option.
        type: string
        required: false
  inlinecount:
    queryParameters:
      inlinecount:
        description: Specifies that the response to the request includes a count of the number of Entries in the Collection.
        type: string
        required: false

Generated Flows in api.xml

The api.raml file will be generated based on the odata.raml file. This file contains the flows for interacting with the Order resource as shown below. The flows will handle GET, POST, PUT, and DELETE requests.

Generated Flows:

GET /orders: Fetch order data from the database.
POST /orders: Insert new order records into the database.
PUT /orders/{id}: Update an existing order by ID.
DELETE /orders/{id}: Delete an order by ID.

Step 3: Implementing Database Operations

Now let’s implement the Get operation to retrieve order records.
You need to configure a Database Connector to connect to your database (e.g., MySQL, SQL Server) and retrieve order data.

Step 4: Run and Test the OData API

Once you have implemented the API, it’s time to run and test it.

Running the API:

Run the Mule project locally using Anypoint Studio.

To Call the REST API:

GET: http://localhost:8081/api/orders

To Call the OData Service:

GET: http://localhost:8081/api/odata.svc

Step 5: Deploy to CloudHub

To expose DB data to Salesforce, you need to deploy your Mule application in CloudHub. After deployment, you can access the OData API from a public URL.

Salesforce OData Integration

STEP 1: Obtain Your OData API Public URL

Before you start integrating the OData API into Salesforce, you need to obtain the public URL for the OData service. This URL is the endpoint exposed by your MuleSoft or other API platform to allow Salesforce to interact with the external data.
Log into your Salesforce account using your credentials.

STEP 2: Create Custom Objects and Fields in Salesforce (optional)

Once you have the public URL for your OData API, the next step is to create custom objects and fields in Salesforce to store and manage the data, as well as to insert the necessary data.

STEP 3: Set Up External Data Sources in Salesforce

In Salesforce, go to the Setup menu.
In the Quick Find search box on the left, type External Data Sources and click on the External Data Sources option under the Integration section.

Now, Click on New external data source.

Paste public url in URL field and give all necessary information and save it.

STEP 4: Create and Sync External Objects

Once the external data source is saved, click on Validate and Sync.
A list of tables (entities) from your OData API will be displayed.
Select the tables/entities you want to sync into Salesforce as External Objects.
Salesforce will automatically create the external objects for you. These external objects will act as placeholders for the data coming from the external database.
After syncing, you can view the external objects by navigating to Objects and Fields > External Objects in the Setup menu.

STEP 5: Enable Lookup Relationships

You can create external lookup or indirect lookup relationships to link external objects with standard or custom Salesforce objects.
This allows you to create seamless relationships between external data and your internal Salesforce data.

STEP 6: Access External Data

To view and interact with the external data in Salesforce, you can either:
1. Create a custom tab to display external object data:
  - Go to Setup > Tabs > New Tab.
  - Select the external object you created from the dropdown list.
  - Choose a tab style and save.
2. Use Lightning App Builder to add the external objects to a custom page:
  - Go to Setup > Lightning App Builder.
  - Create a new record page or modify an existing page.
  - Drag and drop the Related List or External Object components to the page layout.
  - Save and activate the page.

STEP 7: Test and Verify

Navigate to the custom tab or Lightning page you created to ensure the external data is correctly displayed and accessible.
You should now be able to interact with the external data as if it were stored natively in Salesforce.

With these steps completed, Salesforce users can easily access and manage external data directly from within their familiar environment.