At this point, we have Lucid, Adonis' ORM, installed and configured within our application. We've also created a database to work with and configured it's environment variables within our .env file.

We're now ready to populate our project's database with the tables that we'll need to persist data within our application. With Adonis, we can define our database structure programmatically using migrations.

What Are Migrations?

Migrations are a way to programmatically alter a database, and in Adonis, we get to do this in a TypeScript-safe environment. Typically, a project will have a migration for each table the application needs. Or, later in an application's lifespan, you may have a migration for alterations you need to perform on a given table.

To provide migrations to us, Adonis wraps a package called knex.js. Knex provides us all the methods we'll call to alter our database. Knex also allows us to alter our database ourselves by providing the ability to define raw SQL that'll run with our migration.

The Flow of Migrations

Migrations have two directions, up and down. We can run migrations to go up, altering our database with changes we want to persist to our structure. Or, we can rollback migrations to go down, undoing the changes made when the migration was run.

This flow is great for several reasons.

1. During development, we can easily rollback changes that aren't in production to make alterations as needed.

2. As our application ages, we'll maintain a history of the changes we've made to our database.

3. When we deploy code and new migrations that alter our existing database, those alterations will happen with our deployments. So, our codebase is always synced with our database state.

Production Warning

Now, it's worth noting that rollback primarily exists for dev usage. If you're running this in production be very cognizant of what you're doing. The only way to get production data back after a rollback is by restoring from a pre-existing database backup.

Migrations In Adonis

Adonis' Ace CLI provides us a few commands we can run in regards to migrations.

• make:migration - This will allow us to actually create a migration

• migration:run - This allows us to run all migrations that haven't been previously run

• migration:rollback - This allows us to rollback previously run migrations

• migration:status - This allows us to view the run status on our migrations

When we make a new migration within Adonis, our migration will be generated in our /database/migrations directory. The filename will be prefixed with a timestamp to ensure the migrations are run in the order of oldest to newest.

Our migration itself will contain a class, and that class will have the methods up and down. Our up method will run when we run our migrations, and down is run when we rollback our migrations. Additionally, our class will contain a reference to Knex's schema object. This schema object is what we'll use to actually alter our database.

Our Schema

If you're following along with this series, we'll be building a simple project management application. Below is the underlying database structure we'll end up creating via migrations by the end of this lesson

Note that Status and Roles at the bottom will be enums that we'll be defining via TypeScript later on in this series. I just wanted to note them here so the entire structure made sense.

Creating Our Migrations

So, to start with, let's create our migration for our Users table.

$ node ace make:migration create_users_table --table users
# CREATE: database/migrations/TIMESTAMP_create_users_tables.ts

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Now, I like to name my migrations by what I'm doing within the migration. Since I'm doing this, I need to pass in the --table argument with my table name. You could alternatively just pass the table name as the migration name and Adonis will default the table to the migration name. For example, the below is perfectly valid.

$ node ace make:migration users
# CREATE: database/migrations/TIMESTAMP_users.ts

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Setting Up Our Migration

