By Rudderstack Team

How to load data from Xero to PostgreSQL

This post helps you with loading your Xero data to PostgreSQL. If you are looking to get analytics-ready data without the manual hassle, you can integrate Xero to PostgreSQL with RudderStack, so you can focus on what matters, getting value out of your business data.

Extract your data from Xero

Xero has an excellent API, or to be more precise, a number of APIs, and encourages developers to build applications that can be sold on their add-on marketplace. The APIs that they expose are the following:

  • Xero Core (Accounting) API - exposes accounting and related functions of the main Xero application and can be used for various purposes, such as creating transactions like invoices and credit notes, right through to extracting accounting data via our reports endpoint.
  • Xero Payroll API exposes payroll-related functions of Payroll in Xero and can be used for various purposes, such as syncing employee details, importing timesheets, etc.
  • Files API – provides access to the files, folders, and the association of files within a Xero organization.
  • Fixed Assets API – which is under review. This feature is not yet available, but users can vote for it to become publicly available.
  • Xero Practice Manager API – a recently released product built on the WorkflowMax product. Which is an API for managing workflows

In this post, we’ll focus on the Xero Core (Accounting) API, which exposes the core accounting functionalities of the Xero product. The Xero API is a RESTful web service and uses the OAuth (v1.0a) protocol to authenticate 3rd party applications. As an API, you can interact by using tools like CURL, Postman, or using HTTP clients for your favorite language or framework. A few suggestions:

  • Apache HttpClient for Java
  • Spray-client for Scala
  • Hyper for Rust
  • Ruby rest-client
  • Python http-client

As a product and consequently an API that has to deal with sensitive data, Xero API takes really good care of security. For this reason, there are a number of different applications that can be developed and integrate with it, where the main difference is how the application authenticates, how often the tokens expire, and in general security-related aspects.

For more about the different application types, you can consult the application types guides on their documentation.

Xero API requests limits

The Xero API has three different types of limits that enforce the usage of their API. It’s extremely important to keep those in mind when developing against its API and a reason for many headaches when someone attempts to build an infrastructure for extracting data from it.

  • Daily limit – of 1000 API calls per organization.
  • Requests per minute – each OAuth access token can be used up to 60 times in any 60 second period. This rate limit is based on a rolling 60-second window.
  • Request Size Limit – A single POST to the Accounting or Payroll APIs has a size limit of 5MB.

For more information about the API limitations, please consult the documentation for API limits.

Xero API Resources

Requesting data from the Xero API

Let’s assume that you would like to retrieve all the invoices that you have issued through Xero and put the information in your data warehouse to perform analytics and reporting. To do that you should perform a GET request to the https://api.xero.com/api.xro/2.0/Invoices endpoint. A typical result, in XML, from performing such action is like the following: