Let's Learn AdonisJS 7: Database Migrations

Database Migrations

In This Lesson

Learn database migrations in AdonisJS and how they can be used to scaffold and build upon our application's database. Track, share, and rollback database changes with version control for your schema.

Created by: @tomgobich
Published: 6 months ago

Migrations are like version control for our database. They allow us to track, share, and rollback changes to our database structure, like adding a table or changing a column type. They're great for many reasons, but two standout reasons are.

They allow teammates to easily onboard into the project and/or keep their development database up-to-date.
They allow code changes to go out in unison with database changes in our production environment.

Migration Basics

AdonisJS migrations can be found within the database/migrations directory, and if we look there, we can see our starter kit has given us one to start with. You might note its filename:

database/migrations/1758943358073_create_users_table.ts

The number at the beginning of the filename, 1758943358073, is a timestamp of when the migration was created. This is used to naturally sort our migrations, both in our migrations folder and in the order they'll run, giving us a consistent and reliable order for them.

After the timestamp, is the action of the migration, create_users_table, noting that this migration creates the users table within our database. This is purely for informative reasons to help us find specific migrations in our file tree; these may be as simple as the table's name.

Migrations are comprised of a class extending a BaseSchema from Lucid, AdonisJS's Object Relational Mapping (ORM). ORMs are like a translator, taking an object-like representation we'll use in TypeScript and translating it to the relational tables in our database. This gives us simplified syntax for things like querying data or even building tables, like we'll do with migrations.

import { BaseSchema } from '@adonisjs/lucid/schema'

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

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').notNullable()
      table.string('full_name').nullable()
      table.string('email', 254).notNullable().unique()
      table.string('password').notNullable()

      table.timestamp('created_at').notNullable()
      table.timestamp('updated_at').nullable()
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}
  
    
      Copied!
      
        
      
    
  
  
database
migrations
xxxxx_create_users_table.ts

Within the migration class are two methods, up and down. The up method is forward progress. This is where we'll create new tables, constraints, or any other changes to our database we want to apply. The down method allows us to go backward, or undo anything we've done in the up method.

Whatever we do in up we should undo in down. For example, in the users migration above, the up method creates the users table within our database, while the down method drops the users table.

This flow is primarily to aid in development, allowing incremental progress or change without having to create tons of migrations for a single feature. The down method is destructive and shouldn't be run in production, and AdonisJS will require confirmation in order to do so.

Running Migrations

When a migration is run, the up method will be executed. We can easily run migrations using the Ace CLI's migration:run command.

node ace migration:run
# [ error ] Cannot open database because the directory does not exist
  
    
      Copied!

When we first try to run our migration, we may see an error that the directory does not exist. By default, AdonisJS will use SQLite as its database. This is a file-based database that will be housed within our project's temporary directory (tmp). If you get this error, it's because that directory does not exist, so let's create it real quick!

mkdir tmp
  
    
      Copied!

Then, let's try our migration once more.

node ace migration:run
# "sqlite" (112 μs) SELECT `version` FROM `adonis_schema_versions` LIMIT ? [ 1 ]
# "sqlite" (627 μs) INSERT INTO `adonis_schema_versions` (`version`) VALUES (?) [ 1 ]
# [ info ] Upgrading migrations version from "1" to "2"
# "sqlite" (56 μs) SELECT `name`, `batch`, `migration_time`, `id` FROM `adonis_schema` WHERE `batch` > ? ORDER BY `id` DESC [ 0 ]
# "sqlite" (219 μs) UPDATE `adonis_schema_versions` SET `version` = ? WHERE `version` = ? [ 2, 1 ]
# "sqlite" (113 μs) SELECT MAX(`batch`) AS `batch` FROM `adonis_schema` []
# "sqlite" (44 μs) SELECT `name` FROM `adonis_schema` []
# ❯ migrating database/migrations/1758943358073_create_users_table
# (471 μs) CREATE TABLE `users` (`id` INTEGER NOT NULL PRIMARY KEY autoincrement, `full_name` VARCHAR(255) NULL, `email` VARCHAR(254) NOT NULL, `password` VARCHAR(255) NOT NULL, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NULL)
# ❯ migrated database/migrations/1758943358073_create_users_table
# 
# Migrated in 53 ms
# "sqlite" (127 μs) SELECT `name` AS `table_name` FROM `sqlite_master` WHERE `type` = ? AND NOT `name` LIKE ? ORDER BY `name` ASC [ 'table', 'sqlite_%' ]
# ❯ Creating schema classes for scanned tables
# [ success ] Schema classes generated (4.63 ms)
  
    
      Copied!

