It’s a common use case while writing DB backed applications that we need to build SQL queries dynamically based on different filtering criteria.
At Tokopedia we were developing search functionality for searching our database based on certain criteria. Criteria for search was based on the optional filters which a user can apply while searching.
Let’s take a particular scenario where we can search all loan applications based on the phone number, email, application status, range of dates on which applications were applied. In this scenario we need to build the query dynamically since all of these filters are optional. To do this basic code will look like below in go.
We can easily see there are lot of If blocks in the above code. Also most of the code is redundant, most of the time we are just copy pasting and changing variables which need to be checked in the query. To make it simple, remove redundant code and make it more readable we created a library DQB.
DQB (Dynamic Query Builder) is an effort to remove redundant if blocks to a much simple and clean code. Code for writing same conditions in DQB library for above scenario will look like below.
We can see there are no redundant code blocks and total lines of code (LOC) without using DQB for above scenario is 25 and after using DQB is 11. Also code is clean and more readable.
Above example was simple, a more complex scenario can be when you have nested AND’s and OR’s or may be some other expressions in your where clause. Let’s see a more complex example below.
In above example we can see IN expression is used directly. AND / OR functions accept strings, so we can add any custom strings.
It’s important to note that any empty field will be automatically removed from the where clause, so if email is empty in search DQB will remove it from search criteria automatically.
Please let me know if you have any questions. You can find the DQB library here!
Thanks for reading!!