migrations

introduction

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, you've faced the problem that database migrations solve.

each migration file is an es module that must export an asynchronous up function, and optionally a down function. the framework automatically injects a context object containing the Schema service, the table query builder, the raw db connection, and a query helper. these properties are also made available globally during the execution of the migration.

creating tables

to create a new database table, use the create method on the Schema facade. the create method accepts two arguments: the name of the table and a closure which receives a TableBuilder object used to define the new table.

1export async function up({ Schema }) {
2  await Schema.create('users', table => {
3    table.increments('id');
4    table.string('name');
5    table.string('email').unique();
6    table.timestamps();
7  });
8}
9 
10export async function down({ Schema }) {
11  await Schema.dropIfExists('users');
12}

column types

the table builder contains a variety of column types that you may use when building your tables.

column modifiers

in addition to the column types listed above, there are several column modifiers you may use while adding a column to a database table.

foreign keys

dframework supports adding foreign key constraints to your tables. you define the local column using the foreign method, specify the referenced column and table using the references method, and optionally define cascade rules.

1export async function up({ Schema }) {
2  await Schema.create('posts', table => {
3    table.increments('id');
4    table.integer('user_id').unsigned();
5    
6    table.foreign('user_id')
7         .references('id', 'users')
8         .onDelete('cascade');
9  });
10}

modifying tables

the table method on the Schema facade allows you to update existing tables.

1export async function up({ Schema }) {
2  await Schema.table('users', table => {
3    table.string('phone').nullable();
4    table.index('phone');
5  });
6}

you can also rename an existing table using the rename method.

1await Schema.rename('old_table', 'new_table');

dropping tables

to drop an existing table, you may use the drop or dropIfExists methods.

1await Schema.drop('users');
2await Schema.dropIfExists('users');

running migrations

the framework runs migrations sequentially in alphabetical order based on the filenames in your migrations directory. a database table named migrations is automatically generated to track which migrations have already been executed, organizing them into batches.

this ensures that only pending migrations are executed, avoiding duplicate table creations.

rolling back

if you need to revert the latest database changes, the framework allows you to roll back the most recent migration batch. it queries the migrations table to identify the last batch number and executes the down function for those specific files in reverse chronological order.