A lot of information is printed out, detailing every SQL command the migration executed on our behalf. You can see that it first checks the adonis_schema_versions table. This is a table used to track Lucid's schema versioning; we don't need to worry about it.

"sqlite" (112 μs) SELECT `version` FROM `adonis_schema_versions` LIMIT ? [ 1 ]
"sqlite" (627 μs) INSERT INTO `adonis_schema_versions` (`version`) VALUES (?) [ 1 ]
[ info ] Upgrading migrations version from "1" to "2"
"sqlite" (219 μs) UPDATE `adonis_schema_versions` SET `version` = ? WHERE `version` = ? [ 2, 1 ]
  
    
      Copied!

Next, it checks for the last batch that was run. When we run our migrations, Lucid will run any migration that hasn't yet run. All migrations processed in a single run are considered a batch. It keeps track of all this information in a table called adonis_schema.

"sqlite" (56 μs) SELECT `name`, `batch`, `migration_time`, `id` FROM `adonis_schema` WHERE `batch` > ? ORDER BY `id` DESC [ 0 ]
"sqlite" (113 μs) SELECT MAX(`batch`) AS `batch` FROM `adonis_schema` []
"sqlite" (44 μs) SELECT `name` FROM `adonis_schema` []
  
    
      Copied!

Using that info, it determines which migrations within our database/migrations directory need to run, skipping those that have already run.

Small note, the question marks and square brackets in these queries are SQL Parameterization. A precautionary step that protects our database against SQL Injection Attacks. For example, it prevents someone from being able to drop a table in our database via an input shown on our web page. The hard brackets represent an array, and it works left to right to fill in each question mark placeholder within the query.

-- so a parameterized query, like this:
UPDATE `adonis_schema_versions` SET `version` = ? WHERE `version` = ? [ 2, 1 ]
 will get run by SQL as this:
UPDATE `adonis_schema_versions` SET `version` = 2 WHERE `version` = 1
  
    
      Copied!

Next, it runs the up method from our migration.

❯ migrated database/migrations/1758943358073_create_users_table
 
Migrated in 53 ms
  
    
      Copied!

Finally, it generates schema classes from our database, we'll discuss these more in the next lesson.

"sqlite" (127 μs) SELECT `name` AS `table_name` FROM `sqlite_master` WHERE `type` = ? AND NOT `name` LIKE ? ORDER BY `name` ASC [ 'table', 'sqlite_%' ]
❯ Creating schema classes for scanned tables
[ success ] Schema classes generated (4.63 ms)
  
    
      Copied!

At this point, our database now contains an empty users table!

By the way, we'll be using these queries that are printed into our terminal throughout this module to aid in our understanding of what's happening. If, however, at any point you want to turn these off, you can easily do this within your database config.

import app from '@adonisjs/core/services/app'
import { defineConfig } from '@adonisjs/lucid'

const dbConfig = defineConfig({
  // ...

  connections: {
    sqlite: {
      // ...

      /**
       * Enable SQL query debugging in development mode.
       */
      debug: app.inDev,
    },
  },
})

export default dbConfig
  
    
      Copied!
      
        
      
    
  
  
config
database.ts

Rolling Back

When a migration is rolled back, the down method is run that should undo whatever was done in the migration's up method. Again, there is an Ace CLI command for this called migration:rollback.

node ace migration:rollback
# "sqlite" (119 μs) SELECT `version` FROM `adonis_schema_versions` LIMIT ? [ 1 ]
# "sqlite" (177 μs) SELECT MAX(`batch`) AS `batch` FROM `adonis_schema` []
# "sqlite" (84 μs) SELECT `name`, `batch`, `migration_time`, `id` FROM `adonis_schema` WHERE `batch` > ? ORDER BY `id` DESC [ 0 ]
# ❯ reverting database/migrations/1758943358073_create_users_table
# (905 μs) DROP TABLE `users`
# ❯ reverted database/migrations/1758943358073_create_users_table
# 
# Reverted in 27 ms
# "sqlite" (148 μs) SELECT `name` AS `table_name` FROM `sqlite_master` WHERE `type` = ? AND NOT `name` LIKE ? ORDER BY `name` ASC [ 'table', 'sqlite_%' ]
# ❯ Creating schema classes for scanned tables
# [ success ] Schema classes generated (2.43 ms)
  
    
      Copied!

Rollbacks occur one batch at a time, unless otherwise specified, so as we can see, it's querying for the max batch number in our adonis_schema table to then get all migrations in that batch.

