Offensive Security Researcher & Quantum Cryptography Analyst

How to Use the PL/JavaScript Language in PostgreSQL

Abdul Wahab Junaid
How to Use the PL/JavaScript Language in PostgreSQL

JavaScript is everywhere in modern application development, so it’s a natural question to ask: can you write PostgreSQL functions in JavaScript too? The answer is yes, through a procedural language extension generally referred to as PL/JavaScript (historically implemented via projects like PL/V8, which embeds Google’s V8 JavaScript engine directly into PostgreSQL).

In this article, I’ll walk through what PL/JavaScript is, how to install and enable it, the syntax for writing functions, practical real-world examples, and the operational considerations and troubleshooting tips you need to know.

What Is PL/JavaScript?

PL/JavaScript is a procedural language extension that lets you write PostgreSQL functions using JavaScript syntax, executed inside the server process via an embedded JavaScript engine. The most well-known implementation is PL/V8, built on the V8 engine (the same engine that powers Node.js and Chrome), which gives you a genuinely fast, modern JavaScript runtime running directly alongside your data.

Like PL/Ruby, PL/JavaScript is not part of PostgreSQL’s default installation. You need to install it as a separate extension, and its packaging availability depends heavily on your operating system, distribution, and PostgreSQL version.

Why You Might Choose PL/JavaScript

Installing PL/JavaScript (PL/V8)

Package availability varies by platform. On Debian/Ubuntu systems, check for a package matching your PostgreSQL version:

sudo apt search postgresql-plv8

If a package is available for your specific PostgreSQL major version, installation is usually as simple as:

sudo apt install postgresql-16-plv8

(Adjust the version number to match your installed PostgreSQL version.) If no prebuilt package exists for your combination of OS and PostgreSQL version, you’ll need to build from source, which typically involves a more involved compilation process since it bundles or links against V8 itself. Check the project’s build documentation for your specific PostgreSQL version before starting.

Once installed at the OS level, enable it inside your target database:

CREATE EXTENSION plv8;

You can confirm it’s active with:

SELECT * FROM pg_extension WHERE extname = 'plv8';

Trusted vs. Untrusted Considerations

PL/V8 runs in a sandboxed mode by default, similar in spirit to trusted procedural languages elsewhere in PostgreSQL — JavaScript functions don’t get arbitrary filesystem or network access. This makes it reasonably safe for non-superuser roles with function-creation privileges to use, though exact privilege requirements can vary depending on your PL/V8 version and how your server roles are configured, so verify this in your specific environment before assuming broad access is appropriate.

Basic Function Syntax

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT
AS $$
  return "Hello, " + name + "!";
$$ LANGUAGE plv8;

Function arguments become directly available as JavaScript variables matching their SQL parameter names, and you use a standard JavaScript return statement to send back the result, unlike PL/Ruby’s implicit last-expression return.

Practical Examples

Example 1: Basic String Processing

CREATE OR REPLACE FUNCTION slugify(input TEXT)
RETURNS TEXT
AS $$
  return input
    .toLowerCase()
    .trim()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-+|-+$/g, '');
$$ LANGUAGE plv8;

SELECT slugify('Hello, World! This is a Test.');
-- returns: hello-world-this-is-a-test

Example 2: Processing JSONB Data Natively

This is where PL/JavaScript genuinely shines compared to other procedural languages — working with JSON data feels completely native, since PostgreSQL’s JSONB values are automatically converted into real JavaScript objects.

CREATE OR REPLACE FUNCTION extract_tags(data JSONB)
RETURNS TEXT[]
AS $$
  if (!data.tags) return [];
  return data.tags.filter(function(tag) {
    return typeof tag === 'string' && tag.length > 0;
  });
$$ LANGUAGE plv8;

SELECT extract_tags('{"tags": ["postgres", "", "javascript", 42]}'::jsonb);
-- returns: {postgres,javascript}

Example 3: Returning a Set of Rows

CREATE OR REPLACE FUNCTION json_array_to_rows(data JSONB)
RETURNS SETOF JSONB
AS $$
  return data;
