Knex.JS自动更新触发器 [英] Knex.JS Auto Update Trigger

问题描述

我正在使用 Knex.JS 迁移工具.但是，在创建表时，我希望有一个名为 updated_at 的列，该列在数据库中的记录更新时会自动更新.

I am using Knex.JS migration tools. However, when creating a table, I'd like to have a column named updated_at that is automatically updated when a record is updated in the database.

例如，这是一个表:

knex.schema.createTable('table_name', function(table) {
    table.increments();
    table.string('name');
    table.timestamp("created_at").defaultTo(knex.fn.now());
    table.timestamp("updated_at").defaultTo(knex.fn.now());
    table.timestamp("deleted_at");
})

created_at 和 updated_at 列默认为创建记录的时间，这很好.但是，当该记录更新时，我希望 updated_at 列显示该记录自动更新的新时间.

The created_at and updated_at column defaults to the time the record is created, which is fine. But, when that record is updated, I'd like the updated_at column to show the new time that it was updated at automatically.

我不想写原始的postgres.

I'd prefer not to write in raw postgres.

谢谢！

推荐答案

使用Postgres，您将需要触发.这是我成功使用的方法.

With Postgres, you'll need a trigger. Here's a method I've used successfully.

如果按设置顺序有多个迁移文件，则可能需要人为地更改文件名中的日期戳，以使其首先运行(或仅将其添加到第一个迁移文件中).如果无法回滚，则可能需要通过 psql 手动执行此步骤.但是，对于新项目:

If you have multiple migration files in a set order, you might need to artificially change the datestamp in the filename to get this to run first (or just add it to your first migration file). If you can't roll back, you might need to do this step manually via psql. However, for new projects:

const ON_UPDATE_TIMESTAMP_FUNCTION = `
  CREATE OR REPLACE FUNCTION on_update_timestamp()
  RETURNS trigger AS $$
  BEGIN
    NEW.updated_at = now();
    RETURN NEW;
  END;
$$ language 'plpgsql';
`

const DROP_ON_UPDATE_TIMESTAMP_FUNCTION = `DROP FUNCTION on_update_timestamp`

exports.up = knex => knex.raw(ON_UPDATE_TIMESTAMP_FUNCTION)
exports.down = knex => knex.raw(DROP_ON_UPDATE_TIMESTAMP_FUNCTION)

现在该功能应可用于所有后续迁移.

Now the function should be available to all subsequent migrations.

我发现，如果可以避免的话，不要在迁移文件中重复大块SQL更具表现力.我在这里使用了 knexfile.js ，但是如果您不想使其复杂化，则可以在任何位置进行定义.

I find it more expressive not to repeat large chunks of SQL in migration files if I can avoid it. I've used knexfile.js here but if you don't like to complicate that, you could define it wherever.

module.exports = {
  development: {
    // ...
  },

  production: {
    // ...
  },

  onUpdateTrigger: table => `
    CREATE TRIGGER ${table}_updated_at
    BEFORE UPDATE ON ${table}
    FOR EACH ROW
    EXECUTE PROCEDURE on_update_timestamp();
  `
}

使用助手

最后，我们可以相当方便地定义自动更新触发器:

Use the helper

Finally, we can fairly conveniently define auto-updating triggers:

const { onUpdateTrigger } = require('../knexfile')

exports.up = knex =>
  knex.schema.createTable('posts', t => {
    t.increments()
    t.string('title')
    t.string('body')
    t.timestamps(true, true)
  })
    .then(() => knex.raw(onUpdateTrigger('posts')))

exports.down = knex => knex.schema.dropTable('posts')

请注意，删除表足以摆脱触发器:我们不需要显式的 DROP TRIGGER .

Note that dropping the table is enough to get rid of the trigger: we don't need an explicit DROP TRIGGER.

这似乎是一件繁重的工作，但是一旦完成，它就是一劳永逸"的操作，如果您不想使用ORM，则非常方便.

This all might seem like a lot of work, but it's pretty "set-and-forget" once you've done it and handy if you want to avoid using an ORM.

这篇关于Knex.JS自动更新触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！