PLV8JS and PLCoffee Part 1: Upserting

Today's modern web application workflow in its simplest form looks something like this:

  1. Get dataset as JSON object usually using yet another JSON query object to pass the request using a javascript framework like JQuery/ExtJS/OpenLayers/Leaflet etc.
  2. Make changes to JSON dataset object and send back to the web server.
  3. On webserver unravel the JSON object and save to respective database tables. This part is really yucky as it often involves the web application server side language doing the unraveling and then yet another step of setting up stored procedures or other update logic to consume it.

We hate the way people build tiers for the same reason Cartman hates lines at the amusement park. Sure tiers are great for certain things like building connected microcosms, but most of the time they are overkill and if applied too early make your application needlessly complicated. In the end all we care about is data: serving data, analyzing data, getting good data and everything else is just peacock feathers.

The introduction of JSON type support in PostgreSQL 9.2 and languages PL/V8 (PL/Javascript) and its Pythoness-like twin PL/Coffee provides several options for bringing your data and application closer together since they have native support for JSON. In this first part we'll demonstrate one: An upsert stored procedure that takes a single JSON object instead of separate args and updates existing data and adds missing records. In later articles we'll show you the front end app and also add a sprinkle of PostGIS in there to demonstrate working with custom types.

Setting up the tables

We'll use this table and add more tables to this model in later articles:

