Golang tag library to generate Salesforce queries

Atul Kshirsagar — Wed, 27 May 2020 00:00:00 +0000

Golang tag library to generate Salesforce queries

If you are a Golang developer intending to write a client that interacts with Salesforce, this post is for you. We’ll share how you can annotate your Golang structs to generate queries in SQL like DSL (SOQL - Salesforce Object Query Language) for Salesforce to be used in Salesforce APIs and how this will make it easy for you to write Golang clients for Salesforce, without having to hard code those queries.

Querying Salesforce Records via APIs

Salesforce offers REST APIs that allow any third party to integrate with the platform, using their language of choice. One of the API endpoints (/services/data/<version>/query) allows developers to query Salesforce records using SOQL queries. SOQL is a very powerful SQL-like query language that allows developers to query Salesforce. It has extensive support for a conditional expression to filter the objects being retrieved, as well as very powerful way of performing join operations using relationship queries.
Let’s take a look at an example using the Salesforce object Account. Below is an incomplete list of fields for the Account object. For context, you can find the complete list of fields here.

FieldName	Type	Details
Name	string	Account name
AccountNumber	string	Account number assigned to this account
AccountSource	picklist	Source of the account record. For example, Advertisement, Data.com, or Trade Show. The source is selected from a picklist of available values
BillingAddress	string	Billing address for this account.
HasOptedOutOfEmail	boolean	Indicates whether the contact doesn’t want to receive email from Salesforce
LastActivityDate	Date	Value is one of the following, whichever is the most recent: 1) Due date of the most recent event logged against the record. 2) Due date of the most recently closed task associated with the record.
NumberOfEmployees	Number	Number of employees working at the company represented by this account

If you wanted to query all Accounts where AccountSource is one of 'Advertisement' or 'Data.com' you can write the following SOQL query:

SELECT Name,AccountNumber,AccountSource,BillingAddress,HasOptedOutOfEmail,LastActivityDate,NumberOfEmployees FROM Account  WHERE AccountSource IN ('Advertisement', 'Data.com')

You would then pass the above string as a query parameter to the /services/data/<version>/query endpoint to fetch the relevant records from Salesforce as follows:

/services/data/v48.0/query?q=%0ASELECT%20Name%2CAccountNumber%2CAccountSource%2CBillingAddress%2CHasOptedOutOfEmail%2CLastActivityDate%2CNumberOfEmployees%20FROM%20Account%20WHERE%20AccountSource%20IN%20%28%27Advertisement%27%2C%20%27Data.com%27%290A

Now let’s look at how we might fetch Salesforce records using Golang.

Using Golang to fetch Salesforce Records

Golang is a statically typed, compiled programming language designed at Google. In recent years it has gained enough popularity to consider it a widely-adopted enterprise language. Want to learn more? Get started with Golang here.

Let’s see how quickly we can call the Salesforce query API using Golang. You start by defining the Golang struct representing the Salesforce object(s) you want to query. Let’s continue using the Account object described earlier. The Golang struct representation looks as follows (note the JSON annotations to marshal and unmarshal struct to/from JSON):

type Account struct {
    Name               string    `json:"Name"`
    AccountNumber      string    `json:"AccountNumber"`
    AccountSource      string    `json:"AccountSource"`
    BillingAddress     string    `json:"BillingAddress"`
    HasOptedOutOfEmail bool      `json:"HasOptedOutOfEmail"`
    LastActivityDate   time.Time `json:"LastActivityDate"`
    NumberOfEmployees  int       `json:"NumberOfEmployees"`
}

Then we would model the response from query API as follows:

type QueryResponse struct {
    Done            bool      `json:"done"`
    NextRecordsURL  string    `json:"nextRecordsUrl"`
    Accounts        []Account `json:"records"`
    TotalSize       int       `json:"totalSize"`
}

The following snippet shows one of the ways you can call the Salesforce query API and pass along the required SOQL string:

// Select clause specifying which columns to select from Account object
selectClause := "SELECT Name,AccountNumber,AccountSource,BillingAddress,HasOptedOutOfEmail,LastActivityDate,NumberOfEmployees FROM Account"

// Filter criteria for AccountSource
sources := []string{"Advertisement", "Data.com"}

// Build the soql query using strings.Builder
var soqlBuff strings.Builder
soqlBuff.WriteString(selectClause)

