In the SugarCRM integrations types article , we went over some of the varying ways you could integrate data with SugarCRM and in this article what we are going to dive into some of the pros and cons using a database integration or an API level integration.
Sometimes you only have one option. If you are using an older system such as an AS/400 system or an older ERP system, it may not have an API layer that you can integrate to. This mean your only options is to do a database integration using ETL tools or scripts. When it comes to other systems like a lot of the newer marketing systems, you are going to have an API.
Your option on the SugarCRM side is also a database or API level integration if you are hosted on-site. SugarCRM on-demand however, does not allow any direct database access. With SugarCRM on-site, you have database access and we see a lot of companies do database to database level integrations. They will use an ETL tool or a type of integration tool that will pull data straight out of the ERP system from their database and import it directly into the SugarCRM database. A lot of people are also more skilled with SQL triggers, such as an MS SQL environment. They will use stored procedures and triggers or other items instead of adding the cost of an integration tool.
As we continue, there are a few pros and cons to using each of these. The first thing we are going to go over is the API integration. Let’s look at this from the SugarCRM side. It is always recommended that you push data into SugarCRM using the API. The reason is, the API has all of the triggers built in that kick off workflows, logic hooks, and everything else that has been programmed into SugarCRM. If you push in via the API, it adheres to roles and permissions, plus workflow triggers off of that data type with SugarCRM advance workflows in SugarCRM Enterprise or with Process Manager add-on from SierraCRM. This ensures all of those are going to work accordingly because you utilized the API and the framework that SugarCRM provides.
Now your alternative is the database and if you push directly into the database then you bypass all of those triggers we just mentioned. You’re also bypassing any data validation routines that are built into SugarCRM, which allow you to bypass the data security mechanisms. This could result in you or your team putting in poor data that then gets reflected improperly through the UX for the user. This can cause other issues if you are not doing enough validation to make sure your formatting is correct. If you are pushing this through the API and there is an error, you will receive a response from the API that there is an error and what that error is.
What are the ways or reasons you would want to integrate with the database? High volume is one reason. What generally happens is the SugarCRM API is only built to take so many inserts and queries at a time. If you are hitting a lot of those limits and the data absolutely has to be done in a certain timing situation, where timing is absolutely crucial and you’re trying to push thousands of records into SugarCRM, maybe tens of thousands of records a minute then the database is always going to offer faster times. If you don’t have any triggers or logic hooks that have to be kicked off from that data, then it is a perfectly safe thing to do and you’re going to get the speeds and throughput that you need.
The last thing I’d like to mention is that later versions of SugarCRM in the 7x series have a bulk API importing capability as well, so instead of inserting and having to insert one record per API call which causes a bottleneck, you can insert multiple records per call. Sometimes this can be a little harder if you have to do a lookup to map data before you do an insert but that is a capability that SugarCRM offers via the API interface and offsets some of the speed issues of going to the database.
So as a recap, when possible use the API interface for SugarCRM and also use the rest API because it will be a lot faster, which will hopefully make your life a little easier.