Did you know that the same JavaScript engine that powers the web today is available in your database. PLV8 is an open-source procedural language extension for PostgreSQL that allows users to write database functions in JavaScript using the V8 engine.
PLV8 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 and later enhanced with JSONB in Postgres 9.4, offering faster querying and indexing capabilities for JSON data. With geospatial support you removed the need for relying on additional tools for building location based apps. With PLV8, Postgres JavaScript goes beyond SQL bringing the full power of the V8 JavaScript engine to your Heroku Postgres database.
What is a PostgreSQL procedural language?
A procedural language (PL) allows you to write functions that extend the database’s built-in SQL capabilities in other programming languages, such as PL/pgSQL, PLPython, and PLV8 (JavaScript). You can use these languages to create custom functions for more complex logic, perform conditional operations, and execute loops within the database.
Procedural languages in Postgres are “trusted.” They are designed to safely interact with the database while adhering to security and performance best practices. By supporting multiple PLs, PostgreSQL offers flexibility, allowing developers to choose the best language for their needs while keeping data operations close to the database for efficiency.
PLV8 vs Postgres.js
PLV8 and Postgres.js both make it easier to use JavaScript and PostgreSQL together, but they play different roles. The two are occasionally confused, so it's worth clarifying the difference. As we've said, PLV8 is a PostgreSQL extension that embeds the V8 engine, allowing JavaScript to run directly within the database for efficient, server-side data processing.
Postgres.js, on the other hand, is a JavaScript client library that enables applications to connect to and query PostgreSQL from the client side. While PLV8 brings JavaScript into PostgreSQL for custom functions and logic, Postgres.js operates externally to facilitate communication between JavaScript applications and the database.
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.
PLV8, thanks to a lot of work from Hitoshi Harada, is this same V8 but as a procedural language within Postgres. PLV8 is a fully trusted language giving you peace of mind when it comes to 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 unit tests closer to your data? You now can do all of it with PLV8.
Getting started
If you’re already taking advantage of the JSON or JSONB 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 PLV8 Usage
Lets take a look at a more practical PLV8 example. 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 a report, e.g., all cities with population greater than 10,000. To do this you first create a generic function that returns the numeric value of a given key from a set of JSON:
> 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 JavaScript snippets inline in your queries. Postgres allows you to create indexes on any expression, including functions. With PLV8, 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.