// Specify where clause with AccountSource IN as filter
soqlBuff.WriteString(" WHERE AccountSource IN (")

// Iterate over filter criteria and add those 
for indx, source := range sources {
    if indx > 0 {
        soqlBuff.WriteString(",")
    }
    soqlBuff.WriteString("'")
    soqlBuff.WriteString(source)
    soqlBuff.WriteString("'")
}
soqlBuff.WriteString(")")

// Add SOQL query as query parameter
values := url.Values{}
values.Set("q", soqlBuff.String())

// Create the encoded URL path
path := fmt.Sprintf("/services/data/v48.0/query?%s", values.Encode())
serverURL := "https://<your domain>.salesforce.com"

// Create http request
req, err := http.NewRequest(http.MethodGet, serverURL+path, nil)
if err != nil {
    // Handle error case
}
req.Header.Add("Authorization", "Bearer <session ID>")
req.Header.Add("Content-Type", "application/json")

// Create http client
httpClient := &http.Client{}

// Perform the query
resp, err := httpClient.Do(req)
if err != nil {
    // Handle error case
}

// Read the response
payload, err := ioutil.ReadAll(resp.Body)
if err != nil {
    // Handle error case
}

// Unmarshal the response in QueryResponse
var queryResponse QueryResponse
err = json.Unmarshal(payload, &queryResponse)
if err != nil {
    // Handle error case
}

Note that constructing a SOQL query is quite tedious—especially if you want to add or remove any field from the select clause, or add/remove/update filter criteria from the where clause. What is especially error prone is that you have to maintain the select clause separately from Account struct and there is always the danger of things falling out of sync. Additionally, adding a new filter criteria means more code changes with special consideration for different logical operators.

If you were to expose this as a method to be used by different parts of your program, then you will end up taking the SOQL string—or at least the where clause—as parameter. That might look something like this:

func getAccounts(queryCriteria string) ([]Account, error) {
    // Select clause specifying which columns to select from Account object
    selectClause := "SELECT Name,AccountNumber,AccountSource,BillingAddress,HasOptedOutOfEmail,LastActivityDate,NumberOfEmployees FROM Account"

    // Build the soql query using strings.Builder
    var soqlBuff strings.Builder
    soqlBuff.WriteString(selectClause)
    soqlBuff.WriteString(queryCriteria)

    // Rest of the code similar to above snippet
    ...
}

Notice that you expose yourself to SOQL injection as you are constructing the query by concatenating the string passed to your method. You will need to add enough validations in your method to make sure that doesn’t happen.
You might be asking, isn’t there a simpler way? The answer is: Yes! That’s where new go-soql library comes in.

Introducing go-soql Library

As more and more developers embrace Golang it’s important to provide easier means and ways to interact with Salesforce APIs. One such step is to provide a SOQL annotation library that allows Golang developers to tag their structs similarly to how they tag their structs for JSON marshaling/unmarshaling. And that is the aim of new go-soql library.
This library was developed as an internal project within Salesforce, however we saw value in open sourcing this library for benefit of the Golang developer community. That being said, it’s important to note that this library is not officially supported by Salesforce. It is maintained by the team which heavily relies on this library for its own use cases.
Let’s take a look at how it works. We’ll use the same Account object and Golang struct defined above, so you can see exactly how this new SOQL tag library makes your life easier. We start by annotating our Account struct as follows:

type Account struct {
    Name               string    `soql:"selectColumn,fieldName=Name" json:"Name"`
    AccountNumber      string    `soql:"selectColumn,fieldName=AccountNumber" json:"AccountNumber"`
    AccountSource      string    `soql:"selectColumn,fieldName=AccountSource" json:"AccountSource"`
    BillingAddress     string    `soql:"selectColumn,fieldName=BillingAddress" json:"BillingAddress"`
    HasOptedOutOfEmail bool      `soql:"selectColumn,fieldName=HasOptedOutOfEmail" json:"HasOptedOutOfEmail"`
    LastActivityDate   time.Time `soql:"selectColumn,fieldName=LastActivityDate" json:"LastActivityDate"`
    NumberOfEmployees  int       `soql:"selectColumn,fieldName=NumberOfEmployees" json:"NumberOfEmployees"`
}

If we just want to generate the select clause without conditional expressions then it can be done as follows:

import "github.com/forcedotcom/go-soql"
...
selectClause, err := soql.MarshalSelectClause(Account{}, "")

