Keeping Your Customers up to date.

Square customer management can usually meet all your needs, but at times you might need to keep your customer directory in sync with another customer database or order management system. Luckily some new changes to our APIs make that easier than ever.

Overview

In this post we will cover the use case of nightly syncs between a customer directory in Square and a different database. We’ll use a local SQLite database as the other database to sync customers to and Node.js for all of our programming. At a high level, we will use the SearchCustomers endpoint to find recently updated and added customers from our customer directory and then insert them into our SQLite database. Then we will do the same thing on the other side to sync customers back to Square that might have been added into our other database independently through other means. Remember, if you are using OAuth to access data belonging to a Square seller, you must have their permission to access or sync any data associated with their Square account to a different database.

Setup: Initializing the local database and getting API credentials.

Setting up my local database only needs a couple lines of Node.js thanks to the [sqlite3](https://www.npmjs.com/package/sqlite3) package. I am using a local file (database.db) to store my database and a table structure similar to the response bodies of the customer objects.

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('./database.db');
db.run("CREATE TABLE customers (id TEXT, created_at TEXT, updated_at TEXT, synced_at TEXT,given_name TEXT, family_name TEXT, email_address TEXT, creation_source TEXT)");

Getting access to my customer directory with Square’s APIs is just as easy, with a trip to the Square Developer Portal I can get my Personal Access Token that will allow me to access all of the API endpoints for my own Square account. I’m also going to be using the JavaScript SDK for Square’s APIs. I can add that to my app with npm install square-connect and require()ing it in my app.

Getting recently added & updated customers from Square.

There are two types of information changes that we might want to sync from our Square customer directory to our other database: new customers and customers who have had their information updated. Luckily the new SearchCustomers endpoint makes it easy to get both of those groups of customers with as few of API calls as possible.

var SquareConnect = require('square-connect');
SquareConnect.ApiClient.instance.authentications['oauth2'].accessToken = 'sq0atp-XXXX';

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('./database.db');


var apiInstance = new SquareConnect.CustomersApi();

var today = new Date();
var yesterday = new Date();
yesterday.setDate(today.getDate() - 1);

body = {
    'limit': 1000,
    'query': {
        'filter': {
            'created_at': {
                'start_at': yesterday.toISOString(),
                'end_at': today.toISOString(),
            },
        },
    },
};

apiInstance.searchCustomers(body).then(function(data) {
    if (data.customers) {
        data.customers.forEach(function(element) {
            db.run('INSERT into Customers (id , created_at , updated_at , synced_at ,given_name , family_name , email_address , creation_source ) VALUES( $id,  $created_at,  $updated_at, $synced_at, $given_name, $family_name , $email_address , $creation_source )', {
                $id: element.id, $created_at: element.created_at, $updated_at: element.updated_at, $synced_at: today.toISOString(), $given_name: element.given_name, $family_name: element.family_name, $email_address: element.email_address, $creation_source: 'Square: ' + element.creation_source,
            });
        });
    }
}, function(error) {
    console.error(error);
});

To get the customers that were recently created, we’ll use the created_at filter with SearchCustomers. This allows us to filter all of the returned results to only those that were created within a window of times that we specify. This code looks for the customers that were created within the last day, but it could just as easily poll for customers made within the last 5 minutes, or use a timestamp that the directories were last successfully synced to be the start of the window. We could also use the ListCustomers endpoint with the sort_field parameter to list our customers by their creation date, and then just go through that list until we reach our last synced date. Search customers is a better choice since it has the ability to return more customer entries as well as only the ones within a specified window of time.

body = {
    'limit': 1000,
    'query': {
        'filter': {
            'updated_at': {
                'start_at': yesterday.toISOString(),
                'end_at': today.toISOString(),
            },
        },
    },
};

Getting customers that were recently update is just as easy. We’ll swap out the created_at parameter for updated_at in our request body and specify the same time window. This is a big improvement over using the ListCustomers endpoint and having to sync the entire customer directory to find any updates.

This approach is somewhat naive, and doesn’t handle paging. If you are syncing more than 1000 newly created or updated customers for a given time window, you will get back a cursor parameter that you will need to use to get the next page of results. You can learn more about pagination in my previous post *Tips and Tricks for API Pagination.*

Syncing customers to Square

The base case here is pretty simple as well, but in practice it will probably be a little more complex for your own database. We can select the rows that are not in our Square Customer Directory and then run them through the CreateCustomer endpoint. We could identify these customers in many different ways, a source field, entries that don’t have a Square id yet, or looking at customers that were created since the last time the databases have been synced.

var apiInstance = new SquareConnect.CustomersApi();

db.each("SELECT * FROM Customers WHERE ...", function (err, row) {
    apiInstance.createCustomer({
        given_name:row.given_name,
        family_name:row.family_name,
        email_address:row.email_address
    }).then(function (data) {
        console.log('API called successfully. Returned data: ' + data);
    }, function (error) {
        console.error(error);
    });
});

Next Steps

This is just a taste of some of the useful situations the new features in our Customers APIs can be used for. Remember, you will likely need to consider some of the trade offs when implementing this with your own system. Should you sync every day, week, hour? How will your data models be translated between systems? Your implementation will differ depending on how many customers you have, where you sync them to, and what you want to do with that customer information. If you have any questions, or want to share examples of how are syncing customers, feel free to respond to this post, tell us on twitter or our slack community, and sign up for our newsletter to stay up to date with new releases.