此文章是转载文章的一个学习，稍有改动
环境准备
- 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