You are viewing the legacy version of AdonisJS. Visit https://adonisjs.com for newer docs. This version will receive security patches until the end of 2021.

Migrations

Migrations are mutations to your database as you keep evolving your application. Think of them as step by step screenshot of your database schema, that you can roll back at any given point of time.

Also, migrations make it easier to work as a team, where database changes from one developer are easily spotted and used by other developers in the team.

Creating migrations

Let’s start from the beginning where we want to create a users table with the help of migrations.

Make sure the migrations provider is registered inside aceProviders array inside start/app.js file.

We make use of the adonis make:migration command to create a schema file for us.

adonis make:migration users

On prompt choose Create table option and press Enter

Output
✔ create  database/migrations/1502691651527_users_schema.js

Defining schema

Now quickly open the created file and write some code inside it.

'use strict'

const Schema = use('Schema')

class UsersSchema extends Schema {
  up () {
    this.create('users', (table) => {
      table.increments()
      table.string('username').unique()
      table.string('email').unique()
      table.string('password', 80)
      table.timestamps()
    })
  }

  down () {
    this.drop('users')
  }
}

module.exports = UsersSchema

As you can see, it is so simple to create/alter database tables using schema files, since you can chain different methods to define the field attributes.

The schema file is a Javascript ES6 class with two required methods called up and down on it.

up

The up method is used to take action on a table. It can be creating a new table or altering the existing table.

down

The down method is the reverse of the up action. When up method creates a table, you simply drop it inside the down method.

Multiple connections

The schema files can also make use of a different connection by defining a connection property on them. Also make sure that the same connection is defined inside config/database.js file.

A table adonis_schema is created to manage the lifecycle of migrations. This table will always be created inside the primary connection database and there is no option to override it. 
const Schema = use('Schema')

class UsersSchema extends Schema {
  static get connection () {
    return 'mysql'
  }

  up () {
  }

  down () {
  }
}

module.exports = UsersSchema

Run migrations

Finally, we need to call another command to run the migrations, which executes the up method on this class.

adonis migration:run
Output
migrate: 1502691651527_users_schema.js
Database migrated successfully in 117 ms

Migrations status

Also, you can check the migration status by running migration:status command.

adonis migration:status

Output migration status zajqib

There is a Batch next to each migration so that you can rollback to a given batch without manually altering the database tables.

That is how migrations work under the hood.

  1. Every time you run adonis migration:run, a new batch is created for all the pending schema files.

  2. Files which are migrated once, are not executed again.

  3. Running adonis migration:rollback rollbacks the last batch migrations in reverse order.

Do not create all tables in a single schema file, instead create a new file for each database change. This way you keep your database atomic and can roll back to any version.

Migrations commands

Below is the list of available command with their description. Also, make sure to append --help to the command name to see a list of available options. For example:

Commands list

Command Description

make:migration

Create a new migration file,

migration:run

Run all pending migrations.

migration:rollback

Rollback last set of migrations.

migration:refresh

Rollback all migrations to the 0 batch and then re-run them from the start.

migration:reset

Rollback all migrations to the 0 batch.

migration:status

Get status of all the migrations.

Command help

adonis migration:run --help
Output
Usage:
  migration:run [options]

Options:
  -f, --force   Forcefully run migrations in production
  -s, --silent  Silent the migrations output
  --log         Log SQL queries instead of executing them

About:
  Run all pending migrations

Table’s API

Below is the list of methods available to interact with database tables.

create

Create a new database table

up () {
  this.create('users', (table) => {
  })
}

createIfNotExists

Create a new database table only if it doesn’t exists

up () {
  this.createIfNotExists('users', (table) => {
  })
}

rename(from, to)

Rename existing database table

up () {
  this.rename('users', 'my_users')
}

drop

Drop database table

down () {
  this.drop('users')
}

dropIfExists

Drop database table only when it exists

down () {
  this.dropIfExists('users')
}

alter

Select database table for alternation.

up () {
  this.alter('users', (table) => {
    // add new columns or remove existing
  })
}

raw

Run an arbitrary SQL query.

up () {
  this
    .raw("SET sql_mode='TRADITIONAL'")
    .table('users', (table) => {
      table.dropColumn('name')
      table.string('first_name')
      table.string('last_name')
    })
}

hasTable

Tells whether a table exists or not. It is an async method.

async up () {
  const exists = await this.hasTable('users')

  if (!exists)  {
    this.create('up', (table) => {
    })
  }
}

Extensions

Below is the list of extension methods you can execute when running migrations.

Extension only works with PostgreSQL database.

createExtension(extensionName)

Create a database extension.

class UserSchema {
  up () {
    this.createExtension('postgis')
  }
}

createExtensionIfNotExists(extensionName)

Only creates the extension if it does not exists, otherwise silently ignores the createExtension command.

class UserSchema {
  up () {
    this.createIfNotExists('postgis')
  }
}

dropExtension(extensioName)

Drop an existing database extension.

class UserSchema {
  down () {
    this.dropExtension('postgis')
  }
}

dropExtensionIfExists(extensionName)

Drop database extension only if it exists, otherwise silently ignores the dropExtension command.

class UserSchema {
  down () {
    this.dropExtensionIfExists('postgis')
  }
}

Executing arbitrary code

Commands written inside up and down methods are scheduled to be executed later inside a migration. In case you want to execute arbitrary database commands, you can wrap them inside the schedule function.

The schedule method receives the transaction object and it is important to run all database commands inside the same transaction, otherwise queries will hang forever. 
class UserSchema {
  up () {
    // create new table
    this.create('new_users', (table) => {
    })

    // copy data
    this.schedule(async (trx) => {
      const users = await Database.table('users').transacting(trx)
      await Database.table('new_users').transacting(trx).insert(users)
    })

    // copy old table
    this.drop('users')
  }
}

Schema builder API

The schema builder API is exactly same as the knex api, so make sure to read their documentation.

fn.now()

Knex has a method called knex.fn.now(), which is used to set the current timestamp on the database field.

In AdonisJs, you reference this method as this.fn.now().

up () {
  this.table('users', (table) => {
    table.timestamp('created_at').defaultTo(this.fn.now())
  })
}