Про лошадок и слоников

Created by niquola / @niquola

PostgreSQL is best db for JS

Agenda

• jsonb data type & operations
• indexing json
• js in pg (plv8)
• plv8 dev.flow
• PostgREST

PG languages

• Scalar function calls

plv8: V8 JavaScript in pg

• Scalar function calls
• Trigger function calls
• Mapping between JS and DB types
• Prepared Statements and Cursors
• Subtransaction & Window function API
• Remote debugger
• Runtime separation across users

plv8: functions

  CREATE FUNCTION plv8_test(keys text[], vals text[])
  RETURNS json AS $$
    var o = {};
    for(var i=0; i<keys.length; i++){
        o[keys[i]] = vals[i];
    }
    return o;
  $$ LANGUAGE plv8 IMMUTABLE STRICT;

  SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Tom', '29']);
  --         plv8_test
  ---------------------------
  -- {"name":"Tom","age":"29"}
      

plv8: returning function calls

CREATE TYPE rec AS (i integer, t text);
CREATE FUNCTION set_of_records() RETURNS SETOF rec AS
$$
    // plv8.return_next() stores records in an internal tuplestore,
    // and return all of them at the end of function.
    plv8.return_next( { "i": 1, "t": "a" } );
    plv8.return_next( { "i": 2, "t": "b" } );

    // You can also return records with an array of JSON.
    return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ];
$$
LANGUAGE plv8;

SELECT * FROM set_of_records();
      

plv8: triggers

CREATE FUNCTION test_trigger() RETURNS trigger AS
$$
    plv8.elog(NOTICE, "NEW = ", JSON.stringify(NEW));
    plv8.elog(NOTICE, "OLD = ", JSON.stringify(OLD));
    plv8.elog(NOTICE, "TG_OP = ", TG_OP);
    plv8.elog(NOTICE, "TG_ARGV = ", TG_ARGV);
    if (TG_OP == "UPDATE") {
        NEW.i = 102;
        return NEW;
    }
$$ LANGUAGE "plv8";

CREATE TRIGGER test_trigger
    BEFORE INSERT OR UPDATE OR DELETE
    ON test_tbl FOR EACH ROW
    EXECUTE PROCEDURE test_trigger('foo', 'bar');
      

plv8: prepared statements

  var plan = plv8.prepare(
   'SELECT * FROM tbl WHERE col = $1', ['int']
  );
  var rows = plan.execute( [1] );
  var sum = 0;
  for (var i = 0; i < rows.length; i++) {
    sum += rows[i].num;
  }
  plan.free();

  return sum;
      

• write in node
• test in node
• compile and deploy into plv8

pg.js: mock plv8

var Client = require('pg-native')

var client = new Client()
client.connectSync(env.DATABASE_URL);

module.exports = {
  execute: function(){
    return client.querySync.apply(client,arguments)
  },
  elog: function(x, msg){
    console.log(msg)
  },
  ...
}
..

pg.js: write in node

util = require('./util')
exports.create = (plv8, resource)->
  table_name = util.table_name(resource_type)
  json  = JSON.stringify(resource)
  res = plv8.execute """
    INSERT INTO #{table_name}
    (logical_id, version_id, content)
    VALUES ($1,$2,$3)
    """, [logical_id, version_id, json]
  resource

exports.create.plv8_signature = ['json', 'json']

pg.js: test in node

  plv8 = require('../lib/plv8')
  crud = require('../src/crud')
  schema = require('../src/schema')

  describe "CRUD", ()->
    beforeEach ()->
      schema.generate_table(plv8, 'Patient')

    it "read", ()->
      pt = {resourceType: 'Patient', name: {text: 'Albert'}}
      pt_created = crud.create(plv8, pt)
      expect(pt_created.id).toBeTruthy()
      expect(pt_created.meta.versionId).toBeTruthy()

pg.js: compile into plv8

  Module = require("module")
  oldrequire = Module::require
  Module::require = (fl) ->
    currentModule = fl
    oldrequire.apply this, arguments

  oldcompile = Module::_compile
  Module::_compile = (answer, filename) ->
    for k,v of @exports when v.plv8?
        plv8_exports[k] ={fn: v, filename: filename}

pg.js: compile into plv8

  CREATE OR REPLACE FUNCTION #{def_fn} AS $$
  var deps = {}
  var cache = {}
  #{modules_js}
  var require = function(dep){
    if(!cache[dep]) {
      var module = {exports: {}};
      deps[dep](module, module.exports, require);
      cache[dep] = module.exports;
    }
    return cache[dep]
  }
  return require('#{mod}').#{k}#{def_call};
  $$ LANGUAGE plv8 IMMUTABLE STRICT;

Thx

Q?