How to sync your MySQL database with Salesforce in Docker using Boomi
Tags: mysql, ipaas, docker, salesforce
Published: 2024-03-25 21:37:41

[Integrated platforms as a service (iPaaS)](https://www.gartner.com/en/information-technology/glossary/information-platform-as-a-service-ipaas#:~:text=Integration%20Platform%20as%20a%20Service%20(iPaaS)%20is%20a%20suite%20of,individual%20or%20across%20multiple%20organizations.) are the guardians of the very distributed present and future of the modern software development landscape. They empower your team to enjoy all the benefits of a modular, distributed system without experiencing a lot of its cons, such as siloed data and complexities in unifying this data throughout the application.

iPaaS systems like [Boomi](https://boomi.com/) shine even brighter thanks to their intuitive and user-friendly interfaces. These solutions simplify integration with third-party applications,  like [Salesforce](https://www.salesforce.com/products/what-is-salesforce/) for customer management and [Eyer](https://eyer.ai/) for [complete visibility](https://eyer/blog/how-to-unlock-ai-powered-observability-insights-in-your-boomi-integration). No matter how complex the integrations become, iPaaS platforms like Boomi empower us to achieve our goals without compromise.  In other words, we can leverage the power of various applications while maintaining a streamlined system.

At the end of this article, you will understand how Boomi ensures data unification by leveraging Boomi to sync data between your MySQL database and your Salesforce application within a Docker containerized environment.  


## Prerequisites 

To get the most out of this article, you must have the following:



* A Boomi platform account: if you do not have one,[ create a 30-day free trial account](https://boomi.com/)
* A Salesforce account: if you do not have one,[ create a free account](https://www.salesforce.com/)
* A Boomi Atom is installed locally and connected to a MySQL account. To learn how to do this, check out [How to connect your Boomi Atom runtime and MySQL on Docker.](https://dev.to/eyer-ai/connecting-boomi-runtime-with-your-mysql-database-on-docker-18ei)


## Inserting data in your MySQL database 

[MySQL](https://www.mysql.com/) is an open-source [relational database management system (RDBMS)](https://media.dev.to/cdn-cgi/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2F1s5riwln9w8upm9jtef6.jpg) that stores, organizes, and accesses data in a structured format. The prerequisites section discussed [Connecting your Boomi Atom runtime and MySQL on Docker](https://dev.to/eyer-ai/connecting-boomi-runtime-with-your-mysql-database-on-docker-18ei), and this section will build on that knowledge.

Before you can sync your Salesforce account with your database, you'll need to populate the database with some data. To do this, you must change your folder structure and the `docker-compose.yml` file you created when you connected your [Boomi Atom runtime to your MySQL database](https://eyer.ai/blog/how-to-connect-your-boomi-atom-runtime-and-mysql-on-docker/).

In your project's root directory, create a folder named ‘mysql-data’. This folder will store your MySQL data and ensure it persists even when the Docker container stops running.

Next, in your `docker-compose.yml` file, add a volume mapping for the MySQL service. This line will link the ‘mysql-data’ folder on your host machine to the container's internal data directory. 

```yml
 mysql:
    image: mysql:latest
    container_name: mysql
# add this line
    volumes:
      - mysql-data:/var/lib/mysql:Z
    environment: 
      - MYSQL_ROOT_PASSWORD=<root user password>
      - MYSQL_USER=<username>
      - MYSQL_PASSWORD=<password>
      - MYSQL_DATABASE=<name of your MySQL database>
    ports: 
      - "3306:3306"

```

After updating your `docker-compose.yml` file, run this command to build and run your images.

```bash
docker compose up -d
```

**Creating a table in your MySQL Database**

Run this command in your terminal to start an interactive shell in your running MySQL Docker container.

```bash
docker exec -it mysql bash
```

Next, log into this shell using this command.

```bash
mysql -u <mysql_user> -p<mysql_password> <mysql_database>

```

The next step is to create a table named 'customers' to store your customer data. This table will include 'CUSTOMER_ID', 'EMAIL', 'NAME', 'STATE', and 'CITY' fields.

```sql
CREATE TABLE customers (
  CUSTOMER_ID integer,
  NAME varchar(255), 
  STATE varchar(255),
  CITY varchar(255),
  EMAIL varchar(255)  
);

```

To check if your tables have been successfully created, run this command.

```sql
SHOW TABLES;
```

**Inserting data in your MySQL database** 

After you create your tables, use the following command to insert data into this table.

```sql
INSERT INTO customers (CUSTOMER_ID, NAME, STATE, CITY, EMAIL)
VALUES (1, 'user1 name', 'user1 state’, 'user1 city', 'user1 email'),
(2, 'user2 name', 'user2 state’, 'user2 city', 'user2 email'),
(3, 'user3 name', 'user3 state', 'user3 city', 'user3 email');
```

Run this command to see your data.

```sql
SELECT * FROM customers
```


## Creating custom data fields on your Salesforce account

[Salesforce](https://www.salesforce.com/products/what-is-salesforce/) is a powerful cloud-based tool that enables you to manage customer interactions and boost sales.  

However, before importing your database data into your Salesforce account, you need to create a custom ‘email’ and an external ‘customer_id’ field, as the Salesforce Account object does not offer these fields primarily. 

To create these fields, first log in to your [Salesforce account](https://login.salesforce.com/). Then, navigate to **Quick Settings** and **Open Advanced Setup**.




![Salesforce Quick Settings sidebar](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l6b1qeb94lgvtc5unly6.png)


From the **Setup** page, navigate to the **Object Manager** tab at the top. Then, click on the **Account** label.




![Salesforce Setup Home](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ptrtngdgwbdkcsu3tpkp.png)


![Salesforce Account label](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yxkgp6wkhp9rt9l765kn.png)


Navigate to the **Fields & Relationships** section on the **Account object manager** page. In the top-right corner, click the **New** button.


![Salesforce Account page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/betlz1zoeinjtrr1dcqa.png)


![Salesforce Fields & Relationships](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cnqjtdt3ad4rhihhlgi8.png)


Next, choose the type of information the custom field will contain. Since you are creating a custom field for email addresses, select **email**.




![Salesforce Object Manager](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sijkyb4jpz7435plaqcr.png)


 For the **Field Label** and **Field Name**, enter **Email**. Then, click the **Next -> Next -> Save** buttons to save the email custom field. 




![Salesforce Email custom field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zjlbv5em24tojzv7h8dj.png)


To create a new external custom **Customer_ID** field, follow these steps:



* Click **New**.
* Choose **Number** as the field type.
* Enter **Customer_ID** for both the Field Label and Field Name.
* Mark the checkbox next to **External ID** to designate this **Customer_ID** as a unique identifier from an external system.
* Click **Next -> Next -> Save** to finalize the creation of the Customer ID field.



![Salesforce Customer_ID custom field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1y8z4xogbbdq8y4ljg93.png)


You should see both fields in your **Fields & Relationships** page when you are done.



![Salesforce Fields & Relationships page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j45pyaoa7vddyzk83132.png)



## Syncing your MySQL database to your Salesforce account

You'll need to create a Boomi process to synchronize data between these applications. Navigate to the **Integration** page, click the **Create New** button at the top, and select **Process** from the dropdown menu.

This will open the Start Shape sidebar. Choose the **No Data** radio button, indicating the process won't receive external data, and confirm with the **OK** button.




![Boomi Start Shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jcffmfw5heyxlwtk6m82.png)


Next, locate the **Try/Catch Shape** in the sidebar and drag it onto the canvas. This will open the **Try/Catch Shape** sidebar. Click on the** Failure Trigger** dropdown field and change its value from **Document Errors** to **All Errors**. 

To ensure your shapes are properly connected, verify that the lines connecting them have changed from dotted gray lines to solid green lines, as shown in the image below.




![Boomi Try/Catch connector](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lealf11q76b8wk30llb0.png)


Like try/catch blocks in programming languages, the **Try/Catch** shape handles errors during processing, either at the process level or for individual documents.

**Selecting your database connection**

Next, search for the **Database** connector shape in the sidebar and drag the **Database** connector onto the canvas. This opens up the **Connector Shape** sidebar.  


![Boomi Database Connector](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1qdo1bhy1dzqfj0pnkar.png)


In the **Connector Shape** sidebar, click on the **[Connection input field ](https://help.boomi.com/docs/Atomsphere/Integration/Connectors/c-atm-Connectors_bb305b35-0f13-4937-a918-f85dbbe1b27b)**and select the connection you created in the prerequisites section.




![Boomi Connector Shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6vd7j6x65qvfz4lvxek3.png)


**Creating a database operation**

To create a new operation, click the **+** sign in the [Operation input field](https://help.boomi.com/docs/Atomsphere/Integration/Connectors/c-atm-Connectors_bb305b35-0f13-4937-a918-f85dbbe1b27b). In Boomi, a database operation defines how you interact with the database specified in the connection.

Clicking the **+** sign opens the **Database Options** page.




![Database Options](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pcsiv9haia1fhwauv3c1.png)


Navigate to the **Profile** field and click the **+** symbol to create a new **Database Profile** for the operation.

On the **Database Profile** page, click the **Statement** menu, followed by the **Import** button on the top right-hand corner to import your database connection.



![Database Profile](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vj3xqchd760cq86hba2z.png)


The **Import button** opens up a **Database Import Wizard** menu. Here, fill out the following fields with your credentials:



* The Boomi Atom you want to run this Boomi process on
* The Database connection you created
* A schema filter of customers

Once you are done, your **Database Import Wizard** modal should look like the image below:



![Boomi Database import wizard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fzbcfmsnj3p5oe6mcyzg.png)




After clicking **Next**, you'll be prompted to choose a table for synchronization. Select the desired table and click **Next** again.

Next, select the columns you want to move to your Salesforce account. You can choose all columns or exclude any as needed. Once you've made your selections, click **Next**, followed by the **Finish** button.

Finally, expand the **Fields** menu to verify that your Database profile has been imported correctly into the Connector.




![Boomi Database Profile](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yxgnrqfug0f601ya6w39.png)


After saving and closing the **Profile** page, you'll be returned to the **Database Options** page. Here, set up the link between your data points:



* Click on the **Link Element** field.
* From the **Fields** menu, choose **Customer_ID**. This tells Boomi to group records that share the same **Customer_ID** into a single document.

Set the **Batch Count** to 1 for data retrieval to process data one record at a time. Leave **Max Rows** at 0 to retrieve all rows from the database.




![Boomi Database Options](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kfjmw8yp2h8iamqtfe84.png)

After saving and closing the profile page, you'll see the **Connector Shape** sidebar. Click the **OK** button there.


![Fully configured Boomi Connector](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7xjk17t6v9fqflw7c7vd.png)



## Mapping the Database Fields to the Salesforce Account Field

To ensure error handling during syncing, drag the **Try/Catch Shape** block onto the canvas before mapping your database fields to the corresponding Salesforce Account fields.



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j9x8e6w4tgx66piu81be.png)


Next, drag the **Map** shape onto the canvas and connect it to the try path from the **Try/Catch** block. This will open up a **Map** sidebar. In the **Map** input field; click the **+** button to create a new map.

Clicking the **+** button opens up a Map page. On this page, click the **Choose** button in the **Map Source** section. This action opens a **Choose a Profile** modal. In this modal, choose **Database** as the **Profile Type**, and expand on your menu to select the DB profile you created. 

You'll need to create a Salesforce profile before you can map these fields to each other. So, save and close this map for now.




![Boomi map](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p1y4kvm24raoa5g452y4.png)


Next, click the **OK** button on your **Map** Shape sidebar to return to the canvas.


## Adding a Salesforce Connector

Search for the Salesforce connector in your sidebar and drag it onto the canvas. This action opens the **Connector Shape** sidebar, allowing us to configure the Salesforce **Connector Shape**. 




![Boomi Salesforce connector Shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/su5nxfl9z4ep1fmtflav.png)


Since you're upserting data into your Salesforce account, change the action from **Get** to **Send**.

**Creating a Salesforce connection**

A **Salesforce Security Token** is required to connect to your Salesforce application. To obtain your token, log in to your [Salesforce account](https://login.salesforce.com/), click on your avatar, and navigate to **settings**. This process ensures a more secure connection by adding an extra layer of authentication.



![Salesforce settings tab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/644yed0tps7o71z4qk7b.png)


Next, navigate to **Reset Security Token** and then reset your security token. You should see your new token in your email.


![Salesforce Reset security token](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/84pc6si7o7qnstlhjgqn.png)


Next, create a Salesforce connection by clicking the **+** button in the Connection input field. This opens up a new page; fill out this page with your credentials:
* Salesforce username
* Salesforce connector password, which is your Salesforce password, and your Token 

Once you are done, your connection will look like this.


![Salesforce connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mymcx3jitk92qc6kmb5d.png)


**Save and close** this page, which will lead you back to the Salesforce connector sidebar.

**Creating a Salesforce Operation**

Click the **+** sign in the **Operation** input field to create a new Operation.

Next, click the **Import Operation** button to create a profile. Clicking this button will open the **Salesforce Import Wizard**. Choose the Salesforce Connection you created in the modal’s **Connection** field.


![Salesforce import wizard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wdvo1e65yem8172nx5cn.png)


Click on **Next**. In this modal, you specify the Salesforce object where you want to insert the data. This tutorial inserts it into the **Account** object. Then, change the **Action** from **Create** to **Upsert** and click the **Next -> Next -> Finish** buttons.




![Salesforce Import Wizard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8cecmbu0dy9swrwz4nvd.png)


In the next step, choose the **Customer_ID** custom field you created earlier for the **External ID** field. Then, save your changes and close the window.




![Salesforce External ID](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uioz6lonjro4eyi4mfpq.png)


To save your changes, click the **OK** button for the Salesforce **Connector Shape**.


![Fully configured Salesforce connector shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z076pykm9yusyru1vhqs.png)


Next, open the **Database to Salesforce Map** Shape sidebar and click the pencil icon to edit the Map shape.

On the right side of the page, the Map destination, find the **Choose** button and click it. This action will open the **Choose a Profile** modal.

Select **XML** from the **Profile Type** dropdown menu within the modal. Then, expand the menu and choose the Salesforce Profile you just created. Confirm your selection by clicking **OK**.

The next step involves linking the corresponding fields between your database and Salesforce. Simply drag your cursor from a field on the left side and connect it to its matching field on the right. Once you've mapped all the fields, your page should resemble the example provided.




![MySQL database mapped to Salesforce fields](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a2oy9w7nqihbbg8azmyj.png)


 Finally, add two **End and Continue** shapes and connect the shapes to both the Salesforce connector and the Catch path of the second **Try/Catch shape**.




![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/unw8nquqw9muvko5rkx7.png)


Next, click on Arrange, then **save and test** the process.

To verify if your database has successfully synced with your Salesforce Account, click the last **End and Continue** Shape, then follow by clicking the **Shape Source Data** tab below.




![Full Boomi process to sync MySQL data with Salesforce](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lqk7f34pb75fn8pvprwr.png)


You will see packets; click on one to see your data.




![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m3zzyefvx3irj1k0dvm2.png)

After completing the process, your database data will be visible in your Salesforce application.



![Salesforce account page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/av7mkqpyvao4s2at4dxn.png)



## That’s a Wrap!

This guide has demonstrated the allure of Integration Platform as a Service (iPaaS) and its ability to streamline data integration between applications. This article specifically focused on connecting your MySQL database to your Salesforce application, but this is just the tip of the iceberg. Please refer to the[ official documentation](https://help.boomi.com/) to learn about Boomi's extensive capabilities.