Type safe SalesForce Queries (SOQL) in Scala

This blog shows how to implement a relatively simple type safe DSL for the SalesForce Object Query Language (SOQL). SalesForce doesn’t provide much tooling specifically for the Scala world, which got me to writing some support myself.

 

One of the most common ways to interact with the SalesForce API is using the Enterprise or the Partner WSDL. For the purpose of this article, I will be using the Enterprise WSDL. The main difference is that the Enterprise one is type-safe - it contains object definitions for all your data model objects, while the Partner one is more suitable for dynamically typed languages.

 

 

Given that we have a type safe WSDL, how do we get to a type safe Scala DSL? Let me first show an example of what the goal is.

 

Ideally I would be able to write some code like this in Scala:

 

select
     (Case_.caseNumber :: (Case_.account :/ Account_.accountNumber) :: Case_.`engineeringReqNumberC` :: HNil)
from Case_
where (
     (Case_.caseNumber :== "5555") and (Case_.`createdDate` :== Calendar.getInstance())
)

This would be translated to a SOQL query like this one:

 

SELECT CaseNumber, Account.AccountNumber, EngineeringReqNumber__c FROM Case WHERE (((CaseNumber = '5555')) AND (CreatedDate = '2015-06-08T10:01:42+02:00'))

Let’s see what this code actually does step by step:

 

Before digging deeper into the implementation of this code, let’s take a look at how this actually makes our code safe, instead of just writing a String and sending it to the Salesforce:

 

First of all, the list of fields in the “select" clause is compile time checked and only specific types are allowed. We basically only want to allow Salesforce fields or sub-selects to comply with the SOQL specs.

 

Let’s see if we try to add something not allowed to the list of fields in the “select" part:

 

select(Case_.caseNumber :: 4 :: HNil) from Case_

The constants are not allowed, so we get:

 

error: type mismatch;
[ERROR]  found   : shapeless.::[com.github.akovari.typesafeSalesforce.model.Field[String],shapeless.::[Int,shapeless.HNil]]
[ERROR]  required: com.github.akovari.typesafeSalesforce.query.ColumnList[?]
[ERROR]     select(Case_.caseNumber :: 4 :: HNil) from Case_

This may look a bit cryptic but I’ll get to the implementation details later.

 

Similarly we need to make sure only actual SalesForce entities are allowed in the “from” section.

 

The conditions are also interesting. We need to make sure that we are creating conditions from types that match. We know that the caseNumber is of a type String so we shouldn’t be able to compare it with int for example.

 

This should not compile:

Case_.caseNumber :== 4

As we can see, the compiler catches the error as expected:

 

error: type mismatch;
[ERROR]  found   : Int(4)
[ERROR]  required: com.github.akovari.typesafeSalesforce.query.Field[String]
[ERROR]     Case_.caseNumber :== 4

 

 

So this is basically where we want to get, now let’s go step by step how to implement all of the above.

 

1. First of all, we are starting off with an WSDL that looks something like this:

 <complexType name="Case">
 <complexContent>
 <extension base="ens:sObject">
 <sequence>
 <element name="Account" nillable="true" minOccurs="0" type="ens:Account"/>
 <element name="AccountId" nillable="true" minOccurs="0" type="tns:ID"/>
 <element name="ActivityHistories" nillable="true" minOccurs="0" type="tns:QueryResult"/>
 <element name="Asset" nillable="true" minOccurs="0" type="ens:Asset"/>
 <element name="AssetId" nillable="true" minOccurs="0" type="tns:ID"/>
...

 

This is from a WSDL obtained via a developer account on Salesforce, it contains definitions of objects and their fields. The very first step is to generate a WS client, I have tried to use ScalaXB which would make a lot of implementation much simpler, unfortunately it is unable to generate a client for the SalesForce WSDL. Therefore I have used CXF codegen which can generate a Java client for us.

 

2. Next we need to generate a meta model from the client objects, similar to what Hibernate meta model looks like for Database entities. Here we would typically consider at least following options:

