Database Connector in Mule

Overview

The Database Connector in MuleSoft acts as a powerful bridge between Mule applications and relational databases, enabling developers to perform operations such as querying, inserting, updating, and deleting data with minimal effort. By leveraging the connector, teams can integrate databases like MySQL, Oracle, SQL Server, and PostgreSQL seamlessly into their integration flows without writing extensive boilerplate code.

This article walks through the essentials of configuring and using the Database Connector—from establishing connections and executing SQL statements to handling parameterized queries and retrieving results. It also covers best practices for managing database credentials securely, optimizing query performance, and ensuring smooth migrations between environments.

Prerequisites

• MySQL database should be installed. For this project, we will be using MySQL database; if you have installed any other database, you can utilize the same for this demo.

Database connector configuration

Create a Mule project in your local environment with the name ‘database-demo’ and we also need to add database module so that we can configure it in global elements, please go through the below video to configure the MySql database connection in Mule.

Before we start executing database queries, run the following SQL statements to create the database, set up the table, and insert sample rows for our implementation.

create database mule_dev;
use mule_dev;


-- Create employee table
CREATE TABLE employee (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    designation VARCHAR(50),
    salary DECIMAL(10,2),
    join_date DATE
);

-- Insert sample data
INSERT INTO employee (first_name, last_name, department, designation, salary, join_date)
VALUES
('John', 'Doe', 'IT', 'Software Engineer', 65000.00, '2021-05-12'),
('Jane', 'Smith', 'HR', 'HR Manager', 72000.00, '2020-03-18'),
('Michael', 'Brown', 'Finance', 'Accountant', 58000.00, '2022-07-25'),
('Emily', 'Johnson', 'IT', 'QA Analyst', 50000.00, '2021-11-10'),
('David', 'Williams', 'Marketing', 'Marketing Executive', 55000.00, '2019-09-02');

Select database connector in Mule

In this implementation, we have designed a MuleSoft application that retrieves employee data from a database based on the presence of a query parameter. The main flow, database-demoFlow, starts with an HTTP Listener configured to listen on the /employees endpoint.

A Choice Router is used to determine the flow of execution:

If the query parameter emp_id is provided in the request, the request is routed to the select-based-on-id flow. This flow executes a parameterized SQL query to fetch details of a specific employee whose emp_id matches the provided value.

If no emp_id is passed, the request is routed to the select-all-data flow. This flow executes a SQL query to retrieve all employee records from the database.

Both flows use a Database Connector for executing SQL queries and a Transform Message component to return the results in JSON format. This ensures clean, structured responses for easy consumption by clients or downstream systems.

By structuring the application this way, the same endpoint /employees can handle both targeted lookups and full dataset retrieval efficiently, depending on the input provided.

Follow the video below for a step-by-step practical implementation of this setup.

Insert into database in Mule

This MuleSoft flow allows you to insert employee details into a database via an HTTP POST request:

• HTTP Listener – Listens for incoming POST requests at /employee with employee details in JSON format.
• Database Insert – Uses db:insert to add the employee data into the employee table. The SQL query maps values directly from the payload.
• Response Transformation – After insertion, a Transform Message component checks if the insert was successful (affectedRows > 0) and returns a JSON message indicating success or failure.

For detailed step-by-step implementation, refer to the video below.

Sample request payload :

{
  "first_name": "John",
  "last_name": "Doe",
  "department": "IT",
  "designation": "Software Engineer",
  "salary": 65000,
  "join_date": "2021-05-12"
}

Bulk Insert into database in Mule

• HTTP Listener
  • The flow starts with an HTTP Listener that receives a POST request containing a JSON payload of multiple employees.
  • Sample payload.
    

    {
      "employees": [
        {
          "first_name": "John",
          "last_name": "Doe",
          "department": "IT",
          "designation": "Developer",
          "salary": 50000,
          "join_date": "2025-08-15"
        },
        {
          "first_name": "Jane",
          "last_name": "Smith",
          "department": "HR",
          "designation": "Manager",
          "salary": 60000,
          "join_date": "2025-08-12"
        }
      ]
    }
    

     

• Transform Message – Prepare Payload
  • Extracts the array of employees from the incoming JSON.
  • Converts it into a format suitable for database insertion.
• Bulk Insert into Database
  • Efficiently inserts multiple employee records at once.
  • Mule automatically maps each object’s keys (e.g., first_name, last_name) to the corresponding database columns.
• Transform Message – Prepare Response
  • Generates a JSON response showing a success message and total records inserted.

Please refer to the below video for the implementation.

Invoke StoredProcedure in Mule

Before we start the implementation in MuleSoft, we need to create a stored procedure in the database. This procedure will fetch employees from the employee table based on a specific department and a minimum salary.

You can execute the following SQL to create the stored procedure in your database:

DELIMITER //

CREATE PROCEDURE GetEmployeesByDeptAndSalary(
    IN dept_name VARCHAR(50),
    IN min_salary DECIMAL(10,2)
)
BEGIN
    SELECT emp_id, first_name, last_name, department, designation, salary, join_date
    FROM employee
    WHERE department = dept_name
      AND salary >= min_salary
    ORDER BY salary DESC;
END //

DELIMITER ;

Now let’s proceed for the implementation :

• HTTP Listener
  • The flow starts with an HTTP listener that listens for incoming GET requests at /sp. Users can pass query parameters like department and minSalary in the request URL.
• Choice Router
  • The flow uses a choice router to check whether the required query parameters are provided. If both department and minSalary are present, the flow proceeds to call the stored procedure. Otherwise, it returns a JSON message prompting the user to provide the parameters.
• Stored Procedure Call
  • The database stored procedure GetEmployeesByDeptAndSalary is invoked. The parameters department and minSalary are mapped from the query parameters. Mule automatically maps these keys to the stored procedure input parameters.
• Transforming the Result
  • The response from the database stored procedure is usually wrapped inside resultSet1. A DataWeave transform is used to create a clean JSON response with just the results.

Refer to the video below for a step-by-step implementation.

 Now we have covered the majority of the database connectors. There are a few more that follow the same type of implementation, the only difference is how you map the input and output. If you have followed this article step by step, you should be able to implement the other database connectors as well. However, if you need details on any specific connector, please feel free to post it in the comment section, and we will update the article with the required information.