PL/V8

What PL/V8 does

PL/V8 is a procedural language extension that lets you write PostgreSQL functions in JavaScript instead of PL/pgSQL or SQL. Under the hood, it runs Google's V8 engine — the same engine that powers Chrome and Node.js — inside the PostgreSQL backend process. Each database connection gets its own V8 isolate, providing memory isolation between sessions.

The key advantage is natural JSON handling. JavaScript objects map directly to PostgreSQL's jsonb type with no manual serialization. A function that receives a jsonb argument gets a native JavaScript object, and returning an object automatically produces jsonb output. This makes PL/V8 genuinely more ergonomic than PL/pgSQL for JSON-heavy workloads — where PL/pgSQL has you chaining ->> operators like a butler threading a needle with oven mitts.

PL/V8 also provides SPI (Server Programming Interface) access through plv8.execute() and plv8.prepare(), so JavaScript functions can query tables, insert rows, and call other functions. Combined with trigger support, this makes it possible to implement complex business logic entirely in JavaScript within the database.

When to use PL/V8

PL/V8 is a good fit when JavaScript's expressiveness is a clear advantage over PL/pgSQL. Not every function needs it — reaching for V8 to increment a counter would be rather like hiring an orchestra to play a doorbell — but certain patterns benefit significantly:

• JSON transformation — reshaping, validating, or normalizing JSON/JSONB data where PL/pgSQL's operator-based JSON access is cumbersome
• Complex string manipulation — regular expressions, template generation, parsing structured text formats
• Business logic porting — moving Node.js or browser-side validation logic into the database to enforce it at the data layer
• Algorithmic work — graph traversal, scoring algorithms, or any logic that benefits from JavaScript's control flow and data structures
• Rapid prototyping — writing database functions in a language your team already knows, without learning PL/pgSQL
• Trigger logic with JSON audit trails — capturing before/after state as JSON objects using JSON.stringify() on OLD and NEW row variables

Installation and setup

PL/V8 is a third-party extension — it does not ship with PostgreSQL's contrib modules. On self-managed servers, you need to install the plv8 package from your operating system's package manager or compile it from source. The compilation requires the V8 engine headers, which can be — if you will allow me a moment of candour — a genuinely unpleasant afternoon.

Once the shared library is available, creating the extension is straightforward and does not require a server restart.

-- Install the extension (no shared_preload_libraries needed)
CREATE EXTENSION plv8;

-- Verify it's working
SELECT plv8_version();

On managed cloud platforms that support PL/V8, the shared library is pre-installed — you only need to run CREATE EXTENSION plv8; (and on some platforms, allow-list it first).

Writing functions

Basic function with ES6+ syntax

PL/V8 functions use standard CREATE FUNCTION syntax with LANGUAGE plv8. The function body is JavaScript.

-- A simple function using ES6+ syntax
CREATE OR REPLACE FUNCTION hello(name text)
RETURNS text AS $$
  return `Hello, ${name}!`;
$$ LANGUAGE plv8;

SELECT hello('world');
-- Returns: Hello, world!

JSON handling

When a function accepts or returns jsonb, PL/V8 automatically converts between PostgreSQL JSONB and native JavaScript objects. No parsing or stringifying needed.

-- Transform JSON data with native JavaScript
CREATE OR REPLACE FUNCTION normalize_address(input jsonb)
RETURNS jsonb AS $$
  const addr = input;
  return {
    street: (addr.street || '').trim().toUpperCase(),
    city: (addr.city || '').trim(),
    state: (addr.state || '').trim().toUpperCase(),
    zip: (addr.zip || '').replace(/[^0-9]/g, '').slice(0, 5)
  };
$$ LANGUAGE plv8;

SELECT normalize_address('{"street": " 123 main st ", "city": "Portland", "state": "or", "zip": "97201-1234"}');
-- Returns: {"street": "123 MAIN ST", "city": "Portland", "state": "OR", "zip": "97201"}

Native object access. No operator chains. The address goes in untidy; it comes out presentable. This is what JavaScript was put on this earth to do — whatever else it may have wandered into since.

SPI queries with plv8.execute()

plv8.execute(sql, args) runs a SQL query and returns the results as an array of JavaScript objects. Parameters use $1, $2, etc., with values passed in an array — the same parameterized query pattern as any other PostgreSQL client.

