能否在Prisma数据库迁移中手动添加PostgreSQL触发器？

问题背景 #

我正在尝试使用PostgreSQL结合Prisma ORM创建触发器，但Prisma目前似乎还不支持触发器功能。我找到了Stack Overflow上的一个解决方案，但不想在服务端通过中间件实现触发器逻辑。现咨询：

• 能否在数据库迁移层面手动添加触发器？
• 这么做是否会引发问题？

我的应用中有如下迁移文件结构：

├── prisma
│   ├── migrations
│   │   ├── 20230202011931_initial
│   │   │   └── migration.sql
│   │   ├── 20230202012555_default_to_created_at
│   │   │   └── migration.sql
│   │   ├── 20230202130457_mapping
│   │   │   └── migration.sql
│   │   └── migration_lock.toml
│   └── schema.prisma

另外想知道：我可以选择其中一个现有文件添加触发器，还是需要新建一个迁移文件来添加？

解答 #

1. 完全可以在迁移层面手动添加触发器 #

Prisma允许在迁移文件中写入原生SQL，所以你完全可以通过手动编写SQL来创建触发器、触发器函数，直接在数据库层面实现逻辑，不需要依赖服务端中间件。

2. 可能存在的问题 #

• Schema与数据库不一致：触发器属于数据库层面的对象，不会被Prisma的schema.prisma文件追踪。后续团队成员执行prisma db push或重新生成schema时，不会感知到这些触发器的存在，容易出现认知偏差。
• 维护成本：如果触发器逻辑和表结构强相关，后续修改表结构时需要手动同步触发器逻辑，否则可能出现触发器失效的情况。
• 备份遗漏风险：使用Prisma自带工具备份时，可能不会自动包含触发器相关定义，需要额外处理。

3. 迁移文件的选择建议 #

绝对不要修改已存在的迁移文件，原因如下：

• Prisma迁移系统依赖迁移文件的完整性和执行顺序，修改已运行过的迁移文件会导致migration_lock.toml中的哈希值不匹配，后续执行迁移会直接报错。
• 团队协作场景下，其他成员已经执行过旧迁移文件，修改后会导致数据库状态不一致。

正确做法是新建专门的迁移文件：

1. 执行命令：prisma migrate dev --name add_trigger，生成新的迁移目录和空的migration.sql文件。
2. 在新文件中写入创建触发器的原生SQL示例：

-- 先创建触发器函数
CREATE OR REPLACE FUNCTION update_modified_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.modified_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 再创建触发器
CREATE TRIGGER trigger_update_modified_at
BEFORE UPDATE ON your_table_name
FOR EACH ROW EXECUTE FUNCTION update_modified_at();

3. 执行prisma migrate dev应用迁移，触发器即可生效。

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