Migrations

Migrations act as a version control system for your database, allowing your team to develop and share the database schema definition for the application. You've experienced the difficulty that database migrations solve if you've ever had to inform a coworker to manually add a column to their local database schema after pulling in your modifications from source control.

Across all of OstroJS's supported database systems, the OstroJS Schema facade offers database agnostic functionality for generating and manipulating tables. This façade is often used by migrations to create and alter database tables and columns.

To create a database migration, use the make:migration Assistant command. Your database/migrations directory will now include the new migration. Each migration filename includes a date that OstroJS uses to determine the migrations' order:

node assistant make:migration create_flights_table

OstroJS will try to estimate the table's name and whether or not the migration will create a new table based on the migration's name. OstroJS will pre-fill the created migration file with the chosen table if the table name can be determined from the migration name. If not, you may explicitly specify the table in the migration file.

When using the make:migration command, you may use the —path option to provide a specific path for the resulting migration. The specified path must be relative to the base path of your application.

There are two methods in a migration class: up and down. The up method is used to create new tables, columns, or indexes in your database, but the down method should reverse the up method's effects.

You may use the OstroJS schema builder to create and change tables expressively in both of these ways. Check out the Schema builder's documentation to learn about all of the techniques available. The following migration, for example, produces a flights table:

const Schema = require('@ostro/support/facades/schema')
const Migration = require('@ostro/database/migration')

class CreateFlightsTable  extends Migration {
    
    async up() {
        await Schema.create('flights', function ($table) {
            $table.increments('id').primary();
            $table.string('name');
            $table.string('airline');
            $table.timestamps();
        });
    }
    
    
    async down() {
        await Schema.dropIfExists('flights');
    }

}

module.exports = CreateFlightsTable 

You should set the $connection property of your migration if your migration will communicate with a database connection different than your application's default database connection:

const Migration = require('@ostro/database/migration')

class CreateFlightsTable extends Migration {
    /**
     * The database connection that should be used by the migration.
     *
     * @var string
     */
    $connection = 'pgsql';

    /**
     * Run the migrations.
     *
     * @return void
     */
    up(){
        //
    }
}

module.exports = CreateFlightsTable

Execute the migrate Assistant command to complete all of your pending migrations:

node assistant migrate

Some migration processes are harmful, meaning they might result in data loss. To prevent you from accidentally running these commands on your production database, you will be asked to confirm your actions before they are carried out. Use the —force parameter to compel the commands to run without prompting:

node assistant migrate --force

You may use the rollback Assistant command to undo the most recent migration process. This command undoes the most recent "batch" of migrations, which might involve numerous files:

node assistant migrate:rollback

By using the step option with the rollback command, you may roll back a restricted number of migrations. The following command, for example, will roll back the past five migrations:

node assistant migrate:rollback --step=5

All of your application's migrations will be rolled back with the migrate:reset command:

node assistant migrate:reset

All of your migrations will be rolled back with the migrate:refresh command, and then the migrate command will be executed. This command completely re-creates your database:

node assistant migrate:refresh

// Refresh the database and run all database seeds...
node assistant migrate:refresh --seed

By using the step option with the refresh command, you may roll back and re-migrate a limited number of migrations. The following command, for example, will roll back and re-migrate the last five migrations:

node assistant migrate:refresh --step=5

The migrate:fresh command removes all tables from the database before running the migrate command:

node assistant migrate:fresh

node assistant migrate:fresh --seed

Use the create method on the Schema facade to create a new database table. The create function takes two arguments: one is the table's name, and the other is a closure that receives a Blueprint object that may be used to define the new table:

const Schema = require('@ostro/support/facades/schema')

Schema.create('users', function (table) {
    table.increments('id').primary();
    table.string('name');
    table.string('email').unique();
    table.string('password');
    table.timestamps();
});

You can specify the table's columns using any of the schema builder's column methods while building the table.

The hasTable and hasColumn methods can be used to determine whether a table or column exists:

if (await Schema.hasTable('users')) {
    // The "users" table exists...
}

if (await Schema.hasColumn('users', 'email')) {
    // The "users" table exists and has an "email" column...
}

Use the connection method: if you want to conduct a schema action on a database connection that isn't your application's default connection.

await Schema.connection('sqlite').create('users', function ($table) {
    $table.increments('id').primary();
});

A few more attributes and methods may be used to specify the table's construction in various ways. When using MySQL, the engine attribute may be used to identify the table's storage engine:

await Schema.create('users', function ($table) {
    $table.engine('InnoDB');

    // ...
});

When using MySQL, the charset and collation attributes may be used to provide the character set and collation for the newly formed table:

await Schema.create('users', function ($table) {
    $table.charset('utf8mb4');
    $table.collation('utf8mb4_unicode_ci');

    // ...
});

To edit existing tables, use the table method on the Schema facade. The table function, like the create method, takes two arguments: a closure that receives a Blueprint instance that you may use to add columns or indexes to the table:

const Schema = require('@ostro/support/facades/schema')

await Schema.table('users', function ($table) {
    $table.integer('votes');
});

Use the rename method to rename an existing database table:

const Schema = require('@ostro/support/facades/schema')

await Schema.rename($from, $to);

You may use the drop or dropIfExists methods to remove an existing table:

const Schema = require('@ostro/support/facades/schema')

await Schema.drop('users');

await Schema.dropIfExists('users');

To edit existing tables, use the table method on the Schema facade. The table method, like the create method, takes two arguments: a closure that receives an @ostro/database/schema/blueprint object that you can use to add columns to the table:

const Schema = require('@ostro/support/facades/schema')

await Schema.table('users', function ($table) {
    $table.integer('votes');
});

