Accessing Salesforce Data from Ruby

Community-contribution.jpg

Editor's Note: This article is community contributed. Learn why and how you can contribute too.

This article is a brief introduction to the databasedotcom gem. The article demonstrates how to install and configure the gem, and begin using it to access Salesforce objects from within Ruby code. While this article uses a simple Rails application to demonstrate key topics, please realize that most of this works fine in Ruby outside of Rails as well. As a bonus, the last part of the article briefly discusses how to work with the Salesforce Bulk API using the salesforce_bulk gem.

Note: This article explains various gems as of late January 2012. The community is very active in improving these gems, so you should follow changes made after this date.

What is Materializing?

The databasedotcom gem takes a clever approach to creating models for your Salesforce objects: materializing them. What does this mean? Essentially, it means that your app specifies any required Salesforce object and the gem — under the covers — introspects the object on the Salesforce side and then dynamically creates a class that models it with the appropriate properties. You'll see more details below, but here's a quick example to show how easy it is:

client.materialize("User")

This seemingly simple statement creates a model at run-time called User that represents the object of the same name in Salesforce. It's not much different from the way that ActiveRecord models represent an object in a local database, except that the model is materialized at run-time. The materialized model has all of the expected properties available that you can get/set, and you can do a save to persist changes just as you normally would with Ruby.

If you're working in Rails, you can make things even easier by using the little databasedotcom-rails gem. This gem automagically takes care of materializing models inside your controllers — however, the databasedotcom-rails gem doesn't support namespacing into modules (see below for details).

Limitations

The databasedotcom gem is quite new, and as such doesn't do everything that a more mature solution might do such as ActiveRecord. One of the most important gem limitations is a lack of support for relationships — no belongs_to, has_many, and so forth. Handling relationships yourself isn't as much of a burden as you might expect because relationships between Salesforce objects are typically much simpler than you're used to in traditional RDBMS schemas. The Salesforce query language, SOQL, is designed to solve a well-defined and restricted set of problems, and to keep things running smoothly in Salesforce's shared cloud.

Configuration Basics

The following sections teach you how to set up your environment and configure a few things so that you can start accessing Salesforce data.

Install the Gems

Hopefully, you already use rvm to manage your Ruby environment, and Bundler to manage your gems. If not, now is an excellent time to start. This article isn't a tutorial on using these utilities, so the article assumes that you have them up and running, or else you're ready to somehow survive without them.

From this point forward, the article also assumes that you have a Ruby installed and that you have a gemset created. Considering you'll be making a simple Rails application, I'll also assume that you've also created an empty Rails app.

Edit your app's Gemfile and add:

gem 'databasedotcom'
gem 'databasedotcom-rails'

Then run the following to install these gems:

bundle install

Set Up Remote Access

Before your Ruby code can get at your Salesforce data, you need to enable remote access for your application. Log in to your Salesforce account, navigate to Setup, and click through to Develop, where you'll see a link to the Remote Access page. You can find good information on remote access configuration at the "Configuring OAuth 2.0 Access for your Application" section of this article. For this example, keep things simple — create a new remote application called databasedotcomtest with a fake callback URL (callback URLs are important with OAuth authentication, but not with username/password authentication).

Note: OAuth configuration is not in the scope of this article, but for a production application you will want to use it. OmniAuth is a useful gem to explore, as it has a native Saleforce provider that you can use to facilitate OAuth authentication. See the databasedotcom gem's README for some details on the configuration in that case.

Once you save the new remote access configuration, take note of the Consumer Key and Consumer Secret. You'll see below that these can be put into a configuration file that your Ruby code provides to the databasedotcom client object.

By default, the databasedotcom client object uses login.salesforce.com as the host. This is fine when you're using a Force.com Developer Edition account. If you are using another type of org and working in a sandbox (as you should be!), make sure to change the authentication host to test.salesforce.com. See the example below.

The Client Object: ID and Secret

