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

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

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

The world's most advanced open source database

DataTypes

• arrays
• ranges
• timestapm with timezone
• uuid
• jsonb & json
• geometric types

Same data language

JSON and JSONB data types

CREATE EXTENSION "pgcrypto";
-- for gen_random_uuid()

CREATE TABLE resources (
   id uuid primary key default gen_random_uuid(),
   resource jsonb,
   tags text[],
   created_at timestamptz default now()
);

INSERT INTO resources (resource) 
VALUES (
  $JSON$
  {
    "name" : "Nicola",
    "likes" : ["pg", "clj", "fhir"]
    "repos": [
      {"name": "holyjs slides", "url": "github..."}
    ]
  }
  $JSON$
)

Access elements

SELECT resource->>'name'; => "nicola"::text

SELECT resource#>>'{repos,0,name}'; => "holyjs slides"::text

SELECT resource#>'{repos,0}';
=> {"name": "holyjs slides", "url": "github..."}

Building JSON

SELECT jsonb_build_object(
    'name', "nicola",
    'reops', (SELECT json_agg(row_to_json(x.*))
      FROM repos WHERE id='nicola')
); =>

{"name": "nicola", "repos": [{"name": "Holyjs slides"}]}

JSON -> Relation

SELECT x.name, x.url
  FROM jsonb_array_elements(resource->'repos') x; 
=>
"holyjs slides" | "github..."" 
"pgday  slides" | "github...""

Relation -> JSON

SELECT json_agg(row_to_json(x.*)) FROM users;
=>
[
  {id: 1, "name": "nicola"},
  {id: 2, "name": "ivan"}
]

More Functions

JSON vs JSONB

TODO: performance

SEARCH

SELECT *
  FROM resources
 WHERE resource->>'name' ilike '%cola%'
   AND json_array_length(resource->'likes') > 3

FAST SEARCH

CREATE index likes_count_idx
    ON resources
       (json_array_length(resource->'likes'))

FAST SEARCH

CREATE index name_idx
    ON resources
 USING gin (resource->>'name' gin_trgm_ops);

JSQUERY

CREATE EXTENSION "jsquery";

SELECT *
  FROM your_table
 WHERE column @@ '<jsquery expression>'

JSQUERY EXPR

resourceType = "Patient" AND
name.# (given.# = "Jim" AND use = "usual") AND
length.value($ > 135 AND $ < 145)>)

JSQUERY as validator

CREATE TABLE js (
  id serial,
  data jsonb,
  CHECK (data @@ '
    name IS STRING AND
    similar_ids.#: IS NUMERIC AND
    points.#:(x IS NUMERIC AND y IS NUMERIC)
  '::jsquery)
);

PG languages

• Scalar function calls

Languages

• pl/pgsql
• python
• perl
• R
• javascript
• lua

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;
      

ES6 Features

SET plv8.v8_flags = '--es_staging';

Dev Environment

PG + PLV8 + NODEJS = ♥

• 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;

fhirbase

Health IT standard implementation

PostgREST

GET /projects?id=eq.1&select=id, name, client{*}

POST /rpc/proc_name
{ "args": 1 }

POST /table_name
{ "col1": "value1", "col2": "value2" }

Isomorphic apps

Thx

Q?