Type safe SalesForce Queries (SOQL) in Scala

ScalaThis 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.

Custom DSL in Scala

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

Implementation

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

Web Serivces and code generation

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:
  •  since we’re in scala we could use Macros. The problem is that this client is actually Java and not scala code so using Macros doesn’t seem very natural, also writing Macros still looks like a little bit of magic, I know, quasi-qoutes make it much nicer but still – I have no experience with Macros just yet. Maybe in the future together with the ScalaXB this could work.
  •  Java compile time annotation processor combined with a simple template library such as velocity to parse JAXB annotations and generate meta model classes
  •  other approaches to compile time or runtime(reflection) code generation
Implementation of the annotation processor is available at:
It consists of 2 parts:
  • In the binding.xml we annotate all SalesForce object with our custom annotation
 <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

DSL Construction

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))

 

Conclusion

That is more or less all there is to it. We have shown how to enhance a Java only interface and extend it with additional type safety provided by the Scala ecosystem. We have an intuitive interface for writing SOQL queries that also gives us a lots of safety and simplicity thanks to the IDE code completion when typing new queries.

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:
  • Aggregated columns are not type safe – These should only work for Number fields.
  • Improve Case_.account : / Account_.accountNumber – only Account_ fields should be allowed after Case_.account
  • Generalize the Build process to work with external WSDLs
  • Add support for Insert, Update, Upsert and Delete queries

 

Link to the source codes.


Join the Red Hat Developer Program (it’s free) and get access to related cheat sheets, books, and product downloads.

Share