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
table.dropColumn('deleted_at');
dropColumns
Drops multiple columns, specified by an array of column names
table.dropColumns(['deleted_at', 'deleted_by']);
renameColumn
Renames a column from one name to another
table.renameColumn('deleted_at', 'deleted_when');
rememberToken
Adds a remember_token
column to the table
table.rememberToken();
increments
Adds an auto incrementing column. This is the same as integer
with autoIncrement
set to true
.
table.increments('id').primary();
integer
Adds an integer column.
table.integer('age');
bigInteger
Adds a big integer column.
table.bigInteger('views');
tinyint
Adds a tiny integer column.
table.tinyint('views');
smallint
Adds a small integer column.
table.smallint('views');
mediumint
Adds a medium integer column.
table.mediumint('views');
bigint
Adds a big integer column.
table.bigint('views');
text
Adds a text column.
table.text('description');
string
Adds a string column.
table.string('email');
float
Adds a float column.
table.float('amount');
double
Adds a double column.
table.double('amount');
decimal
Adds a decimal column.
table.decimal('amount');
boolean
Adds a boolean column.
table.boolean('confirmed');
date
Adds a date column.
table.date('created_at');
datetime
Adds a datetime column.
table.datetime('created_at');
time
Adds a time column.
table.time('sunrise');
timestamp
Adds a timestamp column.
table.timestamp('created_at');
timestamps
Adds created_at
and updated_at
columns.
table.timestamps();
dropTimestamps
Drops created_at
and updated_at
columns.
table.dropTimestamps();
softDeletes
Adds a deleted_at
column to the table.
table.softDeletes();
dropSoftDeletes
Drops the deleted_at
column from the table.
table.dropSoftDeletes();
binary
Adds a binary column.
table.binary('photo');
enum
Adds an enum column.
table.enum('role', ['admin', 'user']);
json
Adds a json column.
table.json('options');
jsonb
Adds a jsonb column.
table.jsonb('options');
uuid
Adds a uuid column.
table.uuid('id').defaultTo(DB.fn.uuid());
comment
Adds a comment to the column.
table.string('email').comment('The user\'s email address');
engine
Sets the table's storage engine.
table.engine('InnoDB');
charset
Sets the table's character set.
table.charset('utf8mb4');
collate
Sets the table's collation.
table.collate('utf8mb4_unicode_ci');
inherits
Sets the table's inheritance.
table.inherits('users');
specificType
Sets the table's specific type.
table.specificType('email', 'varchar(100)');
index
Adds an index.
table.index('email');
dropIndex
Drops an index.
table.dropIndex('email');
setNullable
Sets the column to be nullable.
table.string('email').setNullable();
dropNullable
Drops the column's nullable property.
table.string('email').dropNullable();
primary
Sets the column to be the primary key.
table.string('email').primary();
unique
Sets the column to be unique.
table.string('email').unique();
foreign
Sets the column to be a foreign key.
table.foreign('user_id').references('id').inTable('users').onDelete('cascade');
dropForeign
Drops the column's foreign key.
table.dropForeign('user_id');
dropUnique
Drops the column's unique property.
table.string('email').dropUnique();
dropPrimary
Drops the column's primary property.
table.string('email').dropPrimary();
For more information, visit the Knex.js documentations.