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.