如何在pg-promise的ColumnSet中调用PostgreSQL原生函数

核心实现思路 #

在定义ColumnSet时，为需要加密的字段添加列修饰器（mod属性），通过该修饰器生成调用pgp_sym_encrypt的SQL表达式，实现插入时自动加密指定字段。

具体代码示例 #

1. 初始化pg-promise连接

const pgp = require('pg-promise')();
// 替换为你的数据库连接信息
const db = pgp('postgres://username:password@localhost:5432/your_db');

2. 定义带加密逻辑的ColumnSet

针对需要加密的字段，使用mod属性生成加密SQL：

// 封装加密逻辑的辅助函数（避免重复代码）
const encryptField = (column) => {
    // $1 对应后续传入的加密密钥参数
    return pgp.as.format('pgp_sym_encrypt(${value}, $1)', { value: column.value }, 1);
};

// 定义ColumnSet
const userColumns = new pgp.helpers.ColumnSet([
    'username', // 不需要加密的字段直接指定列名
    { name: 'email', mod: encryptField }, // 对email字段加密
    { name: 'phone', mod: encryptField }  // 对phone字段加密
], { table: 'users' }); // 指定目标表名

3. 执行加密插入操作

// 待插入的数据
const userData = [
    { username: 'john_doe', email: 'john@example.com', phone: '13800138000' },
    { username: 'jane_smith', email: 'jane@example.com', phone: '13900139000' }
];

// 加密密钥（实际项目中建议从环境变量/密钥管理服务读取，不要硬编码）
const encryptionKey = 'your_strong_encryption_key_here';

// 生成插入SQL并执行
const insertQuery = pgp.helpers.insert(userData, userColumns);
db.none(insertQuery, encryptionKey)
    .then(() => console.log('数据已加密并成功插入'))
    .catch(err => console.error('插入失败:', err));

关键注意事项 #

• 密钥安全：绝对不要将加密密钥硬编码到代码中，建议通过环境变量（如process.env.ENCRYPTION_KEY）或专业密钥管理服务获取。
• 数据库字段类型：被加密的字段在数据库中应定义为text类型，因为pgp_sym_encrypt返回的是文本格式的加密结果。
• 解密查询：如果需要查询解密后的数据，可使用PostgreSQL的pgp_sym_decrypt函数，例如：SELECT username, pgp_sym_decrypt(email, $1) AS email FROM users;

内容的提问来源于stack exchange，提问作者Shashank Anand