Powering modern web applications with Microsoft Dynamics CRM

This post is for you if your business or organization uses Microsoft CRM as a core technology in organizing your business, and your development team wants to present some of that data using modern web application frameworks.

In this post, we’ll lay out how to build a simple, read-only Rails API using Microsoft Dynamics CRM as a data store. Even if you are using an API framework other than Rails, this post may still help you understand some of the considerations that go into working with this kind of infrastructure.

This post is Part 1 of what could potentially be many parts. Here, we will cover the setup, model creation, model relationships, API design, and initial deployment. By the end, you’ll have an API endpoint that displays data from Microsoft CRM. Future posts may include: setting up more complex model relationships, processing and displaying images, and defining custom links in the API.

Last year, the Digital Services team was approached by our Strategic Initiatives team to create MetroFuture in Action, a showcase of the projects our agency works on, and how those projects contribute to our long-term regional plan, MetroFuture.

Why CRM?

The core requirement of MetroFuture in Action was that we show up-to-date project information on all of our projects — and there are hundreds. We know how much work our dedicated staff does, so we wanted to avoid asking them from doing anything outside of their normal workflow. Like many organizations, our core business information system is powered by Microsoft Dynamics CRM. All of our projects, staff, and contacts are stored here, so we realized that the best source of reliable project information — information that would be kept updated in the normal course of business — was CRM.

If we had built a new web form to input project information, we would have had the ease of working with a standard Rails-and-Postgres setup, but we would not have been able to incentivize staff to keep project information up to date in a second location.

Let’s build a CRM-backed Rails API.

The decision to use CRM was, from a staff culture standpoint, the absolute right decision. But technically, this is no easy feat. So let’s build it together, step by step.

Getting Started

Microsoft CRM uses a Microsoft SQL Server as a database, which we’ll need to connect to in order to extract the data we want. CRM itself is a skin for the Microsoft SQL Server database, as will be our Rails application — so we’re going straight to the database.

We developed the app while connected to our production SQL Server database. I think the ideal course of action would have been to copy some data from the tables we were using to a separate database, and develop using that.

Let’s start a new API-only Rails 4 application with SQL Server as the database.

$ gem install rails -v 4.2.5.1
$ gem install rails-api
$ rails-api new my-crm-api --database=sqlserver

This will generate SQL Server-specific database config in a fresh Rails app, but let’s hold off on filling that in for now — we don’t have our database credentials yet.

Connecting to SQL Server for the first time

I asked my IT staff to create a read-only user account with an extremely complex password. The API is “for display only” — CRM should manage CRM data, not our API. With a read-only user, no matter what we tried, we wouldn’t be able to accidentally modify or delete data.

We’re asking a UNIX machine to speak to a Microsoft database, so we need a bridge to connect the two. So, on a Mac or Linux machine, you’ll need a library called FreeTDS to connect to the SQL Server database powering your CRM instance. Install Homebrew if you haven’t already, then run:

brew update && brew install freetds

to get the latest version of FreeTDS.

Once it’s installed, use the `tsql` command with the connection info to make sure you can connect.

$ tsql -H <host> -d <dbname> -p <port> -U <username> -P <pass>

If you can connect, great!

If you can’t connect to the CRM database server with tsql, it could be for a variety of reasons. The most likely one is that port 1433, the default Microsoft SQL Server port, is closed to public access on the CRM database server, and you may need to open it. Our IT staff are primarily Windows admins, and Digital Services is primarily UNIX-based web developers, so we needed to work with our IT staff to get the port opened to the IP addresses our app would be coming from. (We talk about IP whitelisting as it relates to Heroku in the section on deploying.)

Update the Gemfile to add the ActiveRecord SQL Server Adapter, as well as Tiny TDS, a library that will use FreeTDS to connect to ActiveRecord.

# Gemfile.rb
# Truncated: see https://github.com/MAPC/metrofuture-server/blob/develop/Gemfile for our full Gemfile
ruby '2.1.5'
gem 'rails', '4.2.5.1'
gem 'rails-api'
# Support the JSONAPI.org Standard
gem 'jsonapi-resources', 
  git: 'git://github.com/AKHarris/jsonapi-resources.git',
  branch: 'feature/custom-links-defined-per-resource'
