How to do knex.js migrations?

Question

I'm still not sure how to do my migrations with knex. Here is what I have so far. It works on up, but down gives me FK constraint error even though foreign_key_checks = 0.

exports.up = function(knex, Promise) {   return Promise.all([     knex.raw('SET foreign_key_checks = 0;'),      /* CREATE Member table */     knex.schema.createTable('Member', function (table) {       table.bigIncrements('id').primary().unsigned();       table.string('email',50);       table.string('password');        /* CREATE FKS */       table.bigInteger('ReferralId').unsigned().index();       table.bigInteger('AddressId').unsigned().index().inTable('Address').references('id');     }),      /* CREATE Address table */     knex.schema.createTable('Address', function (table) {       table.bigIncrements('id').primary().unsigned();       table.index(['city','state','zip']);        table.string('city',50).notNullable();       table.string('state',2).notNullable();       table.integer('zip',5).unsigned().notNullable();     }),      knex.raw('SET foreign_key_checks = 1;')   ]); };  exports.down = function(knex, Promise) {   return Promise.all([     knex.raw('SET foreign_key_checks = 0;'),      knex.schema.dropTable('Address'),      knex.schema.dropTable('Member'),      knex.raw('SET foreign_key_checks = 1;')    ]); }; 

Answer 1

jedd.ahyoung is correct. You don't need to limit your connection pool to 1. You just need to chain your promises so they are not run in parallel.

For example:

exports.up = function(knex, Promise) {   return removeForeignKeyChecks()     .then(createMemberTable)     .then(createAddressTable)     .then(addForeignKeyChecks);    function removeForeignKeyChecks() {     return knex.raw('SET foreign_key_checks = 0;');   }    function addForeignKeyChecks() {     return knex.raw('SET foreign_key_checks = 1;');   }    function createMemberTable() {     return knex.schema.createTable('Member', function (table) {       table.bigIncrements('id').primary().unsigned();       table.string('email',50);       table.string('password');        /* CREATE FKS */       table.bigInteger('ReferralId').unsigned().index();       table.bigInteger('AddressId').unsigned().index().inTable('Address').references('id');     });   }    function createAddressTable() {     return knex.schema.createTable('Address', function (table) {       table.bigIncrements('id').primary().unsigned();       table.index(['city','state','zip']);        table.string('city',50).notNullable();       table.string('state',2).notNullable();       table.integer('zip',5).unsigned().notNullable();     });   } }; 

Also I may be missing something but it looks like you won't need to remove and then reinstate the foreign key checks if you create the address table before the member table.

Here's how the final code would look:

exports.up = function(knex, Promise) {   return createAddressTable()     .then(createMemberTable);    function createMemberTable() {     return knex.schema.createTable('Member', function (table) {       table.bigIncrements('id').primary().unsigned();       table.string('email',50);       table.string('password');        /* CREATE FKS */       table.bigInteger('ReferralId').unsigned().index();       table.bigInteger('AddressId').unsigned().index().inTable('Address').references('id');     });   }    function createAddressTable() {     return knex.schema.createTable('Address', function (table) {       table.bigIncrements('id').primary().unsigned();       table.index(['city','state','zip']);        table.string('city',50).notNullable();       table.string('state',2).notNullable();       table.integer('zip',5).unsigned().notNullable();     });   } }; 

Answer 2

Figured out that it wasn't working because of connection pooling. It would use a different connection to run each migration task which caused foreign key checks not to be set properly. setting

pool:{   max:1 } 

in the migration config file fixed this.