"sqlite" (177 μs) SELECT MAX(`batch`) AS `batch` FROM `adonis_schema` []
"sqlite" (84 μs) SELECT `name`, `batch`, `migration_time`, `id` FROM `adonis_schema` WHERE `batch` > ? ORDER BY `id` DESC [ 0 ]
  
    
      Copied!

It'll then revert those migrations, running their down method.

❯ reverting database/migrations/1758943358073_create_users_table
(905 μs) DROP TABLE `users`
❯ reverted database/migrations/1758943358073_create_users_table
 
Reverted in 27 ms
  
    
      Copied!

Finally, again regenerating schemas from our updated database structure.

"sqlite" (148 μs) SELECT `name` AS `table_name` FROM `sqlite_master` WHERE `type` = ? AND NOT `name` LIKE ? ORDER BY `name` ASC [ 'table', 'sqlite_%' ]
❯ Creating schema classes for scanned tables
[ success ] Schema classes generated (2.43 ms)
  
    
      Copied!

At this point, we no longer have a users table within our database.

Before we move on, there are a few other Ace CLI commands to note here.

migration:fresh will drop all tables and rerun all our migrations, essentially giving us a clean data slate.
migration:refresh will rollback and remigrate, again, this will do a single batch unless otherwise specified.
migration:reset will roll all the way back, undoing all migrations.
migration:status will read our adonis_schema table to print out the status of all our migrations, showing which have run and which are pending.

Creating Migrations

We'll need more than just a users table and a migration should only consist of creations or alterations for a single table. If you need to make multiple tables or changes to multiple tables, we should create a new migration for each table we need to touch.

Like everything else, we can easily do this via the Ace CLI's make:migration command.

node ace make:migration role
# DONE:    create database/migrations/1770077349836_create_roles_table.ts
  
    
      Copied!

Notice how AdonisJS automatically normalized our table name to plural and gave us a starting create table structure with an auto-incrementing id in our up method and a dropTable statement in our down. Also note, convention states we should define our column names in snake case, meaning all lowercase using underscores to separate words.

import { BaseSchema } from '@adonisjs/lucid/schema'

export default class extends BaseSchema {
  protected tableName = 'roles'

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')

      table.timestamp('created_at')
      table.timestamp('updated_at')
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}
  
    
      Copied!
      
        
      
    
  
  
database
migrations
xxxxx_create_roles_table.ts

Okay, let's add a name column to our roles table!

import { BaseSchema } from '@adonisjs/lucid/schema'

export default class extends BaseSchema {
  protected tableName = 'roles'

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.string('name').notNullable()

      table.timestamp('created_at')
      table.timestamp('updated_at')
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}
  
    
      Copied!
      
        
      
    
  
  
database
migrations
xxxxx_create_roles_table.ts

We start with table and a method to describe the column's type, like string. Inside the type method goes the column's name. Off the type method, we can chain additional alterations for the column. By default, columns will be defined as nullable, so we can make this not null by chaining the notNullable() method on it.

Next, we need to be able to assign a user a role. So, let's add a role_id column to our create_users_table migration.

import { BaseSchema } from '@adonisjs/lucid/schema'

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

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id').notNullable()
      table.integer('role_id').unsigned().references('id').inTable('roles').notNullable().defaultTo(1)
      table.string('full_name').nullable()
      table.string('email', 254).notNullable().unique()
      table.string('password').notNullable()

      table.timestamp('created_at').notNullable()
      table.timestamp('updated_at').nullable()
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}
  
    
      Copied!
      
        
      
    
  
  
database
migrations
xxxxx_create_users_table.ts

The column name role_id also comes from convention. A singular version of the referencing table (role), followed by that table's primary key (id), and joined together with an underscore.

The unsigned() method notes our integer type won't be negative. Then, references('id').inTable('roles') defines a foreign key constraint for role_id ensuring that any value inserted into it needs to exist within our roles.id. This can also be written as just references('roles.id'). Finally, we'll default the value to 1, which will eventually be our "user" role. If we create a user without a role_id specified, 1 will be used as the default.

Migration Order

Now, in most databases, the orderin which we create our migrations does matter. For our user to have a role_id on its table, for example, the roles table must be created first. Otherwise, we won't be able to add a foreign key constraint to ensure the user isn't being assigned a role that doesn't exist. SQLite has a lazy foreign key, though, so this won't be the case. We will, however, treat it as such as good practice to do because if you switch to PostgreSQL or MySQL, it will be an issue.

We can easily fix this by adjusting the timestamp prefix manually to place our roles before users or vice versa. So, instead of:

