Home

What is PgRest

Warning PgRest is still in a very early stage, anything described here could be changed

PgRest enables REST services in PostgreSQL.

In short, PgRest is...

• a JSON document store.
• runing inside PostgreSql
• working with existing relational data
• capable of loading Node.js modules
• compatible with MongoLab's REST API
• = LiveScript + Plv8 + Plv8x + Express

Example:

$ pgrest --db tcp://postgres@localhost/mydb 
Available collections:
users bookmarks webpages news tags        inbox 
Serving `tcp://postgres@localhost/mydb` on http://0.0.0.0:3000/collections

$ curl localhost:3000/collections/users
{"paging":{"count":1,"l":30,"sk":0},"entries":[{"provider_name":"facebook","provider_id":"1231795847","username":"hychen","display_name":null,"name":{"familyName":"Chen","givenName":"Hsin","middleName":"Yi"},"emails":[{}],"photos":null,"_id":1}]}%   

Components

PLV8 - Use JavaScript In PostgresSQL

PLV8 is a PostgreSQL extension that allows you to write stored procedures in JavaScript.

With this program you can write in JavaScript your function that is callable from SQL.

Supported features are:

• Functions can receive any arguments, and return a value or set of records.
• Execute any SQL commands with built-in plv8.execute() function.
• Automatic data conversion between PostgreSQL and JavaScript, including string, numbers, timestamps, arrays, and records.
• Records are converted to JSON which keys are column names.
• Other types are converted to strings.
• Partial support to write TRIGGER handlers.
• Inline commands with DO statement for PG 9.0 or newer versions.
• EXTENSION support for PG 9.1 or newer versions.

plv8x - Helpers for managing javascript modules

• Importing npm(nodejs package manager) modules and creating user functions
• Calling conventions for user functions

LiveScript

LiveScript is a language which compiles to JavaScript. It has a straightforward mapping to JavaScript and allows you to write expressive code devoid of repetitive boilerplate. While LiveScript adds many features to assist in functional style programming, it also has many improvements for object oriented and imperative programming.

In summary, PgREST is an npm package written mostly in LiveScript, loaded into PostgreSQL and exported as PostgreSQL-level user functions. You can then use the included express.js-based pgrest to serve RESTful API based on those functions, or use ngx_postgresql to have nginx to talk PostgreSQL directly (experimental).

How does PgRest work?

PgRest exposes all tables in specified schema (public by default) as RESTful endpoints with a collections prefix.

For example, a table named users will be available at the endpoint http://localhost:3000/collections/users, and HTTP request methods are mapped as PgRest CURD (create, update, read, delete) functions:

• HTTP GET to pgrest_select
• HTTP POST to pgrest_insert
• HTTP PUT to pgrest_upsert
• HTTP DELETE to pgrest_delete

PgRest CURD functions are stored procedures for manipulating data in PostgreSQL.

• pgrest_select - get entities.
• pgrest_insert - add an entity
• pgrest_upsert - update or insert an entity or entities
• pgrest_delete - delete an entity

Example: insert a JSON object to a existing table:

// assign a json to user variable
user = {
  provider_name: profile.provider,
  provider_id: profile.id,
  username: profile.username,
  name: profile.name,
  emails: profile.emails,
  photos: profile.photos
};

// insert a json as a row 
plx.query("select pgrest_insert($1)", [{collection: "users", $: [user]}], function(res) {
  //result in res[0].pgrest_insert
});

With plv8 and plv8x, we could import node.js module, define javascript functions, use javascript functions in database, manipulate json in database. with PgRest, we could maps http CURD to database CURD.

With all the components together, we could move all business logic to database level.