How to add created_at and updated_at columns to your table using knex

  1. Make a database migration (knex .js file) in here

  2. Use this code to create ‘created_at’ and ‘updated_at’ columns

.table('TABLENAME_HERE', function (table) {

table.timestamp('created_at').defaultTo(knex.raw('CURRENT_TIMESTAMP'));

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

})
  1. Add the ‘exports.down’ part, see full example code:
exports.up = function (knex) {

return knex.schema

.table('courses_lectures_ratings_users', function (table) {

table.timestamp('created_at').defaultTo(knex.raw('CURRENT_TIMESTAMP'));

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

})

.table('courses_lectures_bookmarks_users', function (table) {

table.timestamp('created_at').defaultTo(knex.raw('CURRENT_TIMESTAMP'));

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

})

.table('courses_lectures_users_progress', function (table) {

table.timestamp('created_at').defaultTo(knex.raw('CURRENT_TIMESTAMP'));

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

})

.table('schools_notifications', function (table) {

table.timestamp('created_at').defaultTo(knex.raw('CURRENT_TIMESTAMP'));

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

})

.table('release_notes', function (table) {

table.timestamp('created_at').defaultTo(knex.raw('CURRENT_TIMESTAMP'));

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

})

};

exports.down = function (knex) {

return knex.schema

.table('courses_lectures_ratings_users', function (table) {

table.dropColumn('created_at');

table.dropColumn('updated_at');

})

.table('courses_lectures_bookmarks_users', function (table) {

table.dropColumn('created_at');

table.dropColumn('updated_at');

})

.table('courses_lectures_users_progress', function (table) {

table.dropColumn('created_at');

table.dropColumn('updated_at');

})

.table('schools_notifications', function (table) {

table.dropColumn('created_at');

table.dropColumn('updated_at');

})

.table('release_notes', function (table) {

table.dropColumn('created_at');

table.dropColumn('updated_at');

})

};
  1. Save your file, do a full refresh of wappler (control + R) and it should show in the changes

  2. Right click, apply this change

  3. Voila! You have 2 new columns

Note: Wappler db manager doesn’t show the actual time stamp, you can use a different db manager to confirm

2 Likes

Update

Easier way now:

created_at:

  1. Create a timestamp column,
  2. Then use the little clock button:
    image

updated_at:

  1. Create a timestamp column
  2. Paste this in the ‘default’: knex.raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')

**Note: after applying it will say knex.fn.now() for BOTH columns, also the ‘updated_at’ column. However you can ignore that, as it should work as intended: only update the ‘updated_at’ with a ‘now’ timestamp after that row as been edited **

2 Likes