$$ LANGUAGE plv8;

SELECT * FROM json_array_to_rows('[{"a":1},{"a":2},{"a":3}]'::jsonb);

When a PL/V8 function is declared RETURNS SETOF and returns a JavaScript array, each element becomes a row in the result set.

Example 4: Aggregating and Transforming Data Server-Side

CREATE OR REPLACE FUNCTION compute_stats(values NUMERIC[])
RETURNS JSONB
AS $$
  var sum = values.reduce(function(a, b) { return a + b; }, 0);
  var avg = sum / values.length;
  var sorted = values.slice().sort(function(a, b) { return a - b; });
  var median = sorted.length % 2 === 0
    ? (sorted[sorted.length / 2 - 1] + sorted[sorted.length / 2]) / 2
    : sorted[Math.floor(sorted.length / 2)];

  return {
    count: values.length,
    sum: sum,
    average: avg,
    median: median,
    min: sorted[0],
    max: sorted[sorted.length - 1]
  };
$$ LANGUAGE plv8;

SELECT compute_stats(ARRAY[4, 8, 15, 16, 23, 42]);

Example 5: Using Functions as Triggers

PL/JavaScript functions can be used in triggers just like PL/pgSQL functions:

CREATE OR REPLACE FUNCTION validate_email_trigger()
RETURNS TRIGGER
AS $$
  var emailPattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  if (!emailPattern.test(NEW.email)) {
    plv8.elog(ERROR, "Invalid email format: " + NEW.email);
  }
  return NEW;
$$ LANGUAGE plv8;

CREATE TRIGGER check_email
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION validate_email_trigger();

Note the use of plv8.elog() for raising errors and log messages — this is PL/V8’s interface into PostgreSQL’s own logging and error system.

Example 6: Running Queries Inside a Function

PL/V8 exposes plv8.execute() for running SQL from within your JavaScript function body:

CREATE OR REPLACE FUNCTION get_customer_order_count(cust_id INT)
RETURNS INT
AS $$
  var result = plv8.execute(
    "SELECT count(*) AS cnt FROM orders WHERE customer_id = $1",
[cust_id]

); return result[0].cnt; $$ LANGUAGE plv8;

Common Use Cases

Troubleshooting Common Issues

“language plv8 does not exist” Error

The extension hasn’t been created in your current database. Run:

CREATE EXTENSION plv8;

If this fails, the shared library likely isn’t installed at the OS level — check package availability for your specific PostgreSQL version, or build from source.

Extension Installation Fails Due to Version Mismatch

PL/V8 packages are typically built against a specific PostgreSQL major version. If you’ve upgraded PostgreSQL and PL/V8 wasn’t rebuilt or reinstalled against the new version, CREATE EXTENSION will likely fail or the server may fail to load the library at all. Reinstall the matching PL/V8 package for your new PostgreSQL version after any major upgrade.

Function Fails With Unexpected JavaScript Errors

Since function bodies are genuine JavaScript, standard JavaScript errors (TypeError, ReferenceError, and so on) can occur just like in any JS environment — for instance, calling a method on undefined when a JSONB field is missing. Defensive coding (checking for null/undefined before accessing nested properties) matters just as much here as in any other JavaScript codebase.

Performance Degradation With Very Frequent Calls

While V8 is fast, there’s still overhead in crossing from PostgreSQL’s execution engine into the JavaScript runtime on every function call. For extremely hot-path functions called on every row of very large result sets, benchmark against an equivalent PL/pgSQL or native SQL implementation before assuming PL/JavaScript is the faster choice — the JSON-handling convenience doesn’t always outweigh the per-call overhead at very high volumes.

Memory Usage Growing Over Time

Because PL/V8 keeps a persistent JavaScript context across calls within a session for efficiency, poorly written functions that leak references or accumulate global state across many invocations can contribute to unexpected memory growth in long-lived connections. Keep function-scoped variables properly scoped (using var/let within the function body) rather than relying on implicit globals.