This will result in following string:

Name,AccountNumber,AccountSource,BillingAddress,HasOptedOutOfEmail,LastActivityDate,NumberOfEmployees

Now if you add/remove any field in the Account struct it will automatically be reflected in the select clause. As you might have already noticed this annotation goes hand in hand with json annotations so the response from the Salesforce API can be directly unmarshalled into the Account struct (as can be seen from the client code snippet in previous section).

While this addresses one problem in our initial code (addition/removal of new field needs to be maintained in two places), it is, of course, of limited use. We want the full SOQL query to be automatically generated. In order to do so, we need to define a couple more structs:

type AccountQueryCriteria struct {
    AccountSource []string `soql:"inOperator,fieldName=AccountSource"`
}

type AccountSoqlQuery struct {
    SelectClause  Account              `soql:"selectClause,tableName=Account"`
    WhereClause   AccountQueryCriteria `soql:"whereClause"`
}

Now, we can generate a complete SOQL query using the above two structs:

soqlStruct := AccountSoqlQuery{
    SelectClause: Account{},
    WhereClause: AccountQueryCriteria{
        AccountSource: []string{"Advertisement", "Data.com"},
    },
}
soqlQuery, err := soql.Marshal(soqlStruct)

And, voila! This will generate the SOQL query that we expect. Now we can use that in our call to /services/data/<version>/query API. Note that you can change the AccountQueryCriteria struct to modify the conditional expression. If you add more than one field to AccountQueryCriteria then they are combined using the AND logical operator.

Now your client code will look something like this. No more SOQL query string maintenance, or worrying about how to construct where clauses with combinations of different logical operators. Just define your struct and marshal it into SOQL query.

// Construct SOQL struct that includes which object you want to query and its query criteria
soqlStruct := AccountSoqlQuery{
    SelectClause: Account{},
    WhereClause: AccountQueryCriteria{
        AccountSource: []string{"Advertisement", "Data.com"},
    },
}

// Generate SOQL query using the above struct
soqlQuery, err := soql.Marshal(soqlStruct)

// Add SOQL query as query parameter
values := url.Values{}
values.Set("q", soqlQuery)

// Create the encoded URL path
path := fmt.Sprintf("/services/data/v48.0/query?%s",values.Encode())
serverURL := "https://<your domain>.salesforce.com"

// Create http request
req, err := http.NewRequest(http.MethodGet, serverURL+path, nil)
if err != nil {
   // Handle error case
}
req.Header.Add("Authorization", "Bearer <session ID>")
req.Header.Add("Content-Type", "application/json")

// Create http client
httpClient := &http.Client{}

// Perform the query
resp, err := httpClient.Do(req)
if err != nil {
   // Handle error case
}

// Read the response
payload, err := ioutil.ReadAll(resp.Body)
if err != nil {
   // Handle error case    
}

// Unmarshal the response in QueryResponse
var queryResponse QueryResponse
err = json.Unmarshal(payload, &queryResponse)
if err != nil {
   // Handle error case
}

You can also create a function that takes AccountQueryCriteria as a parameter and exposes it to be used by different parts of your program to query Account objects using different query criteria.

func getAccounts(queryCriteria AccountQueryCriteria) ([]Account, error) {
   soqlStruct := AccountSoqlQuery{
        SelectClause: Account{},
        WhereClause: queryCriteria,
    }
    soqlQuery, err := soql.Marshal(soqlStruct)
    // Same code as above snippet to actually construct and perform http query
}

Since SOQL is generated by using this struct, the SOQL injection vulnerability is addressed. go-soql will take care of constructing a valid SOQL by properly escaping the user provided parameters.

As you can see from above, using the go-soql library for writing Golang clients for Salesforce is much easier since all you need to do is define the struct and annotate it.

Summary

Hopefully, you now have an idea of the power of the go-soql library. This blog post just scratches the surface of what the go-soql library can do. The library has extensive support for logical operators as well as child-to-parent and parent-to-child relationships. You can find all those details and more in the README of the repo.

Additional Resources
Git repo: https://github.com/forcedotcom/go-soql
Related Trailhead modules:

DEV Community: Atul Kshirsagar

Golang tag library to generate Salesforce queries

Golang tag library to generate Salesforce queries

Querying Salesforce Records via APIs

Using Golang to fetch Salesforce Records

Introducing go-soql Library

Summary