# Connect your database to CRM
gem 'activerecord-sqlserver-adapter', '4.1.0'                       gem 'tiny_tds'

Then run`bundle install`.

Our database configuration file is a little unusual, so let’s walk through that. In development, we use Foreman to manage our environment, so we use environment variables in a .env file. You could also hardcode these values in database.yml, but going the Foreman/.env route means there’s just about zero risk of passwords being committed and leaked.

# config/database.yml
default: &default
  adapter: sqlserver
  encoding: utf8
  reconnect: <%= ENV.fetch('DB_RECONNECT', false)                %>
  username:  <%= ENV.fetch('DB_USERNAME', '')                    %>
  password:  <%= ENV.fetch('DB_PASSWORD', '')                    %>
  host:      <%= ENV.fetch('DB_PORT_5432_TCP_ADDR', 'localhost') %>
  port:      <%= ENV.fetch('DB_PORT_5432_TCP_PORT', '1433')      %>
  timeout: 25

We host this application on our own infrastructure, which allows us to use the database.yml in combination with environment variables. In the deployment section at the bottom of this article, we cover how to deploy on Heroku and set up the DATABASE_URL variable properly.

Writing the Project model

Our first priority with our API is to present basic project information like name, website / project URL, and description. Each project has a location (city or town), as well as a project manager (one of our staff members).

There are tools that claim to produce model files based on CRM / SQL Server models, but we haven’t used those. Plus, our project is simple enough to start from scratch.

What Rails calls a “model”, Microsoft Dynamics CRM CRM calls an “entity”. CRM 2007 has two tables for each entity— a “base” table used by the system with a few attributes including the ID, and an “extension” table which stores all of the custom fields that your CRM admin defines on each entity.

We used the free version of Navicat to connect to our SQL Server, and poked around the tables to understand the structure of the data. The right table for your model can be difficult to discern, because unlike Rails, CRM does not necessarily create straightforward table names. For example, our Project model had a “base” table named “new_mapcprojectBase”, and an extension table named “new_mapcprojectExtensionBase”.

We decided, in order to keep the concepts simple, to map our models to CRM tables, 1:1. That meant we would have a Base Project class and an Extension Project class which would relate to each other using CRM tables’ primary keys.

We namespaced the extension classes in an Extension module, so the project extension entity is represented in Rails as Extension::Project. I think we could have easily flipped them as Project::Base and Project::Extension, if we had wanted to.

# app/models/project.rb
class Project < ActiveRecord::Base
  self.table_name = 'new_mapcprojectBase'
end
# app/models/extensions/project.rb
class Extension::Project < ActiveRecord::Base
  self.table_name = 'new_mapcprojectExtensionBase'
end

The CRM default is to name the primary key something long and descriptive, so we need to tell Rails to pay attention to that. It may take a little searching, but we found that the primary key for each table was the table name of the base class, minus “Base”, plus “Id”, so, “new_mapcprojectId”.

# app/models/project.rb
class Project < ActiveRecord::Base
  self.table_name  = 'new_mapcprojectBase'
  self.primary_key = 'new_mapcprojectId'
end

# app/models/extension.rb
module Extension
  # No content, just an empty module to hold the extension classes.
end

# app/models/extensions/project.rb
class Extension::Project < ActiveRecord::Base
  self.table_name  = 'new_mapcprojectExtensionBase'
  self.primary_key = 'new_mapcprojectId'
end

While we’re here, let’s hook up the connection between the Base class and its Extension, so that when we have the Base, we also always have a reference to its extension.

# app/models/project.rb
class Project < ActiveRecord::Base
  self.table_name  = 'new_mapcprojectBase'
  self.primary_key = 'new_mapcprojectId'
  has_one :extension,
    class_name:  'Extension::Project',
    foreign_key: 'new_mapcprojectId'
  default_scope { includes(:extension) }
end
# app/models/extensions/project.rb
class Extension::Project < ActiveRecord::Base
  self.table_name  = 'new_mapcprojectExtensionBase'
  self.primary_key = 'new_mapcprojectId'
  belongs_to :base,
    class_name:  'Project',
    foreign_key: 'new_mapcprojectId'
