PLV8 Typescript

This project converts Typescript functions into PostgreSQL database PLV8 functions.

Description

Write PLV8 functions (including trigger functions) in Typescript and enjoy editor support, type checking, linting, and prettier. Following the steps below will set up the compiler and a post processing script to emit SQL that can be executed with \i from the psql command line.

Limitations

• No support for including external packages.

Quick start

To try the example, copy the example files into a clean directory:

curl  https://raw.githubusercontent.com/Davepar/plv8ts/main/example/package.json -O
curl  https://raw.githubusercontent.com/Davepar/plv8ts/main/example/tsconfig.json -O
mkdir src; cd src
curl https://raw.githubusercontent.com/Davepar/plv8ts/main/example/src/sample_function.ts -O

Then install and build:

npm i
npm run build

The result will be in a subdirectory called sql. To add drop statements before each create function statement, use npm run build -- -d instead.

To use the lint and fix commands, copy the other files from the example directory on Github.

Note: You may be tempted to put the example code into an existing project, but the settings inside the example tsconfig.json are necessary to build the code correctly. See below for more details.

Writing functions

Add Typescript functions to the src directory, one function per file. Use the type names from the 'plv8ts' package to define parameters and return types. Be sure to export the function with export function. This trivial example:

export function add2(val: int8): smallint {
  return val + 2;
}

will produce this result:

create or replace function add2(val int8)
returns smallint AS $$
  return val + 2;
$$ language plv8;

To define functions in a schema (other than the default "public") create a subdirectory in src. This example will be defined in the private schema.

Calling PLV8 functions

To call functions defined by PLV8 add:

import {plv8} from 'plv8ts';

and use the functions that it defines. That will enable the Typescript compiler to do type checking. Some of those functions are generics that allow adding a type that will improve the type checking. For example, this code calls execute with an object type:

const value_rows = plv8.execute<{value: string}>(
  "select value from private.keys where key = 'EMAIL_TEMPLATE_PROMOTE_ATTENDEE'");
// Use value_rows[0].value

More examples in the function sample.

To see the full list of types and functions provided, take a look at the source code.

Trigger functions

To write a trigger function, return trigger<MyTableRow> where MyTableRow defines the type of the row for the trigger. You can also add a NEW parameter for insert and update triggers, and OLD for update and delete triggers.

export function sample_trigger(
  NEW: MyTableRow,
  OLD: MyTableRow
): trigger<MyTableRow> { ... }

See sample trigger for more details.

Function options

To add security definer, immutable, or stable to the function definition, add a comment in the function that starts with plv8:. For example:

// plv8: security definer, immutable

Lint

The example inclues Google Typescript Style, which provides a linter, a prettier auto-formatter, and strict code checking by default. This is useful for catching errors before using a function in your Postgres database.

Testing

The example Jasmine test shows how to write a local test for a PLV8 function. Any data returned from plv8.execute() and other PLV8 functions will need to be faked. Run tests with:

npm test

Tech details

The example Typescript config is currently using a target of es2015 and a moduleResolution of node. These were found through experimentation to produce the best output, i.e. the Javascript output is still readable. es2015 also seems to be the Javascript version supported by the current version of PLV8 (as of December 2022). The PLV8 documentation doesn't directly mention which ECMAScript version it supports.