by Craig Kerstiens - Jun 05

The same JavaScript engine that powers the web today is now available in your database.

This is one more step in evolving a data platform to meet all of your data needs. With a key/value store inside Postgres you gained agility in working with your schema. This agility was further improved with the JSON data type in Postgres 9.2. With geospatial support you removed the need for relying on additional tools for building location based apps. And today we’re continuing to expand, going beyond SQL bringing the full power of the V8 JavaScript engine to your Heroku Postgres database. This offering is available immediately in public beta on all production tier databases.

More on V8

V8 is a powerful and fast JavaScript engine that was developed by Google, in addition to powering Google Chrome it can be found in Node.js and MongoDB. From its initial design V8 was intended to work both for browsers to run client side JavaScript and be integrated into other projects such as powering server side execution in the case of Node.js.

PL/V8, thanks to a lot of work from Hitoshi Harada, is this same V8 but as a procedural language within Postgres. PL/V8 is fully trusted language giving you a peace of mind when it comes the safety of your data, but enables a whole new powerful set of functionality. Want to write functions on your data without touching pl-pgsql? Want to put documents within your database? Want to run your CoffeeScript unit tests closer to your data? You now can do all of it with PL/V8.

Getting started

If you’re already taking advantage of the JSON datatype for some of your applications and want to begin using PL/V8, now you can by simply enabling the extension:

> CREATE EXTENSION plv8;

From here we can create a simple JavaScript procedure that returns the values for an array of keys we pass in:

> CREATE OR REPLACE 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;

Of note in the above function is `IMMUTABLE` and `STRICT`. Immutable specifies that the function given the same inputs will return the same result. The optimizer therefore knows that it can pre-evaluate the function. If you lie to the optimizer, it will give you wrong answers. Strict means that if you send in NULL values you’ll get a null result.

And then take advantage of it:

> SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Craig', '29']);
          plv8_test
-----------------------------
 {"name":"Craig","age":"29"}
(1 row)

More Advanced PL/V8 Usage

Lets take a look at a more practical use case. Given some example JSON data such as:

> SELCT * FROM zips;
                                 data
---------------------------------------------------------------------
 {"city": "ACMAR", "loc": [-86.5, 33.5], "pop": 6055, "state": "AL"}
 {"city": "ARAB", "loc": [-86.4, 34.3], "pop": 13650, "state": "AL"}
...

It may be common to filter this data for some report, i.e. all cities with population greater than 10,000. To do this you first create a function – by creating a generic function that returns numeric value of a given key from a set of JSON, you can also re-use it elsewhere:

> CREATE OR REPLACE FUNCTION 
get_numeric(key text, data json)
RETURNS numeric AS $$
return data[key];
$$ LANGUAGE plv8 IMMUTABLE STRICT;
CREATE FUNCTION

Then we can use the function in our query:

> SELECT * 
FROM zips 
WHERE get_numeric('pop', data) > 10000;
                                 data
------------------------------------------------------------------------
{"city": "PERU", "loc": [-89.1, 41.3], "pop": 10050, "state": "IL"}
{"city": "JUNO", "loc": [-84.1, 34.3], "pop": 10196, "state": "GA"}
...

Functional Indexes

The ability to use JavaScript as part of your query through user defined functions provides great flexibility and continues to expand beyond just including javascipt snippets inline in your queries. Postgres allows you to create indexes on any expression, including functions. With PL/V8, it is possible to create an index on the function above:

> CREATE INDEX idx_pop 
ON zips(get_numeric('pop'::text, data));

Functional indexes that take advantage of V8 can also prove some great performance benefits. By adding the above index the query time goes from 206.723 ms down to 0.157 ms.

Summary

The world of application development is rapidly changing delivering new tools every day to make you more productive. Postgres and the database world are no different, now with JavaScript and JSON support. This powerful functionality is now available on all Heroku Postgres production tier databases – run CREATE EXTENSION plv8; on your database to get started today.