PgRest

Enable REST in PostgreSQL

@hychen

Debian Mini Conf Taiwan 2013, 泰雅巴萊部落村

Who am I..

● 10+ years of experiences in participating with open source communities and projects.

● Python/LiveScript● Back to Web development

recently ...I was a PHP web programmer in 2009.

Web ….

IOS

Android

Web

NoSql

Ruby On Rails

Backend

PHP

Python + DjangoRMDB

DB Frontend

SPA - Single Page Application

Credit: http://blog.nodejitsu.com/

RESTful Backend.

IOS

Android

Web

NoSql

Ruby On Rails

Restful Backend

PHP

Python + Django

RMDB

DB Frontend

REST

Cut Middleware with RESTful NoSQL

IOS

Android

Web

Restful DB Frontend

MongoLab(rest mongodb)

FireBase

REST

Summary

● All webs should be intractable.– Allow 3rd to read/wite the data by API.

● More and more features can be implemented in frontend.

● Database As service.● JavaScript + HTML everywhere.

Why not have

RESTful RDBMS???

Supports JSON DataType …..

JSON

In Database...

JavaScript As SQL Procedure

plv8++

-------------- "Taiwan"(1 row)

JS Function in DB

plv8x++

+

@clkao++

JavaScript

Has bad^H^H^H good parts...

and Tax.

LiveScript++

● Avoid JavaScript bad parts● Improved Readability● Less Typing● Support Backcall● Object Oriented Syntax Sugar● Thinking in a Functional Programming

manner

Plv8x: operators

SELECT entry |> 'this.location' FROM debminiconf;– 'taiwan'

Plv8x: operators

SELECT entry ~> '@location' FROM debminiconf;– 'taiwan'

Plv8x: operators

SELECT entry '@locatoin' <~ FROM debminiconf;– 'taiwan'

Plv8x: operators

SELECT ~> 'new Date';

[ { '?column?': '"2013-11-09T07:23:59.772Z"' } ]

Plv8x: command line $ npm i -g plv8x;

$ export PLV8XCONN=dbname;

$ plv8x -r script.ls # js works too.

$ plv8x -E 'plv8.execute(“SELECT entry FROM debminiconf”)'.0.entry.location

Plv8x: modules$ npm i -g qs

$ plv8x -i qs # same as: plv8x -i qs:/path/to/qs/package.json

$ psql debname

# parse a query stringtest=# select ~>'require("qs").parse("foo=bar&baz=1")' as qs; qs------------------------- {"foo":"bar","baz":"1"}

Plv8x: functions$ plv8x -f 'plv8x.json parse_qs(text)=qs:parse'ok plv8x.json parse_qs(text)

# Now parse_qs is a postgresql function:test=# select parse_qs('foo=bar&baz=1') as qs; qs------------------------- {"foo":"bar","baz":"1"}

Summary

● V8V8: JavaScript engine● PLV8PLV8: Stored procedure in JavaScript● Plv8xPlv8x: Package manager for PLV8

– Turn NPMNPM module into SQLSQL functions– JSON expressions with ~>~> and <~<~

● Code reuse for browserbrowser + serverserver + databasedatabase

OK!

We are good to talk PgREST now.

PgREST is ….

● JSON document store● Running inside PostgreSQL● Working with existing relational data● Capable of loading Node.js modules● Comparable with MongoLab's REST

API● = LiveScript + PLV8 + plv8x

$ npm i -g pgrst

$ pgrest –mydb dbnameinfo: Available collections:debminiconfinfo: Serving `tcp://postgres@localhost/mydb` on http://127.0.0.1:3000/collections

Start pgrest..

$ curl localhost:3000/collections/debminiconf{"paging":{"count":1,"l":30,"sk":0},"entries":[{"event":{"location":"Taiwan","year":"2013","people":[{"name":"hychen","country":"Taiwan"},{"name":"jserv","country":"Taiwan"},{"name":"paulliu","country":"Taiwan"}]}}]}

Reading

$ curl -H 'Content-Type: application/json' \ -X POST -d @data.json \ http://localhost:3000/collections/debminiconf

Writing

{"event":{"location": "bali","year":"2012","people":[]}

HTTP Request in REST...

GET => get a entry/entriesPOST => add a entry/entriesPUT => update a entryDELETE => delete a entry

Maps HTTP Requests in DB...

GET => SELECTPOST => INSERTPUT => UPDATEDELETE => DELETE

Access Control?

Use Schema

Public / Private

DO $$ BEGIN IF NOT EXISTS( SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'other' ) THEN EXECUTE 'CREATE SCHEMA other'; END IF; END $$;

$ pgrest –db dbname –schema other

•Define a view as an endpoint•

• Use rule system to redefine Create/Update/Read/Delete

Custom Request Handler

Exmaple of The ruleCREATE OR REPLACE RULE tags_add AS ON INSERT TO kuansim.tags

DO INSTEADWITH auth as (select pgrest_getauth() as auth_id)

INSERT INTO public.tags (name, author_id) VALUES(NEW.name, (SELECT auth_id FROM auth));

Cutting out the middleware

● Serve JSON API from SQL● Share models & validatoin code● Put Bussiness Logic into DB● Perfect fit for Medium Data

Come to join us.

● Website: http://postgre.st/● VCS: https://github.com/clkao/pgrest● IRC: freenode.net #pgrest

謝謝聆聽

Thanks!!

Reference● PgREST – Node.js in Database, Audrey Tang, osdc.tw.

● Single Page Apps with Node.js., Paolo Fragomeni

● plv8 - V8 Engine Javascript Procedural Language add-on for PostgreSQL

● Plv8x - Helpers for managing plv8 javascript modules