Migration

Working with Migrations-

Migrations are simply files that live in the /migrations directory of your module. Knex keeps track of which migrations have been run so that it doesn't try to run the same migration twice. We don't want to create a new migration / file for each migration change. So before getting started run -

migrate:rollback

This will remove all the changes that were made with the existing migrations, if this command is skipped and changes are made then while running 'migrate latest` the changes won't be detected. So make sure to do this befor working with migrations.

1. Steps involved while creating a database table -

1. Create a new migration file with knex cli.
2. Write the code for the exports.up function.
3. Write the code for the exports.down function.
4. Run the migration.

Step 1-

To create a new migration file simply use the knex cli:

knex migrate:make migration_name

Your migration name should be something descriptive, not simply the name of the table that you are creating / modifying. Remember that throughout the lifetime of your app there may be several migrations that all relate to the same database table, so you want your migrations names to inform other developers as to what that migration is doing.

The /migrations/migration_name file will look like this:

exports.up = function(knex, Promise) {
  //
};

exports.down = function(knex, Promise) {
  //
};

• First is exports.up, which specifies the commands that should be run to make the database change that you'd like to make.These are things like creating database tables, adding or removing a column from a table, changing indexes, etc.

• Second function is exports.down. This functions goal is to do the opposite of what exports.up did. If exports.up created a table, then exports.down will drop that table. If exports.up added a column, then exports.down will remove that column. The reason to include exports.down is so that you can quickly undo a migration should you need to.

Step 2-

Write the code for the exports.up function.

Inside of the exports.up function of your newly created migration file add the following code:

exports.up = function(knex, Promise) {
  return Promise.all([
    knex.schema.createTable("table_name", table => {
      table
        .increments("id")
        .unsigned()
        .primary();
      table.dateTime("createdAt").notNull();
      table.dateTime("updatedAt").nullable();
      table.dateTime("deletedAt").nullable();
      table.string("column1").notNull();
      table.text("column2").nullable();
      table.decimal("column3", 6, 2).notNull();
      table.enum("column4", ["value1", "value2", "value3"]).notNull();
    })
  ]);
};

• exports.up and exports.down functions should always return a promise.
• knex.schema.createTable to create a new database table. We pass in the name of the table and a callback function, in which we can specify the columns that should exist on the table.
• Notice that the callback takes an argument 'table', which gives us a reference to the table. For each column that we want to create we specify a column type (increments, dateTime, string, text, decimal, enum, etc.) as well as a column name. Certain column types take additional arguments. We can optionally specify more information about each column such as whether or not the column is nullable, its' default value among others. You can find the full list of functionality in the knex documentation.

Step 3 -

Write the code for the exports.down function.

Inside of the exports.down function of your create_table_name migration file add the following code:

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

In case we need to quickly undo a migration. In this case since we created the table_name table in exports.up we will drop the table_name table in exports.down.

Step 4 -

Run the migration

Now that the migration has been created and defined, we will run the migration on our local database. To do this use the following command:

knex migrate:latest

This will cause any new migrations to run. To quickly undo the migration you may run knex

migrate:rollback