Data integration service

Fliplet Agent (Data integration service) is a command line utility to synchronize data to and from Fliplet Servers.

Fliplet/fliplet-agent - GitHub


Before you start

To synchronize your data with Fliplet servers you will need an authorisation token generated via Fliplet Studio from an organisation admin account.

To generate a token, please follow the docs here.


Install

Our software runs on Node.js, a popular open-source and cross-platform software.

Please install node.js (which also comes bundled with the popular package manager npm) on your computer as a pre-requisite to get started.

Once you have installed Node.js, please open the shell to continue and install our agent:

  • Windows: access the Node.js shell from Start menu > Node.js command prompt
  • Mac OSX: access the shell of your computer from Applications > Utilities > Terminal
  • Unix: access the shell of your computer

Then, run this simple command to install the Fliplet Agent on your machine via the npm package manager:

npm install fliplet-agent -g

You can now use the command fliplet-agent from the command line. Just type fliplet-agent to ensure the software is installed and also see the available options and their example usage.


Update the agent to the latest version

If you need to update the agent to the latest version available on npm, run the following command from the Node.js shell:

npm update -g

Releases changelog

1.4.0 (June 12th, 2019)

1.3.1 (April 12th, 2019)

  • Security patch for the following third-party packages: Sequelize, js-yaml.

1.3.0 (April 3rd, 2019)

  • Support for installing the Agent as a Windows service.

1.2.2 (Feb 8th, 2019)

  • Fixes when deleting columns with non-numeric primary keys.

1.2.1 (Dec 28th, 2018)

  • Added User-Agent to all outgoing requests to improve security.

1.2.0 (Dec 27th, 2018)

  • Support for pulling data via external APIs or from File System.

Get started

Create a simple file with with .yml extension (or grab a sample copy here) somewhere in your filesystem with the following configuration details and replace with your own settings where appropriate:

# Fliplet API authorisation token taken from Fliplet Studio. More documentation available at:
# https://developers.fliplet.com/REST-API/authenticate.html#how-to-create-an-authentication-token
auth_token: eu--123456789

# Define the ID of the target Fliplet Data Source where data will be pushed to.
# This ID can be retrieved from the "Manage app data" section of Fliplet Studio
# under the settings tab. Don't forget to give access to this data source to your token.
# More documentation is available at:
# https://developers.fliplet.com/REST-API/fliplet-datasources.html#access-roles
datasource_id: 123

# Database connection settings below

# Possible values for the driver are: mysql, sqlite, postgres, mssql
database_driver: mssql
database_host: localhost
database_username: sampleuser
database_password: 123456
database_port: 5432
database_name: eu

# MSSQL Server only: uncomment if you need to use these variables.
# database_domain: sampleDomainName
# database_instance: sampleInstanceName
# database_encrypt: true

# ODBC Native driver only: uncomment this and install the driver on your computer
# by pasting this command to the terminal: "npm install sequelize-odbc-mssql -g"
# database_native_odbc: true

# Description of the operation (will be printed out in the logs).
description: Push my users to Fliplet every 15 minutes

# If set to true, the sync will also run when the script starts.
# Otherwise, it will only run according to the frequency setting above.
sync_on_init: true

# Frequency of running using unix cronjob syntax.
# Syntax is [Minute] [Hour] [Day of month] [Month of year] [Day of week]
# You can find many examples here https://crontab.guru/examples.html
# When testing, if you have init sync enabled your agent will sync as soon as it is run
# so restarting the agent is the fastest way to test if the configuration is working.
# A few examples here below. Feel free to uncomment the line you need:
# frequency: '0 */2 * * *'  # every 2 hours
# frequency: '0 8 * * *'    # every day at 8am
# frequency: '0 0 * * 0'    # every week
frequency: '*/15 * * * *' # every 15 minutes

# The query to run to fetch the data to be pushed to Fliplet.
# The column names must match the data source or new columns will be added,
# use SQL "AS" to map database columns to Fliplet data source column names
# and avoid new columns from being created.
query: SELECT id, email as 'Email', fullName as 'Full Name', updatedAt FROM users;

# Define which column should be used as primary key
# to understand whether a record already exists on the Fliplet Data Source.
# If you don't define this, every time the script runs rows will be appended
# to the Fliplet Data Source without running a comparison on whether the row
# has already been added.
primary_column: id

# Define which (optional) column should be used to compare whether
# the record has been updated on your database since it got inserted
# to the Fliplet Data Source hence might require updating
timestamp_column: updatedAt

# Define which (optional) column should be used to compare whether
# the record has been flagged as deleted on your database and should
# be removed from the Fliplet Data Source when the column value is not null.
# Uncomment the following line to enable the feature.
# delete_column: deletedAt

Once you have a configuration file like the one above saved on disk, starting the agent is as simple as running the start command from your shell. While you are setting up the configuration we also suggest using the --test option to perform a dry run and test out the integration without actually sending data to Fliplet servers:

fliplet-agent start ./path/to/configurationFile.yml --test

