Javascript / Node.js PostgreSQL UPSERT query builder

UPSERT is a strongly requested feature in any db.
NoSQL databases, such as MongoDB or Redis, have a builtin support to this operation.
PostgreSQL have a couple of ways to perform an UPSERT, as described here or here.

In this post I would publish my javascript / Node.js UPSERT query builder function that I’m using in some projects.
The goal is to produce a PostgreSQL query, based on Writable CTE (Common Table Expressions) supported from PostgreSQL 9.1, that performs an UPSERT operation on a table taking three parameters:
- the table name
- a list of all primary and unique keys for that table
- a javascript object containing the data to be inserted / updated

An UPSERT function on this table should cover following scenarios:
- insert if not exists any row matching given table keys
- update if exists a row that matches the primary key
- update if exists a row that matches one of the table’s unique keys
- raise a SQL exception if the number of updated / inserted rows is <> 1

Let’s start with the code

var Controller = function();
 
Contoller.prototype.dbEscape = function(v) {
	if (_.isNull(v) || _.isUndefined(v)) {
		return 'null';
	}
	if (_.isNumber(v)) {
		return v;
	}
	if (_.isArray(v) || _.isObject(v)) {
		return "'" + JSON.stringify(v) + "'";
	}
	v = v.replace(/[\0\n\r\b\t\\\'\"\x1a]/g, function(s) {
		switch(s) {
			case "\0": return "\\0";
			case "\n": return "\\n";
			case "\r": return "\\r";
			case "\b": return "\\b";
			case "\t": return "\\t";
			case "\x1a": return "\\Z";
			default: return "\\"+s;
		}
	});
	return "'" + v + "'";
};
 
Controller.prototype.queryUpsert = function(tname, tkeys, entry) {
        var self = this;
	var kq = "(" + tkeys.map(function(tkey) {
		return "(" + tkey.map(function(tkey) {
			return "\"" + tkey + "\"=" + self.dbEscape(entry[tkey]); 
		}).join(' AND ') + ")";
	}).join(' OR ') + ")",
	resq = "(SELECT * FROM upsert0 UNION ALL SELECT * FROM upsert1)", 
	query = "WITH upsert0 AS (UPDATE \"" + tname + "\" SET " +
		_.keys(entry).map(function(k) { return "\"" + k + "\"=" + self.dbEscape(entry[k]); }).join(',') + " " + 
		"WHERE " + kq + " RETURNING *), upsert1 AS (" +
		"INSERT INTO \"" + tname + "\" " +  
		"(" + _.keys(entry).map(function(k) { return "\"" + k + "\""; }).join(',') + ") " + 
		"SELECT " + _.values(entry).map(function(v) { return self.dbEscape(v); }).join(',') + " " +
		"WHERE NOT EXISTS (SELECT 1 FROM upsert0) RETURNING *) " + 
		"SELECT *, " + 
		"(SELECT CASE WHEN COUNT(*) <> 1 THEN raise_exception('ERR-QUERY_ROWMISMATCH') END FROM " + resq + " res) AS __check " +
		"FROM " + resq + " res";
	return query;
};

Follows a SQL helper function that allows raising an exception from a SELECT statement.

CREATE OR REPLACE FUNCTION raise_exception(text) RETURNS void AS
$BODY$
BEGIN
  RAISE EXCEPTION '%', $1;
END;
$BODY$ LANGUAGE plpgsql;

To understand how this solution works, let’s see an example.

CREATE TABLE "Users"
(
  KEY INTEGER NOT NULL DEFAULT NEXTVAL('"Users_key_seq"'::regclass),
  name CHARACTER VARYING(64) NOT NULL,
  TYPE CHARACTER VARYING(16) NOT NULL,
  company INTEGER NOT NULL,
  CONSTRAINT "Users_pkey" PRIMARY KEY (KEY),
  CONSTRAINT "Users_ukey1" UNIQUE (name, company)
);
var ctr = new Controller();
console.log(
    ctr.queryUpsert(
        'Users',
        // here you have to pass an array containing the list of all table's keys (primary and unique)
        [ 
            ['key'], // this is the primary key
            ['name', 'company'] // this is the unique key
        ], 
        { name: 'paolo', type: 'EDITOR', company: 1 }
    )
);

