./ace make:migration users --create=usersYou 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.
Database Migrations is a process of creating, altering and dropping database tables from code, instead of writing SQL queries. AdonisJs has out of the box support for database migrations so that you can set up your database tables within the application codebase.
The naive approach of database management is to create the SQL database tables manually. Which work, but it is not great as it requires a lot of manual re-work.
Let’s take an example of a standard procedure of creating database tables.
Login to SequelPro(or similar) and create database tables.
Now when someone wants to work on the same application, you need to share the database schema or dump with them.
When they make some changes in the database, they have to re-share the new database dump with you.
You cannot run automated tests on TravisCI(or similar) as you need access to the server to create the database tables.
Finally, when deploying the app in production, you need to make a remote connection to your server database to create those tables manually.
With migrations SQL schema is part of your code base which means once your co-workers will check out the code from a git client, they will get the database schema with it, and any progressive changes made by them are available to you.
Migrations are stored inside database/migrations folder.
Each schema file is an ES2015 class with two required methods called up and down.
The up method get executed when running the migrations.
The down method get called during migrations rollback.
Migrations are executed in a batch and give you the flexibility to rollback to a given batch.
Now as you know about the benefits of migrations, let’s take a closer look on how to create SQL database tables using Javascript.
./ace make:migration users --create=userscreate: database/migrations/1464437815620_users.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 = UsersSchemaThe migration:run command is used to execute all the migrations stored inside database/migrations directory. AdonisJs will smartly skip the migrations which have been executed already and no executes the new ones.
./ace migration:runIt is recommended to work with incremental migrations, instead of modifying the same schema file every time. For example
Create a new schema file when you want to create a new table.
Down the line, you realized that you need two extra fields in the same table. Create a new schema file to alter the existing table and add those two new fields.
Again after few days, you have a requirement to rename an existing field. Create a new schema file for that also.
Incremental migrations help you in tracking changes like Version control. When writing code, it is a good practice to make small code commits, so that you have cleaner code history. You must treat your migrations the same way.
Below is the list of ace commands specific to migrations only.
| Command | Options | Purpose |
|---|---|---|
migration:run |
none |
Run all pending migrations by executing up method of all schema files. |
migration:rollback |
[batch=last] |
Rollback migrations to a given batch or default to last batch. |
migration:refresh |
none |
Refresh migrations by dropping and re-running all migrations |
migration:reset |
none |
Rollback to the initial state. |
migration:status |
none |
Check the current status of migrations. |
Create a new database table.
class UserSchema {
up () {
this.create('users', (table) => {
...
})
}
}Only creates the table if it does not exists, otherwise silently ignores the create command.
class UserSchema {
up () {
this.createIfNotExists('users', (table) => {
...
})
}
}Rename an existing database table.
class UserSchema {
up () {
this.rename('users', 'my_users')
}
}Drop an existing database table.
class UserSchema {
down () {
this.drop('users')
}
}Drop database table only if it exists, otherwise silently ignores the drop command.
class UserSchema {
down () {
this.dropIfExists('users')
}
}Resolves with a boolean indicating whether a database table exists or not.
class UserSchema {
up () {
this.has('users').then((exists) => {
if (!exists) {
// do something
}
})
}
}Select a table for alter.
class UserSchema {
up () {
this.table('users', (table) => {
table.dropColumn('deleted_at')
})
}
}Run an arbitrary SQL query in the schema builder chain.
class UserSchema {
up () {
this.raw('SET sql_mode="TRADITIONAL"')
.create('users', (table) => {
table.increments()
})
}
}The db method will give you the access to the Database query builder within your migrations. It is helpful if you want to migrate data while altering the database tables.
class UserSchema {
up () {
this.db(function * (database) {
const names = yield database.from('users').pluck('name')
})
}
}Please refer to the docs of Knex Schema Building, everything from knex is fully supported. Below is the example making use of schema builder to create the users table.
'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', 60)
table.timestamps()
table.softDeletes()
})
}
down () {
this.drop('users')
}
}
module.exports = UsersSchema