e.g. if your file is in the current folder and it’s named sample.yml, you would simply write:

fliplet-agent start sample.yml

Note: on Windows we do recommend using an absolute path to the config file to avoid errors when the file is loaded by the software.

Once you start the agent with the above command an output similar to the one below will be produced:

sample

Any error found in your configuration will be printed out for you to look at.


Install the agent as a service (Windows only)

On Windows you can install any number instances of the agent to run as a service on your machine. Behind the scene we use node-windows to make this happen, however this comes bundled with the Fliplet Agent already hence installing the latter as a service is just about running this command:

fliplet-agent install C:\path\to\sample.yml

Once you run the above command, you’re likely to get asked for confirmation by the OS. A series of 3-4 popups similar to these ones will appear:

img

Just click “Yes” to grant access to the software to install the service on your machine. You can monitor the output of the script through Windows Event Viewer checking under the “Applications” logs:

img

Likewise, you can manage the service by accessing the Windows “Services” application. To remove the service, simply run the uninstall command as below:

fliplet-agent uninstall C:\path\to\sample.yml

Advanced use

Note: this documentation only applies to users using a JavaScript configuration file instead of the simpler YML (YAML) approach.

Running the Fliplet Agent in advanced mode requires you to create a configuration file written in JavaScript (instead of YML) with the following required details:

  1. Fliplet authToken: The authorisation token generated via Fliplet Studio.
  2. Database connection details: Username, password, host, port and database name to connect to your database server.
  3. A list of operations to run: each operation defines how data is pushed, pulled or synced between your database and Fliplet servers.

Here’s a sample configuration file to give you an idea on its structure:

// Save this into a file and run using "fliplet-agent start ./path/to/file.js"

module.exports.config = {
  // Fliplet authorisation token from Fliplet Studio
  authToken: 'eu--123456789',

  // Set to true to test the integration without sending any data to Fliplet servers
  isDryRun: false,

  // If set to true, operations will run when the script starts.
  // Otherwise, they will just run according to their frequency.
  syncOnInit: true,

  // Database connection settings (using Sequelize format)
  // http://docs.sequelizejs.com/
  database: {
    dialect: 'mssql',
    host: 'localhost',
    username: 'foo',
    password: 'bar',
    port: 1234,
    database: 'myDatabaseName',

    // MSSQL Server only
    dialectOptions: {
      domain: 'myDomain',
      instanceName: 'myInstanceName',
      encrypt: false
    }
  }
};

module.exports.setup = (agent) => {

  // Push data from your table to a Fliplet Data Source
  agent.push({
    // Description of your operation (will be printed out in the logs)
    description: 'Pushes data from my table to Fliplet',

    // Frequency of running using unix cronjob syntax
    frequency: '* * * * *',

    // The query (or operation) to run to fetch the data to be pushed to Fliplet.
    // You should define a function returning a promise with the data.
    // In our example, we fetch the data using a SQL query from the local database.
    sourceQuery: (db) => db.query('SELECT id, email, "updatedAt" FROM users order by id asc;'),

    // Define which column should be used as primary key
    // to understand whether a record already exists on the Fliplet Data Source
    primaryColumnName: 'id',

    // Define which (optional) column should be used to compare whether
    // the record has been updated on your database since it got inserted
    // to the Fliplet Data Source hence might require updating
    timestampColumnName: 'updatedAt',

    // Define which (optional) column should be used to compare whether
    // the record has been flagged as deleted on your database and should
    // be removed from the Fliplet Data Source
    deleteColumnName: deletedAt,

    // The ID of the Fliplet Data Source where data should be inserted to
    targetDataSourceId: 123
  });

  // You can define any other operation similar to the above here using "agent.push()"

};

You can define as many push operations as you want inside a single configuration file. They will be run in series and scheduled for future running according to the frequency set.

Once you have a configuration file like the one above saved on disk, starting the agent is as simple as running the following command from your shell:

fliplet-agent start ./path/to/configurationFile.js

Define a PUSH operation

When using the Fliplet Agent in advanced mode via a Javascript file you can choose whether the input data to be sent to Fliplet needs to be manually pulled from a third party system rather than a database. As an example, you can read data from a file or an API. When doing so, keep these few points in mind:

  1. If you don’t have a local database to connect from, you can skip entirely the database key defined in the module.exports.config.
  2. Use source(axios){} instead of sourceQuery(db){} in the definition as described in the example below. As you can see, the first parameter is the popular axios library to make HTTP requests.
module.exports.config = {
  // Fliplet authorisation token from Fliplet Studio
  authToken: 'eu--123456789',

  // Set to true to test the integration without sending any data to Fliplet servers
  isDryRun: false,

  // If set to true, operations will run when the script starts.
  // Otherwise, they will just run according to their frequency.
  syncOnInit: true
};