The databasedotcom gem talks to your org using an instance of the client object, which relies on the ID and Secret from the remote access configuration in your Salesforce account. There are two simple ways to provide these credentials along with other configuration information: as arguments to the client.new method, or as parameter values in a configuration file. The configuration file approach makes your code much cleaner, so that's the approach this article demonstrates.

The databasedotcom gem configuration file is config/databasedotcom.yml and is somewhat similar to the Rails database.yml configuration file. Create and edit that file in your Rails app, following this example:

host: login.salesforce.com        # Use test.salesforce.com for sandbox
client_secret: 1234567890         # This is the Consumer Secret from Salesforce
client_id: somebigidthinghere     # This is the Consumer Key from Salesforce
sobject_module: SFDC_Models       # See below for details on using modules
debugging: true                   # Can be useful while developing
username: [email protected]
password: mypasswordplusmysecuritytoken

Naturally, you should substitute the values specific to your org for the client_secret, client_id, username, and password parameters. Don't forget to use your security token as part of your password.

Also, note the first line, which specifies login.salesforce.com. If you're using a sandbox rather than a Developer Edition account, change this to test.salesforce.com.

Tip About Passwords

I once spent several minutes trying to figure out why I couldn't get authentication to work against a new Developer Edition account. On a hunch, I removed the special characters (ampersand and a slash) from my password. Voila, everything started working. So if you're going to be authenticating with username and password, I recommend ensuring that your password contains only letters and digits to save yourself some debugging. Hopefully this problem will be fixed in the future, but consider this a helpful warning — and another reason to use OAuth in a production environment.

Modules and Namespacing

It's not uncommon to have a local model, such as User or Account, that exists in your application database (PostgreSQL, MySQL, etc.) with the same name as your Salesforce objects. But having two models called Account won't work well, and distinguishing between models with names such as Account and SalesforceAccount just doesn't feel right.

Instead, it's better to modularize things. To do this, create a SFDC_Models module, and namespace all of your materialized Salesforce models inside it by setting the sobject_module property of the client before materializing, like so:

client.sobject_module = "SFDC_Models"
client.materialize("Account")

This type of configuration materializes the model SFDC_Models::Account, and makes it very clear to anyone reading your code which object is which. Even better, you can specify the sobject_module in your configuration file, as you did in the example databasedotcom.yml file shown earlier.

For my Rails application, I like to define the module somewhere out of the way, so I use the file lib/sfdc_models.rb:

# Container into which you will materialize the Salesforce objects.
module SFDC_Models
end

Note: You may need to add the lib directory to the autoload_path in your config/application.rb to ensure that the module gets loaded when you start Rails. Something like:

