Database: Migrations

See how to create and run database migrations in Athenna Framework.

Introduction

Migrations are like version control for your database, allowing your team to define and share the application's database schema definition. If you have ever had to tell a teammate to manually add a column to their local database schema after pulling in your changes from source control, you've faced the problem that database migrations solve.

Generating migrations

You may use the make:migration Artisan command to generate a database migration. The new migration will be placed in your

Path.migrations()

directory. Each migration filename contains a timestamp that allows Athenna to determine the order of the migrations:

node artisan make:migration FlightsMigration

tip

Migrations templates may be customized using the template customization command.

Migration structure

A migration class contains two methods: up() and down(). The up() method is used to add new tables, columns, or indexes to your database, while the down() method should reverse the operations performed by the up() method.

Within both of these methods, you may use the knex schema builder to expressively create and modify tables. For example, the following migration creates a flights table:

import { BaseMigration, type DatabaseImpl } from '@athenna/database'

export class FlightsMigration extends BaseMigration {
  public tableName = 'flights'

  public async up(db: DatabaseImpl) {
    return db.createTable(this.tableName, (table) => {
      table.increments('id')
      table.string('name')
      table.string('airline')
      table.timestamps(true, true, true)
    })
  }

  public async down(db: DatabaseImpl) {
    return db.dropTable(this.tableName)
  }
}

Modifying existing tables

Your application will grow, and sooner or later you will need to change a table that already exists. For that, create a new migration and use the alterTable() method. It works exactly like createTable(), but for an existing table:

import { BaseMigration, type DatabaseImpl } from '@athenna/database'

export class AddGateToFlightsMigration extends BaseMigration {
  public tableName = 'flights'

  public async up(db: DatabaseImpl) {
    return db.alterTable(this.tableName, (table) => {
      table.string('gate').nullable()
    })
  }

  public async down(db: DatabaseImpl) {
    return db.alterTable(this.tableName, (table) => {
      table.dropColumn('gate')
    })
  }
}

Setting the migration connection

If your migration will be interacting with a database connection other than your application's default database connection, you should set the static getter connection in your migration:

import { BaseMigration, type DatabaseImpl } from '@athenna/database'

export class FlightsMigration extends BaseMigration { 
  public static connection() {
    return 'postgres'
  }

  public async up(db: DatabaseImpl) {
    // ...
  }

  public async down(db: DatabaseImpl) {
    // ...
  }
}

Running migrations

To run all of your outstanding migrations, execute the migration:run Artisan command:

node artisan migration:run

You can use the --connection option to run migrations for a specific connection:

node artisan migration:run --connection=postgres

warning

If postgres is your default connection then all the migrations using the default value in the static connection() method will run too.

Reverting

To revert all your migrations, you may use the migration:revert Artisan command. This command will revert all your migrations:

node artisan migration:revert

You can use the --connection option to revert migrations for a specific connection:

node artisan migration:revert --connection=postgres

warning

If postgres is your default connection than all the migrations using the default value in the static connection() method will be reverted too.

Wiping and refreshing the database

While developing, it's very common to want to start from scratch. The db:wipe command reverts all your migrations and drops the migrations table, leaving your database clean:

node artisan db:wipe

The db:fresh command goes one step further: it wipes your database and runs all your migrations again. If you also want to populate it with your seeders, add the --with-seeders option:

node artisan db:fresh

node artisan db:fresh --with-seeders

Both commands also accept the --connection option:

node artisan db:fresh --connection=postgres

caution

These commands delete all the data of your database. Never run them against your production database.

note

When using the mongo driver, db:wipe drops all the collections of your database and db:fresh doesn't run migrations, since Mongo doesn't use them.

The migrations table

Athenna keeps track of which migrations have already run in a table called migrations. If you prefer a different name, set the migrations.tableName option in your connection configuration inside

Path.config('database.ts')

export default {
  connections: {
    postgres: {
      driver: 'postgres',
      // ...
      migrations: {
        tableName: 'schema_migrations'
      }
    }
  }
}

Migrations are registered in this table without the file extension. This way, running your migrations from your TypeScript source code or from your compiled JavaScript code will never register the same migration twice.

info

Older versions of @athenna/database used to register migrations with their extension (.ts or .js). Don't worry, Athenna fixes these old records automatically before running or reverting your migrations, removing duplicated ones when needed.

If you still need to run an older version of @athenna/database against the same database, you can turn this behavior off by setting migrations.normalizeNames to false in your connection configuration.

Introduction​

Generating migrations​

Migration structure​

Modifying existing tables​

Setting the migration connection​

Running migrations​

Reverting​

Wiping and refreshing the database​

The migrations table​