Migrations¶

Migration CLI commands to allow you to version control your database schema.

With Migrations your team is able to easily modify and share the database schema to stay up to date. Migrations are typically paired with the Schema Builder which is inspired by the Laravel Schema Builder to easly manage your database’s schema.

Generating Migrations¶

Use the migration:make command to create a new Migration. This will create the following new Migration class in your migrations folder. In order to determine the order of the migrations, each migration file name contains a timestamp.

Arguments:

name	Name of the migration

Migration Structure¶

Each Migration class contains two methods: Migration.up() and Migration.down(). In the Migration.up() method you add new tables, columns, or indexes to your database. In the Migration.down() method you should revert those changed made in the Migration.up() method.

Inside the Migration class you have access to the Schema Builder to easily create and modify tables. For more information check out its documentation: Schema

Running Migrations¶

To run the latest outstanding Migration, just use the migration:up command. To revert the last Migration operation, just use the migration:down command.

To roll back all migrations and then execute all migrations, just use the migration:refresh command. This command effectively re-creates your entire database.

To see the status of the migrations, just use the migration:status command. This would output something like this:

|-------------------------------------------+--------|
| Migration                                 | Ran?   |
|-------------------------------------------+--------|
| 20180814111005_create_users_table         | Yes    |
| 20180815101334_add_avater_to_users_table  | No     |
|-------------------------------------------+--------|

The Schema class provides a database agnostic way of manipulating tables.

Tables¶

Creating Tables¶

To create a new database table, the create() method is used. The create() method accepts a table name as its argument and returns a Blueprint instance that can be used to define the new table. When creating the table, you may use any of the Blueprint column methods to define the table’s columns:

with self.schema.create('users') as table:
    table.increments('id')

Checking Existence¶

To check if a table or column exist you can use the has_table() or has_column() methods respectively:

if self.schema.has_table('users'):
    # ...

if self.schema.has_column('users', 'email'):
    # ...

Renaming / Dropping Tables¶

To rename an existing database table, use the rename() method:

self.schema.rename('from', 'to')

To drop a table, you can use the drop() or drop_if_exists() methods:

self.schema.drop('users')
self.schema.drop_if_exists('users')

The Blueprint class lets you create, delete, or alter columns of a table.

Columns¶

Adding Columns¶

To update an existing table, you can use the Schema.table() method. The method accepts a table name as its argument and returns a Blueprint instance that can be used to add columns to the table:

with self.schema.table('users') as table:
    table.string('email')
    table.string('address').nullable()
    table.integer('age').nullable().unsigned()

The table builder contains a plethora of column types that you can use when building your tables:

Command	Description
`table.array('ratings', 'integer', 2)`	ARRAY column type. Only supported in postgresql!
`table.big_increments('id')`	Auto-incrementing ID using a “big integer” (primary key) equivalent column.
`table.big_integer('votes)`	BIGINT equivalent column.
`table.binary('data')`	BLOB equivalent column.
`table.boolean('confirmed')`	BOOLEAN equivalent column.
`table.char('name', 4)`	CHAR equivalent column with a length.
`table.date('created_on')`	DATE equivalent column.
`table.datetime('created_at')`	DATETIME equivalent column.
`table.decimal('amount', 5, 2)`	DECIMAL equivalent column with a precision (total digits) and a scale (decimal digits).
`table.double('column', 15, 8)`	DOUBLE equivalent column with a precision (total digits) and a scale (decimal digits).
`table.enum('choices', ['foo', 'bar'])`	ENUM equivalent column.
`table.float('amount')`	FLOAT equivalent column.
`table.increments('id')`	Auto-incrementing ID using a “integer” (primary key) equivalent column.
`table.integer('votes')`	INTEGER equivalent column.
`table.json('options')`	JSON equivalent column.
`table.long_text('description')`	LONGTEXT equivalent column.
`table.medium_integer('votes')`	MEDIUMINT equivalent column.
`table.medium_text('description')`	MEDIUMTEXT equivalent column.
`table.small_integer('votes')`	SMALLINT equivalent column.
`table.string('email')`	VARCHAR equivalent column.
`table.string('votes', 100)`	VARCHAR equivalent column with a length.
`table.text('description')`	TEXT equivalent column.
`table.time('sunrise')`	TIME equivalent column.
`table.timestamp('added_at')`	TIMESTAMP equivalent column.

Column Modifiers¶

Each of the column methods above returns a Column instance, which lets you add several column “modifier” while adding a column to a table. For example, to make a column “nullable” you can use the nullable() method:

with self.schema.table('users') as table:
    table.string('email').nullable()

The column modifiers are the following (does not include index modifiers):

Modifier	Description
`.default('default_value')`	Specify a default value for the column.
`.nullable()`	Designate that the column allows NULL values.
`.unsigned()`	Set INTEGER column as UNSINGED (MySQL).

Dropping Columns¶

To drop a column from a table, you can use the drop_column() method.

Dropping a column from a database table:

with self.schema.table('users') as table:
    table.drop_column('votes')

Dropping multiple columns from a database table:

with self.schema.table('users') as table:
    table.drop_column('votes', 'avatar', 'location')

Indexes¶

The schema builder supports several types of indexes which you can add to and/or drop from columns. You can create the index after defining the column. For example:

table.string('email')
table.unique('email')

Available Index Types¶

Each index method accepts an optional second argument to specify the name of the index. If omitted, the name will be derived from the names of the table and column(s). Below is a list of all available index types:

Command	Description
`table.primary('id')`	Addys a primary key.
`table.primary(['id', 'parent_id'])`	Adds composite keys.
`table.unique('email)`	Adds a unique index.
`table.index('state')`	Adds a basic index.

Note

In MySQL/MariaDB and PostgreSQL the length of indexes are limited. experimentum generates index names based on table and column names, therefore if your column names are too long you can pass the name keyword argument to specify your own index name:

table.index(
    ['some_field_with_a_really_long_name', 'another_really_long_field'],
    name='my_idx_name'
)

Dropping Indexes¶

To drop an index you must specify the index’s columns. experimentum assigns a reasonable name to the indexes by default. If you have specified a custom index name, you have to add it as the optional second argument.

Command	Description
`table.drop_primary('id')`	Drop a primary key from the “id” column.
`table.drop_unique('email)`	Drop a unique index from the “email” column.
`table.drop_index('state')`	Drop a basic index from the “state” column.
`table.drop_foreign('user_id')`	Drop a foreign key from the “user_id” column.

Foreign Key Contraints¶

experimentum also provides support for adding foreign key constraints to your tables:

with self.schema.table('posts') as table:
    table.increments('user_id')
    table.foreign('user_id')\
        .references('id').on('users')\
        .on_delete('cascade')\
        .on_update('cascade')

In this example, we are stating that the user_id column references the id column on the users table and set the “on update” and “on delete” actions to cascade. Make sure to create the foreign key column first!

To drop a foreign key, you can use the drop_foreign() method. It works just like dropping an index.