module.exports.setup = (agent) => {

  // 1. Example pulling data from a 3rd party JSON API endpoint
  agent.push({
    description: 'Pushes data from an API to Fliplet',
    frequency: '* * * * *',
    source: (axios) => axios.get('https://jsonplaceholder.typicode.com/todos'),
    primaryColumnName: 'id',
    timestampColumnName: 'updatedAt',
    targetDataSourceId: 123
  });

  // 2. Example pulling hardcoded data
  agent.push({
    description: 'Pushes data from my static data to Fliplet',
    frequency: '* * * * *',
    source() {
      return Promise.resolve([ { id: 1, foo: 'bar' }, { id: 2, bar: 'baz' } ]);
    },
    primaryColumnName: 'id',
    timestampColumnName: 'updatedAt',
    targetDataSourceId: 123
  });

  // 3. Example reading from a local JSON file
  agent.push({
    description: 'Pushes data from a local JSON file to Fliplet',
    frequency: '* * * * *',
    source() {
      const fs = require('fs');
      const contents = fs.readFileSync('./path/to/file.json', 'utf8');
      const data = JSON.parse(contents);
      return Promise.resolve(data);
    },
    primaryColumnName: 'id',
    timestampColumnName: 'updatedAt',
    targetDataSourceId: 123
  });
};

Define a PULL operation

When using the Fliplet Agent in advanced mode via a Javascript file you can define a pull operation if you simply want to grab the data from a Fliplet Data Source.

Version 1.4.0 required: Please note that this feature does require the version 1.4.0 or newer of Fliplet Agent to work.

If you don’t need to push the retrieved data to your database, you can skip to define the database key defined in the module.exports.config.

The following properties can also be used when querying your data source:

  • where
  • join
  • attributes (Array of column names to select)
module.exports.config = {
  // Fliplet authorisation token from Fliplet Studio
  authToken: 'eu--123456789',

  // Set to true to test the integration without sending any data to Fliplet servers
  isDryRun: false,

  // If set to true, operations will run when the script starts.
  // Otherwise, they will just run according to their frequency.
  syncOnInit: true
};

module.exports.setup = (agent) => {

  // Pull data from a Fliplet Data Source
  agent.pull({
    description: 'Pull data from a Fliplet data source',
    frequency: '* * * * *',
    // The target data source ID
    targetDataSourceId: 123,

    // Define an optional "where" query filter using sequelize operators
    // e.g. only fetch entries that have not been sync
    where: {
      syncedAt: ''
    },

    // Action to run when the data is retrieved
    action: (entries, db) => {
      console.log(entries)

      // You can write data back to Fliplet APIs if necessary,
      // e.g. confirm you have sync these entries
      return Promise.all(entries.map((entry) => {
        return agent.api.request({
          url: `v1/data-sources/456/data/${entry.id}`,
          method: 'PUT',
          data: {
            syncedAt: Date.now()
          }
        });
      }));
    }
  });

};

List of messages logged by the agent

Here follows a list of all log messages produced by the agent. Critical messages stops the agent and do require manual intervention from the user.

Type Text
Critical Auth token is required
Critical You don’t have access to the dataSource (ID). Please check the permissions of your Fliplet user.
Critical There was an error running your config file. (Error message)
Critical Unable to connect to the database: (Error message)
Critical Unable to authenticate with Fliplet API: (Error message)
Critical Cannot sync data to Fliplet servers: (Error message)
Critical Cannot execute database query: (Error message)
Error A row is missing its primary key value from the database. Skipping: (Row details)
Warning A primary key has not been set, which means rows will always be appended to the data source whenever this script runs. To allow updating rows, please define a primary key to be used for the comparison.
Info Agent started successfully. Press Ctrl+C to quit.
Info User Agent for outgoing HTTP requests has been set to (UA)
Info Initialising connection with source database…
Info Connection has been established successfully.
Info Authenticating with Fliplet API…
Info Authentication has been verified successfully. You’re logged in as (Email)
Info Fetching data via Fliplet API…
Info Nothing to commit.
Info Dry run mode is enabled. Here’s a dump of the commit log we would have been sent to the Fliplet API: (JSON)
Info Entries to delete: (JSON)
Info Sync finished. (Count) data source entries have been affected.
Info Finished to run all operations.
Info Dry run finished. Aborting process.
Info Scheduling (Count) operation(s) to run with their set frequency…
Info Scheduling complete. Keep this process alive and you’re good to go!
Debug Regional URL has been set to (URL)
Debug Fetched (Count) entries from the data source.
Debug Successfully fetched data from the source.
Debug Row (ID) has been marked for inserting since we don’t have a primary key for the comparison.
Debug Row (ID) is not present on Fliplet servers and it’s locally marked as deleted. Skipping…
Debug Row (ID) has been marked for inserting.
Debug Row (ID) already exists on Fliplet servers with ID (Fliplet ID)
Debug Row (ID) has been marked for deletion on Fliplet servers with ID (Fliplet ID).
Debug Row (ID) already exists on Fliplet servers with ID (ID) and does not require updating.
Debug Row (ID) has been marked for updating.
This documentation is maintained by Fliplet. Full text search provided by Algolia.