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 documented database mutations, created throughout your application’s development lifecycle that you can roll back or re-run at any point in time.

Migrations make it easier to work as a team, enabling database schema changes from one developer to be easily tracked and then applied by other developers in your organization.

Creating Migrations

To use migrations, the Migrations Provider must first be registered inside the start/app.js file’s aceProviders array.

Let’s create a users table with the help of migrations.

First, call the adonis make:migration command to create a schema file:

> adonis make:migration users

When prompted, choose the Create table option and press Enter:

Output
✔ create  database/migrations/1502691651527_users_schema.js

Your new schema file (prefixed with the current timestamp) is created in the database/migrations directory, ready to modify as required:

database/migrations/…​users_schema.js
'use strict'

const Schema = use('Schema')

class UsersSchema extends Schema {
  up () {
    this.create('users', (table) => {
      table.increments()
      table.timestamps()
    })
  }

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

module.exports = UsersSchema

Schema Files

A schema file requires two methods: up and down.

up()

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

down()

The down method is used to revert the changes applied in the up method. When up is used to create a table, down would be used to drop that table.

Update the schema file you just created with the following code:

database/migrations/…​users_schema.js
'use strict'

const Schema = use('Schema')

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

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

module.exports = UsersSchema

The example above demonstrates how to create/alter a database table using schema files, chaining together different column type/modifier methods to define the characteristics of individual field attributes in the up method.

Column Types/Modifiers

For the full list of schema column type and modifier methods, see the Knex API documentation.

Column Types

Method Description

table.bigInteger(name)

Adds a bigint column.

table.binary(name, [length])

Adds a binary column.

table.boolean(name)

Adds a boolean column.

table.date(name)

Adds a date column.

table.datetime(name, [precision])

Adds a datetime column.

table.decimal(name, [precision], [scale])

Adds a decimal column.

table.enu(col, values, [options])

Adds a enum column.

table.float(name, [precision], [scale])

Adds a float column.

table.increments(name)

Adds an auto incrementing column.

table.integer(name)

Adds an integer column.

table.json(name)

Adds a json column.

table.string(name, [length=255])

Adds a string column.

table.text(name, [textType])

Adds a text column.

table.time(name, [precision])

Adds a time column.

table.timestamp(name, [useTz], [precision])

Adds a timestamp column.

table.timestamps([useTimestamps], [defaultToNow])

Adds created/updated columns.

table.uuid(name)

Adds a uuid column.

Column Modifiers

Method Description

.after(field)

Set column to be inserted after field.

.alter()

Marks the column as an alter/modify.

.collate(collation)

Set column collation (e.g. utf8_unicode_ci).

.comment(value)

Set column comment.

.defaultTo(value)

Set column default value.

.first()

Set column to be inserted at the first position.

.index([indexName], [indexType])

Specifies column as an index.

.inTable(table)

Set foreign key table (chain after .references).

.notNullable()

Set column to not null.

.nullable()

Set column to be nullable.

.primary([constraintName])

Set column as the primary key for a table.

.references(column)

Set foreign key column.

.unique()

Set column as unique.

.unsigned()

Set column to unsigned (if integer).

Multiple Connections

Schema files can use a different connection by defining a connection getter (ensure your different connection exists inside the config/database.js file):

database/migrations/…​users_schema.js
const Schema = use('Schema')

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

  // ...
}

module.exports = UsersSchema
The database table adonis_schema is always created inside the default connection database to manage the lifecycle of migrations (there is no option to override it).

Run Migrations

We need to call the migration:run command to run migrations (which executes the up method on all pending migration files):

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

Migration Status

You can check the status of all migrations by running the following command:

> adonis migration:status

migration status zajqib

The batch value exists as a reference you can use to limit rollbacks at a later time.

That is how migrations work under the hood:

  1. Calling adonis migration:run runs all pending schema files and assigns them to a new batch.

  2. Once a batch of migration files are run, they are not run again.

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

Don’t create multiple 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.

Migration Commands

Below is the list of available migration commands.

Command 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 then re-run them from the start.

migration:reset

Rollback all migrations to the 0 batch.

migration:status

Get the status of all the migrations.

Command Help

For detailed command options, append --help to a each migration command:

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

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

About:
  Run all pending migrations

Schema Table API

Below is the list of schema 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 exist):

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

rename(from, to)

Rename an existing database table:

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

drop

Drop a database table:

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

dropIfExists

Drop a database table (only if it exists):

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

alter

Select a database table for alteration:

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

Returns whether a table exists or not (this 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.

Extensions only work with a PostgreSQL database.

createExtension(extensionName)

Create a database extension:

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

createExtensionIfNotExists(extensionName)

Create a database extension (only if doesn’t exist):

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

dropExtension(extensioName)

Drop a database extension:

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

dropExtensionIfExists(extensionName)

Drop a database extension (only if it exists):

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

Executing Arbitrary Code

Commands written inside the up and down methods are scheduled to be executed later inside a migration.

If you need to execute arbitrary database commands, wrap them inside the schedule function:

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)
    })

    // drop old table
    this.drop('users')
  }
}
The schedule method receives a transaction object. It is important to run all database commands inside the same transaction, otherwise your queries will hang forever.

Schema Builder API

The schema builder API uses the Knex API, so make sure to read their documentation for more information.

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())
  })
}