How To Do Knex.js Migrations?

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.

Solution 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);

  functionremoveForeignKeyChecks() {
    return knex.raw('SET foreign_key_checks = 0;');
  }

  functionaddForeignKeyChecks() {
    return knex.raw('SET foreign_key_checks = 1;');
  }

  functioncreateMemberTable() {
    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');
    });
  }

  functioncreateAddressTable() {
    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);

  functioncreateMemberTable() {
    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');
    });
  }

  functioncreateAddressTable() {
    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();
    });
  }
};

Solution 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.

Solution 3:

I solved this problem by using a transaction

transation.js

module.exports = functiontransaction(fn) {
    returnfunction_transaction(knex, Promise) {
        return knex.transaction(function(trx) {
            return trx
                .raw('SET foreign_key_checks = 0;')
                .then(function() {
                    returnfn(trx, Promise);
                })
                .finally(function() {
                    return trx.raw('SET foreign_key_checks = 1;');
                });
        });
    };
}

Migration file

var transaction = require('../transaction');

functionup(trx, Promise) {
    return trx.schema
       .createTable('contract', function(table) {
           table.boolean('active').notNullable();                                              
           table.integer('defaultPriority').unsigned().references('priority.id');                             
           table.integer('defaultIssueStatus').unsigned().references('issueStatus.id');
           table.integer('owner').notNullable().unsigned().references('user.id'); 
       })

       .createTable('user', function(table) {
           table.increments('id').primary();
           table.datetime('createdAt');
           table.datetime('updatedAt');

           table.string('phoneNumber').notNullable().unique();
           table.string('password').notNullable();            
           table.string('name').notNullable().unique();       
           table.string('email');                             
           table.string('status');                            
           table.string('roles').defaultTo('user');           
           table.integer('contract').unsigned().references('contract.id');
       });
}

functiondown(trx, Promise) {
    return trx.schema
        .dropTable('contract')
        .dropTable('user');
}

exports.up = transaction(up);
exports.down = transaction(down);

Solution 4:

inTable() should be placed after references():

inTablecolumn.inTable(table)

Sets the table where the foreign key column is located after calling column.references.

Documentation.

Solution 5:

I thought I'd update this, since there have been some additions to Javascript that make this quite a bit easier. knex still requires that you return a Promise but inside that Promise you can do a lot of things to clean up the code related to the creation/modification of tables. My preference is to use a combination of async/await and Promise.all.

exports.up = function(knex, Promise) {
    returnnewPromise(async (resolve, reject) => {
        try {
            awaitPromise.all([
                knex.schema.createTable('videos', table => {
                    table.increments('id');
                    table.string('title');
                    table.string('director');
                    table.json('meta');
                }),
                knex.schema.createTable('books', table => {
                    table.increments('id');
                    table.string('title');
                    table.string('author');
                    table.json('meta');
                })
            ]);

            console.log('Tables created successfully');
            resolve();
        } catch(error) {
            reject(error);
        }
    })
}

If you prefer to create each table individually, then I'd just use async/await.

exports.up = function(knex, Promise) {
    returnnewPromise(async (resolve, reject) => {
        try {
            await knex.schema.createTable('videos', table => {
                table.increments('id');
                table.string('title');
                table.string('director');
                table.json('meta');
            });
            console.log('videos table created successfully!');
            await knex.schema.createTable('books', table => {
                table.increments('id');
                table.string('title');
                table.string('author');
                table.json('meta');
            });
            console.log('books table created successfully!');
            resolve();
        } catch(error){
            reject(error);
        }
    })
}

This keeps things a lot cleaner, not requiring you to daisy chain a bunch of thens or wrap them in surrounding functions. You just await for each table creation to resolve and then resolve their encapsulating Promise! Yay for async/await!

You can follow this pattern for the dropping of tables in your down migration, too. Simply replace the knex.schema.createTable statements with knex.schema.dropTable statements.