How to automate email sending from MySQL databases using Boomi
Tags: ipaas, mysql, devops, boomi
Published: 2024-04-19 14:49:39

An [Integration platform 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.), like [Boomi](https://boomi.com/), is known for integrating platforms and data from different services. However, another interesting feature of the iPaaS solution is its ability to ensure efficient communication with your business clients. 

Automating email sending directly from your MySQL databases using Boomi allows you to establish personalized and timely communication without manual intervention. This eliminates the time-consuming task of manually sending emails and ensures that your customers receive relevant information promptly. 

This guide discusses automating sending out emails, empowering you to leverage your MySQL data to trigger custom email notifications.


## TL;DR

To automate email sending from your MySQL database with Boomi, follow these steps:



* Create a new Boomi integration process
* Collect the data from the database
* Transform the data type from database to CSV
* Split the CSV files  by line
* Set Properties
* Create the Message
* Send the email


## 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 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)
* A Boomi Mail connection; to learn how to create one, check out this [Creating the Mail connection and Operation](https://help.boomi.com/docs/Atomsphere/Integration/Getting%20started/int-Creating_the_Mail_connection_and_operation_96283ae7-a4e4-4589-9e0a-8117286c3e89) Boomi documentation


## Inserting data in your MySQL database 

[MySQL](https://www.mysql.com/) is an open-source [relational database management system (RDBMS)](https://www.techtarget.com/searchdatamanagement/definition/RDBMS-relational-database-management-system) 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.

The MySQL database will hold the user information, including their names and the email addresses to which you will send your desired message.  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 your host machine's ‘mysql-data’ folder 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.

```
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.

```

docker exec -it mysql bash

```

Next, log into this shell using this command.

```

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 ‘**id**’, ‘**first_name**’, ‘**last_name**’, and  ‘**email**’ fields.

```mysql

CREATE TABLE users (
 id integer,
 first_name varchar(255), 
 last_name varchar(255),
 email varchar(255)

);

```

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

```mysql

SHOW TABLES;

```

**Inserting data in your MySQL database** 

After you create your tables, use the following command to insert data into this table, replacing the placeholders with their actual values.

```mysql
INSERT INTO users  (id, first_name, last_name, email)
VALUES (1, 'user1 first name', 'user1 last name’, ‘user1 email'),
(2, 'user2 first name', 'user2 last name’, ‘user2 email');

```

Run this command to see your data.

```mysql
SELECT * FROM users
```


## Automate email sending with Boomi

This section explains how to set up a Boomi process to automatically collect data from your MySQL database and send a customized email.

To create this process, follow these steps:



**1.Create a new Boomi integration process**

Go to the **Integration** page, click the **Create New button**, and choose **Process** from the dropdown menu.
 
![The Create New dropdown](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c1k84jxjtofmsbklkcb5.png)



This action will open the **Start Shape** sidebar. Choose the **No Data** radio button, indicating that the process will not receive external data, and then confirm by clicking the **OK** button.
 


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

**2.Collect the data from the database**

After initiating your new Boomi process, add your MySQL database connector, which holds the user information.


To add a shape to a Boomi process, click on the small box at the end of the dotted lines, leaving the previous shape (in this case, the **Start Shape**). Then, search for the shape you want to add to the process (the **Database shape**).


    

![The Database connector](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/59u1dwbqo8kpgphy58hy.png)



Next, click on the **Database** connector to configure the shape. This action opens up the **Connector Shape** sidebar.
  

![The Database Connector Sidebar](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8das2ci5j9bze4oaplkl.png)


In this sidebar, click the **Connection** input field and select the MySQL connection you created in the prerequisites section. 


If you have not created the MySQL database connection, 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) to do so.


![The Connector Shape Action dropdown](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h00hbn6vdmwe8fpznew8.png)




Next, click on **+** sign in the Connector **Operation** input field to create a new operation. In Boomi, a database operation defines how you interact with the database specified in the connection.


    

![The Database Connector Operation input field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wjy7kymirlgkg16c5w7a.png)



This action opens up the **Database Options** page.


In the **Database Options** page, click on the **+** symbol to create a new Database profile for the operation.


    

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


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.


    

![The Statement details page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q68v9shojj3xf6fef4rr.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 users

Once you are done, your **Database Import Wizard** modal should look like the image below:
 
![The Database Import Wizard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bi2ercbanxxbtnxs6w33.png)



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


![The Choose Tables Modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zlc3gwsn2aodv77kb8ee.png)



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


![The Choose Columns Modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ol8uzyvuy2is194ab294.png)



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


![The Statement Details page with the imported fields](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/emx2etjs42vsuf0y6qk3.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 **id**. This tells Boomi to group records that share the same **id** into a single document.

    


![The Link Element input field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2wwieivul83pnbn8h6z9.png)



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.

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


Finally, click the **Save and Close** button to return to the connector sidebar. 


    

![The Database connector with the loaded database connection and operation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/afm04zxdwy89tcskmon0.png)



In the sidebar, click the **OK** button to return to the canvas.

**3.Transform the data type from database to CSV**

Next, transform the files from the database to a CSV format, which will make it easier for you to parse and retrieve information from the database.


Add a **Map** Shape to the Boomi process to change the data format. Click on the Shape to configure it, which opens up a Map sidebar.


    

![The Map Shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tp6i6xidvb3lor5rw5qs.png)



Click the **+** sign in the Map input field in this sidebar to open the dedicated Map page.


    


![The Map Shape input field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w9kj7u9xo8jqj759k8ta.png)



On the Map page, go to the **Map Source** section and select the **Choose** link.


    


![The Map Shape input field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/253n2el94l7rcb1acegi.png)



This action opens the **Choose a Source Profile**. In this modal, select the **Profile Type** of Database, and in the **Profile** field, select the database profile you created for your **Database Options** page.


    

![The Choose a Source Profile modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7ejjbqbxa0vg9bil7cos.png)



Click the **OK** button to see your data fields populated in the **Elements** menu.


    


![The loaded fields in the Elements menu](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hf7lkya7yto0oqsp4zuz.png)



On the right side of the page, the **Map Destination**, find the **Choose** button and click on it.


![The Map destination Choose link](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ibldpzb5xkv9tw6ogo99.png)

This action will open the **Choose a Destination Profile** modal.


![The Choose a Destination Profile](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gbmx2mpd9klb7ds3xyxr.png)


Select Flat file from the modal's **Profile Type** dropdown menu. Then, click the **+** button in the **Profile** input field to create a new profile for the CSV file.
 

![The Choose a Destination Profile modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3raz45fk0zouno1m43m5.png)


This action opens up a New Flat File Profile page. Right-click the downwards-facing arrow next to **Elements**, and select **Add Multiple Elements** from the menu. 


![The Elements menu](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0x58ruor3f9euqpwxa0u.png)



    

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



This opens up a modal that asks you to choose how many elements you'd like to add. This tutorial decides to create 4 new elements. Click on the **OK** button to close the modal.


    

![The How many modal, with an input field of 4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w7hb8oghpu6teriqfzvi.png)




Next, click on each of the newly created elements and change them to correspond with the fields in your database. This tutorial creates fields for **id**, **first_name**, **last_name**, and **email**.


    

![The data fields in the Elements menu](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sr6mp7e3krmji8k6kal9.png)




Next, navigate to the **Options** tab, check the **Use Column Headers** checkbox, and select the **Comma Delimited** option from the **File Delimiter** dropdown.  This indicates that you want to use the column headers present in the data and separate the data fields by a comma. 


Click on **Save and Close** and the **OK** buttons to save this profile.


    


![The options tab describing how to present the CSV data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t3hx3a8n74y4fbn31pyb.png)



To see all your newly created fields, click the downward-facing arrow next to the **Elements** menu in the **Map Destination** section and select **Expand**.


    


![The Map source and Map destination loaded with data fields](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kxs9w6vadracxb233jth.png)



Next, map the corresponding fields between your database and CSV profiles.


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.


     


![The fields on both the Map source and Map destination mapped to each other](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o9nqpjihm0s1nbpwfnx5.png)



Click the **Save and Close** button to save this Map and return to the Map sidebar. 


    


![The Map sidebar loaded with the Map information](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9ef5f1w0q7sgudblg0sj.png)



Click the **OK** to save the Map configuration and return to the Boomi canvas.

**4.Split the CSV files by line**

Next, you want to split each piece of information in the file by users. To do this,  you need to add the **Data Process** Shape to the Boomi process.




![The Data process Shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gm7qzuboi5qqor2s7e52.png)


This **Data Process** shape allows you to split your document line by line, which in your case means user by user.


Next, click on the **Data Process** shape to open up its dedicated sidebar. In this dedicated sidebar, click on the **+** sign to specify a processing step for your data.


 
![The Data Process Properties](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hvd4mbhs5mdbzxmwj5h9.png)   



This action opens up a form; in this form, select:

*  **Split Documents** from the **Processing Step** dropdown
*  A **Profile Type** of Flat File to match the CSV file format 
* **Split Options** of **Split By Line** 
* A **Headers Option** of **Retain first line as Column Headers**

    

![The Data Process Properties form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7xx4ffkyzvwzqly10mjh.png)




Click the **OK** button to save this **Data Process** Shape configuration.


     

**5.Set Properties**

Next, you will use the **Set Properties** shape to create and set two properties dynamically:

* The user's first name property is set as the first_name in the data
* The receiver's email address property is set as the email address stated in the data file.

You will need these dynamic properties to create and send out these emails. To create the first name property, click the **+** button in the **Properties to Set** section. 




![The Set Properties Shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jdw22e1p38pzn0u9g5gx.png)


This action opens up a **Choose Property** modal. In this modal, select the **Property Type** of Dynamic Document Property and select the **Property Name** as first_name.



![The Choose Property modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6v6jdlmsih89o1cwzgx4.png)


Click on the **OK** button to save this property.  

You should see the Dynamic Document Property parameter. Select this parameter, and click the **+** sign in the **Property Value** section to create the parameter’s value. 



![The Property Value](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yt0m31mg4kk1z7657adt.png)



This action opens up the **Parameter Value** modal. Fill out the **Parameter Value** modal with the following information:



* **Type** -> Profile Element
* **Profile Type** -> Flat File
* **Profile** ->  The CSV profile you created during the Map process.
* **Element** -> Expand the menu and select the first_name field

Your **Parameter Value** modal should look like this when you are done. Click the **OK** button to save the parameter value information.



![The Property Value modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hqh0k61w0ckarngksir6.png)



Next, create another parameter for the receiver’s email address. To create this parameter, click the **+** sign in the **Properties to Set** section. This action opens up the **Choose Property** modal. Fill out this modal with the following information:



* **Property Type** -> Document Property
* **Source Type** -> Connectors
* **Connector** -> Mail
* **Property** -> To Address 

This creates a dynamic property that holds the receiver's email address.




![The Choose Property modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nilpqhgjt7namatgj377.png)


Click the **OK** button to save this property and return to the sidebar. In this sidebar, you should see a **Mail - To Address** property. 

Select this parameter, and click the **+** sign in the **Property Value** section. This action opens up the **Parameter Value** modal. Fill out the **Parameter Value** modal with the following information:



* **Type**  -> Profile Element
* **Profile Type** -> Flat File
* **Profile** -> Mail Flat File Profile
* **Element** -> Expand the Elements menu and select the email field

When you are done, your **Parameter Value** modal should look like this. Click the **OK** button to save this parameter value and return to the sidebar.



![The Parameter Value modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e3q0l9l38zwsjr10k3de.png)


Click the **OK** button in this sidebar to save your **Set Properties** configuration shape and return to the Boomi canvas.



![The Set Properties Shape with all your loaded properties](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0t8uhxlbmbg3uh7s9yes.png)





**6.Create the Message**

Next, you need to create the message in the email you want to send out. Add the **Message** shape to your Boomi process to create this custom message.



![The Message Shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c8iolqxpsyc75q0odh4k.png)



Click this process shape to open its dedicated sidebar.


![The Message shape sidebar](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wcr0pimwtwcc0u7xxjzh.png)


In this sidebar, write the message you want to send out in the **Message** input field. This tutorial writes out this message.

```txt
    Hey there {1}!


    I hope everything is going great for you.
```

The `{1}` is a variable you will set to the **first_name** property you created in the **Set Properties** Shape. 


Click the **+** button to set this variable value in the **Variables** section. This action opens a **Parameter Value** modal. 


![The Message shape variables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0fgk6l6g7fqqhnjr66la.png)



In this modal, fill in a **Type** of Document Property. Next, click the search icon next to the **Document Property** input field to open up the **Choose Property** modal.



![The Parameter Value modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2doa9u8s42s6skept5tt.png)    
     


In this modal, fill out the following credentials:

* **Property Type** -> Dynamic Document Property
* **Property Name** -> first_name

Click the **OK** button to save and close this modal. 



![The choose property modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g0ip6iws4gtzjjagcell.png)



You should see your **Document Property** loaded in the **Parameter Value** modal. 




![The Parameter Value modal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hdbntc4ua012fm67x56x.png)


Click the **OK** button to save your parameter value and return to the sidebar. Your sidebar should look like this.




![The message Shape loaded with the message information ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ino8plivrgycwgyg6ypx.png)

After creating your message and setting your dynamic variable, you instruct Boomi to replace the` {1}` with the user’s **first_name** from the file when it sends the email. Finally, click the **OK** button to save your message configuration and return to the Boomi canvas.



**7.Send the email**

Now, the best part is actually sending the email. The **Mail Connector** Shape allows you to add this shape to your Boomi process.


 
![The mail connector](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cpgidb83mi2j2q72y7b1.png)   




Click this **Mail** connector to configure this shape. This opens up the **Mail** connector sidebar. 


    


![The connector shape database ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cciti50996qqza6b2xl0.png)


In this sidebar, go to the **Connection** input field and select the **Mail** Connection you created in the prerequisites section. If you have not created the Mail Connection yet, check out [Creating the Mail connection and Operation](https://help.boomi.com/docs/Atomsphere/Integration/Getting%20started/int-Creating_the_Mail_connection_and_operation_96283ae7-a4e4-4589-9e0a-8117286c3e89).


Next, select an **Action** of Send, as you will be sending emails.


In the **Operation** input field, you can either select a previously created Mail operation or create a new one. This tutorial shows you how to create a new operation.


Click the **+** sign in the Operation input field to create a new operation. This action opens up a new Mail operation page.


    

![The Mail connector Operation input field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/13ls3usox4vymcvmiwf0.png)



Fill out the input fields on this page with the following information:

* **From**: This is the email you used when creating your connection. It is the email address that shows up as the sender in your Mail app
* **To**: Leave **To** as blank as you want to dynamically set that property depending on the email address in the data file from your database
* **Disposition**: Select **Inline** because the document data is sent in the body of the email message instead of as an attachment.
* **Content-Type**: text/plain, as you will be sending texts



![The Operation page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2etso9eckpl4pgb0fd0f.png)


When you are done, your sidebar should look like this: click the **OK** button to save this sidebar and return to the Boomi canvas.



![The Mail connector sidebar with its connection and operation values](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nil2t0bu9q9iy8dipnlj.png)




**8.Test the process**

To verify if the process is working, click the **Arrange** button, then click the **Test** button to test the process.




![The Arrange tab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6dbn6xyxecbonzvqs8lr.png)

This action asks you to select the Atom you want to run this Boomi process on; select the Atom you created in the prerequisites section. After running the process, you should see the custom email in the provided email address.


## That’s a Wrap!

This guide has shown you how to dynamically send custom emails to different email addresses in your MySQL database using Boomi. You can also build on this tutorial to create personalized messages based on user information and trigger automated emails based on specific actions.

However, as interesting as this exercise is, this is only just a starting point for exploring the full capabilities of Boomi.  Refer to the [official documentation](https://help.boomi.com/) for more information on what Boomi can do.