-- Query other tables using plv8.execute()
CREATE OR REPLACE FUNCTION get_user_summary(user_id int)
RETURNS jsonb AS $$
  const user = plv8.execute(
    'SELECT name, email FROM users WHERE id = $1', [user_id]
  )[0];

  const orders = plv8.execute(
    'SELECT count(*)::int AS total, sum(amount)::numeric AS revenue FROM orders WHERE user_id = $1', [user_id]
  )[0];

  return {
    name: user.name,
    email: user.email,
    totalOrders: orders.total,
    totalRevenue: parseFloat(orders.revenue)
  };
$$ LANGUAGE plv8;

Prepared statements with plv8.prepare()

For repeated queries within a single function call, plv8.prepare() avoids re-planning on each execution. The prepared plan must be freed with plan.free() before the function returns — neglecting this is the sort of memory leak that compounds quietly until three in the morning.

-- Use prepared statements for repeated queries
CREATE OR REPLACE FUNCTION batch_lookup(ids int[])
RETURNS jsonb AS $$
  const plan = plv8.prepare('SELECT id, name, status FROM items WHERE id = $1', ['int']);
  const results = [];

  for (const id of ids) {
    const rows = plan.execute([id]);
    if (rows.length > 0) {
      results.push(rows[0]);
    }
  }

  plan.free();
  return results;
$$ LANGUAGE plv8;

Startup procedures

PL/V8 supports a startup procedure that runs once when the V8 runtime initializes for a connection. This is configured via the plv8.start_proc GUC variable. The initialization function can attach shared utilities, constants, or lookup data to the global scope, making them available to all subsequent PL/V8 function calls in that session.

-- Define an initialization function
CREATE OR REPLACE FUNCTION plv8_init()
RETURNS void AS $$
  // Attach shared utilities to the global object
  this.formatCurrency = function(amount, currency) {
    return currency + ' ' + amount.toFixed(2);
  };

  this.isValidEmail = function(email) {
    return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email);
  };
$$ LANGUAGE plv8;

-- Set the startup procedure (in postgresql.conf or per-session)
SET plv8.start_proc = 'plv8_init';

-- Now any plv8 function can use the globals
CREATE OR REPLACE FUNCTION validate_order(input jsonb)
RETURNS jsonb AS $$
  const errors = [];
  if (!isValidEmail(input.email)) errors.push('Invalid email');
  if (input.amount <= 0) errors.push('Amount must be positive');

  return {
    valid: errors.length === 0,
    errors: errors,
    formatted: errors.length === 0 ? formatCurrency(input.amount, 'USD') : null
  };
$$ LANGUAGE plv8;

Set plv8.start_proc before any PL/V8 function runs — including before CREATE FUNCTION ... LANGUAGE plv8, since that statement also initializes the runtime. The safest approach is to set it in postgresql.conf.

Trigger functions

PL/V8 trigger functions receive the standard trigger variables — NEW, OLD, TG_OP, TG_TABLE_NAME, and others — as JavaScript globals. The NEW and OLD variables are JavaScript objects with properties matching the table's column names.

-- Use PL/V8 for trigger functions
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS trigger AS $$
  const old_row = OLD ? JSON.stringify(OLD) : null;
  const new_row = NEW ? JSON.stringify(NEW) : null;

  plv8.execute(
    'INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_at) VALUES ($1, $2, $3, $4, now())',
    [TG_TABLE_NAME, TG_OP, old_row, new_row]
  );

  return NEW;
$$ LANGUAGE plv8;

CREATE TRIGGER orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit_changes();

Cloud availability

How Gold Lapel relates

I should be forthcoming here: PL/V8 functions are largely opaque to me. Gold Lapel operates at the proxy layer, so when your application calls SELECT my_function(args), I see the function invocation — I can tell you how often it runs and how long it takes. But the JavaScript executing inside it, and any queries it issues through plv8.execute(), run through the SPI interface within the backend. They never pass through the proxy. I cannot see them.

This is not a limitation I enjoy admitting, but a butler who overstates his reach does no one any favours. For workloads that lean heavily on PL/V8, the view from the proxy is coarser — the function appears as a single query pattern rather than the decomposed operations within it.

If you would like Gold Lapel's full attention on your heaviest queries, consider keeping the data-intensive SQL in the application layer where I can see and attend to it, and letting PL/V8 handle what it does best: the JSON transformation, validation, and string work that belongs in JavaScript. A sensible division of labour, if I may say so.