Problems creating schema by Knex js

Question

I am trying to create a relationship scheme between tables in Node express, and that apparently is right, even so is generating this error message.

knex migrate:Latest

migration file "20210419105543_create_table_articles.js" failed
migration failed with error: alter table `categories` add constraint `categories_parentid_foreign` foreign key (`parentId`) references `categories` (`id`) - ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint
Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint
    at Query.Sequence._packetToError (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
    at Protocol._parsePacket (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<anonymous> (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\Connection.js:526:10)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:284:9)
    at Socket.Readable.push (_stream_readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    --------------------
    at Protocol._enqueue (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\protocol\Protocol.js:144:48)
    at Connection.query (C:\S\NODE\projeto\versao-inicial\backend\node_modules\mysql\lib\Connection.js:198:25)
    at C:\S\NODE\projeto\versao-inicial\backend\node_modules\knex\lib\dialects\mysql\index.js:154:18
    at Promise._execute (C:\S\NODE\projeto\versao-inicial\backend\node_modules\bluebird\js\release\debuggability.js:384:9)
    at Promise._resolveFromExecutor (C:\S\NODE\projeto\versao-inicial\backend\node_modules\bluebird\js\release\promise.js:518:18)
    at new Promise (C:\S\NODE\projeto\versao-inicial\backend\node_modules\bluebird\js\release\promise.js:103:10)
    at Client_MySQL._query (C:\S\NODE\projeto\versao-inicial\backend\node_modules\knex\lib\dialects\mysql\index.js:148:12)
    at Client_MySQL.query (C:\S\NODE\projeto\versao-inicial\backend\node_modules\knex\lib\client.js:202:17)
    at C:\S\NODE\projeto\versao-inicial\backend\node_modules\knex\lib\transaction.js:272:21
    at tryCatcher (C:\S\NODE\projeto\versao-inicial\backend\node_modules\bluebird\js\release\util.js:16:23)
    at Function.Promise.attempt.Promise.try (C:\S\NODE\projeto\versao-inicial\backend\node_modules\bluebird\js\release\method.js:39:29)
    at Client_MySQL.trxClient.query (C:\S\NODE\projeto\versao-inicial\backend\node_modules\knex\lib\transaction.js:269:34)
    at Runner.<anonymous> (C:\S\NODE\projeto\versao-inicial\backend\node_modules\knex\lib\runner.js:146:36)
    at Runner.tryCatcher (C:\S\NODE\projeto\versao-inicial\backend\node_modules\bluebird\js\release\util.js:16:23)
    at Runner.query (C:\S\NODE\projeto\versao-inicial\backend\node_modules\bluebird\js\release\method.js:15:34)
    at Runner.<anonymous> (C:\S\NODE\projeto\versao-inicial\backend\node_modules\knex\lib\runner.js:213:19)

These are my tables;

User

    exports.up = function (knex, Promise) {
        return knex.schema.createTable('users', table => {
            table.increments('id').primary()
            table.string('name').notNull()
            table.string('email').notNull().unique()
            table.string('password').notNull()
            table.boolean('admin').notNull().defaultTo(false)
        })
    };

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

Categories

exports.up = function(knex, Promise) {
    return knex.schema.createTable('categories', table => {
        table.increments('id').primary()
        table.string('name').notNull()
        table.integer('parentId').references('id')
            .inTable('categories')
    })
};

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

Articles

exports.up = function(knex, Promise) {
    return knex.schema.createTable('articles', table => {
        table.increments('id').primary()
        table.string('name').notNull()
        table.string('description', 1000).notNull()
        table.string('imageUrl', 1000)
        table.binary('content').notNull()
        table.integer('userId').references('id')
            .inTable('users').notNull()
        table.integer('categoryId').references('id')
            .inTable('categories').notNull()
    })
};

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

Please someone can identify what is wrong?

Answer 1

And I’m going to propose an answer that I can’t guarantee will work, but when I had the same problems, it solved for me.

Knex presented problems every time I tried to create relationships in a Mysql database in this way that you did, for example, in Categories:

table.integer('parentId').references('id')
            .inTable('categories')

The solution was to create an unassociated integer (unsigned) and only then set as foreign key and then associate it to a table. It was done as follows, adapting to your code:

/**
 * Primeiro você faz unsigned para o "parentId".
 */
table.integer("parentId").unsigned()
      .notNullable();   // notNullable não obrigatório
/**
 * Depois voce vai definir "parentId" como chave estrangeira e associá-lo 
 * a tabela "categories".
 */
table
      .foreign('parentId')
      .references('id')         
      .inTable('categories');

This logic should be repeated for all the other Migrations you have relationships with (Articles, etc....).

This was a problem I had when I used Knex with Mysql and solved using this solution. Try it and give me the feedback.