Integration of Dolibarr to the Airbyte data integration platform
Introduction
Airbyte (https://airbyte.com/) is an open-source ELT platform that allows to create data integration streams with no code interface, in this tutorial will be explained the use of the Dolibarr source connector available in the marketplace of the different options of Airbyte installations. This connector also could be edited, personalized, and updated to contribute by the users through the graphic UI Connector Builder of the Airbyte platform.
Prerequisites
For this guide you need to complete the following prerequisites:
- Have an instance of Dolibarr (in cloud SaaS service, on-premises installation with a public URL or in a cPanel server or hosting server) and configure it to use SSL encryption (with the HTTPS protocol)
- Have a Dolibarr user account with administrator rights.
- Activate the “Web Services API REST” module following the official documentation
- Generate an API key for your current admin user account or create a new user to generate in this new user the API Key that you will use to execute the data integration streams.
- Activate the Dolibarr modules which you want to get data from.
- Generate operational data in Dolibarr by your business activity.
- Give read permissions, to the user with the API key generated, for the modules that will be source of data in the data extraction streams. (Some streams or end points, like GET company information, will require that your user be administrator or have read/write permissions to works fine with the GET end point)
Create the Airbyte Connection
Access to your Airbyte installation.
If you don’t have it, you could follow the next guide: https://docs.airbyte.com/platform
For the Open Source version: https://docs.airbyte.com/platform/using-airbyte/getting-started/oss-quickstart
When you have your Airbyte instance, now you can create a connection where you will select a source and destination. In this guide we will only do the Dolibarr source connector configuration, but you can select any destination connector from the available options and follow the specific configuration guide for each one, or you can create your own destination connector with the UI Connector Builder.
Select Dolibarr source connector.
In the home page of your Airbyte installation select the bottom “Create Connection”, then you will be able to select the source connector from a list. You must select Dolibarr connector from the Marketplace connectors list.
You can find additional documentation of the Dolibarr source connector in: https://docs.airbyte.com/integrations/sources/dolibarr
Enter the authentication credentials for the Dolibarr connector:
After selecting the Dolibarr source connector you will be required to enter the public domain of your Dolibarr instance (please follow the Dolibarr connector instructions that appears in Airbyte) and the API key that you previously configured for your Dolibarr user.
Additionally, you must enter the “Start Date” that defines the oldest date of the data you want to synchronize.
Airbyte will make a connection test previously to save the configuration and let you continue to the destination configuration. For the destination you must follow the configuration guide for your selected connector.
Select the sync mode and streams for the schema:
After the source and destination connector configuration, you need to select the sync mode you want and the streams and fields you want to include in your schema of the data you will extract and synchronize. For each stream in your schema, you will be able to select the sync mode, is recommended to select “Full refresh | overwrite” initially and if you want to enable the incremental sync modes you will need to validate the compatibility with your destination doing some sync tests.
As a guide to select the streams and fields for your schema you could keep in mind the streams and fields description table below. Some fields where modified or created inside the Dolibarr source connector and may not match with the original Dolibarr database field names.
Remember that your Dolibarr user with the API key must have the adequate permissions for the modules related with the streams and fields you are extracting for synchronization.
Overview of streams or data tables with important column names on each sheet and their corresponding meaning
| - Product list stream (list of all products) | |
| barcode | Product barcode |
| cost_price | Purchase price (manually defined cost price) |
| date_creation | creation date in Unix timestamp format |
| date_modification | Date of modification in Unix timestamp format |
| label | Product Name |
| pmp | Average purchase price (cost price calculated from supplier invoices) |
| price_min_ttc | minimum sale price (maximum discount) |
| price_ttc | Sale price |
| product_id | Product Identifier |
| ref | Product reference |
| status | Availability for sale status |
| status_buy | Availability for purchase status |
| stock_reel | actual inventory to date |
| stock_theorique | Theoretical inventory stocks to date |
| - Products category id list stream (listing of all product ids with their respective product category id) | |
| category_id | Product Category Identifier |
| product_id | Product Identifier |
| - Product categories list stream (listing all product categories) | |
| category_id | Product Category Identifier |
| date_creation | creation date in Unix timestamp format |
| date_modification | Date of modification in Unix timestamp format |
| description | Product Category Description |
| label | Product Category Name |
| - Customer invoices list stream (list of all sales invoices to customers to date) | |
| customer_id | Identifier of the customer to whom the sale was made |
| date_creation | creation date in Unix timestamp format |
| date_modification | Date of modification in Unix timestamp format |
| date_validation | validation date in Unix timestamp format |
| invoice_id | Customer invoice ID |
| module_source | Invoice generator module |
| multicurrency_code | Used Currency Code |
| paye | invoice payment status |
| pos_source | POS-generated invoice status |
| ref | Customer Invoice reference |
| total_ttc | Total amount of the invoice including taxes. |
| totalpaid | Total invoice payment amount |
| user_creation_id | Creator User ID |
| user_validation_id | validating user identifier |
| - Customer invoices lines list stream (list of all product lines associated with all customer invoices) | |
| description | Manually entered product or service name |
| invoice_id | ID of the customer invoice to which the line corresponds |
| marge_tx | Profit margin over the purchase price of the line |
| marque_tx | Profit margin over the sale price of the line |
| multicurrency_code | Used Currency Code |
| product_barcode | Product barcode |
| product_id | product identifier on the line |
| product_label | Product Name |
| product_ref | Product reference |
| qty | Quantity of products |
| total_sell_price | Total sale price of the line |
| unit_buy_price | Purchase unit price or cost of the product in the line |
| unit_sell_price | unit selling price of the product in the line |
| - Customer invoices payments list stream (list of payments associated with each customer invoice) | |
| amount | total amount of the payment. |
| date | date of payment. |
| invoice_id | Identifier of the invoice to which the payment belongs. |
| num | Reference of the customer invoice to which the payment belongs. |
| ref | Payment identifier. |
| type | type of payment (CB: credit card, LIQ: cash, PRE: debit payment order, RAP: Rappi transfer, VIR: bank transfer). |
| - Customers list stream (list of all customers) | |
| address | Residence address. |
| client | client status. |
| code_client | Customer reference code. |
| country_code | country code of residence. |
| customer_id | customer identifier. |
| date_creation | creation date in Unix timestamp format |
| date_modification | Date of modification in Unix timestamp format |
| Email address | |
| fournisseur | supplier status. |
| idprof1 | R.U.T. |
| idprof2 | Citizenship card/NIT/Foreigner's card |
| name | Customer's name. |
| name_alias | short name of the client. |
| phone | telephone. |
| prospect | Prospect status. |
| status | active or inactive status. |
| town | city or town of residence. |
| url | Website Address |
| - Supplier invoices list stream (list of all supplier purchase invoices to date) | |
| date_echeance | payment deadline in Unix timestamp format |
| datec | creation date in Unix timestamp format |
| multicurrency_code | Used Currency Code |
| paye | invoice payment status |
| ref | Supplier invoice reference |
| supp_invoice_id | Supplier invoice ID |
| supplier_id | Supplier ID from whom the purchase invoice was received |
| tms | Date of modification in Unix timestamp format |
| total_ttc | Total amount of the invoice including taxes. |
| user_creation_id | Creator User ID |
| user_validation_id | validating user identifier |
| - Supplier invoices lines list stream (listing of all product lines associated with all supplier invoices) | |
| buy_unit_price | Purchase unit price or cost of the product in the line |
| multicurrency_code | Used Currency Code |
| product_barcode | Product barcode |
| product_id | product identifier on the line |
| product_label | Product Name |
| product_ref | Product reference |
| qty | Quantity of products |
| supp_invoice_id | Identifier of the supplier invoice to which the line corresponds |
| total_buy_price | Total purchase price of the line |
| - Supplier invoices payments list stream (list of payments associated with each supplier invoice) | |
| amount | total amount of the payment. |
| date | date of payment. |
| ref | Payment identifier. |
| supp_invoice_id | Identifier of the invoice to which the payment belongs. |
| type | type of payment (CB: credit card, LIQ: cash, PRE: debit payment order, RAP: Rappi transfer, VIR: bank transfer). |
| - Suppliers list stream (list of all suppliers) | |
| address | Residence Address |
| client | Client status |
| code_fournisseur | Supplier reference code |
| country_code | Country code of residence |
| date_creation | creation date in Unix timestamp format |
| date_modification | Date of modification in Unix timestamp format |
| Email address | |
| fournisseur | Supplier status |
| idprof1 | R.U.T. |
| idprof2 | Citizenship card/NIT/Foreigner's card |
| name | Supplier Name |
| name_alias | Supplier's short name |
| phone | telephone |
| prospect | Customer Prospect Status |
| status | Active or inactive status |
| supplier_id | Supplier Identifier |
| town | city or town of residence |
| url | Website Address |
| - Internal Users stream (list of company users in the ERP/CRM system) | |
| address | Residence Address |
| country_code | Country code of residence |
| datelastlogin | date of last login in Unix timestamp format |
| datem | date of user modification in Unix timestamp format |
| datepreviouslogin | date of prior entry into the system in Unix timestamp format |
| User's email address | |
| firstname | User Name |
| job | role or job within the company |
| lastname | User's Last Name |
| office_phone | Work Office Phone |
| user_id | User Identifier |
| user_mobile | cell phone |
| - Company profile data stream (ERP/CRM company profile data) | |
| address | Business or premise location address |
| company_id | Company identifier in the ERP system |
| country_code | country code of operation |
| default_lang | Default language |
| Email address | |
| idprof1 | R.U.T. |
| idprof2 | Legal ID/NIT of the company |
| managers | Names of the administrators |
| name | Legal Company name |
| phone | telephone |
| socialnetworks | Social Media URLs |
| town | city or town of company location |
| url | Website Address |
| zip | zip code |
Final connection configuration:
Finally, you must configure the name, schedule type, frequency and destination namespace for your connection. After these definitions you could save your connection and Airbyte will execute the first synchronization.
Go further
The Dolibarr source connector in Airbyte will let you modify and customize the streams, fields and API methods used by editing the connector in the UI Connector Builder, is important that you explore the Airbyte public documentation (https://docs.airbyte.com/platform/connector-development/connector-builder-ui/overview ) to make improvements to your connectors and get the maximum benefits of the Dolibarr data integration with this tool and contribute to the connector update.
Conclusion
Airbyte is versatile and powerful open source tool that allows you to create a no code data integrations between Dolibarr and many third-party applications using the Web services API REST to explode your operational data in analytics, business intelligence, AI tools to have added value from your data to make business decisions.