plv8ify bundles typescript/javascript input and writes SQL file(s) containing Postgres functions using PLV8.

• Interchange code between API and Database
• Write code in Typescript and use it in Postgres

1. npm install -g plv8ify
2. Sample input.ts

const { point: turfPoint } = require('@turf/helpers') export function point(lat, long) { const pt = turfPoint([lat, long]) return pt }

3. Run plv8ify generate
4. Step 3 emits SQL file with names like ./plv8-dist/plv8ify_point.plv8.sql (one for each exported function)
5. Execute the generated ./plv8-dist/plv8ify_point.plv8.sql using a Postgres client
6. Call the generated function using a Postgres client SELECT plv8ify_point(52.5200,13.4050);

See all examples in the examples folder. Use yarn examples to apply any changes to all the examples.

To generate a function to be deployed on a schema different than the default one (usually: public) decorate the function with a /** @plv8ify_schema_name <schemaname> */ jsdoc tag

/** @plv8ify_schema_name testschema */ export function hello() { return 'world' }

will generate

DROP FUNCTION IF EXISTS testschema.plv8ify_hello(); CREATE OR REPLACE FUNCTION testschema.plv8ify_hello() RETURNS text AS $plv8ify$ // input.ts function hello() { return "world"; } return hello() $plv8ify$ LANGUAGE plv8 IMMUTABLE STRICT;

To write a trigger function, decorate the function with a /** @plv8ify_trigger */ jsdoc tag, and have the function return a testRow type where testRow 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. (Tip: you can add @types/pg and @types/plv8-internals to get all standard postgres types/defines and plv8 specific functions recognized by the type checker)

type Row = { // Either JS or plv8 types can be used here id: number event_name: string event_date_time: Date } /** @plv8ify_trigger */ export function test(NEW: Row, OLD: Row): Row { plv8.elog(NOTICE, 'NEW = ', JSON.stringify(NEW)); plv8.elog(NOTICE, 'OLD = ', JSON.stringify(OLD)); plv8.elog(NOTICE, 'TG_OP = ', TG_OP); plv8.elog(NOTICE, 'TG_ARGV = ', TG_ARGV); if (TG_OP === 'UPDATE') { NEW.event_name = NEW.event_name ?? OLD.event_name return NEW } if (TG_OP === 'INSERT') { NEW.id = 102 return NEW } }

By default plv8ify converts typescript types to postgres types using the following map:

 private _typeMap = { number: 'float8', string: 'text', boolean: 'boolean', } 

and defaults all other types to either JSONB or the type passed in using the --fallback-type option It is possible to define additional type mapping by using a custom file (by default types.ts) with the following format:

typeMap = { test_type: 'test_type', 'test_type[]': 'test_type[]', } 

The custom types will be merged with the default ones at runtime and will allow using either internal postgres type or custom defined types

Example:

types.ts

typeMap = { test_type: 'test_type', 'test_type[]': 'test_type[]', }

input.ts

interface test_type { name: string age: number } export function hello(test: test_type[]) { return { name: `Hello ${test[0].name}`, age: test[0].age, } }

cli command line:

plv8ify generate input.ts --types-config-file types.ts 

will generate this function:

DROP FUNCTION IF EXISTS plv8ify_hello(test test_type[]); CREATE OR REPLACE FUNCTION plv8ify_hello(test test_type[]) RETURNS JSONB AS $plv8ify$ // input.ts function hello(test) { return { name: `Hello ${test[0].name}`, age: test[0].age }; } return hello(test) $plv8ify$ LANGUAGE plv8 IMMUTABLE STRICT;

Additionally, you can decorate your functions with special jsdoc tags to control the type mapping of params and return types on a per function basis:

Example:

input.ts

/**  * @plv8ify_param {varchar(255)} first_name  * @plv8ify_param {text} last_name  * @plv8ify_return {char(255)}  */ export function howdy(first_name: string, last_name: string): string { return `Howdy ${first_name} ${last_name}` }

cli command line:

plv8ify generate input.ts 

will generate this function:

DROP FUNCTION IF EXISTS howdy(first_name varchar(255),last_name text); CREATE OR REPLACE FUNCTION howdy(first_name varchar(255),last_name text) RETURNS char(255) AS $plv8ify$ // examples/hello-custom-type/input.ts function hello(test) { return { name: `Hello ${test[0].name}`, age: test[0].age }; } function howdy(first_name, last_name) { return `Howdy ${first_name} ${last_name}`; } return howdy(first_name,last_name) $plv8ify$ LANGUAGE plv8 IMMUTABLE STRICT;

Print the version

Generate PLV8 functions for an input typescript file

Deploy an output folder to a Postgres database (defined by env var DATABASE_URL)

• Very early, only a small number of types supported
• Only supports typescript as input at the moment
• Scaling an application server is easier than scaling a database server, moving the logic in database as postgres functions makes it easier (IMO SQL is declarative, like React for data) to call the code/do some things but you are effectively shifting compute from application server to database server which might be a bad idea in most cases.

• https://github.com/CSTARS/es6-to-plv8
• https://github.com/clkao/plv8x

If you want to reach out to me, please DM me on https://twitter.com/divyenduz or email me at mail at divyendusingh.com