Helpers for managing plv8 javascript modules
Clone or download


Build Status

plv8x helps you manage functions and packages in plv8, postgresql's javascript procedural language support.

Quick start with docker

Using the docker-based postgresql with plv8js enabled:

% docker run -p 5433:5432 -d --name postgres clkao/postgres-plv8:9.4

% createdb -U postgres -h -p 5433 test
% export PLV8XDB=postgres://postgres@

% plv8x --list
plv8x: 392.25 kB

# import the qs package from npm
% npm i qs; plv8x -i qs; plv8x --list
plv8x: 392.25 kB
qs: 9.37 kB

# this is now evaluated inside postgresql
% plv8x -e 'require("qs").parse("foo=bar&baz=1")'
{ foo: 'bar', baz: '1' }

# .. which  is actually equivalent to:
% psql $PLV8DB -c 'select |> $$ require("qs").parse("foo=bar&baz=1") $$'
(1 row)

Install plv8js

Note: Requires postgresql 9.0 or later.

postgresql PGDG apt respository now ships plv8js extension:

wget --quiet -O - | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.2-plv8

Or you can install with pgxnclient:

sudo easy_install pgxnclient
sudo pgxn install plv8

Install LiveScript (pre-requisite)

% npm i -g LiveScript

Install plv8x

% git clone git://; cd plv8x
% npm i -g .

Quick start

Enable plv8x for your database:

% createdb test
% plv8x -d test -l
plv8x: 491425 bytes

We support synonymous PLV8XDB and PLV8XCONN environment variables, so there's no need to type -d over and over again on the command line:

% export PLV8XDB=test

To connect with ident (local Unix user) authentication, specify the path to the socket directory with -d:

% plv8x -d /var/run/postgresql -l
plv8x: 491425 bytes

Now create some test data with json columns: (example table from Postgres 9.3 feature highlight: JSON operators)

% psql test
test=# CREATE TABLE aa (a int, b json);
test=# INSERT INTO aa VALUES (1, '{"f1":1,"f2":true,"f3":"Hi I''m \"Daisy\""}');
test=# INSERT INTO aa VALUES (2, '{"f1":{"f11":11,"f12":12},"f2":2}');
test=# INSERT INTO aa VALUES (3, '{"f1":[1,"Robert \"M\"",true],"f2":[2,"Kevin \"K\"",false]}');

Instead of b->'f1', we use b~>'this.f1', which means bind b as this and evaluate the right hand side (this.f1):

test=# SELECT b~>'this.f1' AS f1, b~>'this.f3' AS f3 FROM aa WHERE a = 1;
 f1 |         f3
 1  | "Hi I'm \"Daisy\""

If you like coffee, @ works too:

test=# SELECT b~>'@f1' AS f1, b~>'@f3' AS f3 FROM aa WHERE a = 1;
 f1 |         f3
 1  | "Hi I'm \"Daisy\""

For multiple keys, you can of course do b~>'@f1'~>'@f12', but single expression will do:

test=# SELECT b~>'@f1'~>'@f12' AS f12_long, b~>'@f1.f12' AS f12 FROM aa WHERE a = 2;
 f12_long | f12
 12       | 12

Ditto for arrays:

postgres=# SELECT b~>'@f1[0]' as f1_0 FROM aa WHERE a = 3;

Unary ~> for just evaluating the expression:

test=# SELECT ~>'[1 to 10]' AS f1

~> is actually a shorthand for |> '~>...'. Using raw |> for plain old javascript:

test=# SELECT '{"foo": [1,2,3]}'::json |> 'function() { return[1] }';

Expression works too:

test=# SELECT '{"foo": [1,2,3]}'::json |> 'return[1]';


test=# SELECT '{"foo": [1,2,3]}'::json |> '@foo[1]';

<| is |> reversed:

test=# SELECT '@foo.1 * 5' <| '{"foo": [1,2,3]}'::json

|> as unary operator:

test=# SELECT |> '~> plv8x.require "LiveScript" .compile "-> \Hello" {+bare}';
 "(function(){\n  return Hello;\n});"

Importing nodejs modules and creating user functions

Let's try reusing some existing npm modules:

% npm i -g qs
% plv8x -i qs # same as: plv8x -i qs:/path/to/qs/package.json
% psql test

# parse a query string
test=# SELECT |>'require("qs").parse("foo=bar&baz=1")' AS qs;

# actually use the parsed query string as json
test=# SELECT qs~>'@foo' AS foo FROM  (SELECT ~>'require("qs").parse("foo=bar&baz=1")' AS qs) a;

# create a user function from qs so we don't have to require it:
% 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;

Calling conventions for user functions

We support both synchronous and async functions, as well as bare functions defined in module.exports.

By default, the first two arguments to an async (back-call) function is taken to be error and result respectively:

% plv8x -f 'fn(text):text=pkg:'           # out = pkg(x)
% plv8x -f 'fn(text):text=pkg:method'     # out = pkg.method(in)
% plv8x -f 'fn(text):text=pkg:<-'         # pkg(x, cb(err, out))
% plv8x -f 'fn(text):text=pkg:<-method'   # pkg.method(x, cb(err, out))

Using an underscore, one can specify exactly which async callback parameter to expect from the lifted function:

% plv8x -f 'fn(text):text=pkg:<-'         # pkg(x, cb(err, out))
% plv8x -f 'fn(text):text=pkg:_<-'        # pkg(x, cb(out))
% plv8x -f 'fn(text):text=pkg:,_<-'       # pkg(x, cb(_0, out))
% plv8x -f 'fn(text):text=pkg:,,_<-'      # pkg(x, cb(_0, _1, out))