Question

For some reason, I can't use knex migrations in the production environment.

Can I generate raw SQL from knex migrations like knex select/insert/update operations?

For example:

I can get raw SQL from select sentences with:

Knex('t_example').select('id').toQuery()
// => select id from t_example

But I don't know how to generate raw SQL from the migration files:

import { Knex } from 'knex'

// how to get raw SQL like
//    "alter table `t_example` add `foo` int";
// from the "up" function
export async function up (knex: Knex): Promise<void> {
  return knex.schema.table('t_example', (table) => {
    table.integer('foo')
  })
}

export async function down (knex: Knex): Promise<void> {
  return knex.schema.table('t_example', (table) => {
    table.dropColumn('foo')
  })
}

My Attempts:

import the migration file and invoke the up function (from this github issue)

something like

const knex = require('knex')({...});
const {up, down} = require('./migrations/20200304140624_createUsersTable.js');

console.log(up(knex).toSQL().toNative());
console.log(down(knex).toSQL().toNative());

The above code snippet works fine for some migrations (createTable in my case), while not working for others (addColumn).

For example, with this migration file:

import { Knex } from 'knex'

export async function up (knex: Knex): Promise<void> {
  return knex.schema.table('t_example', (table) => {
    table.integer('foo')
  })
}

export async function down (knex: Knex): Promise<void> {
  return knex.schema.table('t_example', (table) => {
    table.dropColumn('foo')
  })
}

It throws error message: TypeError: up(...).toSql is not a function

and

UnhandledPromiseRejectionWarning: Error: ER_DUP_FIELDNAME: Duplicate column name 'foo'

run migration with DEBUG flag

Firstly, rollback the migrations with knex migrate:rollback

Then, run the migrations with DEBUG flag:

DEBUG=knex:query knex migrate:latest

It outputs SQL logs like

knex:query alter table `t_example` add `bar` int trx2 +8ms`

I can get SQL from these outputs now, but it requires some additional format tasks.

Is there a better solution?