Recently, due to choices outside of my control, I was working on a project that uses Salesforce as a database. And CRM. And many other things. You can imagine that when a tool is not intended to be used in a certain manner the number of hacks you need to implement keeps growing.

While the rant is still here, the point of this write-up was something that came along with exploring this integration. And as they say, there is always something interesting to learn and experiment with. Not sure who would say that, I just need something to keep me going.

And here we get to the point of me playing around to implement an ORM for the SOQL. Yes, you read it correctly. Salesforce Object Query Language. It is also a fun experiment to see how you would implement an ORM to simplify some of these things. Or for fun of it, let me call it SORM. They’re not the only ones that can be “clever”.

The syntax of SOQL will not be strange to anyone who has tried SQL. Thou, these rare unicorns are becoming almost impossible to find in the sea of ORMs. Yeah, I know and can understand the hypocrisy of these words while writing one.

SELECT Id, FirstName, LastName
FROM Account
WHERE Id = '12345'

As you can see, nothing strange here, except this will be sent as a REST API query. There are other ways, I am gonna scope this write-up to REST. Now, what you get back will be something along these lines:

{  
    "totalSize": 1,  
    "done": true,  
    "records": [  
        {  
            "attributes": {  
                "type": "Account",  
                "url": "/services/data/v60.0/sobjects/Account/12345"  
            },  
            "Id": "12345",  
            "FirstName": "First",  
            "LastName": "Last",
        }  
    ]
}

Naturally, you will have something along these lines on your end to deserialize this response:

internal record Account  
{  
    [JsonPropertyName("Id")]  
    public string Id { get; init; } = string.Empty;  
    
    [JsonPropertyName("FirstName")]  
    public string FirstName { get; init; } = string.Empty;  

    [JsonPropertyName("LastName")]  
    public string LastName { get; init; } = string.Empty;  
}

public record Response<T>
{
    [JsonPropertyName("totalSize")]
    public int TotalSize { get; init; }
    
    [JsonPropertyName("done")]
    public bool Done { get; init; }
    
    [JsonPropertyName("records")]
    public List<T> Records { get; init; }
}

Seems pretty much straightforward. And then after writing a couple of these queries and models and mappings, the brain starts to go: “Hmmm, these things look similar would there be a way to simplify it a bit?”.  And I will go and jump into that rabbit hole. Not for the sake of writing it. Just to see if can it be done. I honestly don’t see anything wrong with having queries written as they’re intended to be. While I do like to have fun as well.

An hour or two later, there is a first draft version of it. That would do a simple query from above. My first implementation relies on using just standard attributes from the System.Text.Json namespace. And that worked nice, but then you get into a bit more complex queries with nested types and also how SOQL handles certain relationships. And so on and so on. So I decided, like any good ORM developer to add some stuff into the mix and introduce a couple of custom attributes of my own. Why not?

[AttributeUsage(AttributeTargets.Class | AttributeTargets.Property)]
public class TableAttribute : Attribute
{
    public TableAttribute(string name)
    {
        Name = name;
        InlineSelect = false;
    }

    public TableAttribute(string name, bool inlineSelect)
    {
        Name = name;
        InlineSelect = inlineSelect;
    }

    public string? Name { get; }
    
    public bool InlineSelect { get; }
}

[AttributeUsage(AttributeTargets.Property)]
public class ColumnAttribute : Attribute
{
    public ColumnAttribute(string name)
    {
        Name = name;
    }
    
    public string? Name { get; }
}

The naming probably should already dictate what they will be doing. So let me update the models from my previous example with these new things:

[Table("Account")]
internal record Account 
{
    [JsonPropertyName("Id"), Column("Id")]
    public string Id { get; init; } = string.Empty;
    
    [JsonPropertyName("FirstName"), Column("FirstName")]
    public string FirstName { get; init; } = string.Empty;

    [JsonPropertyName("LastName"), Column("LastName")]
    public string LastName { get; init; } = string.Empty;

    [JsonPropertyName("Owner"), Table("Owner")]
    public Owner Owner { get; init; }
}

[Table("Owner")]
internal record Owner
{
    [JsonPropertyName("Id"), Column("Id")]
    public string Id { get; init; } = string.Empty;

    [JsonPropertyName("Name"), Column("Name")]
    public string Name { get; init; } = string.Empty;
}

