npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

plv8ify

v0.0.60

Published

## Introduction

Downloads

598

Readme

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

| Behavior | JSDoc Tag | Example | | -------- | --------- | ------------- | | 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 Flags | Type | Description | Default | | ---------------------- | ---- | ----------- | ------- |

Generate

Generate PLV8 functions for an input typescript file

| Generate Command Flags | Type | Description | Default | | ----------------------- | ------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------- | | --debug | Boolean | Print additional debug information | false | | --bundler | 'esbuild' or 'bun' | Pick bundler. Bun runtime is needed for 'bun' | esbuild | | --write-bundler-output | Boolean | Write the intermediate bundled Javascript output from bundler (currently, only ESBuild interface exists) | false | | --input-file | String | Specify an input file path (only Typescript supported at the moment) | input.ts | | --output-folder | String | Specify an output folder | plv8ify-dist | | --scope-prefix | String | Specify a scope prefix, by default (empty string), adds provided string as prefix for exported typescript functions | plv8ify | | --pg-function-delimiter | String | Specify a delimiter for the generated Postgres function | $plv8ify$ | | --fallback-type | String | Specify a fallback type when plv8ify fails to map a detected Typescript type to a Postges type | JSONB | | --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_proc | inline | | --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-file | String | Specify a custom types config file | types.ts |

Deploy

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

| Generate Command Flags | Type | Description | Default | | ---------------------- | ------ | ----------------------------------------------------------------------- | -------------- | | --output-folder | String | Specify an output folder | plv8ify-dist | | --deploy-concurrency | Number | Specify the maximum allowed deployment tasks to run in parallel | 10 |

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

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

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