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         .onUpdate('cascade');
10  });
11}

modifying tables

the table method on the Schema facade allows you to update existing tables: adding columns, modifying column definitions, renaming columns, dropping columns, and managing indexes or foreign keys.

1export async function up({ Schema }) {
2  await Schema.table('users', table => {
3    // add a new column
4    table.string('phone').nullable();
5 
6    // modify an existing column definition (fluent syntax)
7    table.string('email', 150).nullable().modify();
8 
9    // modify an existing column definition (direct syntax)
10    table.modify('bio', 'TEXT', { nullable: true });
11 
12    // rename a column
13    table.renameColumn('username', 'handle');
14 
15    // drop a single column or multiple columns
16    table.dropColumn('temp_token');
17    table.dropColumns('old_field_a', 'old_field_b');
18 
19    // add indexes
20    table.index('phone');
21    table.index(['organization_id', 'role'], 'idx_users_org_role');
22  });
23}
24 
25export async function down({ Schema }) {
26  await Schema.table('users', table => {
27    table.dropForeign('fk_users_org_id');
28    table.dropIndex('idx_users_org_role');
29    table.renameColumn('handle', 'username');
30  });
31}

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

to execute pending migrations, run the migrate command:

1dstrn migrate

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.

when running in production (APP_ENV=production), the command requires confirmation before applying changes. pass the --force flag to run migrations non interactively in automated deployment pipelines:

1dstrn migrate --force

rolling back

if you need to revert the latest database changes, run:

1dstrn migrate:rollback

the framework queries the migrations table to identify the last batch number and executes the down function for those specific files in reverse chronological order.

in production (APP_ENV=production), the command requires confirmation before applying rollbacks. pass --force to run non interactively in automated pipelines:

1dstrn migrate:rollback --force

transactional migrations and ddl statements

migrations execute within database transactions to ensure changes roll back if an error occurs. however, mysql implicitly commits ddl statements (such as create table, alter table, and drop table). if a migration contains multiple ddl statements and fails halfway, the statements executed before the failure will remain committed in mysql while the migration entry is not recorded in the migrations table. keep migrations focused and inspect your database schema if a ddl migration fails.