Best Practices

  1. Lean into JSON/JSONB use cases. This is where PL/JavaScript’s advantages over PL/pgSQL are most concrete — don’t reach for it just for general-purpose logic that PL/pgSQL already handles cleanly.
  2. Reinstall or rebuild PL/V8 after every PostgreSQL major version upgrade, and test thoroughly in staging first, since compatibility isn’t automatic.
  3. Write defensive JavaScript, checking for null/undefined on JSONB-derived values before accessing nested properties, just as you would in any JavaScript codebase handling untrusted or variable-shaped input.
  4. Benchmark before committing to PL/JavaScript for extremely high-frequency function calls. V8 is fast, but the call overhead compared to native SQL still matters at scale.
  5. Use plv8.elog() for proper error handling and logging rather than relying on uncaught JavaScript exceptions to propagate implicitly — explicit error handling gives clearer messages to whoever ends up debugging a failure later.
  6. Keep functions focused and well-documented. As with any less mainstream procedural language choice, future maintainers benefit from clear comments explaining why PL/JavaScript was chosen for a particular function over PL/pgSQL.

Comparing PL/JavaScript to Other Procedural Language Options

As with any non-default procedural language choice, it’s worth understanding where PL/JavaScript fits relative to the alternatives.

PL/pgSQL remains the sensible default for most stored procedures — tightly integrated, officially maintained, and well understood by essentially every PostgreSQL user. For straightforward data manipulation logic, it’s usually the right first choice.

PL/Python is a strong general-purpose alternative with broad community support, a mature ecosystem, and wide familiarity, making it a common choice for teams that want a general scripting language without being specifically tied to JSON-heavy workloads.

PL/JavaScript (PL/V8), by contrast, earns its place specifically around JSON/JSONB-heavy processing and teams with strong existing JavaScript expertise. Its V8-powered performance is a genuine advantage for computationally intensive logic compared to some other embedded scripting options, and its native JSON handling is difficult to match in PL/pgSQL, which requires more verbose built-in functions (jsonb_build_object, jsonb_each, and so on) to accomplish similar transformations.

If your workload is JSON-light and your team doesn’t have particular JavaScript investment, PL/pgSQL or PL/Python will likely serve you better with less installation and maintenance friction. If you’re doing heavy JSONB transformation work and already have JavaScript expertise on the team, PL/JavaScript is a genuinely strong fit worth the extra installation effort.

Custom Aggregates With PL/JavaScript

Beyond simple functions, PL/V8 can also back custom aggregate functions, which is particularly powerful when combined with JavaScript’s native array and object handling for accumulating complex state across rows:

CREATE OR REPLACE FUNCTION jsonb_merge_state(state JSONB, val JSONB)
RETURNS JSONB
AS $$
  return Object.assign({}, state, val);
$$ LANGUAGE plv8;

CREATE AGGREGATE jsonb_merge_agg(JSONB) (
    SFUNC = jsonb_merge_state,
    STYPE = JSONB,
    INITCOND = '{}'
);

SELECT jsonb_merge_agg(data) FROM some_table;

This merges JSONB values across all rows in a group into a single combined object, something that would require considerably more verbose code to express directly in PL/pgSQL.

Debugging PL/JavaScript Functions

Since these functions run inside the PostgreSQL server process rather than in a browser or Node.js environment, typical JavaScript debugging tools (browser dev tools, Node’s debugger) aren’t available. The most practical debugging approach is liberal use of plv8.elog() at the NOTICE level during development, to trace values and execution flow:

CREATE OR REPLACE FUNCTION debug_example(data JSONB)
RETURNS JSONB
AS $$
  plv8.elog(NOTICE, "Input received: " + JSON.stringify(data));
  var result = { processed: true, original: data };
  plv8.elog(NOTICE, "Result: " + JSON.stringify(result));
  return result;
$$ LANGUAGE plv8;

With client_min_messages set to NOTICE or lower in your session, these messages appear directly in your psql output, giving you a reasonably effective, if old-fashioned, debugging workflow for tracing what’s happening inside a function during development.