Ok, so now there is just one single place where I am gonna handle what I would be needing for this simple example. I have thrown in for good measure also nested types. Yes, it could have been simplified, reducing the need to repeat the values. That is valid feedback, but I wrote this in a couple of hours as a challenge to myself. Not to, for now, make it an open-source project.

And when you see custom attributes, naturally you start thinking: Where is reflection? No worries, I am gonna spare you from it. For now… The first part, is that the example above will allow you to easily with few lines of code construct the basis of your SOQL query.

var soql = 
    new QueryBuilder<Account>()
        .Where(account => account.Id == "12345");

The previous example will generate the following query:

SELECT Id, FirstName, LastName, Owner.Id, Owner.Name 
FROM Account 
WHERE Id = '12345'

Not bad for an hour or two of work. And the response will also be nicely deserialized using the same object. So mapping and translation are now encapsulated in a single place. Ok, I am not mad about it. There are a lot of points for improvement, naturally, but it gives me ideas on how to expand from here. And the amount of code to achieve this is ~100 lines of code. Yes, it handles only basic queries. But extending it to support other types is now just reading SOQL specification and extending the parsing and projection of the expression tree.

The response will also be handled by DTOs above. without any problems:

{
    "totalSize": 1,
    "done": true,
    "records": [
        {
            "attributes": {
                "type": "Account",
                "url": "/services/data/v60.0/sobjects/Account/12345"
            },
            "Id": "12345",
            "FirstName": "First",
            "LastName": "Last",
            "Owner": {
                "attributes": {
                    "type": "User",
                    "url": "/services/data/v60.0/sobjects/User/12345"
                },
                "Id": "12345",
                "Name": "Name"
            }
        }
    ]
}

Now, this response should answer why I have introduced custom attributes. I can now have in the model the properties defined only for a response without them interfering with the process of using the same type to construct the SOQL query. And still, be encapsulated within the same type:

[Table("Owner")]
internal record Owner
{
    [JsonPropertyName("attributes")]
    public Attributes Attributes { get; init; }

    [JsonPropertyName("Id"), Column("Id")]
    public string Id { get; init; } = string.Empty;

    [JsonPropertyName("Name"), Column("Name")]
    public string Name { get; init; } = string.Empty;
}

and SOQL query will still work as expected:

SELECT Id, FirstName, LastName, Owner.Id, Owner.Name 
FROM Account 
WHERE Id = '12345'

The SOQL specification has several SQL-like supported operations to query data. For example, overload constructor in the Table with inlineSelect parameter (again, hours of work, naming not best) is to allow you to select children objects in another table. For example, if I do it on the object above for the owner it would result in the following query:

SELECT Id, FirstName, LastName, (SELECT FirstName, LastName FROM Owner) 
FROM Account 
WHERE Id = '12345'

Now this comes with a whole other set of headaches that I can’t even begin to fathom. That is the fun part for me. How to solve these challenges. I have several ideas which I may decide to expand on down the road.  As an example, adding IN for WHERE with a custom expression was not much work, just adding some new methods and overloads. And a couple of new properties. And reusing the existing code:

SELECT Id, FirstName, LastName 
FROM Account 
WHERE Id IN (SELECT Id FROM Owner WHERE FirstName = 'John')

Now, not the best code I wrote and it does not cover the entire SOQL specification, but it does produce a valid query. So I am proud of what I have done in a couple of hours.

Now, even as I did this I doubt this is a viable option for the current project. Way too much time invested for a couple of queries. Not worth the project’s money. I think it would be a nice side project and make it open-source. I doubt that I am the only one hitting this wall and would be interesting to see what others think and how they would implement this. As stated, I also don’t have problems writing plain old queries as they were intended. Even if they’re SOQL ones as well.

This entire write-up also should remind you, if you had the opportunity to work with them, on things like GraphQL or HATEOAS (Hypermedia as the Engine of Application State). And there are some other projects out there that also expose SQL over the REST API. So it is not a novelty of sorts, while still being an interesting choice to go for. And yes, sarcasm. With so many different ways to solve querying of the data, I am not sure if building my own would be a way to go. I guess you need those buzzwords to sell the product to management somehow.

This was me just challenging myself to see if I, after being in Agile for so long, can still write some code. And not just attend meetings. Still need to finish that rant piece. Oh well, one step at a time. And to share that even in “not so fun” projects you can still find some fun and expand yourself as a developer.

Disclaimer; I didn’t share code here that builds these queries as it is nothing special and uses reflection and expressions from standard SDK. If this becomes a project, will update the write-up towards it.

Until that open-source project, have a good one!