There’s a brand new Mule 4 Anypoint Connector for Database (DB) that you can use to connect to any relational Database engine. Unlike other connectors such as File or FTP, this connector has a pretty similar UX compared to the one in Mule 3.x, with some considerable improvements, including:
– Improved operations: Operations are streamlined and now simpler to use. We’ve extracted bulk functionality to their own set of operations so that you no longer have operations that change behaviors depending on the received payload
– Dynamic queries simplified: Now there’s a single experience for executing static and dynamic queries.
– Embeddable transformations: you can now embed DataWeave transformations inside the insert/update operations so that you can construct the datasets you want to send to the DB without having a side effect on the message or using enrichers
– Streaming simplified: you no longer have to worry about configuring streaming on your operations. The connector will use Mule’s new streaming framework to handle that automatically. You can now even execute a select statement and process the results asynchronously without worrying about leaking connections!
Let’s take a quick tour and see what’s new.
Select
The select operation is used to retrieve information from the RDBMS. The primary concept of this operation is that you will supply a SQL query and use DataWeave to supply the parameters:
As you can see in the above example, input parameters are supplied as key-value pairs, which we can now create by embedding a DataWeave script. Those keys are used in conjunction with the semicolon character (:) to reference to a parameter value by name. This is the recommended approach for using parameters in your query. The advantage of doing it this way are:
- The query becomes immune to SQL injection attacks
- The connector can perform optimizations that are not possible otherwise, which improves the application’s overall performance
Dynamic Queries
Sometimes, you not only need to parameterize the WHERE clause, but also parameterize parts of the query itself. Example use cases for this would be queries which need to hit online/historic tables depending on a condition, or complex queries where the project columns need to vary.
In Mule 3, the concept of select was split in parameterized and dynamic queries, and you couldn’t use both at the same time. You had to choose between having a dynamic query or having the advantages of using parameters (SQL Injection protection, PreparedStatement optimization, etc.). Furthermore, the syntax to do one or the other was different, so you had to learn two different ways of doing the same thing.
But with the new Database Connector in Mule 4, you can now use both methods at the same time by using expressions in the query. In this example, you can see how a full expression is used to produce the query by building a string in which the table depends on a variable. An important thing to notice is that although the query text is dynamic, it is still using input parameters:
Why do I need dynamic queries at all for the example above? Can I just treat the table like another Input Parameter? Then answer is no. Input Parameters can only be applied to parameters in a WHERE clause. To modify any other part of the query, you need to use DW’s interpolation operator.
Streaming large results
Database tables tend to be big. One single query might return tens of thousands of records, especially when dealing with integration use cases. Streaming is a great solution for this. What does streaming mean? Suppose you have a query which returns 10K rows, attempting to fetch all those rows at once will result in the following:
- Performance degradation, since that’s a big pull from the network
- A risk of running out of memory, since all that information needs to be loaded into RAM.
Streaming means that the connector will not fetch the 10K rows at once; instead, it will fetch a smaller chunk, and once that chunk has been consumed it will go fetch the rest. That way, you can reduce pressure over the network and memory.
Streaming in Mule 4 vs Streaming in Mule 3
In Mule 3.x this was something you had to specifically enable because it was disabled by default. In Mule 4, this is transparent and always enabled, you don’t have to worry about it anymore, you can simply trust that the feature is there.
Another improvement from Mule 3 is that we can now use the new repeatable streams mechanism from Mule 4. That means that streams are now repeatable and you can have DataWeave and other components process the same stream many times, even in parallel.
Insert, Update and Delete
The insert, update, and delete operations were also upgraded in the same way. You can use DataWeave parameters and get dynamic queries as well:
Bulk Operations
The inset, update, and delete operations we saw above are fine for the cases in which each input parameter can take only one value.
For example, when deleting, many rows could match the criteria and get deleted, but only one criteria (POSITION = X) is provided. The same concept applies for update, if you do UPDATE PRODUCTS set PRICE = PRICE * 0.9 where PRICE > :price, you may be applying a 10% discount on many products, but the price input parameter will only take one value.
What happens if we want to apply different discount rates on products that have different prices? Well, we could do it by executing many operations. For example, assume you have a payload which is a list of object of the following structure { price : number, discountRate: number}, then, we could do this:
That method would certainly get the job done; however, it is highly inefficient. One query needs to be executed per each element in the list. That means that for each element we will have to:
- Parse the query
- Resolve parameters
- Grab a connection to the DB (either by getting one for the pool or establishing a new one)
- Pay all the network overhead
- The RBMS has to process the query and apply changes
- Release the connection
You can avoid all of the above steps by doing a bulk operation. When you look at it, there’s only one query here; the update statement is constant, not dynamic. The only thing that changes is that, on each iteration, we supply a different set of parameters.
Bulk operations allow you to do exactly that: to run one single query using a set of parameters values. Make no mistake though, this is not just a shortcut for the same <foreach> above, this uses features on the JDBC API so that:
- Query is parsed only once
- Only one DB connection is required since a single statement is executed
- Network overhead is minimized
- RBDMS can execute the bulk operation atomically
For these use cases, the connector offers three operations, <bulk-insert>, <bulk-update> and <bulk-delete>.
These are pretty similar to their single counterparts, except that instead of receiving input parameters as key-value pairs, they expect them as a list of key-value pairs.
Let’s look at an example:
Try it Now!
These are the most important improvements in the Database connector. Just like in Mule 3, you also have support for executing scripts and stored procedures, syntactic sugar for connecting to the most common Database systems, and DataSense support on all operations––including complex select projections and more! For more information, please visit our documentation page.
The Mule 4 Release Candidate is already out for you to try! Get started today, by simply downloading Mule 4 RC.
Also, try out the new flow designer. With it, you can design and publish your integration directly on the cloud and, yes, the Database connector is already available there too!