Working With Arrays and Composite Types

Beyond JSONB, PL/JavaScript also handles PostgreSQL arrays and composite types in a way that maps naturally onto JavaScript’s own array and object structures, which is another point in its favor for certain data-shaping tasks:

CREATE OR REPLACE FUNCTION array_stats(nums NUMERIC[])
RETURNS TABLE(total NUMERIC, mean NUMERIC, max_val NUMERIC)
AS $$
  var sum = nums.reduce(function(a, b) { return a + b; }, 0);
  return [{
    total: sum,
    mean: sum / nums.length,
    max_val: Math.max.apply(null, nums)
  }];
$$ LANGUAGE plv8;

SELECT * FROM array_stats(ARRAY[10, 25, 30, 45, 60]);

PostgreSQL arrays passed into a PL/V8 function arrive as genuine JavaScript arrays, so all the usual array methods (map, filter, reduce, sort) work exactly as you’d expect from any other JavaScript context, without needing PostgreSQL-specific array-handling syntax.

Startup and Per-Connection Initialization

PL/V8 supports a special initialization function that runs once per session/connection when the first PL/V8 function in that session executes, useful for setting up shared state or helper functions that multiple PL/V8 functions in the same session might want to reuse:

-- Configure this as plv8.start_proc in postgresql.conf, or session-level
CREATE OR REPLACE FUNCTION plv8_init()
RETURNS VOID
AS $$
  plv8.roundTo = function(num, places) {
    var factor = Math.pow(10, places);
    return Math.round(num * factor) / factor;
  };
$$ LANGUAGE plv8;

Once configured as the designated startup procedure (via the plv8.start_proc configuration parameter), helper functions like plv8.roundTo become available to every subsequent PL/V8 function call within that session, avoiding the need to redefine common helper logic inside every single function body.

Security Considerations Specific to PL/V8

Even in trusted mode, it’s worth understanding what PL/V8’s sandboxing does and doesn’t cover. It restricts direct filesystem and network access from within JavaScript code, similar in spirit to other trusted procedural languages. However, because JavaScript functions can still consume significant CPU and memory (an inefficient or accidentally infinite loop, for instance), it’s worth pairing PL/V8 usage with reasonable statement_timeout settings at the role or session level, so a poorly written function can’t tie up a database connection indefinitely:

ALTER ROLE app_user SET statement_timeout = '30s';

This isn’t unique to PL/V8 specifically, but it’s a particularly relevant safeguard given how easy it is to accidentally write a runaway loop in JavaScript compared to, say, straightforward PL/pgSQL data manipulation logic.

When PL/JavaScript Is the Wrong Choice

It’s worth being just as clear about when PL/JavaScript is a poor fit as when it’s a good one. For simple data validation or straightforward CRUD-adjacent logic with no JSON involved, PL/pgSQL is almost always simpler to write, debug, and maintain, without the added installation and version-compatibility burden. For CPU-intensive numerical work at very high call volumes, the interpreter crossing overhead can outweigh V8’s raw execution speed advantage, especially compared to logic that could be expressed as set-based SQL operating on the whole table at once rather than row-by-row function calls. Reserve PL/JavaScript specifically for the cases where its JSON-native handling or JavaScript-specific ecosystem familiarity provides a clear, demonstrable advantage over the simpler alternatives already available in PostgreSQL core.

Wrapping Up

PL/JavaScript, most commonly encountered as PL/V8, is a genuinely capable option for teams that want fast, native JSON handling and JavaScript’s familiar syntax directly inside PostgreSQL. It’s particularly well-suited to JSONB-heavy transformation logic and data validation in triggers, where JavaScript’s object handling is a natural fit for PostgreSQL’s semi-structured data types. As with any non-default procedural language, plan for the installation and version-compatibility overhead, write defensively, and reserve it for the use cases where its strengths — JSON handling and V8’s performance — genuinely outweigh sticking with PL/pgSQL.

Exit mobile version