So, now that we have our first migration created, let's open it up and you should see something like the below.

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class Users extends BaseSchema {
  protected tableName = 'users'

  public async up() {
    this.schema.table(this.tableName, (table) => {
    })
  }

  public async down() {
    this.schema.table(this.tableName, (table) => {
    })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
database
migrations
TIMESTAMP_create_users_tables.ts

Note that if you created your migration using make:migration users, you'll start with the createTable and dropTable alterations we'll be making next.

We have our migration's class called Users with a protected variable in it that holds our table's name. We then have an up and a down method. Currently, both these methods are just grabbing a reference of our table using Knex's schema, however, we need to create our table with this migration. So, let's clear out both the up and down methods.

public async up() {
}

public async down() {
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Up

For the up method within our migration we're going to want to use Knex's createTable method to actually create our table.

public async up() {
  this.schema.createTable(this.tableName, (table) => {
  })
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

The second argument to createTable is a callback function, which is provided a table instance. We can use this table instance to define the schema for this table.

public async up() {
  this.schema.createTable(this.tableName, (table) => {
    table.increments('id').primary()
    table.string('username', 50).unique().notNullable()
    table.string('email', 255).unique().notNullable()
    table.string('password', 180).notNullable()
    table.string('remember_me_token').nullable()
    table.timestamps(true, true)
  })
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

So, the main takeaways here are that table provides us methods we can call that are column types. The first argument of these column type methods is the column name, generally. For strings, the second argument is the max-length. Then, chainable off our column type methods are additional methods we can use to better define that column.

• primary, marks the column as a primary key

• unique, ensures the column values are all unique.

• notNullable, ensures all records have a value for that column

• nullable, will allow null values for that column

• timestamps, will add a created_at and updated_at column to our table.

  • The first true ensures these columns are of type timestamp

  • The second true sets the default value as the current timestamp

Down

Since we're creating our table in our up method, we'll want to undo that creation in our down method. To do this, we can make use of Knex's dropTable method.

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

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

That's it! Now, if we were to run this migration and rollback this migration we would successfully create the table then delete the table. That is exactly the flow we're looking for with migrations.

Running

Now that we have our up and down defined, we're all clear to run our migration. So, head into your terminal and run:

$ node ace migration:run
# > migrated database/migrations/TIMESTAMP_create_users_tables

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

You can use a GUI, like TablesPlus, to confirm that the table was indeed created.

Relationships & Foreign Keys

Before we continue onward with our intermediary tables, I'd like to quickly cover how to define foreign key relationships. All we need to do is chain two methods off our column definition, references and inTable.

• inTable accepts the table our column relates to

• references accepts the column in the related table our column references

Just by adding these two chained methods, Knex will create our foreign keys for us. If you don't want foreign keys, for whatever reason, you can simply omit these two methods from your column definition.

So, for example, in our tasks table, we'll have a created_by column that references our users' table. Here's how we can define that relationship and create a foreign key for that column:

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

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

We need to make our created_by column unsigned here because our users table's id column is also unsigned and types must match for the foreign key to successfully create.

Filling Out The Rest

Now that we've got a migration under our belt, let's quickly run through the rest of our schema.

Tasks

Create the migration:

$ node ace make:migration create_tasks_table --table tasks
# CREATE: database/migrations/TIMESTAMP_create_tasks_tables.ts

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Define the schema:

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().notNullable().references('id').inTable('users');
      table.integer('assigned_to').unsigned().nullable().references('id').inTable('users');
      table.timestamps(true, true)
    })    
  }

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

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
database
migration
TIMESTAMP_create_tasks_tables.ts

Projects

Create the migration:

$ node ace make:migration create_projects_table --table projects
# CREATE: database/migrations/TIMESTAMP_create_projects_tables.ts

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Define the schema:

export default class Projects extends BaseSchema {
  protected tableName = 'projects'

  public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').primary()
      table.string('name').notNullable()
      table.text('description').nullable()
      table.integer('status_id').unsigned().notNullable().defaultTo(1)
      table.timestamps(true, true)
    })
  }

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

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
database
migrations
TIMSTAMP_create_projects_tables.ts

ProjectUsers

Create the migration:

$ node ace make:migration create_project_users_table --table project_users
# CREATE: database/migrations/TIMESTAMP_create_project_users_tables.ts

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Define the schema:

// database/migrations/TIMESTAMP_create_project_users_tables

export default class ProjectUsers extends BaseSchema {
  protected tableName = 'project_users'

  public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').primary()
      table.integer('project_id').unsigned().references('id').inTable('projects')
      table.integer('user_id').unsigned().references('id').inTable('users')
      table.integer('role_id').unsigned().notNullable().defaultTo(1)
      table.timestamps(true, true)
    })
  }

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

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

ProjectTasks

Create the migration:

$ node ace make:migration create_project_tasks_table --table project_tasks
# CREATE: database/migrations/TIMESTAMP_create_project_tasks_tables.ts

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Define the schema:

export default class ProjectTasks extends BaseSchema {
  protected tableName = 'project_tasks'

  public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').primary()
      table.integer('sort_order').unsigned().notNullable().defaultTo(0)
      table.integer('project_id').unsigned().notNullable().references('id').inTable('projects')
      table.integer('user_id').unsigned().notNullable().references('id').inTable('users')
      table.timestamps(true, true)
    })
  }

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

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  
database
migrations
TIMESTAMP_create_project_tasks_tables.ts

Migration Batches

Now that we have the remainder of our migrations defined, it's time to run them. Since we already ran our createuserstable migration, we should only see the four latest tables we created (unless you ran rollback).

$ node ace migration:run
# > migrated database/migrations/TIMESTAMP_create_tasks_tables
# > migrated database/migrations/TIMESTAMP_create_projects_tables
# > migrated database/migrations/TIMESTAMP_create_project_users_tables
# > migrated database/migrations/TIMESTAMP_create_project_tasks_tables

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Every time we call run on one or more migrations, we're creating what Adonis refers to as a batch. So, when we initially ran our createuserstable migration, we ran that as a member of batch 1. When we ran our migrations this second time, we had four migrations as members of batch 2.

When we rollback migrations, by default, Adonis will rollback a single batch. So at this point and time, if we were to rollback Adonis would call the down method on all four of our migrations that were members of batch 2.

Alternatively, we can define which batch we want to rollback to by running

$ node ace migration:rollback --batch 1

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Where 1 is the batch number you'd like to rollback to. Passing 0 in here will rollback all the way, clearing out your database.

Next Up

Next, we'll have a quick lesson covering how to alter our database using migrations. I wanted to include that within this lesson, but this lesson is quite long as-is. Then after that lesson, we'll move into learning about models.