plv8 + hashids 生成短连接id

此文章是转载文章的一个学习，稍有改动

环境准备

• plv8 环境

version: '3.6'

services:

  postgres:

    image: dalongrong/plv8:2.3.12

    ports:

    - "5432:5432"

    environment: 

    - "POSTGRES_PASSWORD=dalong"

  graphql-engine:

    image: hasura/graphql-engine:v1.0.0-beta.9

    ports:

    - "8080:8080"

    depends_on:

    - "postgres"

    environment:

      HASURA_GRAPHQL_DATABASE_URL: postgres://postgres:dalong@postgres:5432/postgres

      HASURA_GRAPHQL_ENABLE_CONSOLE: "true" # set to "false" to disable console

      HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log

注册hashids

通过plv8 npm 包

• package.json

{

  "name": "node-plv8",

  "version": "1.0.0",

  "main": "app.js",

  "bin": "app.js",

  "license": "MIT",

  "dependencies": {

    "cuid": "^2.1.6",

    "hashids": "^2.1.0",

    "knex": "^0.20.1",

    "lodash": "^4.17.15",

    "pg": "^7.12.1",

    "plv8": "^2.1.4",

    "shortid": "^2.2.15",

    "uuid": "^3.3.3"

  },

  "scripts": {

    "init:app": "node app"

  }

}

• 注册代码

// setup plv8 connection

const PLV8 = require('plv8')

const knex = require('knex')

const knexHandle = knex({

    client: 'pg',

    connection: {

        host: "127.0.0.1",

        user: "postgres",

        password: "dalong",

        database: "postgres"

    }

})

const plv8 = new PLV8(knexHandle)

​

// setup a log listener

plv8.on('log:error', msg => {

    console.error(msg)

})

plv8.install({ modulePath: require.resolve("hashids/cjs"), moduleName: "hashids" })

    .then(() => {

​

        // eval some code

        return plv8.eval(() => {

            const hashids = require("hashids")

            var h = new hashids("test", 10);

            var key = 333

            return h.encode(key);

        })

    })

    .then(result => {

        console.log(result)

    }).catch(err => {

        console.log(err)

    })

sql 集成

• sql 表结构以及函数定义
  主要使用了触发器的方式进行短连接id 的生成

 

CREATE TABLE products (

id BIGSERIAL,

title TEXT NOT NULL,

hashid TEXT NOT NULL

);

​

CREATE FUNCTION gen_hashid(salt TEXT, min_length BIGINT, key BIGINT) RETURNS TEXT AS $$

    const hashids = require("hashids")

    let h = new hashids(salt, min_length);

    return h.encode(key);

$$ LANGUAGE PLV8 IMMUTABLE STRICT;

​

CREATE FUNCTION products_pre_insert() RETURNS trigger AS $$

BEGIN

NEW.hashid := gen_hashid('dalong', 8, NEW.id);

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER products_pre_insert BEFORE INSERT ON products FOR EACH ROW EXECUTE PROCEDURE products_pre_insert();

• 插入数据

INSERT INTO products (title) VALUES ('dalong1');

INSERT INTO products (title) VALUES ('dalong2');

• 效果

 

 

参考资料

https://blog.abevoelker.com/2017-01-03/generating-youtube-like-ids-in-postgres-using-plv8-and-hashids/ 
https://github.com/iCyberon/pg_hashids 
https://www.npmjs.com/package/hashids 
https://hashids.org/ 
https://github.com/langateam/node-plv8 
https://github.com/plv8/plv8