sri2postgres
This is a module to sync any SRI interface to one database table in a postgres 9.4 instance. The SRI specification can be found here.
Installing
Installation is simple using npm :
$ cd [your_project]
$ npm install --save sri2postgres
About
The main idea of this module is to read all resources from an Api and store them into a database in json format. A resource is basically a json object with attributes, like an school or a file or maybe a car.
Usage
Start by requiring the module in your code.
var Sri2postgres = require('sri2postgres');
Then you will need to tell which api and which database trough a config file:
var config = {
"baseApiUrl" : "http://dump.getpostman.com",
"functionApiUrl" : "/status",
"dbUser": "admin",
"dbPassword": "admin",
"database": "postgres",
"dbPort": "5432",
"dbHost": "localhost",
"dbSsl": false
}
var client = new Sri2postgres(config);
Now we are ready to start sync resources.
Function Definitions
Below is a description of the different types of functions that you can use. It describes the inputs and outputs of the different functions. Most of these function return a Q promise.
connect
This is a simple wrapper for pg.connect:
sri2postgres.connect(function (error) {
//once it is connected you can call others sri2postgres methods
});
saveResource
This is a q promise method and it allows you to save just ONE resource to the designed table. It perform an SQL UPSERT
sri2postgres.saveResource()
So if the resource already exit in the table it will be updated. Yo can try yourself by doing:
sri2postgres.saveResource().then(sri2postgres.saveResource);
saveResources
This is a q promise method that saves ALL resources from given api. First this method DELETE all content from the designed table. Then does an SQL TRANSACTION with n INSERT statements for each page of the API. Lets show you an example:
Suppose we want to save all customers. Then our config json will look like:
config.baseApiUrl = " http://api.mine.org/";
config.functionApiUrl = "/customers?limit=500";
config.dbTable = "shema.table";
...
About the table
sri2postgres assumes that the schema.table you set in config follows this structure:
CREATE TABLE table (key uuid unique,value jsonb);
It is also very important to understand that "limit" parameter determines the "n" number for INSERT statements of each TRANSACTION. If just one INSERT query cannot be done, the whole TRANSACTION (in this 500 resources) will not be saved. Once a TRANSACTION is finish the method will ask for the following page to the api ( in this case by asking http://api.mine.org/customers?limit=500&offset=500) until there are no more resource to save. Finally it tells you:
- How many resources were saved.
- How many resources were NOT saved.
Code example:
sri2postgres.connect(function () {
sri2postgres.saveResources().then(function(result){
result.resourcesSync
result.resourcesNotSync
});
});
In addition you can ask the client when last sync was:
sri2postgres.lastSync
saveResources(customFilter)
sometimes you would like not to save all resources from an api, but someones that apply to one or more conditions. In this cases saveResources receives a customFilter object that works like an java interface. It MUST have a method isValid(resource) that return true or false. Let's show you an example:
If your resources are cars like:
[
{
key: uuid-1,
brand : 'Ford',
model: 'Focus',
color: 'RED'
},
{
key: uuid-2,
brand : 'Chevrolet',
model: 'Camaro',
color: 'YELLOW'
}
]
And you want to save just RED cars then you have to write your customFilter with, at least, this 3 mandatory methods:
function CustomFilter (){
this.isValid = function (resource){
return resource.color == 'RED';
};
this.getKeyFrom = function(resource){
return resource.key;
};
this.getValueFrom = function (resource) {
// As I know that 'resource' is a JSON object I need to stringify it to be persisted on DB
return JSON.stringify(resource);
};
};
module.exports = CustomFilter;
As you KNOW the resource you will be able to write your own filter as complex as you need. Usage can be:
var filterObject = new CustomFilter();
sri2postgres.saveResources(filterObject).then(function(result){
result.resourcesSync
});
saveResourcesInProperty
After calling saveResources() this method will allow you to get the content from a specific property (nested objects). We assume this property has as value a link to an api to get a resource.
In the following example we want to obtain the content that engine property points to.
[
{
key: uuid-1,
brand : 'Ford',
model: 'Focus',
color: 'RED',
engine: 'https://myengine.api.com/engine-uuid-2
},
{
key: uuid-2
brand : 'Chevrolet',
model: 'Camaro',
color: 'YELLOW',
engine: 'https://myengine.api.com/engine-uuid-3
}
]
Before calling saveResourcesInProperty we need to tell sri2postgres which attribute and which new table is going to use for this purpose.
var propertyConfig = {
propertyName : "value->'engine'",
targetTable: "schema.engine_table"
};
Again a table like this will be required:
CREATE TABLE engine_table (key uuid unique,value jsonb);
sri2postgres will insert it one by one.
So this code:
var propertyConfig = {
propertyName : "value->'engine'",
targetTable: "schema.car_engine",
queriesPerTransaction: 20
};
sri2postgres.saveResources().then(function(){
// As engine has its own api, we must set baseApiUrl to blank.
// Remember that sri2postgres will construct the full api url by doing: url = baseApiUrl + functionApiUrl
sri2postgres.baseApiUrl = '';
sri2postgres.saveResourcesInProperty(propertyConfig).then(function(result){
result.resourcesSync
result.resourcesNotSync
});
});
Will Store:
CAR
+--------------+-------------------------------------------------------------------------------------------------------------------+
| key | value |
+--------------+-------------------------------------------------------------------------------------------------------------------+
| uuid-1 | {key: uuid-1,brand : 'Ford',model: 'Focus',color: 'RED',engine: 'https://myengine.api.com/engine-uuid-2} |
| uuid-2 | {key: uuid-2,brand : 'Chevrolet',model: 'Camaro',color: 'YELLOW',engine: 'https://myengine.api.com/engine-uuid-3} |
+--------------+-------------------------------------------------------------------------------------------------------------------+
CAR_ENGINE
+--------------+-------------------------------------------------+
| key | value |
+--------------+-------------------------------------------------+
| uuid-1 | {key: 'engine-uuid-2', type : '1.6', HP: '150'} |
| uuid-2 | {key: 'engine-uuid-3', type : '2.0', HP: '200'} |
+--------------+-------------------------------------------------+
Tests
In order to run tests locally it is needed to install postgres 9.4 or superior in your machine. see http://stackoverflow.com/a/29415300
Then you will need a user and pass for accessing your local postgres database see also http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
We use mocha for testing. You will find a config.json file in test folder:
{
"baseApiUrl" : "http://dump.getpostman.com",
"functionApiUrl" : "/status",
"dbUser": "admin",
"dbPassword": "admin",
"database": "postgres",
"dbPort": "5432",
"dbHost": "localhost",
"dbSsl": false
}
All postgres related test takes its configuration from that file. So make sure it is corrected full filled.
After that you are available to run test doing:
$ cd [your_project]
$ mocha --recursive
and you will see:
Accessing external json Api
✓ should respond to GET (526ms)
passing null URL
✓ throws an error
with basic auth
✓ should respond OK with valid credentials (360ms)
✓ should return 401 error with invalid username and password (365ms)
sri2postgres save content
✓ should throw an error if not table is defined
✓ persist JSON from api to configured postgres table (765ms)
✓ should update the same resource if it is saved again (574ms)
Accessing local json Api
✓ should respond to GET
Connecting to a correct Postgres DataBase
✓ should respond with no error
sri2postgres save an array of resources
✓ persist JSON from api to configured postgres table (51263ms)
✓ should saved last sync time
sri2postgres.saveResources with filter
✓ should persist less resources than sri2postgres.saveResources without filter (61595ms)
sri2Postgres read an url property from jsonb
✓ should save the data content in passed table (219054ms)
13 passing (7m)