Below the produced SQL query

WITH 
    upsert0 AS (UPDATE "Users" SET "name"='paolo',"type"='EDITOR',"company"=1 WHERE (("key"=NULL) OR      ("name"='paolo' AND "company"='1')) RETURNING *), 
    upsert1 AS (INSERT INTO "Users" ("name","type","company") SELECT 'paolo','EDITOR','1' WHERE NOT EXISTS (SELECT 1 FROM upsert0) RETURNING *) 
SELECT 
    *, 
    (
       SELECT 
       CASE WHEN COUNT(*) <> 1 THEN raise_exception('ERR-QUERY_ROWMISMATCH') END 
       FROM (SELECT * FROM upsert0 UNION ALL SELECT * FROM upsert1) res
    ) AS __check 
FROM (SELECT * FROM upsert0 UNION ALL SELECT * FROM upsert1) res

The query produced by this function has been tested using node-postgres on a PostgreSQL 9.3.

Note: this function may contain trace amounts of lodash / underscore

Let me know your opinion about this solution.

Twitter Finance : an introduction

Hello everybody,

In this period I wrote two posts for Fincluster ‘s blog about Twitter Finance.
This topic has been widely discussed in this period, but at Fincluster we would like to try ourselves, learning how much reliable is Twitter platform for the financial sector and perform any test that came in our minds :-)

Results have been interesting and we are planning to run some more elaborate analysis in the next future, maybe exploiting machine based algorithms ;-)

TWITTER FINANCE : TWITTER ASSET ALLOCATION (PT1)

TWITTER FINANCE: TOP DISCUSSED YAHOO TICKER LIST

Enjoy reading :-)

Yahoo Finance stock quotes and market data query realtime – YQL and jQuery

Hello guys,

this is just a little piece of Javascript code for querying Yahoo Finance stock quotes using YQL and jQuery.
Probably you can find something similar elsewhere, but all the examples that I’ve found before coding my own function were formatting results in HTML without giving access to raw data.
You can easily integrate it into your website or WordPress theme/widget displaying retrieved quotes as you need.

Note: check out the new version of Javascript / jQuery Yahoo Finance YQL for historical data and quotes

Check out new version of Yahoo Finance stock Javascript function at Fincluster

Code

function getStock(opts, complete) {
    var defs = {
        desc: false,
        baseURL: 'http://query.yahooapis.com/v1/public/yql?q=',
        query: 'select {display} from yahoo.finance.quotes where symbol in ({quotes}) | sort(field="{sortBy}", descending="{desc}")',
        suffixURL: '&env=store://datatables.org/alltableswithkeys&format=json&callback=?'
    };

    opts = $.extend({
        display: ['*'],
        stocks: []
    }, opts || {});

    if (!opts.stocks.length) {
        complete('No stock defined');
        return;
    }

    var query = {
        display: opts.display.join(', '),
        quotes: opts.stocks.map(function (stock) {
            return '"' + stock + '"';
        }).join(', ')
    };

    defs.query = defs.query
        .replace('{display}', query.display)
        .replace('{quotes}', query.quotes)
        .replace('{sortBy}', defs.sortBy)
        .replace('{desc}', defs.desc);

    defs.url = defs.baseURL + defs.query + defs.suffixURL;
    $.getJSON(defs.url, function (data) {
        var err = null;
        if (!data || !data.query) {
            err = true;
        }
        complete(err, !err && data.query.results);
    });
}

Usage

getStock({
    stocks: ['AAPL', 'GOOG']
}, function (err, data) {
    if (err) {
        alert('Error:' + error);
        return;
    }
    console.log(data);
});

// or
getStock({
    stocks: ['AAPL', 'GOOG'],
    display: ['Name', 'Ask']
}, function (err, data) {
    if (err) {
        alert('Error:' + error);
        return;
    }
    console.log(data);
});

Bye

Large integer factorization algorithms

This is a summarizing post of large integers factorization methods I have exposed in my articles.