postgresql Knex.JS 自动更新触发器

Knex.JS Auto Update Trigger

提问by aashah7

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

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

For example, here is a table:

例如，这里有一个表：

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");
})

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

在created_at与updated_at列默认为创建记录的时间，这是罚款。但是，当该记录更新时，我希望该updated_at列显示它自动更新的新时间。

I'd prefer not to write in raw postgres.

我不想用原始的 postgres 编写。

Thanks!

谢谢！

回答by Rich Churcher

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

使用 Postgres，你需要一个trigger。这是我成功使用的方法。

Add a function

添加功能

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:

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

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.

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

Define a knex.rawtrigger helper

定义knex.raw触发器助手

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

我发现如果可以避免的话，不在迁移文件中重复大量 SQL 会更有表现力。我在knexfile.js这里使用过，但如果你不想让它复杂化，你可以在任何地方定义它。

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')

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

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

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.

这一切看起来似乎需要做很多工作，但是一旦你完成了它，它就非常“一劳永逸”，如果你想避免使用 ORM，它会很方便。

回答by Benny Neugebauer

You can create a knex migration using timestamps:

您可以使用时间戳创建 knex 迁移：

exports.up = (knex, Promise) => {
  return Promise.all([
    knex.schema.createTable('table_name', (table) => {
      table.increments();
      table.string('name');
      table.timestamps(false, true);
      table.timestamp('deleted_at').defaultTo(knex.fn.now());
    })
  ]);
};

exports.down = (knex, Promise) => {
  return Promise.all([
    knex.schema.dropTableIfExists('table_name')
  ]);
};

With timestampsa database schema will be created which adds a created_atand updated_atcolumn, each containing an initial timestamp.

使用时间戳将创建一个数据库模式，该模式添加一个created_at和updated_at列，每个都包含一个初始时间戳。

To keep the updated_atcolumn current, you'll need knex.raw:

要使updated_at列保持最新，您需要knex.raw：

table.timestamp('updated_at').defaultTo(knex.raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

To skip the knex.rawsolution, I suggest using a high level ORM like Objection.js. With Objection.jsyou could implement your own BaseModelwhich then updates the updated_atcolumn:

要跳过knex.raw解决方案，我建议使用像Objection.js这样的高级 ORM 。使用Objection.js，您可以实现自己的BaseModel，然后更新updated_at列：

Something.js

东西.js

const BaseModel = require('./BaseModel');

class Something extends BaseModel {
  constructor() {
    super();
  }

  static get tableName() {
    return 'table_name';
  }
}

module.exports = Something;

BaseModel

基础模型

const knexfile = require('../../knexfile');
const knex = require('knex')(knexfile.development);
const Model = require('objection').Model;

class BaseModel extends Model {
  $beforeUpdate() {
    this.updated_at = knex.fn.now();
  }
}

module.exports = BaseModel;

Source: http://vincit.github.io/objection.js/#timestamps

来源：http: //vincit.github.io/objection.js/#timestamps

回答by Swaraj

This is not a feature of Knex. Knex only creates the columns, but does not keep them up to date for you.

这不是 Knex 的功能。Knex 仅创建列，但不会为您保持最新状态。

If you use, the Bookshelf ORM, however, you can specify that a table has timestamps, and it will set & update the columns as expected:

但是，如果使用 Bookshelf ORM，则可以指定表具有时间戳，并且它将按预期设置和更新列：

• Bookshelf docs
• Github issue

• 书架文档
• Github 问题

回答by Alex Rizvi

This is my way of doing that in Mysql 5.6+

这是我在 Mysql 5.6+ 中的做法

The reason I didn't use table.timestamps is because I use DATETIME instead of timestamp.

我没有使用 table.timestamps 的原因是因为我使用 DATETIME 而不是时间戳。

table.dateTime('created_on')
        .notNullable()
        .defaultTo(knex.raw('CURRENT_TIMESTAMP'))

table.dateTime('updated_on')
        .notNullable()
        .defaultTo(knex.raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

回答by Sushant Chavan

You can directly use this function

可以直接使用这个功能

table.timestamps()

This will create the 'created_at' and 'updated_at' columns by default and update them accordingly

这将默认创建“created_at”和“updated_at”列并相应地更新它们

https://knexjs.org/#Schema-timestamps

https://knexjs.org/#Schema-timestamps