database/
└── migrations/
    ├── 1758943358073_create_users_table.ts
    └── 1770077349836_create_roles_table

We can instead do:

database/
└── migrations/
    ├── 1770077349836_create_roles_table.ts
    └── 1770077349837_create_users_table.ts

Noting that we've just taken our create_roles_table timestamp, incremented it by one and applied it as our create_users_table timestamp to change the natural sort.

Filling In Our Database

Great, so let's go ahead and run through a few more migrations to fill out the complete schema structure of our database, starting with a profile for our user.

node ace make:migration profiles
# DONE:    create database/migrations/1770079110036_create_profiles_table.ts
  
    
      Copied!

Within this migration, we'll want each row to be bound to a user, as this will be a one-to-one relationship. One user has one profile. Then, we'll also let them have a brief bio, giving this column a max size of 255 characters. Finally, adding a boolean to determine whether their profile is public or not. We can also default this to true.

import { BaseSchema } from '@adonisjs/lucid/schema'

export default class extends BaseSchema {
  protected tableName = 'profiles'

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.integer('user_id').unsigned().references('users.id').notNullable()
      table.string('bio', 255)
      table.boolean('is_public').defaultTo(true)

      table.timestamp('created_at')
      table.timestamp('updated_at')
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}
  
    
      Copied!
      
        
      
    
  
  
database
migrations
1770079110036_create_profiles_table.ts

Next, we'll have a challenges table.

node ace make:migration challenges
# DONE:    create database/migrations/1770079110048_create_challenges_table.ts
  
    
      Copied!

Inside of which, we'll note which user created the challenge, have text noting what the challenge is, and points noting a point value, the challenge is worth. This will be a many-to-one relationship. A user can have many challenge,s but a challenge can only be created by one user, which we'll use creator_id to track.

import { BaseSchema } from '@adonisjs/lucid/schema'

export default class extends BaseSchema {
  protected tableName = 'challenges'

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.integer('creator_id').unsigned().references('users.id').notNullable()
      table.string('text', 255).notNullable()
      table.integer('points').notNullable()

      table.timestamp('created_at')
      table.timestamp('updated_at')
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}
  
    
      Copied!
      
        
      
    
  
  
database
migrations
1770079110048_create_challenges_table.ts

Finally, we'll let users join a challenge via a challenge_user pivot table. Pivot tables allow us to join together many-to-many relationships. Users can join many challenges, and challenges have many users that've joined.

node ace make:migration challenge_user
# DONE:    create database/migrations/1770079119171_create_challenge_users_table.ts
  
    
      Copied!

Inside here, we'll first want to change the table name from plural to singular. The default naming convention Lucid will look for with pivot tables is a sorted concatenation of the two related entities in singular form. So, challenge comes before user when sorted, meaning the default would want challenge_user.

In addition to that, we want one column per joined entity along with a foreign key constraint. Finally, we can track when and if a user has completed the challenge in one go using a nullable timestamp. When null, the user hasn't completed the challenge, and when not null, then the user has completed the challenge, and we know when they've done so.

import { BaseSchema } from '@adonisjs/lucid/schema'

export default class extends BaseSchema {
  protected tableName = 'challenge_user'

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.integer('challenge_id').unsigned().references('challenges.id').notNullable()
      table.integer('user_id').unsigned().references('users.id').notNullable()

      table.timestamp('completed_at')
      table.timestamp('created_at')
      table.timestamp('updated_at')
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}
  
    
      Copied!
      
        
      
    
  
  
database
migrations
1770079119171_create_challenge_users_table.ts

Great, let's run our migrations!

node ace migration:run
  
    
      Copied!

Inspecting SQLite

You can either take my word for it that our tables are in our database, or we can see it for ourselves! Let's install a new extension in VS Code called SQLite Viewer. This will allow us to click on the SQLite database in our project to see its contents.

As mentioned earlier, our SQLite database is stored within the tmp directory, so let's open that up and give it a click, and we should now see a list of our tables.

If we make changes to our migrations, our database won't automatically be updated to match. Instead, we'd need to rollback and rerun the migration to apply whatever changes we've made. Once a migration has hit production, the flow changes. We should instead add a new migration to make whatever change we need because rolling back migrations in production is a destructive action and can lead to data loss.

Join the Discussion 0 comments

Be the first to comment!

Playing Next Lesson In

Let's Learn AdonisJS 7 #3.0

Database Migrations

Migration Basics

Running Migrations

Rolling Back

Creating Migrations

Migration Order

Filling In Our Database

Inspecting SQLite

Join the Discussion 0 comments

Project Foundations

Fundamentals

Database

Authentication