Knex migrations are broken after refactoring

Question

I refactored my migrations for neatness' sake, and updated the knex_migrations table in MySQL. However, when I run the migrate:latest command, I get this error message:

Error: The migration directory is corrupt, the following files are missing: [list of old migration file names]

Question: where is it keeping these old migration file names and what do I need to change to get this to work?

Answer 1

You should not change migrations after they have been applied to your database, you wont know anymore for sure that new installations will actually have the same kind of schema that the database, where old migrations were ran.

Anyways if you are sure that you hadn't made any mistakes there is a table called knex_migrations. By default it contains list of all migration files that has been ran against your database. You can edit contents of that table to match file names you have in your migration file directory and everything should start working just fine.

In comparison to for example hibernate (or liquibase), knex is not storing any hash of contents of each file, so matching file names will be enough.

Answer 2

Your problem and the error is complaining about the migration files that you removed!

My answer here cover it well: https://stackoverflow.com/a/61582622/7668448

Which i'm repeating it here:

To remove either you can rollback and so rollback then remove.

Or you can not ! And follow on the bellow:

Error: The migration directory is corrupt

Which will happens if

The migrations files are deleted, while the records on the migration table created by knex remains there!

So simply clear them up!

Important to note the migration table by now is knex_migration. Don't know if it was different in the past!

But better list the db tables to make sure!

I'm using postgres! Using psql :

> \d

i get :

You can do it with Raw SQL! Using your db terminal client, Or using knex itself! Or any other means (an editor client (pgAdmin, mysql workbench, ...).

Raw sql

DELETE FROM knex_migration
WHERE knex_migration."name" IN ('20200425190608_yourMigFile.ts', ...);

Note you can copy past the files from the error message (if you get it)

ex: 20200425190608_creazteUserTable.ts, 20200425193758_createTestTestTable.ts

from

Error: The migration directory is corrupt, the following files are missing: 20200425190608_creazteUserTable.ts, 20200425193758_createTestTestTable.ts

Copy past! And it's fast!

(you can get the error by trying to migrate)

Using knex itself

knex('knex_migration')
    .delete()
    .whereIn('name', ['20200425190608_yourMigFile.ts', ...]);

Create a script! Call your knex instance! Done! Cool!

After cleaning

The migrations will run nicely! And your directory no more corrupt! How much i do love that green!

Happy coding!

Alter migration to alter only (No)

If you are like me and like to update the migration directly in the base migration! And you may think about creating an alter migration! run it then remove it!

A fast flow! You just make the update on the base creation table! Copy past into the new created alter table! And run it then remove it!

If you're thinking that way! Don't !!!

You can't rollback because it's the changes that you want! You can't cancel them!

You can do it! And then you have to clear the records! Or you'll get the error! And just not cool!

Better create an later file script! Not a migration file! And run it directly! Done!

My preference is to create an alter.ts (.js) file in the Database folder! Then create the alter schema code there! And create an npm script to run it!

Each time you just modify it! And run!

Here the base skeleton:

import knex from './db';

(async () => {
    try {
        const resp = await knex.schema.alterTable('transactions', (table) => {
            table.decimal('feeAmount', null).nullable();
        });
        console.log(resp);
    } catch (err) {
        console.log(err);
    }
})();

And better with vscode i just use run code (if run code extension is installed! Which is a must hhhh)!

And if no errors then it run well! And you can check the response!

You can check up the schema api in the doc!

Also to alter you'll need to use alter() method as by the snippet bellow that i took from the doc:

// ________________ alter fields
// drops previous default value from column, change type
// to string and add not nullable constraint
table.string('username', 35).notNullable().alter();
// drops both not null constraint and the default value
table.integer('age').alter();

Now happy coding!