0.0.60 • Published 10 months ago

plv8ify v0.0.60

Weekly downloads
-
License
MIT
Repository
-
Last release
10 months ago

PLV8ify

Introduction

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

Why

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

Example

  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
}
  1. Run plv8ify generate
  2. Step 3 emits SQL file with names like ./plv8-dist/plv8ify_point.plv8.sql (one for each exported function)
  3. Execute the generated ./plv8-dist/plv8ify_point.plv8.sql using a Postgres client
  4. 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.

Deploy on custom schema

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;

Trigger functions

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
  }
}

Custom Postgres Types

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;

JSDoc Tag Reference

BehaviorJSDoc TagExample
set the volatility of the generated Postgres function/** @plv8ify_volatility <STABLE,IMMUTABLE,VOLATILE> *//** @plv8ify_volatility STABLE */
set the schema of the generated Postgres function/** @plv8ify_schema_name <schemaname> *//** @plv8ify_schema_name my_schema */
set the TS->SQL type mapping for a parameter/** @plv8ify_param {<my_sql_type>} <my_param> *//** @plv8ify_param {timestamptz} ts */
set the TS->SQL type mapping for the return type/** @plv8ify_returns {<SQL TYPE>} *//** @plv8ify_returns {setof my_table} */
designate the function is a TRIGGER/** @plv8ify_trigger *//** @plv8ify_trigger */

CLI Usage

Version

Print the version

Generate Command FlagsTypeDescriptionDefault

Generate

Generate PLV8 functions for an input typescript file

Generate Command FlagsTypeDescriptionDefault
--debugBooleanPrint additional debug informationfalse
--bundler'esbuild' or 'bun'Pick bundler. Bun runtime is needed for 'bun'esbuild
--write-bundler-outputBooleanWrite the intermediate bundled Javascript output from bundler (currently, only ESBuild interface exists)false
--input-fileStringSpecify an input file path (only Typescript supported at the moment)input.ts
--output-folderStringSpecify an output folderplv8ify-dist
--scope-prefixStringSpecify a scope prefix, by default (empty string), adds provided string as prefix for exported typescript functionsplv8ify
--pg-function-delimiterStringSpecify a delimiter for the generated Postgres function$plv8ify$
--fallback-typeStringSpecify a fallback type when plv8ify fails to map a detected Typescript type to a Postges typeJSONB
--mode'inline', 'bundle' or 'start_proc''inline' will bundle the library in each function, both 'bundle' and 'start_proc' creates a {prefix}_init function that loads the library. 'bundle' adds a check to each function to call 'init' if required, whereas 'start_proc' is designed to be used with plv8.start_procinline
--volatility'IMMUTABLE' or 'STABLE' or 'VOLATILE'Change the volatility of all the generated functions. To change volatility of a specific function use the jsdoc /** @plv8ify_volatility STABLE in the input typescript file (see examples/turf-js/input.ts).IMMUTABLE
--types-config-fileStringSpecify a custom types config filetypes.ts

Deploy

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

Generate Command FlagsTypeDescriptionDefault
--output-folderStringSpecify an output folderplv8ify-dist
--deploy-concurrencyNumberSpecify the maximum allowed deployment tasks to run in parallel10

Caveats

  • 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.

Prior Art

Contact

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

0.0.60

10 months ago

0.0.59

11 months ago

0.0.57

1 year ago

0.0.58

1 year ago

0.0.56

1 year ago

0.0.55

1 year ago

0.0.54

1 year ago

0.0.53

2 years ago

0.0.46

2 years ago

0.0.47

2 years ago

0.0.51

2 years ago

0.0.52

2 years ago

0.0.50

2 years ago

0.0.48

2 years ago

0.0.49

2 years ago

0.0.41

2 years ago

0.0.42

2 years ago

0.0.43

2 years ago

0.0.44

2 years ago

0.0.45

2 years ago

0.0.40

2 years ago

0.0.39

2 years ago

0.0.37

3 years ago

0.0.38

3 years ago

0.0.35

3 years ago

0.0.36

3 years ago

0.0.30

3 years ago

0.0.31

3 years ago

0.0.32

3 years ago

0.0.33

3 years ago

0.0.34

3 years ago

0.0.29

3 years ago

0.0.27

3 years ago

0.0.28

3 years ago

0.0.20

3 years ago

0.0.21

3 years ago

0.0.22

3 years ago

0.0.23

3 years ago

0.0.24

3 years ago

0.0.25

3 years ago

0.0.17

3 years ago

0.0.18

3 years ago

0.0.19

3 years ago

0.0.26

3 years ago

0.0.16

3 years ago

0.0.15

3 years ago

0.0.14

3 years ago

0.0.13

3 years ago

0.0.12

3 years ago

0.0.11

3 years ago

0.0.10

3 years ago

0.0.9

3 years ago

0.0.8

3 years ago

0.0.7

3 years ago

0.0.6

3 years ago

0.0.5

3 years ago

0.0.4

3 years ago

0.0.3

3 years ago

0.0.2

3 years ago

0.0.1

3 years ago