config.autoload_paths += %W(#{config.root}/lib)

As noted earlier, the databasedotcom-rails gem doesn't support namespacing. However, because of the way it enables access to the Salesforce objects, using a module is less important — it's pretty clear to anyone reading your controller code which objects are in Salesforce and which are in your database. You'll see an example of this below.

Smoke Testing

Before enjoying the convenience of the databasedotcom-rails gem, you should try things manually. It's a good way to make sure that you understand what's going on before the inevitable debugging sessions to come. Now that you have your empty Rails app, installed the gems, and created your databasedotcom.yml configuration file, try a smoke test to make sure it's all working:

$ cd <root directory of your Rails app>
$ rvm use <ruby version and gemset>
$ rails console

Once you are in the console, instantiate a new client, authenticate it, and then use it to materialize a standard object such as User.

> client = Databasedotcom::Client.new("config/databasedotcom.yml")
'''=> #<Databasedotcom::Client:0x00000104131078 ...etc...'''
> client.authenticate :username => '[email protected]',
:password => 'mypasswordplusmysecuritytoken'
'''=> "00DV000andabunchmoregibberish"'''
> client.materialize("User")
'''=> SFDC_Models::User < Databasedotcom::Sobject::Sobject'''
> SFDC_Models::User.count
***** REQUEST: https://na14.salesforce.com/services/data/v22.0/query?q=SELECT%20COUNT()%20FROM%20User
***** RESPONSE: Net::HTTPOK -> {"totalSize":3,"done":true,"records":[]}
 => 3

If you see responses similar to those above, then things are going well with your configuration. Of course, instead of seeing "3," you'll see a count of the number of users in your org. You may note that the username and password are already in the configuration file, so in theory you shouldn't have to do explicit authentication. At the moment, however, you will have to do that, or you'll receive an error when you try to do the materialize.

This is a good opportunity to look at a useful convenience method. One of the tricky things about working with Salesforce objects, particularly custom objects, is knowing the name of each field. For example, custom fields have "__c" appended to the name you would expect to see. Because of this, it's extremely useful to have the attributes method. Use it to get a list of the fields and their names so that you know what's available and how to get at them:

> SFDC_Models::User.attributes
=> ["Id", "Username", "LastName", "FirstName", ...etc...]

From this output, you know about the FirstName attribute and can now dig deeper into the model:

> u = SFDC_Models::User.first
 => #<SFDC_Models::User:0x00000101315108 @Id="00540000000o2dhEFG", @Username="[email protected]", @LastName="Smith", @FirstName="Fred", ...etc
> u.FirstName
 => "Fred"

Notice how a Salesforce model acts like a standard model, with each attribute available, similar to traditional ActiveRecord models. It's important to note, however, that while attributes gives you a list, you can't tell from it what the data types are for the fields. We'll look into this issue in more detail in subsequent section.

List and Display Users

Now it's time to start building your Rails application making use of the databasedotcom-rails gem.

Important Note: Using namespacing in conjunction with the databasedotcom-rails gem can lead to some confusing behavior with regard to routes, so before you continue, comment out the sobject_module line in the config/databasedotcom.yml file.

First, create a Users controller with two actions: index retrieves a list of the first 20 users, and show retrieves a single user. Here's what your app/controllers/users_controller.rb should look like:

class UsersController < ApplicationController
  include Databasedotcom::Rails::Controller

  def index
    @users = User.all()[0..19]
  end

  def show
    @user = User.find(params[:id])
  end
end

Next, add the following line to config/routes.rb to configure the standard routes for the new Users controller:

        resources :users

Now create corresponding views for each action. Here's app/views/users/index.html.erb:

<h1>Users</h1>

<% @users.each do |u| %>
  <%= link_to "#{u.Name}", u %><br/>
<% end %>

Note: You can see that the attribute u.Name has an upper-case 'N'. This is a small but important thing. Salesforce fields are case-sensitive, thus the Salesforce models care about the capitalization. If you try this with "u.name" instead, you'll get an error.

And here's app/views/users/show.html.erb:

<h1>User <%= @user.Name %></h1>

<% @user.attributes.each do |a| %>
    <%= a[0] %>: <%= a[1] %><br/>
<% end %>

So what do you have here and how does it all function?

The controller is key, but there's not much to it, is there? It includes Databasedotcom::Rails::Controller and as such inherits its magic such as catching the lack of a User model and materializing it from Salesforce. After that, you are good to proceed as you normally would. For example, in the index action, there's a typical User.all method call to get the first twenty users.

The app/views/users/index.html.erb view iterates over the users and creates a link for each one, displaying the user name as the link text. Clicking on a link calls the show action in the Users controller that does exactly what you'd expect: it retrieves a specific user by id. Note, though, that the id in this case is a Salesforce Id.

Start up your Rails server, load http://localhost:3000/users, and click on a user. Notice that the URL looks something similar to http://localhost:3000/users/00540000000oHhDTYA — that long id is the REST URL with the Salesforce object Id of that user. And you now see the output of the app/views/users/show.html.erb view, which displays all of the attributes of the user.

One thing you might be wondering is why the index controller action retrieves all of the users but only uses the first twenty via an Array operator. It's not good practice to retrieve all those records and use only a few. Why not just use LIMIT to cut down on the amount of data sent from Salesforce?

As it turns out, the all method doesn't support LIMIT, or any other options for that matter. Instead, however, you can use the query method. Replace the line:

@users = User.all()[0..19]

with

@users = User.query("Id != NULL LIMIT 20")

That's more efficient, isn't it? Well, sort of. It's great that you have the LIMIT now, so the app is operating more efficiently. But what's with the Id != NULL? Well, the query method takes the required parameter your app sends and uses it as the WHERE clause for a query. So your app has to send a complete condition. Because Id can't be null, Id != NULL is a nice safe condition to get the job done.

Remember, the databasedotcom gem is relatively immature, so you can expect improvements to idiosyncrasies like this one. In fact, if you want to add support for LIMIT, I'm sure a pull request would be happily accepted!

The query method can also do more complex queries than a simple find. For example, if you want to get all of the users whose last names begin with 'W', just use this:

@users = User.query("LastName LIKE 'W%'")

Use your imagination from here and learn more about SOQL queries while you're at it. The SOQL documentation is a fine place to start.

Update and Create Tasks

You can now display data from Salesforce, but what about adding or changing data? In general, it's done the same way as you would using ActiveRecord in a basic Rails app. Add the standard edit and update actions to app/controllers/users_controller.rb:

def edit
  @user = User.find(params[:id])
end

def update
  @user = User.find(params[:id])
  @user.update_attributes(params[:user])
  render "show"
end

Then create the app/views/users/edit.html.erb file for the view:

<h1>Editing user  <%= @user.Name %> </h1>

<%= form_for @user do |f| %>
    <div class="field">
        <%= f.label :CompanyName %><br />
        <%= f.text_field :CompanyName %>
    </div>
    <div class="actions">
        <%= f.submit %>
    </div>
<% end %>

<br/>

<%= link_to 'Show', @user %> |
<%= link_to 'Back', users_path %>

Also, add this to app/views/users/show.html.erb just after the header:

<%= link_to 'Edit', edit_user_path(@user) %> |
<%= link_to 'Back', users_path %>
<br/>
<br/>

Now you'll have a navigation link to the Edit view from the Show view. In the running app, click that link to display the Edit view for a record and modify the user's Company Name. When you submit the form, the app updates the user in Salesforce and then renders the Show view for the same record where you can see the change.

The Edit view form is quite simple, but feel free to add other fields. Note, however, that some fields such as Name may be protected fields that you can't modify. As an experiment, if you're using a standard Developer Edition account, try adding the Name field and submitting the form. You will receive an error warning that your security settings won't allow you to change that field.

As you've seen, the databasedotcom gem makes accessing Salesforce data very similar to a standard ActiveRecord/Rails app, so you shouldn't be surprised to find that creating an object looks familiar too. To demonstrate, we won't continue with the User object because, if you're using a Developer Edition account, you won't be able to create a User. Instead, switch focus to the Task object. As an exercise, make a new controller, app/controllers/tasks_controller.rb, with the index and show actions. To do this quickly, copy and modify app/controllers/users_controller.rb. Also, don't forget to add the Tasks route definition in config/routes.rb.

Next, add the new and create actions to app/controllers/tasks_controller.rb:

  def new
    @task = Task.new
  end

  def create
    task = Task.new(params[:task])
    task.IsRecurrence = false
    task.IsReminderSet = false
    task.Priority = "Normal"
    user = User.first
    task.OwnerId = user.Id
    if (task.save)
      redirect_to(task, :notice => 'Task was successfully created.')
    end
  end

You also need the app/views/tasks/new.html.erb view:

<h1>Create task</h1>

<%= form_for @task do |f| %>
    <div class="field">
        <%= f.label :Subject %><br />
        <%= f.text_field :Subject %>
    </div>
    <div class="field">
        <%= f.label "Status" %>
        <%= f.select :Status, options_for_select([['Not Started', 'Not Started'], ['In Progress', 'In Progress'], ['Completed','Completed']]) %>
    </div>
    <div class="actions">
        <%= f.submit %>
    </div>
<% end %>

<br/>

<%= link_to 'Back', tasks_path %>

Update app/views/tasks/index.html.erb and add the following to provide a navigation link to the New view.

<%= link_to 'New', new_task_path(@task) %> |
<%= link_to 'Back', tasks_path %>
<br/>
<br/>

To keep the view simple, there's only two fields, with defaults for other required fields in the controller's create action. There's nothing special about the form: there's a text field for the task's Subject, and a select list for the Status. There are a couple of other possible values for a task's Status, but these three options are enough to demonstrate the concept.

Looking at the controller, the new action is standard Rails. The create action does the normal instantiation of a Task object using the form parameters, and then sets three more fields to default values. The only non-standard bit, perhaps, is that one of the required fields is the task's OwnerId, which has to contain the Salesforce Id of a User. To keep it simple, the controller just grabs the Id of the first User and assigns it to the new task's OwnerId. Of course, in a real application, you'd do something more appropriate, perhaps letting the user select someone from a list. As an exercise, try building that into the Task creation form.

For completeness, add the destroy action to app/controllers/tasks_controller.rb:

  def destroy
    task = Task.find(params[:id])
    task.delete
    redirect_to(tasks_path, :notice => "Task '#{task.Subject}' was successfully deleted.")
 end

Assuming you already created app/views/tasks/show.html.erb based on app/views/users/show.html.erb and then updated it to work with Tasks, append a link to delete the Task you're viewing. For example:

<%= link_to 'Back', tasks_path %> | <%= link_to 'Delete', @task, :confirm => 'Are You Sure?', :method => :delete %>

So far, so good. Now add these lines to app/views/tasks/index.html.erb under the header:

<% if flash[:notice] %>
  <%= flash[:notice] %><br/><br/>
<% end %>

After you delete a task and load the Index view, in standard Rails fashion, the Task list displays with a nice status message.

As you've seen so far, the databasedotcom gem makes most everything work like a basic Rails app. You'll always have to keep in mind some unique characteristics of Salesforce when it comes to querying, field names, and dealing with required fields. But with the gem, you can easily write a Rails application or Ruby script to integrate with your Salesforce organization.

Additional Gem Methods

The databasedotcom gem provides many useful methods. Here are a few noteworthy ones.

The field_type method is a handy method to display a given field's datatype:

> client.materialize('Task')
> SFDC_Models::Task.field_type('Status')
 => "picklist"

When a field is a picklist, it's extremely useful to be able to get the values for it. For example, when you build a view around a Salesforce object, you can provide a convenient select list and populate it with values using the picklist_values method.

> SFDC_Models::Task.picklist_values('Status')
 => [{"value"=>"Not Started", "active"=>true, "label"=>"Not Started", "defaultValue"=>true, "validFor"=>nil}, {"value"=>"In Progress", "active"=>true, "label"=>"In Progress", "defaultValue"=>false, "validFor"=>nil}, {"value"=>"Completed", "active"=>true, "label"=>"Completed", "defaultValue"=>false, "validFor"=>nil}, {"value"=>"Waiting on someone else", "active"=>true, "label"=>"Waiting on someone else", "defaultValue"=>false, "validFor"=>nil}, {"value"=>"Deferred", "active"=>true, "label"=>"Deferred", "defaultValue"=>false, "validFor"=>nil}]

Also convenient for views is the label_for method that lets you to get the label metadata for a given attribute:

> SFDC_Models::Task.label_for('Who')
 => "Contact/Lead ID"

Considering that the attribute name and label for a field in Salesforce are often very different, using the label_for method helps you keep the Salesforce view and your custom application view in sync and avoid user confusion.

Note: I recommend looking over the gem's rubydocs to see what other methods are available, particularly focusing on the Databasedotcom::Sobject::Sobject class.

Errors

If you try to do something like creating an object without all of the required fields, you'll get an error, which will be an exception: Databasedotcom::SalesForceError. As an example, update app/controllers/tasks_controller.rb so the create method is as follows:

  def create
    task = Task.new(params[:task])
    task.IsRecurrence = false
    task.IsReminderSet = false
    task.Priority = "Normal"
    user = User.first
    #task.OwnerId = user.Id
    begin
      if (task.save)
        redirect_to(task, :notice => 'Task was successfully created.')
      end
    rescue Databasedotcom::SalesForceError => e
      redirect_to(tasks_path, :notice => "Error creating task: #{e.message}")
    end
  end

The action now catches the exception if there's an error when saving a new Task. The line assigning OwnerId is commented out, so an error will occur. Try creating a new Task now, and you'll see the Task list with the error message: "Error creating task: Assigned To ID: owner cannot be blank". (Don't forget to uncomment the missing line after testing!)

Use the Bulk API

When manipulating lots of data, consider using another gem that provides a simple interface to the Salesforce Bulk API. This API can be very efficient when dealing with significant updates or object creation, so it's worth a quick review in this article. You can find more information about the Bulk API on Github.

To test drive the Bulk API, add the gem to your app's Gemfile with:

gem 'salesforce_bulk'

Then:

bundle install

Fire up a Rails console and create the bulk client, as described in the gem's README:

> require 'salesforce_bulk'
> client = SalesforceBulk::Api.new("[email protected]", "mypasswordplusmysecuritytoken")
 => #<SalesforceBulk::Api:0x00000100e96370 @connection=#<SalesforceBulk::Connection:0x00000100e96348 @username="[email protected]", @password="...etc.

If the authentication fails, you won't get a very friendly error as of this writing (remember that this gem is quite new); the code will fail to parse the response and you'll see "NoMethodError: undefined method `[]' for nil:NilClass".

Warnings:

  • As was noted above for the databasedotcom gem, if your password contains special characters the authentication may fail. To be on the safe side, I recommend making sure your password is only letters and numbers (of course, keep in mind that this will make for a weaker password).
  • At the time of this writing, the salesforce_bulk gem can only authenticate using login.salesforce.com, meaning that it will not work if you're trying to use a sandbox at test.salesforce.com.

You can use the Bulk API to query, just as you can using the databasedotcom gem, though with a slightly different syntax. As an example, create a couple of Tasks in your account, and fetch the list:

tasks = client.query("Task","select id,whoid,activitydate,status,subject from Task limit 3")
 => [["00TG000000cM0ZUMA0", "", "2011-12-05", "Not Started", "Test 1"], ["00TG000000cM0bXMAS", "", "2011-12-05", "Not Started", "Test 2"]]

That's not such an interesting use of the Bulk API, though. Instead, create a bunch of tasks. The create method is simple: it wants an array of records, each of which is a Hash containing the fields for the record. So you can create 100 tasks very efficiently:

new_tasks = Array.new
100.times do |i|
        new_tasks << {'activitydate' => '2011-12-05', 'status' => 'Not Started', 'subject' => "more to do #{i}"}
end
result = client.create("Task", new_tasks)
 => [["00TG000000cM0gmMAC", "true", "true", ""], ...]

Now when you browse to your home page in your account and look at your tasks, you'll feel very bad because you have so much to do. So delete a bunch of them.

The results from the create method call are an array of arrays, and the first element of the inner array is the Salesforce Id of the Task. Thus, you can create a new array of elements to delete, because the delete method wants an array of Hashes, each containing the Id of a record to delete:

to_delete = Array.new
result[0..50].each do |r|        # delete half of them
        to_delete << {'id' => r[0]}
end
del_result = client.delete('Task', to_delete)
 => [["00TG000000cM0gmMAC", "true", "false", ""], ...]

That's better. Now you only have half as much work to do.

The salesforce_bulk gem also supports updating and upserting, which operate exactly as you would guess. Take a look at the examples on the salesforce_bulk gem's Github page and you'll be in good shape.

Conclusion

This article showed you how to get started integrating your Ruby code with Salesforce. As noted throughout, this is all pretty new and will doubtless evolve significantly in the future. You'll likely want to add these gems to your Github "watch" list. There's also a Google Group to follow, which is a valuable source of information and assistance.

About the Author

Mason Jones is Chief Architect, Information Products at RPX Corporation in San Francisco. He's worked at many startups over the years, from video advertising to peer-to-peer business process automation. After years spent developing in C and then Java, for the past several years he's found Ruby on Rails to be an enjoyable platform. Follow @masonoise on Twitter and read his technical blog.