Dropping and recreating a table in Knex.js

In the previous article, we saw how to set up a migration file. We saw how to create a table, namedfruits, with anid,name,weight, anddelicious columns.

We set thename column to be required, by calling the.notNullable() method of Knex.notNullable() makes sure that an error is raised if we try to create a record without a name.

This is how the code for creating the table and columns looks like at this point:

exports.up=function(knex){returnknex.schema.createTable('fruits',tbl=>{tbl.increments('id');tbl.text('name',128).unique().notNullable();tbl.decimal('weight');tbl.boolean('delicius');})};

After we set up the migration, we run it with this command:

knex migrate:latest

Since we didn't have a database already, this command created one for us with afruits table and the specified columns.

So far so good, right?

Oh, no! We just remembered that theweight column has another requirement. It cannot haveNULL values. In other words, it’s a required field.

This means that we should have added thenotNullable() method call to the call totbl.decimal('weight')
The problem is that we have already run the migration. How can we fix this situation?

Rollback to the rescue.

At this point we have a couple of options to fix our little mistake.

We could just create a second migration, that will run after the first one, and inside the up method we could modify the column to make the weight required.
This is certainly a valid option, especially if we already had some data in the database.

The second option would be to simply roll back the migration and try again. Rolling back the migration means running the code in thedown method in the migration file, which destroys the wholefruits table and all the data in it.

We could then adjust the line where the weight column is created by adding the call tonotNullable(). We could finally run the migration again. This would fix our little mistake and have us up and running in no time.

Migration rollback

Since we haven't added any data to our database yet, probably the best option in this case is the second one, just roll back the migration and start over. We have nothing to lose.
To run a migration rollback we simply issue this command at the console:

knex migrate:rollback

Knex will run the code in thedown method, in the last migration file, and respond with a message telling us the migration has been rolled back and the database has been brought to its previous state.

Using environment: developmentBatch 1 rolled back: 1 migrations

Notice that we only want to roll back a migration if we are working on the latest migration file and if we don't have any data that we may need in the database.
In our case, rolling back will destroy thefruits table, so it's not a viable option if we need the data in it.
Once the migration has been rolled back, we can chain on the.notNullable() method to that column:

tbl.decimal('weight').notNullable();

We then save the migration file, and run the migration again with:

knex migrate:latest

This will recreate the table with the correct setting for the weight column.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox bysubscribing to my newsletter.