In the last lesson, we discussed what migrations are and how to use them. In this lesson, I'd like to expand further on migrations by talking about how to alter tables using migrations. While this isn't something we'd typically need to do during development, once our site is out in production and purging our database is no longer an option this will be a must.

Setting The Story

So, let's say when we originally created our database we didn't have a description on our tasks table. So, for example, let's say our tasks migration looked like the below. If you're following along, just comment out your description column.

// database/migrations/TIMESTAMP_create_tasks_tables.ts

export default class Tasks extends BaseSchema {
  protected tableName = 'tasks'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').primary()
      table.string('name').notNullable()
      table.timestamp('due_at').nullable()
      table.integer('status_id').unsigned().notNullable().defaultTo(1)
      table.integer('created_by').unsigned().references('id').inTable('users')
      table.integer('assigned_to').unsigned().references('id').inTable('users')
      table.timestamps(true, true)
    })
  }

  public async down() {
    this.schema.dropTable(this.tableName)
  }
}

Let's also say we have production data in our database, so rolling back and re-running the migration is not an option. The way we'd go about altering our tasks table is by creating a new migration specifically to alter the tasks table.

Now, if you're following along, in order to reflect this in our database let's rollback our database all the way (this is just for demonstration purposes).

$ node ace migration:rollback --batch 0

Then, let's re-run all our migrations so we no recreate all our tables, minus the description column on our tasks table.

$ node ace migration:run

Great! Now we're all set to move forward.

Creating Our Migration

To start, let's head into our terminal and get a new migration created.

$ node ace make:migration alter_tasks_add_description
# CREATE database/migrations/TIMESTAMP_alter_tasks_add_descriptions.ts

Now, let's head into our newly created alter_tasks_add_descriptions migration. You should see something like the below.

// database/migrations/TIMESTAMP_alter_tasks_add_description.ts

export default class AlterTasksAddDescriptions extends BaseSchema {
  protected tableName = 'alter_tasks_add_descriptions'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.timestamps(true)
    })
  }

  public async down() {
    this.schema.dropTable(this.tableName)
  }
}

First, let's update the tableName to tasks so this affects our tasks table. You could also define this via --table tasks in the migration creation command.

protected tableName = 'tasks'

Second, due to the way I defined the migration, Adonis will assume I want to create a new table. So, we can just clear out both our up and down methods.

public async up() {

}

public async down() {

}

Now we have our migration in a good state to move forward.

Defining Our Migration

So, instead of creating a new table, our goal with this migration will be to alter an existing table. To do this, we can use the alterTable method on our schema property. This accepts the same parameters as createTable, however, within our table callback instead of defining all columns we'll only want to make our alterations.

public async up() {
  this.schema.alterTable(this.tableName, (table) => {
    table.text('description').nullable()
  })
}

Now whenever we run this migration this will alter our tasks table adding in the description column.

Next, we need to define our down method for our rollbacks. Since we're altering our tasks table by adding a description column in up, what we'll want to do in down is alter our tasks table again by removing the description column.

public async down() {
  this.schema.alterTable(this.tableName, (table) => {
    table.dropColumn('description')
  })
}

That should do it! Now, let's go ahead and jump back into our terminal and run our new migration.

$ node ace migration:run
# > migrated database/migrations/TIMESTAMP_alter_tasks_add_description.ts

You can confirm this did indeed add the description column to our tasks table by inspecting the table if you wish.

Now, let's confirm our rollback is working okay.

$ node ace migration:rollback
# > reverted database/migrations/TIMESTAMP_alter_tasks_add_description.ts

Again, you can confirm this removed our description column if you wish.

Wrapping Up

So, that's really the flow to alter tables via migrations regardless of what it is you're changing. In up call alterTable and define your alterations in the callback function. In down call alterTable and revert all alterations you make in up.

Now, since we're not in production and can rollback, if you'd like to keep your migrations clean you can undo everything we did in this lesson.

1. Rollback all the way

   $ node ace migration:rollback --batch 0

2. Delete your new altertasksadd_description migration

3. Add the description column back to the createtaskstables migration

   export default class Tasks extends BaseSchema {
     protected tableName = 'tasks'
   
     public async up() {
       this.schema.createTable(this.tableName, (table) => {
         table.increments('id').primary()
         table.string('name').notNullable()
         table.text('description').nullable()
         table.timestamp('due_at').nullable()
         table.integer('status_id').unsigned().notNullable().defaultTo(1)
         table.integer('created_by').unsigned().references('id').inTable('users')
         table.integer('assigned_to').unsigned().references('id').inTable('users')
         table.timestamps(true, true)
       })
     }
   
     public async down() {
       this.schema.dropTable(this.tableName)
     }
   }

4. Rerun your migrations

   $ node ace migration:run

Next Up

That should wrap up everything we need to cover with migrations. In the next lesson, we'll start learning about Models, how to create them, what they're for, and how to define them.