Knex.js迁移能否在raw查询中用自定义函数？Postgres密码加密问题

为啥你原来的代码不行？ #

你写的knex.raw(UPDATE users SET passhash = encrypt(passhash))里的encrypt是Postgres数据库层面的函数，但你导入的是Node.js项目里的本地加密函数——数据库根本识别不了这个本地函数，自然执行失败。

两种可行实现思路 #

思路1：Node.js端查询后批量更新 #

直接把所有用户数据拉到本地，用你现有的encrypt函数处理，再分批更新回数据库，不用改数据库逻辑：

const { encrypt } = require("/path/to/function");

exports.up = async function(knex) {
  // 第一步：修改字段长度（已成功，保留）
  await knex.schema.table("users", table => {
    table.string("passhash", 500).alter();
  });

  // 第二步：拉取所有用户的id和原密码哈希
  const users = await knex("users").select("id", "passhash");

  // 第三步：分批更新，避免一次性处理大量数据导致内存/性能问题
  const batchSize = 100; // 可根据数据库性能调整
  for (let i = 0; i < users.length; i += batchSize) {
    const batch = users.slice(i, i + batchSize);
    // 对批次内的用户密码加密
    const updatePromises = batch.map(user => 
      knex("users")
        .where("id", user.id)
        .update("passhash", encrypt(user.passhash))
    );
    // 等待批次更新完成
    await Promise.all(updatePromises);
  }
};

exports.down = function(knex) {
  // 注意：如果你的encrypt是不可逆哈希，这里没法恢复原密码，只能改回字段长度
  return knex.schema.table("users", table => {
    table.string("passhash", 64).alter();
  });
};

优缺点：不用改数据库逻辑，直接复用现有加密代码；但用户量极大时，拉取+更新的耗时会比较长，必须分批处理。

思路2：数据库端创建加密函数，用knex.raw调用 #

把加密逻辑搬到Postgres里，创建数据库函数后直接执行更新，适合用户量大的场景：

exports.up = async function(knex) {
  // 1. 修改字段长度
  await knex.schema.table("users", table => {
    table.string("passhash", 500).alter();
  });

  // 2. 启用pgcrypto扩展（如果用Postgres自带加密工具），并创建自定义加密函数
  await knex.raw(`
    CREATE EXTENSION IF NOT EXISTS pgcrypto;
    CREATE OR REPLACE FUNCTION encrypt_passhash(p_text text) RETURNS text AS $$
    BEGIN
      -- 替换成和你Node.js端一致的加密逻辑，比如用AES加密
      -- 注意：密钥不要硬编码，可通过环境变量传入
      RETURN pgp_sym_encrypt(p_text, :'ENCRYPT_KEY', 'cipher-algo=aes256');
    END;
    $$ LANGUAGE plpgsql;
  `, { ENCRYPT_KEY: process.env.ENCRYPT_KEY }); // 从环境变量取密钥

  // 3. 执行全量更新
  await knex.raw(`UPDATE users SET passhash = encrypt_passhash(passhash)`);
};

exports.down = async function(knex) {
  // 如果加密可逆，先解密再回滚字段长度；不可逆则跳过解密步骤
  await knex.raw(`
    UPDATE users SET passhash = pgp_sym_decrypt(passhash::bytea, :'ENCRYPT_KEY')
  `, { ENCRYPT_KEY: process.env.ENCRYPT_KEY });
  
  await knex.raw(`DROP FUNCTION IF EXISTS encrypt_passhash(text)`);
  await knex.schema.table("users", table => {
    table.string("passhash", 64).alter();
  });
};

优缺点：数据库端执行速度快，适合大数据量；但需要把Node.js的加密逻辑转成Postgres支持的PL/pgSQL代码，还要注意密钥的安全管理。

必看注意事项 #

• 先备份数据：操作密码字段前一定要全量备份数据库，避免不可逆操作导致数据丢失。
• 加密可逆性：如果你的encrypt是不可逆的哈希算法（比如bcrypt），回滚操作无法恢复原密码，down函数只能改回字段长度。
• 性能优化：不管用哪种思路，批量操作时都要控制批次大小，避免给数据库造成过大压力。

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