[Knex Postgres Timestamps] timestamps with precision for Knex + Postgres migrations #knex #pg #db

README.md

knex-pg-timestamps

timestamps with precision for Knex + Postgres migrations

Usage

const {
  addTimestamps, // (knex, table, precision = 3) : Promise
  addFunctionOnUpdateTimestamp, // (knex) : Promise
  dropFunctionOnUpdateTimestamp // (knex) : Promise
} = require('./knex-pg-timestamps.js');

exports.up = async knex => {
  await addFunctionOnUpdateTimestamp(knex);

  await knex.schema.createTable('users', t => {
    addTimestamps(knex, t);
    // ...
  })
};

exports.down = async knex => {
  await dropFunctionOnUpdateTimestamp(knex);
  // ...
};

knex-pg-timestamps.js

async function addTimestamps(knex, table, precision = 3) {
  const now = knex.raw(`CURRENT_TIMESTAMP(${precision})`);
  const columns = ['created_at', 'updated_at'];

  columns.forEach(column => {
    table
      .timestamp(column, false, precision)
      .notNullable()
      .defaultTo(now);
  });

  await _addTriggerOnUpdateTimestamp(knex, table._tableName);
}

function _addTriggerOnUpdateTimestamp(knex, tableName) {
  return knex.raw(`
    CREATE TRIGGER ${tableName}_updated_at
    BEFORE UPDATE ON ${tableName}
    FOR EACH ROW
    EXECUTE PROCEDURE on_update_timestamp();
  `);
}

function addFunctionOnUpdateTimestamp(knex, precision = 3) {
  return knex.raw(`
    CREATE OR REPLACE FUNCTION on_update_timestamp()
    RETURNS trigger AS $$
    BEGIN
      NEW.updated_at = CURRENT_TIMESTAMP(${precision});
      RETURN NEW;
    END;
  $$ language 'plpgsql'
  `);
}

function dropFunctionOnUpdateTimestamp(knex) {
  return knex.raw(`DROP FUNCTION on_update_timestamp`);
}

module.exports = {
  addTimestamps,
  addFunctionOnUpdateTimestamp,
  dropFunctionOnUpdateTimestamp
};