auto_timemarks.ts

import * as knex from 'knex';

const connection = knex({client: 'mysql', /* ... */});

const createTableWithManualTimeMarksQuery = connection.schema.createTableIfNotExists('users', table => {
    table.increments();

    table.timestamp('created_at')
        .defaultTo(connection.fn.now())
        .notNullable();

    table.timestamp('updated_at')
        .defaultTo(connection.raw("NOW() ON UPDATE NOW()"))
        .notNullable();

}).toString();

console.log(createTableWithManualTimeMarksQuery);

/*
OUTPUT:

create table if not exists `users` (
    `id` int unsigned not null auto_increment primary key,
    `created_at` timestamp not null default CURRENT_TIMESTAMP,
    `updated_at` timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 */


// ==== INCORRECT SOLUTIONS ====

const createTableWithAutoTimemarksQuery = connection.schema.createTable("users", table => {
        table.increments();
        table.timestamps(true, true);
}).toString();

console.log(createTableWithAutoTimemarksQuery);

/* OUTPUT:

create table `users` (
  `id` int unsigned not null auto_increment primary key,
  `created_at` timestamp not null default CURRENT_TIMESTAMP,
  `updated_at` timestamp not null default CURRENT_TIMESTAMP
);
 */

const createTableWithAutoUpdateQuery = connection.schema.createTable("users", table => {
    table.increments();

    table.timestamp('created_at')
        .defaultTo('CURRENT_TIMESTAMP')
        .notNullable();

    table.timestamp('updated_at')
        .defaultTo("CURRENT_TIMESTAMP")
        .onUpdate('CURRENT_TIMESTAMP')
        .notNullable();

}).toString();

/* Throws:

   TypeError: table.timestamp(...).defaultTo(...).onUpdate is not a function
   
   https://github.com/tgriesser/knex/issues/1151
   `Realized on update is only available on reference columns. so it was user error, but also uncaught :)`

   https://github.com/tgriesser/knex/issues/306
   `Yeah, I mean on update is normal but only as it pertains to a foreign key.
   There's also now a schema.raw method you can use in your schema building chain to try and take care of edge cases like this.`
*/