How to connect your Boomi Atom runtime and MySQL on Docker

The push for modularity and composable architectures has revolutionized the software development landscape, enabling a level of flexibility and agility previously unimaginable. Still, it does come with a price: increased and almost unmanageable complexity.

But with Boomi, that's a thing of the past. This leading integration platform acts as the central nervous system, seamlessly connecting applications, databases, and APIs regardless of origin or location. Its pre-built connectors make integration effortless, empowering you to send and receive data from your database, and incredible third-party solutions like Salesforce, Eyer, etc.

This article equips you with the steps to create a MySQL database and configure a Boomi Database Connector. This connector empowers your Boomi processes to read and write data directly within your MySQL database.

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
• A Boomi Atom is installed locally. To understand how to install a Boomi Atom on Docker, check out this Installing Boomi Atom runtime on Docker tutorial

Connecting a MySQL database to the Boomi Atom

MySQL is an open-source relational database management system (RDBMS) that stores, organizes, and accesses data in a structured format.

The prerequisites section discussed installing a Boomi Atom in Docker, and this section will build on that knowledge. It will guide you through creating and connecting a MySQL database to your Boomi Atom. To achieve this, update your docker-compose.yml file with additional information and replace placeholders with your specific values.

services:
  atom:
    image: boomi/atom:5.0.2-rhel
    container_name: mysql-connector
    volumes:
      - <your host directory>:/mnt/boomi:Z
    environment:
      - BOOMI_ATOMNAME=<your atom name>
      - INSTALL_TOKEN=<your atom installer token>
      - BOOMI_ENVIRONMENTID=<your environment ID>
      - ATOM_LOCALHOSTID=<your atom name>
      - MYSQL_HOST=mysql  
      - MYSQL_PORT=3306  
      - MYSQL_USER=<username>
      - MYSQL_PASSWORD=<password>  
      - MYSQL_DATABASE=<name of your MySQL database>
    ports: 
      - "9090:9090"
  mysql:
    image: mysql:latest
    container_name: mysql
    environment: 
      - MYSQL_ROOT_PASSWORD=<root user password>
      - MYSQL_USER=<username>
      - MYSQL_PASSWORD=<password>
      - MYSQL_DATABASE=<name of your MySQL database>
    ports: 
      - "3306:3306"

This code block above defines two services:
I. The Boomi Atom service which is configured by the following fields:

• image: boomi/atom:5.0.2-rhel: This specifies the Boomi Docker image used for the service.
  
• container_name: This allows you to personalize the container name, making it easier to identify and manage
• <your host directory>:/mnt/boomi:Z: This mounts a host directory onto the container's /mnt/boomi directory with the "Z" compression option. Make sure the host directory exists and has the necessary permissions.
  
• BOOMI_ATOMNAME, INSTALL_TOKEN, BOOMI_ENVIRONMENTID, ATOM_LOCALHOSTID variables: These are specific to your Boomi Atom Setup and are discussed intensively in this Installing Boomi Atom runtime on Docker article. 
• MYSQL_HOST: This is your MySQL server's hostname or container name
  
• MYSQL_PORT: This is the port of your MySQL server. By default, MySQL uses port 3306
  
• MYSQL_USER, MYSQL_PASSWORD: Sets your MySQL username and password.
  
• MYSQL_DATABASE: This holds the name of your database
  
• "9090:9090": This maps the container's port 9090 to the host's port 9090.
  

II. The MySQL database service, which is configured by the following fields

•  image: mysql:latest: This uses the latest official MySQL Docker image
• MYSQL_ROOT_PASSWORD: This sets the password of the root user
  
• MYSQL_USER, MYSQL_PASSWORD: Sets your MySQL username and password. These values must match the username and password configured in the Boomi atom service
  
• MYSQL_DATABASE: Enter a name for your database. These values must match the MYSQL_DATABASE configured in the Boomi atom service
  
• "3306:3306": This maps the container's port 3306 (default MySQL port) to the host's port 3306.
  

Adding the MySQL JDBC driver to the Boomi Atom

Boomi integration processes use a Java database connectivity(JDBC) API driver to allow you to connect to and execute queries within the database. While Boomi offers pre-loaded JDBC drivers for some databases, licensing restrictions prevent the inclusion of the MySQL driver. Therefore, downloading and installing the MySQL JDBC driver is necessary for integration.

To download the JDBC driver for the MySQL database, do the following:

• Visit the MySQL download page
• Choose the Connector/J version compatible with your MySQL server and Java version. This article uses the Platform Independent version.
• Select the desired download format (e.g., ZIP, TAR.GZ).
• Click the download button to begin the process.

After the download finishes, extract the contents of the file. You'll need the .jar file located within the downloaded folder to proceed.

Uploading the JAR file in the Account Libraries

Next, upload the .jar file to the account library by navigating to Settings > Account Information and Setup > Account Libraries

On the Manage Account Libraries page, click the Upload a File button to upload your newly downloaded .jar file

Creating a custom library

Once you've uploaded the .jar file, navigate to Services > Integration and create a Custom Library using that file. These Custom Library components are collections of Java Archive (JAR) files that you can use to support those integration processes.

Next, click on the Create New button and search for Custom Library.

On the Custom Library page, select Connector in the Custom Library Type field and choose Database in the Connector field. Then, search and click your recently uploaded JAR file under Included JAR Files. Finally, click the Save button. Your Custom Library page should look like this when you are done.

To verify if you successfully created a custom library component, locate and expand the Custom Libraries menu on the left sidebar; you should see your custom library component there.

Creating a packaged component

You must convert your processes and components into packaged components before they can be deployed or shared. To package your custom library component, navigate to the page's right and click Create Packaged Components.

Next, proceed to the Add Details section by clicking the Next: Add Details button. Once there, finalize the process by clicking Create Packaged Components (1).

If you have successfully created your packaged component, a Packaged Components Successfully Created modal will appear. Click the Deploy button to deploy your newly created component.

Next, select the target Boomi environment for your packaged component deployment. Click on the Next: Select Versions button. Review your selection and click Next: Review.

Finally, deploy your packaged components.

To verify if you have installed the JAR file correctly, navigate to the Manage tab and click on Atom Management.

Next, select the atom where you deployed your packaged component, and then navigate to the Installed Libraries tab on the sidebar to the left.

Searching through the list of installed libraries, you should find the JAR file you just installed.

Creating a database connector

To interact with your database using Boomi, you need a database connector. This connector enables Boomi to create, and insert data. To configure this, create a database connector by navigating to the Build tab.

Next, click the Create New button and select Connection to open the Create a Connection modal.

Within the modal, choose Database from the Connector dropdown menu. This initiates the database connection setup process.

Fill the Database Connection page with the credentials required to connect to the SQL database.

Click on the Save button. You should see your database connector when you expand the Connectors > Database > Connections menu.

To verify the database connection, click on the Test Connection button on the right. This action will open up a Test Connection modal.

Select the Boomi Atom with which you want to use this connection and click the Next button.

If your connection is successful, this confirmation modal will appear.

That’s a wrap

Following this comprehensive guide, you have successfully connected your Boomi runtime with your MySQL database. You can now leverage Boomi’s integration capabilities to streamline data exchange within your applications.

But this is only the beginning; with this new connection, you can automate data flows between your MySQL database and other applications, develop robust integration with third-party solutions like Eyer, and gain deeper insights from your data.

Check out the official documentation to learn more about what Boomi can offer.