end

You’ll notice that we added a default_scope that includes the Extension class, so it’s always around and ready without having to query the database again to get it.

We thought about creating a CRMBase and CRMExtension abstract classes from which our models would inherit. These would automatically define the relationships to between base and extension tables using some metaprogramming, but we haven’t yet implemented that.

Aliasing complicated field names

One downside of using CRM is that it’s not going to be the elegant, clean code you might expect from a typical Rails application — working with CRM might make you appreciate the thought that went into defining Rails conventions to make them human-friendly.

Custom fields are always defined on the extension entity in CRM, so let’s take a look at the table and build out the Extension::Project model.

At first glance, we see column names like:

• new_name
• new_Showonwebsite
• new_ShortDescription
• new_LocationCity
• new_LocationState
• new_Website
• new_count

Purely from a time and energy perspective, I’m disinclined to write “new_” whenever I’m referencing a field name in the console or elsewhere. That prefix — I’m assuming a default in CRM —only confuses the real meaning of the data. Lastly, the joint snake_casing and CamelCasing of column names goes against Ruby’s convention of using plain_old_snake_case.

Let’s make our lives easier by writing Rubyish aliases for all of these attributes, so we never have to write “new_LocationCity” when we really mean “city”.

# app/models/extensions/project.rb
class Extension::Project < ActiveRecord::Base
  self.table_name  = 'new_mapcprojectExtensionBase'
  self.primary_key = 'new_mapcprojectId'
  belongs_to :base,
    class_name:  'Project',
    foreign_key: 'new_mapcprojectId'
  # Alias attributes in the extension class, since
  # custom attributes are defined on this table.
  
  alias_attribute :title,   :new_name
  alias_attribute :desc,    :new_ShortDescription
  alias_attribute :city,    :new_LocationCity
  alias_attribute :state,   :new_LocationState
  alias_attribute :website, :new_Website
  alias_attribute :number,  :new_count
  alias_attribute :public,  :new_Showonwebsite
  alias_attribute :public?, :new_Showonwebsite
end

Note: I don’t know what happens when you `alias_attribute :id, :your_crm_id_column`, because I didn’t try it.

If you have a lot of fields, I recommend breaking it out into a module and include it. I call mine FieldAliases because Aliases is a namespace conflict. In this example from a different project, I do this with concerns, but it’s not a great architecture decision and I don’t recommend it.

Delegating attributes

In controllers and elsewhere, we’ll be querying the base model, but we’ll need the attributes of the extension module accessible. Let’s add the Forwardable module, and direct method calls on the base model to the extension.

# app/models/project.rb
class Project < ActiveRecord::Base
  
  # previous lines omitted
  # The %i method returns an array of symbols.
  delegate %i(
    title desc city state website number public public?
  ) => :extension
end

Now, when we call Project#state, it will send the #state method to Extension::Project.

Appropriate scope

Not all of the projects in CRM are ready for public display through our API. We only want to show projects that project managers decide to show, so let’s set a default scope on the Project model. Normally I steer away from default scopes, but this is the appropriate time for one — we don’t want to display any projects that aren’t approved for public viewing by CRM maintainers.

You’ll notice we have a column in the extension entity, “new_Showonwebsite”. Let’s scope our base model so we only show those projects which are set to be shown on the website. Because this attribute is on the extension class, but we’ll be querying on the base class, we use a ‘where’ query that references the relation and its newly-aliased attribute.

# app/models/project.rb
class Project < ActiveRecord::Base
  self.table_name  = 'new_mapcprojectBase'
  self.primary_key = 'new_mapcprojectId'
  has_one :extension,
    class_name:  'Extension::Project',
    foreign_key: 'new_mapcprojectId'
  default_scope { 
    includes(:extension).
      where(extension: { public: true })
  }
end

Developing the API

At MAPC, we skip the bikeshedding — the turmoil about how to represent data — in designing our APIS, and we use the JSONAPI.org standard. Thankfully, Cerebris created the wonderful gem known as JSONAPI Resources that does most of the API work for us.