Implementation of the annotation processor is available at:

 

 

 <jxb:bindings node="//xs:complexType">
 <annox:annotate target="class">
 <annox:annotate annox:class="com.github.akovari.typesafeSalesforce.annotations.SalesForceEntity"/>
 </annox:annotate>

@SupportedAnnotationTypes({"com.github.akovari.typesafeSalesforce.annotations.SalesForceEntity"})
@SupportedSourceVersion(SourceVersion.RELEASE_8)
public class SalesForceAnnotationProcessor extends AbstractProcessor

 

3. Let’s jump into the actual DSL construction.

 

We are only going to implement support for select-s in this blog. Our primary interface is defined this way:

 

import shapeless._

case class SelectQuery[C <: HList](columns: ColumnList[C],
                       entities: Seq[Entity] = Seq.empty,
                       filter: Option[Filter] = None,
                       orders: Option[OrderList[_ <: HList]],
                       groupBys: Seq[GroupBy[_]] = Seq.empty,
                       limit: Option[Limit] = None)

This is an underlying model used by the select … from… function on the top. It seems that most of the elements are quite obvious, the reason why columns and orders are defined as Shapeless HList is because we need to allow heterogenous elements in the collection of elements, such as Field[Integer], Field[String] … and also we need to not allow just anything, so we do not allow constants for example. The actual implementation of how this works will be shown later.

 

The DSL functions themselves:

 

object SelectQuery {
  type SelectableColumn[T, C <: HList] = Either[SimpleColumn[T], EmbeddedSelectColumn[T, C]]

  def select[C <: HList](columns: ColumnList[C]) = SelectQueryStep(columns)

  sealed trait QueryStep[C <: HList] {
    val query: SelectQuery[C]

    override def toString = query.toString
  }

  case class SelectQueryStep[C <: HList](columns: ColumnList[C]) extends QueryStep[C] {
    override val query = SelectQuery(columns = columns, orders = None)

    def from(entities: Entity*): FromQueryStep[C] = FromQueryStep(query, entities)
  }

  case class FromQueryStep[C <: HList](sq: SelectQuery[C], entities: Seq[Entity]) extends QueryStep[C] {
    override val query = SelectQuery(columns = sq.columns, entities = entities, orders = None)

    def where(filter: Filter): WhereQueryStep[C] = WhereQueryStep(query, filter)

    def orderBy[O <: HList](orders: OrderList[O]): OrderQueryStep[C, O] = OrderQueryStep(query, orders)

    def groupBy(groupBys: GroupBy[_]*): GroupByQueryStep[C] = GroupByQueryStep(query, groupBys)

    def limit(limit: Limit): LimitQueryStep[C] = LimitQueryStep(query, limit)
  }

  case class WhereQueryStep[C <: HList](sq: SelectQuery[C], filter: Filter) extends QueryStep[C] {
    override val query = SelectQuery(columns = sq.columns, entities = sq.entities, filter = Some(filter), orders = None)

    def orderBy[O <: HList](orders: OrderList[O]): OrderQueryStep[C, O] = OrderQueryStep(query, orders)

    def groupBy(groupBys: GroupBy[_]*): GroupByQueryStep[C] = GroupByQueryStep(query, groupBys)

    def limit(limit: Limit): LimitQueryStep[C] = LimitQueryStep(query, limit)
  }

  case class OrderQueryStep[C <: HList, O <: HList](sq: SelectQuery[C], orders: OrderList[O]) extends QueryStep[C] {
    override val query = SelectQuery(columns = sq.columns, entities = sq.entities, filter = sq.filter, orders = Some(orders))

    def groupBy(groupBys: GroupBy[_]*): GroupByQueryStep[C] = GroupByQueryStep(query, groupBys)

    def limit(limit: Limit): LimitQueryStep[C] = LimitQueryStep(query, limit)
  }

  case class GroupByQueryStep[C <: HList](sq: SelectQuery[C], groupBys: Seq[GroupBy[_]]) extends QueryStep[C] {
    override val query = SelectQuery(columns = sq.columns, entities = sq.entities, filter = sq.filter, orders = sq.orders, groupBys = groupBys)

    def limit(limit: Limit): LimitQueryStep[C] = LimitQueryStep(query, limit)
  }