The schema builder blueprint has a number of methods that correlate to the various sorts of columns that may be added to your database tables. In the table below, each of the available ways is listed:

When adding a column to a database table, you may use numerous column "modifiers" in addition to the column types indicated above. To create a column "nullable," for example, you may use the nullable method:

const Schema = require('@ostro/support/facades/schema')

await Schema.table('users', function ($table) {
    $table.string('email').nullable();
});

All of the possible column modifiers are included in the table below. Index modifiers are not included in this list:

A value or an @ostro/database/queryExpression object is accepted by the default modifier. When you use an Expression instance, OstroJS will not wrap the value in quotes, allowing you to employ database-specific methods. When you need to give default values to JSON columns, this is a particularly handy feature:

const Schema = require('@ostro/support/facades/schema')
const Migration = require('@ostro/database/migration')

class CreateFlightsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    async up() {
       await Schema.create('flights', function (Blueprint $table) {
            $table.id();
            $table.json('movies').default('(JSON_ARRAY())');
            $table.timestamps();
        });
    }

}

module.exports = CreateFlightsTable

The dropColumn method on the schema builder blueprint may be used to remove a column. If your application uses a SQLite database, you'll need to use the Composer package manager to install the doctrine/dbal package before you can use the dropColumn method:

await Schema.table('users', function ($table) {
    $table.dropColumn('votes');
});

By giving an array of column names to the dropColumn function, you may remove several columns from a table:

await Schema.table('users', function ($table) {
    $table.dropColumn(['votes', 'avatar', 'location']);
});

OstroJS includes a number of useful ways for removing common sorts of columns. The table below details each of these methods:

Several sorts of indexes are supported by the OstroJS schema builder. The example below establishes a new email column with the requirement that its values be unique. We can use the unique approach to build the index by chaining it onto the column definition:

await Schema.table('users', function ($table) {
    $table.string('email').unique();
});

You may also construct the index after you've defined the column. To accomplish so, you should use the schema builder blueprint's unique technique. This method takes the name of the column for which a unique index should be created:

$table.unique('email');

You can also build a compound (or composite) index by passing an array of columns to an index method:

$table.index(['account_id', 'created_at']);

OstroJS will automatically produce an index name based on the table, column names, and index type when establishing an index, but you may override this by passing a second parameter to the method:

$table.unique('email', 'unique_email');

The schema builder blueprint class in OstroJS includes methods for constructing each type of index that OstroJS supports. The name of the index can be specified as an optional second argument to each index method. The name will be derived from the names of the table and column(s) used for the index, as well as the index type, if this parameter is left blank. In the table below, each of the various index techniques is described:

OstroJS utilises the utf8mb4 character set by default. If you're using an earlier version of MySQL or MariaDB than 10.2.2, you may need to manually change the default string length created by migrations so that MySQL can construct indexes for them. Within the boot function of your AppProvidersAppServiceProvider class, utilise the Schema::defaultStringLength method to set the default string length:

const Schema = require('@ostro/support/facades/schema')


/**
 * Bootstrap any application services.
 *
 * @return void
 */
boot() {
    Schema.defaultStringLength(191);
}

Alternatively, you may set your database's innodb large prefix option. Instructions on how to correctly activate this option may be found in your database's documentation.

The renameIndex function supplied by the schema builder blueprint may be used to rename an index. The current index name is supplied as the first parameter, while the desired name is passed as the second argument:

$table.renameIndex('from', 'to')

To remove an index, you must first give it a name. OstroJS creates an index name based on the table name, the name of the indexed column, and the index type by default. Some instances are as follows:

The conventional index name will be produced based on the table name, columns, and index type if you send an array of columns to a procedure that removes indexes:

await Schema.table('geo', function ($table) {
    $table.dropIndex(['state']); // Drops index 'geo_state_index'
});

Foreign key constraints, which are used to enforce referential integrity at the database level, are also supported by OstroJS. Let's say you want to add a user id column to the posts table that refers to the id column in the users table:

const Schema = require('@ostro/support/facades/schema')

await Schema.table('posts', function ($table) {
    $table.unsignedBigInteger('user_id');

    $table.foreign('user_id').references('id').on('users');
});

OstroJS includes extra, terser methods that leverage conventions to give a better developer experience because this syntax is very lengthy. The example above may be rebuilt as follows when using the foreignId method to build your column:

await Schema.table('posts', function ($table) {
    $table.foreignId('user_id').constrained();
});

The foreignId method generates a UNSIGNED BIGINT equivalent column, whereas the restricted approach relies on conventions to figure out which table and column to use. If the name of your table does not follow OstroJS's rules, you may give it as a parameter to the restricted method:

await Schema.table('posts', function ($table) {
    $table.foreignId('user_id').constrained('users');
});

You can additionally define the intended action for the constraint's "on deletion" and "on update" properties:

$table.foreignId('user_id')
      .constrained()
      .onUpdate('cascade')
      .onDelete('cascade');

For these acts, an alternative, expressive syntax is also provided:

Before calling the restricted method, any further column modifiers must be called:

$table.foreignId('user_id')
      .nullable()
      .constrained();

You may use the dropForeign method to remove a foreign key constraint by giving the name of the foreign key constraint to be removed as an argument. Foreign key constraints and indexes use the same naming convention. In other words, the name of the foreign key constraint is derived from the name of the table and the constraint's columns, followed by the suffix "_foreign":

$table.dropForeign('posts_user_id_foreign');

You can also send an array to the dropForeign function that contains the column name that carries the foreign key. Using OstroJS's constraint naming standards, the array will be translated to a foreign key constraint name:

$table.dropForeign(['user_id']);

You may use the following ways to activate or disable foreign key restrictions in your migrations:

Schema.enableForeignKeyConstraints();

Schema.disableForeignKeyConstraints();