Skip to main content

Salesforce as an ORM

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)


Anonymous said…
"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."

Or, you can install FreeBSD with python, uwsgi, nginx, postgresql and/or redis, and build a real site without a tangled, awful mess of curly braces and semicolons, inline SQL queries, or ridiculous arbitrary restrictions on a system completely under your control without even breaking a sweat.

I know you Microsoft ecosystem/business software people really hate hearing this, but FreeBSD is a superior operating system for powering servers and Python is a superior language to C#, Java and all the other ugly, overly verbose, curly brace and semicolon-riddled stuff you guys use for pretty much everything including web development. Nginx is the best web server around and Postgres is a thousand times better than MSSQL.

Popular posts from this blog

SSRS multi-value parameters with less fail

SSRS supports multi-value parameters, which is nice, but there are a few issues with them. This is how I deal with them. Two of the problems with SSRS multi-value parameters are: You have to jump through a few hoops to get them to work with stored procedures The (Select All) option, as shown above The reason the (Select All) option is a problem is that it is a really inelegant way of saying 'this parameter does not matter to me'. If you have a list with hundreds of values, passing all of them as a default option just seems wrong. Also, if your report shows the user which items they selected, printing the whole list when they choose (Select All) is excessive. So in this post I'm going to show my particular way of: Jumping through the hoops to get Multi-Value params in a stored procedure Adding a single '--All--' value that the report interprets as meaning all the options. Getting Multi-Value params to work with Stored Procedures This is

Copying data to Salesforce Sandboxes using TalenD

A common problem with Salesforce Developer Sandboxes is that they are blank. Really you're going to want some data in there, so there are various strategies for copying data from your live instance to the Sandbox. There are some paid-for solutions - SFXOrgData , Salesforce Partial Data Sandboxes - but if you've got a decent ETL tool you can build your own. There are a bunch of free ETL tools for Salesforce: JitterBit Data Loader is good for quick ad-hoc tasks but the free version makes it difficult to manage specific ETL projects or share projects with other users Pentaho Community Edition - an open source edition of the enterprise version Apatar was a free open source Salesforce ETL which still works but development seems to have stopped since 2011 TalenD Open Studio is an open source ETL tool For the task of copying data from live to a Sandbox, either Pentaho or TalenD Open Studio could be used, depending on preference. Here's a good comparison of the dif

Bug Hunter in Space

In 1987, Acorn launched the Archimedes home computer. At the time, it was the fastest desktop computer in the world, and at the time, I was fortunate enough to have one to experiment with. The Archimedes was great, but it never really took off commercially. However, it was built around the ARM processor, which Acorn had designed itself when it could not find any existing processors suitable for its 32-bit ambitions. The ARM processor was a masterpiece of simple and intuitive design, and its still around today, with most of the instruction set pretty much unchanged. In fact, you've probably got one in your pocket right now. Its design makes it process very efficiently on low energy intake, and hence it is estimated that about 98% of all mobile phones contain an ARM chip. Over 10 billion ARM chips have been shipped, and they outnumber Intel's long running x86 series of chips by a factor of about 5 to 10. I had learned programming on the BBC Model B , and when we got the A