Database Migrations

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.

Out of the box, Formidable provides a simple database migration system that allows you to define and share your database schema definition. This is a great way to ensure that your database schema is always in sync with your application code.

Formidable Database Migrations are stored in the /database/migrations directory and are powered by Knex.js.

Creating a Migration

To create a new migration, run the following command:

node craftsman make:migration CreatePostsTable --table=posts

The command above will create a new migration file under /database/migrations and will generate the following migration:

exports.up = (knex) => {
    return knex.schema.createTable('posts', (table) => {

    });
};

exports.down = (knex) => knex.schema.dropTable('posts');

We can now define our migration's schema in the up function:

exports.up = (knex) => {
    return knex.schema.createTable('posts', (table) => {
        table.increments('id').primary();
        table.foreign('user_id').references('id').inTable('users').onDelete('cascade');
        table.string('title');
        table.text('body');
        table.timestamps();
    });
};

After defining our schema, we can now run the migration:

node craftsman migrate:latest

This will add a mew table to your database.

Migration Modifications

Formidable allows you to modify existing tables by creating new migrations that modify the existing tables.

Adding A Column

Here is an example of a migration that adds a new column to an existing table:

node craftsman make:migration AddSoftDeletesToPostsTable --table=posts --alter

/**
 * Add a softDeletes (delete_at) column to the posts table.
 */
exports.up = (knex) => {
    return knex.schema.table('posts', (table) => {
        table.timestamp('deleted_at').nullable();
    });
};

/**
 * Remove the softDeletes (deleted_at) column from the posts table.
 */
exports.down = (knex) => knex.schema.dropColumn('deleted_at');

This migration will add a new column to the posts table when migrate up is ran, and will remove the column when migrate down is ran.

Removing A Column

Here is an example of a migration that removes a column from an existing table:

/**
 * Remove the softDeletes (deleted_at) column from the posts table.
 */
exports.up = (knex) => {
    return knex.schema.table('posts', (table) => {
        table.dropColumn('deleted_at');
    });
};

/**
 * Add a softDeletes (delete_at) column to the posts table.
 */
exports.down = (knex) => {
    return knex.schema.table('posts', (table) => {
        table.timestamp('deleted_at').nullable();
    });
};

Renaming A Column

Here is an example of a migration that renames a column in an existing table:

/**
 * Rename the deleted_at column to delete_when in the posts table.
 */
exports.up = (knex) => knex.schema.table('posts').renameColumn('deleted_at', 'deleted_when');

/**
 * Rename the deleted_when column from posts table back to deleted_at.
 */
exports.down = (knex) => knex.schema.table('posts').renameColumn('deleted_when', 'deleted_at');

Changing A Column

Here is an example of a migration that changes a column in an existing table:

/**
 * Change the type of the deleted_at column from timestamp to boolean.
 */
exports.up = (knex) => {
    return knex.schema.table('posts', (table) => {
        table.boolean('deleted_at').alter();
    });
};

/**
 * Change the type of the deleted_at column from boolean to timestamp and make it nullable.
 */
exports.down = (knex) => {
    return knex.schema.table('posts', (table) => {
        table.timestamp('deleted_at').nullable().alter();
    });
};

For more information, visit the Knex.js documentations.