Salesforce as an ORM

Published on 01 June 2010

To me, Salesforce is not a very attractive word. It basically conjures up an image of an army of salespeople, weilding laptops and software-enhanced selling skills. What could be less appealing than that?

But having used their system for a bit, and written code for it, I'm starting to like it. It also helps that they have a charity-foundation wing that sells the software to not-for-profits on a not-for-profit basis.

They way the object/database mapping is handled in Salesforce is pretty interesting, compared to, say, a C# program that uses Entity Framework or NHibernate or Linq-to-Sql to talk to a SQL Server. So this blog post is sort of about that. But first, a bit of background:

Salesforce is SaaS on a PaaS

Salesforce is a ready built Software-as-a-Service CRM, but it runs on their more generic Platform-as-a-Service. This means its very customizable because you can take the Salesforce CRM as a starting point but then pretty much develop anything you want. (Or, you can just purchase the platform as a starting point and develop something completely new from scratch.

See the Salesforce Documentation for ERDs of the main areas of the vanilla Salesforce CRM - this gives an idea of the kinds of things it covers.

Salesforce has three levels of customization

There are different ways of customizing Salesforce, aimed at users with different skillsets:

- Point and Click: many predictable customizations can be done by going to the 'setup' console and pointing-and-clicking. e.g. new fields, new tables, new page layouts
- Excel type Formulas: when setting up workflow rules, an Excel-style formula language is used to describe more complex logic - such as when to apply a particular rule (e.g. Field1 is A and Field2 is B or starts with C)
- Programming: Customisations can be done at the platform level using the Salesforce API with the java-like Apex language and VisualForce UI framework.

'Salesforce API' usually refers to the internal API that you use within the platform to interact with the database/objects. But Salesforce also has a Web Services API that works along very similar lines, like a thin web services layer over the existing API. Which means the experience of programming 'internally' with Salesforce using Apex is pretty similar to programming 'externally' using the Web Services and your language of choice.

Salesforce as an ORM

There are certain reasons (explored below) why the Salesforce API isn't really an Object Relational Mapping (ORM) system. But comparing it to other ORM systems such as NHibernate or Microsoft's Entity Framework or Linq-to-SQL is a good way to explore the design of the Salesforce API.

Its quite a good example of simple, focussed design - as in it does some things well, and just doesn't do some things at all, which means the bits that do work, work simply, without getting too complex or cluttered.

1. Querying data

For querying data, Salesforce uses SOQL, which looks identical to SQL for simple queries:

SELECT Id, Name, BillingCity   
FROM Account  
WHERE Name like 'A%'

This query would return a list of Account objects, with just the Id, Name and BillingCity fields filled in.

When you want to join tables together, however, SOQL starts to look very different to SQL. The reason for the difference is that the results are different - SOQL aims to return you an object graph (lists of objects with other related objects already linked in) rather than the flat tabular result sets that SQL returns.

Here is an SOQL example linking a child table to a parent table:

SELECT Id, Name, Account.Name   
FROM Contact   
WHERE Account.Industry = 'media'

The Contact 'table' is joined to the parent 'Account' table by adding Account.(fieldname) to the select list - Salesforce already knows how the two tables relate, so no info about primary and foreign keys is needed (strictly speaking, Account is actualy the name of a relationship in this example, not the table per se). The result set is a list of Contact object (with only Id and Name filled in), and each Contact object has an accompanying Account object with just Name filled in.

Here's an SOQL example linking in the other direction (parent to child):

SELECT Name, (SELECT LastName FROM Contacts)   
FROM Account  
WHERE Account.Industry = 'media'

This would return a list of Account objects, each of which containing a list of the related Contact objects. Note that the pluralised name Contacts is used - this is actually the name of the 'relationship' from Account down to Contact, and so again Salesforce does not need info about primary and foreign keys because it already knows the relationship.

An important restriction with SOQL is that you can only join tables together along the pre-defined relationships. Again, this is tied into the way results are returned - SOQL results are object graphs, and so by definition only the pre-defined relationships can be queries, because they are the only ones that can be expressed in the results set.

2. Loading objects

Normally, in an ORM like NHibernate or Entity Framework there is a 'get' or 'load' mechanism for loading a single object. The Salesforce API doesn't have that - the only way to load an object is to issue an SOQL query. So typically if you know which object you want, you would query it by ID.

Which means in the Salesforce API, there is no difference between query results and fetched objects - they are the same thing.

3. Lazy loading

In most ORMs, Lazy Loading is a mechanism to allow you to explore and object graph and have it loaded as necessary, rather than all loading in advance. For example, where object A has a one-to-many link to object B, NHibernate will give you object A and tell you how many object Bs are related, but will only load them once you start retrieving them from the collection. Sometimes lazy-loading is also applied to individual fields of an object as well - they are only loaded when they are asked for.

The Salesforce API doesn't do lazy loading. If you want a field to be filled in, you have to specify it in the SELECT list of the SOQL query. Similarly, if you want related objects, you either have to specify that in the SOQL, or fire off another SOQL query yourself.

This is less sophisticated that lazy loading perhaps, but also makes the whole API much simpler. And in most scenarios, it is not much of a problem.

(Part 2 coming soon: Saving data, Validation and Domain Rules)