Migration: add a column after a specific column

Question

This is easy to do with SQL but I need to write a Knex migration script which I am not familiar with. The following adds the order_id column at the end of the row in the order table. I want order_id to be added after id. How do I do that?

const TABLE_NAME = 'order';
exports.up = function (knex) {
    return knex.schema.alterTable(TABLE_NAME, table => {
        table
            .specificType('order_id', 'char(10)')
            .unique()
            .notNullable();

    });
};

exports.down = function (knex) {
    return knex.schema.table(TABLE_NAME, function (t) {
        t.dropColumn('order_id');
    });
};

Answer 1

Old topic, but I found a answer:

return knex.schema.table('the_table', table => {    
    table.tinyint('new_column').defaultTo('0').after('other_column')
})

This work on Mysql databases.

Answer 2

There is no SQL that determines column order on altering a table. The order is determined on query so that, for example:

knex
  .select('id', 'order_id')
  .from('order')

yields

id | order_id
===+=========
1  | 2

whereas

knex
  .select('order_id', 'id')
  .from('order')

yields

order_id | id
=========+===
2        | 1

If an individual database engine did support changing the order of columns after table creation, that would be specific to that engine and not easy for a SQL generator like Knex to manipulate. See How do I alter the position of a column in a PostgreSQL database table? for more.