Salesforce bizarre practices: SOQL Query Builder
Dear reader, I believe you wrote a couple of lines of apex code already, otherwise, why would you read it? Maybe you were forced by your team lead to use some kind of SOQL query builder framework or you even created one and are proud of it. After a brief search on GitHub I found around 20 query builders https://github.com/search?l=Apex&p=2&q=query&type=Repositories
Have you ever asked yourself or your teammates WHY? What is the pros of using dynamic salesforce queries? I did ask myself and did not find any advantage apart from ‘it looks cool and ‘professional’. In other hand I have found a few big disadvantages listed below:
1. Query builder is harder to use
Let’s start with the most obvious. If somebody asks you what is the simplest language you’ve ever seen I bet everyone will answer SQL. Just compare which piece of code is easier for your understanding, easier for a newbie, easier for code review?
List<Account> = [
SELECT Id, Name, CreatedDate
FROM Account
WHERE Name = 'Alpha'
ORDER BY Name DESC
];and
List<Account> = new SQuery('Account')
.addFields('Id,Name,CreatedDate')
.addCondition(new Field('Name').equals('Alpha'))
.orderBy('Name',SortDirection.DESCENT)
.runQuery();Do you really want to invest your valuable time of life to learn query builder library?
2. Error-prone / vulnerable for typos
This one is not so obvious but from my point of view, the most powerful argument to never use query builders instead of out-of-the-box inline SOQLs. The main problem with the majority of query builders for APEX is they are generating a string query and take field names, object names, conditions as parameters of type string.
What is wrong with string queries? You can make a typo in a field name, in condition, anywhere — guess what the compiler say when you save this code? Nothing.
[SELECT Id, Naaaame FROM Account]; //impossible to saveDatabase.query('SELECT Id, Naaaame FROM Account'); //ok
In the worst case, the line of code where you run this dynamic-query is not covered by a test-methods and you are in a risk to deploy an invalid query to production.
3. Consistency issues
Similar to the previous one. String queries are not checked by salesforce. In case you remove a field or change API name of a field and this field is mentioned only in string query you will not be stopped. Unlike the inline SOQL which will be considered by force.com platform when validating field change/remove.
4. Vulnerable to injections
Again the root cause of a problem is a String query that is not validated by the platform. Inline SOQL is recommended (on salesforce security review) when you deal with client input.
5. Maintenance and debugging
Most likely new joiners of your team will not be familiar with your hand-made query builder or the open-source library you are using, but they have to know the basics of SQL. Sometimes query builder library can return a misleading error or have too many weird things in an exception stack trace.
Bonus: fflib (SoC) creators opinion
If you do not know what is Separation Of Concerns and the framework for implementation of this enterprise pattern I would recommend you to check it out. But trust me it is good for large salesforce implementations
Surprise surprise this framework has built-in query builder (I do not know why they include it there). But even creators of the QueryFacroty leave a comment to warn developers to use this QueryFactory only in exceptional cases (mean almost never):
