Introduction to Database Migrations

This article will introduce you to the concept of migrations in AdonisJs.

In the previous articles, we learned 2 approaches to working with events in AdonisJs. In this sixth installment of the everything you need to know about adonisJs series, we will familiarize ourselves with the concept of migrations. The aim is to have a broader understanding of how our database state will look like.

Source code for this tutorial can be found here

I will link the resources at the end of the article.

Let's get started.

Our Database Structure

Just to remind you how our database should look like, we'll reference this diagram.

Defining Model Relationships

Database migrations and modeling can sometimes be a pain point for many devs to fully understand. It's important to plan your database in the most optimal way that meets business requirements. Therefore, before we start coding, it's crucial to understand know just how our models will be related to each other. I'll attempt to explain this from a high-level view, then head over to the implementation.

We'll use the diagram above as a guide to modeling our database.

• A user can own many stores and a store can only be owned by one user, hence this would be a many-to-one relationship.
• A store can be followed by many users and a user can follow many stores. So this would be represented by a many-to-many relationship.
• An item(product) can only belong to a single store, but a store can have many items, hence this is a one-to-many relationship.
• An item can only be uploaded by one user, and a user can upload many items. This will also be represented as a one-to-many relationship.

many-to-one and one-to-many are the same things.

Migrations

Migration files allow us to create schema definitions using code. They are a way to alter a database programmatically. Adonis allows us to do so in a typescript-safe environment.
In most cases, an application will have a migration file for each table it needs. Later on, in the application's lifespan, you may have migrations for any alteration(s) you need to perform in a given table.

The flow of migrations

Migrations have two directions, up and down. We run migrations to go up when we have changes we'd like to persist in our database and down when we want to roll back (undoing changes) when the changes were done.

This flow is good for several reasons:

1. We can easily roll back changes that aren't in production to make alterations as needed, during development.
2. We can maintain a history of the changes made to our application database over time.
3. Alterations made to our migrations are enforced at the database level, hence when we deploy to production, the changes are always synced with our database state. This means that the database is always updated with the latest changes.

It's worth noting that rolling back migrations in production should be done with caution. This is because it poses the risk of losing data. Always have a backup of the database if you wish to roll back in production.

Migration commands and setting up our first migration file

The Adonis CLI ships with several commands that we can run in regards to migrations

• make:migration - allows us to create a migration
• migration:run - allows us to run all migrations that haven't been previously run, i.e, sync them with the database
• migration:rollback - This allows us to roll back previously run migrations. (Return to a previous state)
• migration:status - allows us to view the run status on our migrations, i.e, whether the migrations altered the database state.

Migration folders are contained in the database/migrations directory.

Setting up our store Migration

For this tutorial, I'll set up the store migration. All other migration folders will follow the same flow. You can recreate them following similar steps.
If you run into any errors, feel free to check out the source code or leave a comment.

We'll run the make migration command to set up our store schema.

  node ace make:migration stores

This will create a migration file in the migrations directory. Paste the missing values in the folder.

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

export default class Stores extends BaseSchema {
  protected tableName = 'stores'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').primary()
      table.string('name', 255).notNullable()
      table.integer('owner').references('users.id').onDelete('CASCADE')
      table.string('description').notNullable()
      table.string('cover_photo').nullable()
      table.string('slug').notNullable().unique()
      table.string('email', 255).notNullable()
      table.string('phone_number', 30)
      table.boolean('is_active').defaultTo(true)
      /**
       * Uses timestamptz for PostgreSQL and DATETIME2 for MSSQL
       */
      table.timestamp('created_at', { useTz: true })
      table.timestamp('updated_at', { useTz: true })
    })
  }

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

As you will notice, the migration filename is prefixed with some numeric value. We add the current timestamp to the filename so that the migration files are sorted in the order created.
By default, migration includes an id and timestamps column.
The rest of the fields are defined by the developer. If you look at the migration folder above, you can see that it's self-descriptive,

Up

The up method defines the record initialization logic. The first line instructs our migration file to create a table called stores within our database. The following are then methods that enable us to define the column types or attributes of our records.

• table -> means we're initializing a database column
• table.string('description') -> the column is to be of type string with a name, 'description`. There are other column types, including; boolean, enum, integer, and so on.
• notNullable/nullable -> defines whether the field is allowed to have NULL values.
• primary -> defines the column as the primary key of that table
• unique -> ensures that the value of the database columns is all unique. We will implement this functionality in a later article.

Down

The down method is used to roll back the actions executed by the up method. For example, if the up method creates a table, the down method should drop the same table.

`tsx

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

`

Relationships and foreign keys

As you may have noticed from our migration and the database schema diagram, our stores will be owned by a user. So how do we go about this in a migration file?

We can use a couple of approaches.

One is exactly as I've defined in my migration file.

`tsx

table.integer('owner').references('users.id').onDelete('CASCADE')

`

Here we are telling adonis that the column named, "owner" is an integer that references the user's table, specifically the id column, which we know is an integer. the onDelete() method enforces certain rules for when a user is deleted and they have an existing store. In our case, we go with CASCADE, meaning that when a user is deleted, then recursively delete all their stores. Other options include; PROTECT and SET_NULL.

Another approach would be;

`tsx

table.integer('owner').index()
table.foreign('owner').references('users.id').onDelete('CASCADE')

`

or

`tsx

table.integer('owner').references('id').inTable('users')

`

As you can tell, there's more than one to achieve an objective in Adonis. The approach you choose is entirely up to you.

For many-to-many relationships, we'll look into it once we start working with models in another article.

Running and Rolling Back migrations

Now that we have our up and down methods defined, we can go ahead and run our migrations by running the command on our terminal.

node ace migration:run

This command executes the up method in all migration files.

SQL statements for every migration file are wrapped inside a transaction. So if one statement fails, all other statements within the same file will rollback.

Also, in case of failure, the subsequent migrations will be aborted. However, the migrations before the failed migration stay in the completed state.

If the migration was successful, the command line GUI should not throw any error.

That's it! We have other migration operations which I will not cover in-depth as they have been comprehensively covered in the official docs.

Resources

Some of the references that I used to cover this article were acquired from the following sources.

1. The official AdonisJS documentation on schema migrations.
2. This awesome article from Jagr

Sponsors

*Disclosure: I only recommend products I would use myself and all recommendations here are my own. This post may contain affiliate links that at no additional cost to you, I may earn a small commission.

• Scraper API is a startup specializing in strategies that'll ease the worry of your IP address from being blocked while web scraping. They utilize IP rotation so you can avoid detection. Boasting over 20 million IP addresses and unlimited bandwidth. Using Scraper API and a tool like 2captcha will give you an edge over other developers. The two can be used together to automate processes. Sign up on Scraper API and use this link to get a 10% discount on your first purchase.

• Do you need a place to host your website or app, Digital ocean
  is just the solution you need, sign up on digital ocean using this link and experience the best cloud service provider.

• The journey to becoming a developer can be long and tormentous, luckily Pluralsight makes it easier to learn. They offer a wide range of courses, with top quality trainers, whom I can personally vouch for. Sign up using this link and get a 50% discount on your first course.

I hope you've had a better understanding of database migrations.
If you have any questions on this topic, feel free to leave a comment or get in touch directly on twitter