JavaScript
inside
PostgreSQL

Created by niquola / @niquola

JavaScript inside PostgreSQL

FHIR: Fast Healthcare Interoperability Resources

Polyglot

Ruby, Clojure, JVM, JavaScript, .NET ...

fhirbase


       SELECT fhir.create( $JSON$
         {
          resourceType: "Patient",
          name: "Ivan",
          birthDate: "1981-01-02"
         }
       $JSON$);

       SELECT fhir.search('Patient', 'name=ivan&birthdate=>1970');
      

It works!


  • fhirface, Aidbox - clojure
  • Netrika (SPb) - .NET
  • Kainos (UK) - Java

But, programmer UX is :(

  • own preprocessor
  • own modules
  • SQL & PL/PGSQL not expressive
  • own test framework

Preprocessor


func _build_url(_cfg_ jsonb, VARIADIC path text[]) RETURNS text
  SELECT _cfg_->>'base' || '/' || (SELECT string_agg(x, '/')
    FROM unnest(path) x)


CREATE OR REPLACE FUNCTION
module._build_url(_cfg_ jsonb, VARIADIC path text[]) RETURNS text
AS $$
  SELECT _cfg_->>'base' || '/' || (SELECT string_agg(x, '/')
  FROM unnest(path) x)
$$ language SQL immutable;
    

Modules


-- #import ./fhirbase_json.sql
-- #import ./fhirbase_gen.sql
-- #import ./fhirbase_coll.sql
-- #import ./fhirbase_util.sql
-- #import ./fhirbase_generate.sql

func _build_url(_cfg_ jsonb, VARIADIC path text[]) RETURNS text
  SELECT _cfg_->>'base' || '/' || (SELECT string_agg(x, '/')
    FROM unnest(path) x)
      

SQL PG/PLSQL

func _expand_search_params(_resource_type text, _query text) RETURNS setof query_param
  WITH RECURSIVE params(parent_resource, link_path, res, chain, key, operator, value) AS (
    SELECT null::text as parent_resource, -- we start with empty parent resoure
           '{}'::text[] as link_path, -- path of reference attribute to join
           _resource_type::text as res, -- this is resource to apply condition
           ARRAY[_resource_type]::text[] || key as chain, -- initial chain
           key as key,
           operator as operator,
           value as value
    FROM fhirbase_params._parse_param(_query)
    WHERE key[1] NOT IN ('_tag', '_security', '_profile', '_sort', '_count', '_page')
    UNION
    SELECT res as parent_resource, -- move res to parent_resource
           fhirbase_coll._rest(ri.path) as link_path, -- remove first element
           this.get_reference_type(x.key[1], re.ref_type) as res, -- set next res in chain
           x.chain AS chain, -- save search path
           fhirbase_coll._rest(x.key) AS key, -- remove first item from key untill only one key left
           x.operator,
           x.value
     FROM  params x
     JOIN  searchparameter ri
       ON  ri.name = split_part(key[1], ':',1)
      AND  ri.base = x.res
     JOIN  structuredefinition_elements re
       ON  re.path = ri.path
    WHERE array_length(key,1) > 1
  )
  SELECT
    parent_resource as parent_resource,
    link_path as link_path,
    res as resource_type,
    fhirbase_coll._butlast(p.chain) as chain,
    ri.search_type,
    ri.is_primitive,
    ri.type,
    fhirbase_coll._rest(ri.path)::text[] as field_path,
    fhirbase_coll._last(key) as key,
    operator,
    value
  FROM params p
  JOIN searchparameter ri
    ON ri.base = res
   AND ri.name = key[1]
 where array_length(key,1) = 1
  ORDER by p.chain
      

Tests


BEGIN;
_extract_id('rid/_history/vid') => 'rid'
-- SELECT expect(_extract_id('rid/_history/vid'),'rid')

SELECT fhirbase_generate.generate_tables('{Patient}');

setv('createOutcome',
  fhirbase_crud.create('{}'::jsonb, :'pt_json')
);

getv('createOutcome')->>'resourceType' => 'OperationOutcome'
getv('createOutcome')#>>'{issue,0,code,coding,1,code}' => '400'

ROLLBACK;
      

Logic in DB


Logic in DB: PRO


  • Performance (faster transactions, data locality)
  • Consistency (like incapsulation)
  • Integration by db
  • Reuse

Logic in DB: CONTRA


  • Overload database
  • No good pracitces (TDD, modules etc)
  • Archaic languages
  • Slow development

Unresolvable?


  • +modern language
  • +modularity
  • +packages
  • +good practices

Postgres: OS for data


  • extensions (SQL, C, C++)
  • custom types
  • advanced indexes
  • pluggable languages (python, plpgsql)
  • FDW (Foreign Data Wrappers)

pg: extensions



  -- PostGIS: a spatial database extender
  SELECT superhero.name
  FROM city, superhero
  WHERE ST_Contains(city.geom, superhero.geom)
  AND city.name = 'Gotham';
      

http://pgxn.org/

pg: custom types



  CREATE TYPE address AS (city text, street text);

  typedef struct Complex {
      double      x;
      double      y;
  } Complex;

  CREATE FUNCTION complex_in(cstring)
  RETURNS complex AS 'filename'
  LANGUAGE C IMMUTABLE STRICT;
      

pg: indexes



-- GIN Index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX users_search_idx ON users
USING gin (first_name gin_trgm_ops, last_name gin_trgm_ops);

SELECT count(*) FROM users where first_name ilike '%aeb%';
      

pg: languages



CREATE LANGUAGE plpython;
CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ LANGUAGE plpythonu;
      

pg: FDW



CREATE EXTENSION file_fdw;
CREATE SERVER data FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE pglog (
  name text,
  address tetx
) SERVER data
OPTIONS ( filename '/users.csv', format 'csv' );
      

What is a most popular lang now?

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 text AS $$
    var o = {};
    for(var i=0; i<keys.length; i++){
        o[keys[i]] = vals[i];
    }
    return JSON.stringify(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: cursors


  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;
      

plv8: performance


      

PG + PLV8 + NODEJS?


pg.js: concept


  • write in node
  • compile into plv8

pg.js: mock plv8


var Client = require('pg-native')

var client = new Client()
client.connectSync('postgres://root:root@localhost:5432/test');

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')
uuid = (plv8)->
  plv8.execute('select gen_random_uuid() as uuid')[0].uuid

exports.uuid = uuid
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

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}

github

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;

pg.js: call in postgres


select fhir.read('StructureDefinition', 'Patient') as read

pg.js: Road Map


  • Remove death code (Google closure)
  • Implement most of plv8
  • Make it npm package

Thx


Q?