  case class LimitQueryStep[C <: HList](sq: SelectQuery[C], limit: Limit) extends QueryStep[C] {
    override val query = SelectQuery(columns = sq.columns, entities = sq.entities, filter = sq.filter, orders = sq.orders, groupBys = sq.groupBys, limit = Some(limit))
  }
}

 

We define a class for each step of the query creation. It may be worth noting that all of this code is purely immutable.

 

Let’s finally get to the use of Shapeless HLists for polymorphic lists of fields and order by fields.

 

For columns or fields this is all we need:

 

case class ColumnList[L <: HList](l : L)

object ColumnPoly extends Poly1 {
  implicit def caseField[T, S <% ModelField[T]] = at[ModelField[T]](f => fieldToColumn(f).toString)

  implicit def caseSelectQueryStep[T, C <: HList, QS <: SelectQueryStep[C], S <% QS] = at[SelectQueryStep[C]](qs => EmbeddedSelectColumn[T, C](qs).toString)
  implicit def caseFromQueryStep[T, C <: HList, QS <: FromQueryStep[C], S <% QS] = at[FromQueryStep[C]](qs => EmbeddedSelectColumn[T, C](qs).toString)
  implicit def caseWhereQueryStep[T, C <: HList, QS <: WhereQueryStep[C], S <% QS] = at[WhereQueryStep[C]](qs => EmbeddedSelectColumn[T, C](qs).toString)

  implicit def caseOrderQueryStep[T, C <: HList, O <: HList, QS <: OrderQueryStep[C, O], S <% QS] = at[OrderQueryStep[C, O]](qs => EmbeddedSelectColumn[T, C](qs).toString)
  implicit def caseGroupByQueryStep[T, C <: HList, QS <: GroupByQueryStep[C], S <% QS] = at[GroupByQueryStep[C]](qs => EmbeddedSelectColumn[T, C](qs).toString)
  implicit def caseLimitQueryStep[T, C <: HList, QS <: LimitQueryStep[C], S <% QS] = at[LimitQueryStep[C]](qs => EmbeddedSelectColumn[T, C](qs).toString)

  implicit def caseSimpleColumn[T, SC <: SimpleColumn[T], S <% SC] = at[SimpleColumn[T]](_.toString)
  implicit def caseEmbeddedSelectColumn[T, C <: HList, SC <: EmbeddedSelectColumn[T, C], S <% SC] = at[EmbeddedSelectColumn[T, C]](_.toString)
}

implicit def hlistToColumnList[L <: HList, M <: HList](a: L)(implicit mapper: Mapper.Aux[ColumnPoly.type, L, M]): ColumnList[M] =
  new ColumnList[M]((a map ColumnPoly))

We are using the Poly1 map compile time transformation of the HList for those very specific types as defined in the ColumnPoly. I’ll not go too deep in the details, that would go beyond the scope of this blog.

 

Similarly we implement a very similar Poly1 for the OrderList:

 

object OrderPoly extends Poly1 {
  implicit def caseAsc[T, S <% AscendingOrder[T]] = at[AscendingOrder[T]](_.toString)

  implicit def caseDesc[T, S <% DescendingOrder[T]] = at[DescendingOrder[T]](_.toString)
}

implicit def hlistToOrderList[L <: HList, M <: HList](a: L)(implicit mapper: Mapper.Aux[OrderPoly.type, L, M]): OrderList[M] =
  new OrderList[M]((a map OrderPoly))

One last part of this codebase worth mentioning is the actual query interface that hooks this SelectQuery class back to the Java SalesForce client.

 

I am using Lasius library that handles the reconnections etc and all that was left to do can be found in SalesForceConnection class.

 

This code is not ready to be used as a library. I have yet to solve how to integrate a third-party WSDLs into this build process. After that, there are still a bunch of things to work on.

 

Among other things: