knex.raw(...) in migrations seems not to work

Question

I use knex with MySql. This is my migrations file:

const { onUpdateTrigger } = require('../../../../knexfile')
const { onInsertTrigger } = require('../../../../knexfile')

exports.up = function (knex, Promise) {

    return knex.schema.createTable('users', (table) => {

        console.info("------> Creating table");
        table.increments('id').unsigned().primary();
        table.string('username').unique().notNullable();
        table.string('password').notNullable();
        table.timestamp('modified').notNullable();
        table.timestamp('created').notNullable().defaultTo(knex.raw('NOW()'));
    }).then(function () {

        console.info("------> Creating trigger");
        knex.raw(onUpdateTrigger('users'));
        knex.raw(onInsertTrigger('users'));
    });
};

exports.down = function (knex, Promise) {
    return knex.schema.dropTable('users');
};

The knexfile.js is as follows:

...

development: {
    client: 'mysql',
    connection: {
        host: 'localhost',
        user: 'pbrause',
        password: '********',
        database: 'mydb',
        charset: 'utf8',
        multipleStatements : true
    },
    debug: true,
    migrations: {
        directory: __dirname + '/src/server/db/migrations'
    },
    seeds: {
        directory: __dirname + '/src/server/db/seeds'
    }
},

...

onInsertTrigger: function(table) {
    `DELIMITER $$
     CREATE TRIGGER \`mydatabase\`.\`${table}_BEFORE_INSERT\`
            BEFORE INSERT ON \`${table}\`
            FOR EACH ROW
     BEGIN
         SET new.modified = NOW();
     END$$
     DELIMITER ;`
},

onUpdateTrigger: function(table) {
    `DELIMITER $$
     CREATE TRIGGER \`mydatabase\`.\`${table}_BEFORE_UPDATE\`
            BEFORE UPDATE ON \`${table}\`
            FOR EACH ROW
     BEGIN
         SET new.modified = NOW();
     END$$
     DELIMITER ;`
}
...

I have tried two variations - one where the SQL code is inside the knex.raw(...) statement and the way you see above. In both cases the trigger are not created and the knex debug output tells me that these 'raw' statements are not executed. The table is created correctly in both cases.

Any idea why this does not work?

Answer 1

First of all you are calling knex.raw() to times parallel, without waiting that execution of them is finished:

}).then(function () {

    console.info("------> Creating trigger");
    knex.raw(onUpdateTrigger('users'));
    knex.raw(onInsertTrigger('users'));
});

One should use knex.schema.raw or return promise from then:

}).then(function () {
    console.info("------> Creating on update trigger");
    return knex.raw(onUpdateTrigger('users'));
}).then(function () {
    console.info("------> Creating on insert trigger");
    return knex.raw(onInsertTrigger('users'));
});

or

return knex.schema.createTable('users', (table) => {
    console.info("------> Creating table");
    table.increments('id').unsigned().primary();
    table.string('username').unique().notNullable();
    table.string('password').notNullable();
    table.timestamp('modified').notNullable();
    table.timestamp('created').notNullable().defaultTo(knex.raw('NOW()'));
})
.raw(onUpdateTrigger('users'))
.raw(onInsertTrigger('users'));

Another problem might be that knex.raw() (actually db drivers doesn't allow it normally) doesn't support sending multiple SQL statements as single query to DB by default.

If you are using mysql you can enable support for it by setting multipleStatements : true configuration option for driver (https://github.com/mysqljs/mysql#connection-options).

EDIT (the real answer why those knex raw queries were not ran):

All the above is true and if you would have changed your code in a way I suggested, it would have fixed your problem already.

The reason why those knex.raw() queries are not ran is beacause you are only building the queries, but never executing them (they are not returned from promise, which would automatically trigger the query, nor you are calling .then() for those queries which will explicitly send the query to the database.

// queries like this are only built, but never sent to server
knex.raw(onUpdateTrigger('users'));
knex.raw(onInsertTrigger('users'));

// one more the correct way to do it so that queries will be executed too
return knex.raw(onUpdateTrigger('users'))
  .then(() => knex.raw(onInsertTrigger('users')));