Knex migration adds "on update CURRENT_TIMESTAMP" to created_at column

Wappler General How To
#1

Edit: Jump to Knex migration adds "on update CURRENT_TIMESTAMP" to created_at column

Wappler Version : 4.4.5
Operating System : MacOS Monterey
Server Model: NodeJS
Database Type: MariaDB 10.5
Hosting Type: Docker

How to reproduce

Create a table people:

  • name, primary key
  • created_at, timestamp
  • present_at, timestamp

Migration file for your convenience:

exports.up = function(knex) {
  return knex.schema
    .createTable('people', function (table) {
      table.string('name').primary();
      table.timestamp('created_at');
      table.timestamp('present_at');
    })
};

exports.down = function(knex) {
  return knex.schema
    .dropTable('people')
};

Custom Query in Server Action:

INSERT INTO people (name, present_at) VALUES (:P1, :P2) ON DUPLICATE KEY UPDATE present_at=:P2;

:P1 is a random name, :P2 is {{NOW}}

Insert, and then insert again with the same data, it should update due to “ON DUPLICATE KEY”. The problem is, the created_at column is updated as well. I’ve tested this with MariaDB alone (made outside Wappler) and it doesn’t happen, it only happens within Wappler, so either Wappler or Knex is doing some trickery…

#2

I found why the column created_at mysteriously updates, this is in the database schema:

Screenshot

I want current_timestamp() by default, but not “on update”. Who’s the responsible for setting this setting on the DB schema during migrations? Is it Wappler, or Knex?

exports.up = function(knex) {
  return knex.schema
    .createTable('people', function (table) {
      table.string('name').primary();
      table.timestamp('created_at'); // Here, it adds on update CURRENT_TIMESTAMP
      table.timestamp('present_at'); // And not here!
    })
};
#3

Default values in all databases are used when you insert records and the column having a default value is not present with value.

In all other cases like update you have to supply the values you want - there is no default behavior in there. Not from Wappler and not from the database itself.

#4 
table.timestamp('created_at'); // Here, it adds on update CURRENT_TIMESTAMP
table.timestamp('present_at'); // And not here!

If you inspect the database schema using a third-party SQL viewer, you will notice the column “created_at” has a “on update CURRENT_TIMESTAMP” instruction. The same didn’t happen for “present_at”, despite having exactly the same data type.

As you can see, this is irregular behaviour. Special treatment was applied for column with name “created_at”

#5

Automatic Values

MariaDB has special behavior for the first column that uses the TIMESTAMP data type in a specific table. For the first column that uses the TIMESTAMP data type in a specific table, MariaDB automatically assigns the following properties to the column:

  • DEFAULT CURRENT_TIMESTAMP
  • ON UPDATE CURRENT_TIMESTAMP

This means that if the column is not explicitly assigned a value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column’s value with the current date and time.

2 Likes
#6

Thanks! Here’s I managed to workaround:

table.timestamp('created_at').defaultTo(knex.fn.now());

This will create the column with the default value of CURRENT_TIMESTAMP, but without the “on update” statement