knex doesn't run migrations with foreign keys

Question

struggling to make successful migration with Knex.js. I have 3 tables(users, employees, clients). Employees and clients has FK from users. However, I can't make migration if I use .foreign property, however any other property(lets say I've tried with bigInt) runs well. I saw in one article that it's important to have PK created first, so by date my users should run first as I understand. The error i get:

migration failed with error: alter table "clients" add constraint "clients_user_id_foreign" foreign key ("user_id") references "users" ("user_id") on delete CASCADE - column "user_id" referenced in foreign key constraint does not exist

20220216185323_users:

const {hashPassword} = require("../../utils/bcryptUtils");
exports.up = function (knex) {
    return knex.schema.createTable("users", table => {
        table.increments("user_id").primary();
        table.string("username").unique().notNullable();
        table.string("password").notNullable();
        table.string("email").unique().notNullable();
        table.string("role").defaultTo("Client");
        table.string("type").defaultTo("Client");
        table.timestamp("created_at").defaultTo(knex.fn.now());
    }).then(async function () {
        const {hashPassword} = require("../../utils/bcryptUtils");

        return knex("users").insert([
            {
                "username": process.env.USERNAME,
                "password": await hashPassword(process.env.PASSWORD),
                "email": process.env.EMAIL,
                "role": process.env.ROLE,
                "type": process.env.TYPE
            }
        ]);
    });
};

exports.down = function (knex) {
    return knex.schema.dropTable("users");
};

20220217200112_clients:

exports.up = function (knex) {
    return knex.schema.createTable("clients", table => {
        table.increments("client_id").primary();
        table.foreign("user_id").references("user_id").inTable("users").onDelete("CASCADE");
        table.string("client_name").notNullable();
        table.string("client_email").unique().notNullable();
        table.string("client_vat").unique().notNullable();
        table.string("client_auth_key").notNullable();
        table.string("client_plan").defaultTo("Bronze");
        table.timestamp("created_at").defaultTo(knex.fn.now());
    });
};

exports.down = function (knex) {
    return knex.schema.dropTable("clients")
};

20220217200128_employees:

exports.up = function (knex) {
    return knex.schema.createTable("employees", table => {
        table.increments("employee_id").primary();
        table.foreign("user_id").references("user_id").inTable("users").onDelete("CASCADE");
        table.string("employee_firstname").defaultTo(null);
        table.string("employee_lastname").defaultTo(null);
        table.string("employee_email").defaultTo(null);
        table.timestamp("created_at").defaultTo(knex.fn.now());
    });
};

exports.down = function (knex) {
    return knex.schema.dropTable("employees");
};

Answer 1

Looks like you are missing a type for your user_id foreign key column on the employees and client tables. If you add the type it should work.

exports.up = function (knex) {
    return knex.schema.createTable("clients", table => {
        table.increments("client_id").primary();
        table.integer("user_id").unsigned();
        table.foreign("user_id").references("user_id").inTable("users").onDelete("CASCADE");
        table.string("client_name").notNullable();
        table.string("client_email").unique().notNullable();
        table.string("client_vat").unique().notNullable();
        table.string("client_auth_key").notNullable();
        table.string("client_plan").defaultTo("Bronze");
        table.timestamp("created_at").defaultTo(knex.fn.now());
    });
};

exports.up = function (knex) {
    return knex.schema.createTable("employees", table => {
        table.increments("employee_id").primary();
        table.integer("user_id").unsigned();
        table.foreign("user_id").references("user_id").inTable("users").onDelete("CASCADE");
        table.string("employee_firstname").defaultTo(null);
        table.string("employee_lastname").defaultTo(null);
        table.string("employee_email").defaultTo(null);
        table.timestamp("created_at").defaultTo(knex.fn.now());
    });
};