Home » AdonisJs

Creating Migration Scripts in AdonisJs

Here, we are going to learn how to create migration scripts in AdonisJs?
Submitted by Radib Kar, on January 13, 2021

In the previous tutorial, we learnt and updated an existing migrations script. Now, in this tutorial, we will create migrations scripts from scratch.

As we know that the ultimate goal of this tutorial is to create a backend CRUD application, here you will start your first hands-on towards the project.

Let's brief about what we want to build at the end of this tutorial series. We want to build a todo application backend where there will be users. Users will have projects & projects will have tasks.

So how many tables you need to implement this backend?

Of course, you need three tables at least, which are

• Users
• Projects
• Tasks

We have already created our users table in our last tutorial. (Yes, you have done this already)

Now, in this tutorial, we will create two more tables, projects & tasks.

Let's start with the project table first.

The project table should have the following attributes

1. "id": id of the project
2. "title": title of the project which is of type "String" & can have a maximum of 100 characters. Also, this attribute should not be Nullable
3. "description": description of project if required which is of type "String" & can have a maximum of 600 characters. This attribute can be Nullable
4. "user_id": this is the foreign key which refers to the user table. This makes sense as a user has this project & thus in the database we need to store which user this project belongs to

If you want to add any other attribute, feel free to add.

Similarly, we will create the task table also.

The task table should have the following attributes

1. "id": id of the task
2. "title": title of the task which is of type "String" & can have a maximum of 100 characters. Also, this attribute should not be Nullable
3. "description": description of task if required which is of type "String" & can have a maximum of 600 characters. This attribute can be Nullable
4. "project_id": this is the foreign key which refers to the project table. This makes sense as task belong to some project & thus in the database we need to store which project this task belongs to.

If you want to add any other attribute, feel free to add.

Okay, for both the table, we will create the script similarly as we saw in user table migration script. Here we only have one additional field which is a foreign key.

Let's first create the migration script for the projects table.

To create a migration script,

adonis make:migration projects

This will create a new migration script for projects table[select create table in the command prompt]

So we can see by default it creates the script structure which has supports for id & timestamp. Thus we need to

Now create the attributes similarly as we saw in the user table. So your script should look like below after updating.

'use strict'

/** @type {import('@adonisjs/lucid/src/Schema')} */
const Schema = use('Schema')

class ProjectsSchema extends Schema {
	up () {
		this.create('projects', (table) => {
		table.increments()
		table.string('title', 100).notNullable()
		table.string('description', 600).nullable()
		table.timestamps()
		})
	}

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

module.exports = ProjectsSchema

We haven't added the foreign key yet. We will come to that after creating the script for tasks. Now you create the task migration script similarly. It should look like below.

'use strict'

/** @type {import('@adonisjs/lucid/src/Schema')} */
const Schema = use('Schema')

class TasksSchema extends Schema {
	up () {
		this.create('tasks', (table) => {
		table.increments()
		table.string('title', 100).notNullable()
		table.string('description', 600).nullable()
		table.boolean('completed').notNullable()
		table.timestamps()
		})
	}

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

module.exports = TasksSchema

Okay, now letse see how to add foriegn keys. When we created the boilerplate, there was two migration scripts created by default. One is user which we alreday went through and another one is token which is like below:

class TokensSchema extends Schema {
	up () {
		this.create('tokens', (table) => {
		table.increments()
		table.integer('user_id').unsigned().references('id').inTable('users')
		table.string('token', 255).notNullable().unique().index()
		table.string('type', 80).notNullable()
		table.boolean('is_revoked').defaultTo(false)
		table.timestamps()
		})
	}

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

Okay, so if we look carefully the up method has something like below:

table.integer('user_id').unsigned().references('id').inTable('users')

Is this what we are looking for? What this means. Well, this means that the token table has an attribute namely user_id which references to attribute id in table users.

This is what we need, so add this in the project script & similarly add such support in your task script.

So in project script we will add,

table.integer('user_id').unsigned().references('id').inTable('users')

&
in task script we will add
table.integer('project_id').unsigned().references('id').inTable('projects')

Thus finally they will look like:

Now run the migration scripts,

adonis migration:run

[You don't need to run separately, since the above command runs all pending migrations]

And given that it successfully runs, you should see your projects & tasks table created in MySQL workbench,

Congrats, you have learnt how to create a migration script from scratch? In our next tutorials, we will start learning routing & controllers.