Once you install it, generate a Project resource. At this stage in the process, it might look like:

# app/resources/project.rb
class ProjectResource < JSONAPI::Resource
  key_type :uuid
  attributes :title, :city, :state, :website, :number
  attribute :description
  def description
    @model.desc
  end
end

CRM uses UUIDs as their IDs, and we need to tell JSONAPI Resources to expect that. Then, we alias our `desc` attribute as `description` in the API.

At this point, we should have something working! Add a few projects via the console and take a look.

The Second Model

Earlier, we mentioned that each project has a project manager, and each project manager is a staff member. Let’s set up a Project Manager model relationship so that we can show the project manager with the project.

# app/models/project_manager.rb
class Project < ActiveRecord::Base
  # previous lines omitted
  belongs_to :manager,
    class_name:  'StaffMember',
    foreign_key: 'OwnerId'
end

This took quite a bit of hunting, but in our CRM implementation, the project manager for a project is the staff member who “owns” the project. We connect the two via the OwnerID column.

We also discovered that there was one type of ownership that signified a project management relationship, and by poking through the database, saw that it was indicated by the integer 8.

# app/models/staff_member.rb
class StaffMember < ActiveRecord::Base
  self.table_name =  'OwnerBase'
  self.primary_key = 'OwnerId'
  OWNER_ID_TYPE = 8 # Project manager relationship
  default_scope { where :OwnerIdType => OWNER_ID_TYPE) }
  has_many :projects,
    class_name:  'Base::Project',
    foreign_key: 'OwnerId'
  alias_attribute :name, :Name
end

Now that the models are related, generate the JSONAPI resource for this model. All we want to do is show the name, and for now, we’re not interested in showing all of the projects that belong to the manager, so we don’t define a relationship here.

class StaffMember < JSONAPI::Resource
  key_type :uuid
  attribute :name
end

Then, update the Project resource to display the project manager information.

# app/resources/project.rb
class ProjectResource < JSONAPI::Resource
  key_type :uuid
  has_one :manager  # <-- Add this line
  attributes :title, :city, :state, :website, :number
  attribute :description
  def description
    @model.desc
  end
end

And if you want to see all the project manager information included in the project index, request the relationship the way the JSONAPI.org standard lays out, with a “include=relationship-name” parameter.

http://localhost:3000/projects/?include=manager

And there you have it! A project API that shows related resources, all with CRM as a backend.

Deploying

To deploy this on Heroku, you need to make sure the FreeTDS libraries are installed before your Rails application is set up, because FreeTDS has to be in place before the tiny_tds gem is installed with your app.

Set the buildpacks using Heroku’s documentation on using multiple buildpacks, so that the foraker/heroku-buildpack-freetds comes before the standard heroku/ruby buildpack.

At the end of this process, a `heroku buildpacks` command should return the following:

$ heroku buildpacks
=== my-crm-api Buildpack URLs
1. https://github.com/foraker/heroku-buildpack-freetds
2. heroku/ruby

You may also need to do a little extra to set up your database.

Heroku doesn’t read from the database.yml, instead using the DATABASE_URL environment variable. Heroku also adds a standard Postgres database to every new app, so we’ll have to remove that first.

Remove the Postgres database from the app (see Resources) in the app’s admin console, and then set the DATABASE_URL environment variable to your SQL Server. It will look something like this:

heroku config:set DATABASE_URL=sqlserver://username:password@host:port_probably_1433/database_name?reconnect=true

What’s the deal with the`?reconnect=true` bit? The standard parameters in a database URL are scheme, username, password, host address or IP, port, and database name. However, sometimes we need to add additional parameters, like `schema_search_path` in Postgres or `reconnect` in SQL Server. We can pass those extra variables along as regular URL parameters.

For example, if you needed to set encoding, timeout, and reconnect to something other than the defaults, after `database_name`, you could set the DATABASE_URL variable to something like this:

sqlserver://username:password@host:port_probably_1433/database_name?encoding=utf16&timeout=100&reconnect=true

Digital Services at MAPC is available for consulting on setting up Rails APIs and more, regardless of whether they use CRM. Feel free to connect with us at data@mapc.org if you have questions or want to build something together.