Skip to main content

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:

const { Database } = require('@formidablejs/framework');

/** @param {Database} DB */
exports.up = (DB) => {
    return DB.schema.createTable('posts', (table) => {

    });
};

/** @param {Database} DB */
exports.down = (DB) => DB.schema.dropTable('posts');

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

exports.up = (DB) => {
    return DB.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 new 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
const { Database } = require('@formidablejs/framework');

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

/**
 * Remove the softDeletes (deleted_at) column from the posts table.
 *
 * @param {Database} DB
 */
exports.down = (DB) => {
    return DB.schema.table('posts', (table) => {
        table.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:

const { Database } = require('@formidablejs/framework');

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

/**
 * Add a softDeletes (delete_at) column to the posts table.
 *
 * @param {Database} DB
 */
exports.down = (DB) => {
    return DB.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:

const { Database } = require('@formidablejs/framework');

/**
 * Rename the deleted_at column to delete_when in the posts table.
 *
 * @param {Database} DB
 */
exports.up = (DB) => DB.schema.table('posts').renameColumn('deleted_at', 'deleted_when');

/**
 * Rename the deleted_when column from posts table back to deleted_at.
 *
 * @param {Database} DB
 */
exports.down = (DB) => DB.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:

const { Database } = require('@formidablejs/framework');

/**
 * Change the type of the deleted_at column from timestamp to boolean.
 *
 * @param {Database} DB
 */
exports.up = (DB) => {
    return DB.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.
 *
 * @param {Database} DB
 */
exports.down = (DB) => {
    return DB.schema.table('posts', (table) => {
        table.timestamp('deleted_at').nullable().alter();
    });
};

Running Migrations​

To run all of your outstanding migrations, execute the migrate:latest Craftsman command:

node craftsman migrate:latest

To run the next outstanding migration, execute the migrate:up Craftsman command:

node craftsman migrate:up

To run a specific migration, execute the migrate:up Craftsman command with the -m option:

node craftsman migrate:up -m 20210820161410_create_posts_table

Rolling Back Migrations​

To roll back all of your migrations, execute the migrate:rollback Craftsman command:

node craftsman migrate:rollback

To roll back a specific migration, execute the migrate:down Craftsman command with the -m option:

node craftsman migrate:down -m 20210820161410_create_posts_table

Schema Builder​

The Formidable Framework uses Knex.js to build database schemas.

Let's take a look at an example migration:

const { Database } = require('@formidablejs/framework');

/**
 * Create the posts table.
 *
 * @param {Database} DB
 */
exports.up = (DB) => {
    return DB.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();
    });
};

/**
 * Drop the posts table.
 *
 * @param {Database} DB
 */
exports.down = (DB) => DB.schema.dropTable('posts');

The up method receives a Database instance as its first argument. This instance provides a variety of methods that may be used to define the schema for the table. The down method receives the same Database instance, allowing you to reverse the operations performed by the up method.

Creating Tables​

To create a new database table, use the createTable method on the Database instance you receive in your migration:

exports.up = (DB) => {
    return DB.schema.createTable('users', (table) => {
        table.increments('id').primary();
        table.string('email').unique();
        table.string('password');
        table.timestamps();
    });
};

The createTable method accepts two arguments: the first is the name of the table, while the second is a callback which receives a TableBuilder instance that may be used to define the new table's columns.

Column Types​

The TableBuilder class contains a variety of column types that you may use when building your tables:

exports.up = (DB) => {
    return DB.schema.createTable('users', (table) => {
        table.increments('id').primary();
        table.string('email').unique();
        table.string('password');
        table.timestamps();
    });
};

Schema Building​

dropColumn​

Drops a column, specified by the column's name

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.dropColumn('deleted_at');
    });
};
dropColumns​

Drops multiple columns, specified by an array of column names

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.dropColumns(['deleted_at', 'deleted_by']);
    });
};
renameColumn​

Renames a column from one name to another

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.renameColumn('deleted_at', 'deleted_when');
    });
};
increments​

Adds an auto incrementing column. This is the same as integer with autoIncrement set to true.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.increments('id').primary();
    });
};
integer​

Adds an integer column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.integer('age');
    });
};
bigInteger​

Adds a big integer column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.bigInteger('views');
    });
};
tinyint​

Adds a tiny integer column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.tinyint('views');
    });
};
smallint​

Adds a small integer column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.smallint('views');
    });
};
mediumint​

Adds a medium integer column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.mediumint('views');
    });
};
bigint​

Adds a big integer column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.bigint('views');
    });
};
text​

Adds a text column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.text('description');
    });
};
string​

Adds a string column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.string('email');
    });
};
float​

Adds a float column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.float('amount');
    });
};
double​

Adds a double column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.double('amount');
    });
};
decimal​

Adds a decimal column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.decimal('amount');
    });
};
boolean​

Adds a boolean column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.boolean('confirmed');
    });
};
date​

Adds a date column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.date('created_at');
    });
};
datetime​

Adds a datetime column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.datetime('created_at');
    });
};
time​

Adds a time column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.time('sunrise');
    });
};
timestamp​

Adds a timestamp column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.timestamp('created_at');
    });
};
timestamps​

Adds created_at and updated_at columns.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.timestamps();
    });
};
dropTimestamps​

Drops created_at and updated_at columns.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.dropTimestamps();
    });
};
binary​

Adds a binary column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.binary('photo');
    });
};
enum​

Adds an enum column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.enum('role', ['admin', 'user']);
    });
};
json​

Adds a json column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.json('options');
    });
};
jsonb​

Adds a jsonb column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.jsonb('options');
    });
};
uuid​

Adds a uuid column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.uuid('id').defaultTo(DB.fn.uuid());
    });
};
comment​

Adds a comment to the column.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.string('email').comment('The user\'s email address');
    });
};
engine​

Sets the table's storage engine.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.engine('InnoDB');
    });
};
charset​

Sets the table's character set.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.charset('utf8mb4');
    });
};
collate​

Sets the table's collation.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.collate('utf8mb4_unicode_ci');
    });
};
inherits​

Sets the table's inheritance.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.inherits('users');
    });
};
specificType​

Sets the table's specific type.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.specificType('email', 'varchar(100)');
    });
};
index​

Adds an index.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.index('email');
    });
};
dropIndex​

Drops an index.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.dropIndex('email');
    });
};
setNullable​

Sets the column to be nullable.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.string('email').setNullable();
    });
};
dropNullable​

Drops the column's nullable property.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.string('email').dropNullable();
    });
};
primary​

Sets the column to be the primary key.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.string('email').primary();
    });
};
unique​

Sets the column to be unique.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.string('email').unique();
    });
};
foreign​

Sets the column to be a foreign key.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.foreign('user_id').references('id').inTable('users').onDelete('cascade');
    });
};
dropForeign​

Drops the column's foreign key.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.dropForeign('user_id');
    });
};
dropUnique​

Drops the column's unique property.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.string('email').dropUnique();
    });
};
dropPrimary​

Drops the column's primary property.

exports.up = (DB) => {
    return DB.schema.table('users', (table) => {
        table.string('email').dropPrimary();
    });
};

For more information, visit the Knex.js documentations.