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.