My Sql Alter Table using knex.js

Question

Need to change a Data type and Default value of a column in MySql Db using knex; Later the data type was date and needed to change that in to dateTime and also the default value needed to change from NULL to CURRENT_TIMESTAMP

MySql Query to achieve the corresponding is given below

ALTER TABLE `Employees` 
CHANGE COLUMN `added_date` `added_date` DATETIME DEFAULT CURRENT_TIMESTAMP ;

I have created the knex migration file to run the above changes:

Content of Migration file is given below:

exports.up = function(knex, Promise) {
return knex.schema.alterTable('Employee', function(t) {
       t.dateTime('added_date').defaultTo(knex.fn.now());       
});
} ;

exports.down = function(knex, Promise) {
  return knex.schema.alterTable('Employee', function(t) {
 t.date('added_date').nullable();       
});
 };

But this always throws error while building. Which is like

Knex:warning - migrations failed with error: alter table Employee add added_date datetime default CURRENT_TIMESTAMP - ER_DUP_FIELDNAME: Duplicate column name 'added_date' error in migrating latest version Error: ER_DUP_FIELDNAME: Duplicate column name 'added_date'

Can anyone share the exact method/syntax to ALTER a table with the above given changes?

Answer 1

There is currently (knex 0.12.6) no way to do this with out raw() call. In future if https://github.com/tgriesser/knex/pull/1759 is finished more complex column altering functions will be available.

exports.up = function(knex, Promise) {
  return knex.schema.raw('ALTER TABLE `Employees` CHANGE COLUMN `added_date` `added_date` DATETIME DEFAULT CURRENT_TIMESTAMP');
};

exports.down = function(knex, Promise) {
  return knex.schema.raw('ALTER TABLE `Employees` CHANGE COLUMN `added_date` `added_date` DATE DEFAULT NULL');
};

EDIT: I started finishing that pull request and in next knex (0.12.7 or 0.13.0) release one will be able to do:

exports.up = function(knex, Promise) {
  return knex.schema.alterTable('Employee', function(t) {
    t.dateTime('added_date').defaultTo(knex.fn.now()).alter();       
  });
};

exports.down = function(knex, Promise) {
  return knex.schema.alterTable('Employee', function(t) {
    t.date('added_date').nullable().alter();       
  });
};

I'll add these also to integration test to make sure they work.