CREATE TABLE inventory (prod_code varchar(20) primary key
    , prod_name varchar(50)
    , loc_id integer
    , date_add timestamptz DEFAULT CURRENT_TIMESTAMP
    , date_upd timestamptz DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO inventory (prod_code, prod_name, loc_id)
VALUES ('BB1', 'Breadboard 174mm x 67mm', 1)
    , ('SIR', 'Solder Iron', 2)
    , ('TI_TMS5100', 'Texas Instruments TMS5100', 3);

The Upsert Stored procedure

PL/Javascript implementation
-- this will take a json dataset as input and do a data merge


CREATE OR REPLACE FUNCTION upsert_inventory(param_inv json) RETURNS
text AS $$
var o = JSON.parse(param_inv);
/** update plan **/
var uplan = plv8.prepare('UPDATE inventory SET prod_name = $1, loc_id = $2 WHERE prod_code = $3', ['text', 'int', 'text'] );
/** insert plan **/
var iplan = plv8.prepare('INSERT INTO inventory( prod_name, loc_id, prod_code) VALUES($1, $2, $3)', ['text', 'int', 'text'] );
var num_changed;
var num_ins = 0, num_upd = 0;
if (typeof o != 'object')
   return NULL;
else {
    for(var i=0; i<o.length; i++){
      num_changed = uplan.execute([o[i].prod_name, o[i].loc_id, o[i].prod_code]);
      num_upd += num_changed;
      if (num_changed == 0){ /** we got no updates, so insert **/
        num_changed = iplan.execute([o[i].prod_name, o[i].loc_id, o[i].prod_code]);
        num_ins += num_changed;
      } 
    } 
}
iplan.free();
uplan.free();
return num_upd + ' record(s) updated and ' + num_ins + ' records inserted';
$$ LANGUAGE plv8 VOLATILE;



PL/CoffeeScript equivalent

CREATE OR REPLACE FUNCTION upsert_inventory(param_inv json) RETURNS
text AS $$
o = JSON.parse(param_inv)
# update plan 
uplan = plv8.prepare("UPDATE inventory SET prod_name = $1, loc_id = $2 WHERE prod_code = $3", [ "text", "int", "text" ])
# insert plan
iplan = plv8.prepare("INSERT INTO inventory( prod_name, loc_id, prod_code) VALUES($1, $2, $3)", [ "text", "int", "text" ])
num_changed = undefined
num_ins = 0
num_upd = 0
unless typeof o is "object"
  return NULL
else
  i = 0
  while i < o.length
    num_changed = uplan.execute([ o[i].prod_name, o[i].loc_id, o[i].prod_code ])
    num_upd += num_changed
    if num_changed is 0 # we got no updates, so insert 
      num_changed = iplan.execute([ o[i].prod_name, o[i].loc_id, o[i].prod_code ])
      num_ins += num_changed
    i++
iplan.free()
uplan.free()
return num_upd + ' record(s) updated and ' + num_ins + ' records inserted'$$ 
LANGUAGE plcoffee VOLATILE;




Testing out the stored proc



-- this is to simulate data coming from jquery
-- or some other javascript client api
-- we aggregate all rows we want to change in an array
-- and then we convert the array of rows to
-- a single json object that will serve as our dataset
SELECT upsert_inventory(array_to_json(array_agg(inv)) )
FROM (SELECT replace(prod_name, 'Bread', 'Butter') As prod_name
 , loc_id
 , replace(prod_code, 'SIR', 'SIR2') as prod_code
FROM inventory ) As inv;




-- output of query --
2 record(s) updated and 1 records inserted




--Lets see what we have now --
SELECT prod_code, prod_name 
FROM inventory ORDER BY prod_code;




 prod_code  |         prod_name
------------+---------------------------
 BB1        | Butterboard 174mm x 67mm
 SIR        | Solder Iron
 SIR2       | Solder Iron
 TI_TMS5100 | Texas Instruments TMS5100
PLV8JS and PLCoffee Part 1: Upserting - Postgres OnLine Journal
      PLV8JS and PLCoffee Part 1: Upserting

Today's modern web application workflow in its simplest form looks something like this:

  1. Get dataset as JSON object usually using yet another JSON query object to pass the request using a javascript framework like JQuery/ExtJS/OpenLayers/Leaflet etc.
  2. Make changes to JSON dataset object and send back to the web server.
  3. On webserver unravel the JSON object and save to respective database tables. This part is really yucky as it often involves the web application server side language doing the unraveling and then yet another step of setting up stored procedures or other update logic to consume it.

We hate the way people build tiers for the same reason Cartman hates lines at the amusement park. Sure tiers are great for certain things like building connected microcosms, but most of the time they are overkill and if applied too early make your application needlessly complicated. In the end all we care about is data: serving data, analyzing data, getting good data and everything else is just peacock feathers.

The introduction of JSON type support in PostgreSQL 9.2 and languages PL/V8 (PL/Javascript) and its Pythoness-like twin PL/Coffee provides several options for bringing your data and application closer together since they have native support for JSON. In this first part we'll demonstrate one: An upsert stored procedure that takes a single JSON object instead of separate args and updates existing data and adds missing records. In later articles we'll show you the front end app and also add a sprinkle of PostGIS in there to demonstrate working with custom types.

Setting up the tables

We'll use this table and add more tables to this model in later articles:

CREATE TABLE inventory (prod_code varchar(20) primary key
    , prod_name varchar(50)
    , loc_id integer
    , date_add timestamptz DEFAULT CURRENT_TIMESTAMP
    , date_upd timestamptz DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO inventory (prod_code, prod_name, loc_id)
VALUES ('BB1', 'Breadboard 174mm x 67mm', 1)
    , ('SIR', 'Solder Iron', 2)
    , ('TI_TMS5100', 'Texas Instruments TMS5100', 3);

The Upsert Stored procedure

PL/Javascript implementation
-- this will take a json dataset as input and do a data merge


CREATE OR REPLACE FUNCTION upsert_inventory(param_inv json) RETURNS
text AS $$
var o = JSON.parse(param_inv);
/** update plan **/
var uplan = plv8.prepare('UPDATE inventory SET prod_name = $1, loc_id = $2 WHERE prod_code = $3', ['text', 'int', 'text'] );
/** insert plan **/
var iplan = plv8.prepare('INSERT INTO inventory( prod_name, loc_id, prod_code) VALUES($1, $2, $3)', ['text', 'int', 'text'] );
var num_changed;
var num_ins = 0, num_upd = 0;
if (typeof o != 'object')
   return NULL;
else {
    for(var i=0; i<o.length; i++){
      num_changed = uplan.execute([o[i].prod_name, o[i].loc_id, o[i].prod_code]);
      num_upd += num_changed;
      if (num_changed == 0){ /** we got no updates, so insert **/
        num_changed = iplan.execute([o[i].prod_name, o[i].loc_id, o[i].prod_code]);
        num_ins += num_changed;
      } 
    } 
}
iplan.free();
uplan.free();
return num_upd + ' record(s) updated and ' + num_ins + ' records inserted';
$$ LANGUAGE plv8 VOLATILE;



PL/CoffeeScript equivalent

CREATE OR REPLACE FUNCTION upsert_inventory(param_inv json) RETURNS
text AS $$
o = JSON.parse(param_inv)
# update plan 
uplan = plv8.prepare("UPDATE inventory SET prod_name = $1, loc_id = $2 WHERE prod_code = $3", [ "text", "int", "text" ])
# insert plan
iplan = plv8.prepare("INSERT INTO inventory( prod_name, loc_id, prod_code) VALUES($1, $2, $3)", [ "text", "int", "text" ])
num_changed = undefined
num_ins = 0
num_upd = 0
unless typeof o is "object"
  return NULL
else
  i = 0
  while i < o.length
    num_changed = uplan.execute([ o[i].prod_name, o[i].loc_id, o[i].prod_code ])
    num_upd += num_changed
    if num_changed is 0 # we got no updates, so insert 
      num_changed = iplan.execute([ o[i].prod_name, o[i].loc_id, o[i].prod_code ])
      num_ins += num_changed
    i++
iplan.free()
uplan.free()
return num_upd + ' record(s) updated and ' + num_ins + ' records inserted'$$ 
LANGUAGE plcoffee VOLATILE;




Testing out the stored proc



-- this is to simulate data coming from jquery
-- or some other javascript client api
-- we aggregate all rows we want to change in an array
-- and then we convert the array of rows to
-- a single json object that will serve as our dataset
SELECT upsert_inventory(array_to_json(array_agg(inv)) )
FROM (SELECT replace(prod_name, 'Bread', 'Butter') As prod_name
 , loc_id
 , replace(prod_code, 'SIR', 'SIR2') as prod_code
FROM inventory ) As inv;




-- output of query --
2 record(s) updated and 1 records inserted




--Lets see what we have now --
SELECT prod_code, prod_name 
FROM inventory ORDER BY prod_code;




 prod_code  |         prod_name
------------+---------------------------
 BB1        | Butterboard 174mm x 67mm
 SIR        | Solder Iron
 SIR2       | Solder Iron
 TI_TMS5100 | Texas Instruments TMS5100