You can create new tables by using the create method. This method takes two arguments: the name of the table you want to create and a callback. The callback that receive as an argument, an instance of the Opis\Database\Schema\CreateTable class, which will be further used to add columns, indexes and constraints to the newly created table.

Opis\Database\Schema\CreateTable;

$db->schema()->create('users', function(CreateTable $table){
    //add table members
});

Adding columns

The schema builder provides a series of methods that allows you to add columns, having different data types, to a table. The first argument that these methods take, represents the column name. Here is a list of supported column types and their associated methods:

Integer

$table->integer('age');

Adds an INTEGER equivalent column

Float

$table->float('height');

Adds a FLOAT equivalent column

Double

$table->double('distance');

Adds a DOUBLE equivalent column

Decimal

// Default precision
$table->decimal('ammount');

// Explicit precision
$table->decimal('ammount', 16, 4);

Adds a DECIMAL equivalent column, and optionally specify decimal’s precision

Boolean

$table->boolean('registered');

Adds a BOOLEAN equivalent column

Binary

$table->binary('picture');

Adds a BLOB equivalent column

String

// Default length of 255
$table->string('email');

// Explicit length
$table->string('email', 128)

Adds a VARCHAR equivalent column, and optionally specify its length

Fixed

// Default length of 255
$table->fixed('country_code');

// Explicit length
$table->fixed('country_code', 2);

Adds a CHAR equivalent column, and optionally specify its lengths

Text

$table->text('description');

Adds a TEXT equivalent column

Time

$table->time('sunrise');

Adds a TIME equivalent column

Timestamp

$table->timestamp('created_at');

Adds a TIMESTAMP equivalent column

Date

$table->date('birthday');

Adds a DATE equivalent column

DateTime

$table->dateTime('appointment');

Adds a DATETIME equivalent column

Column size

For integer, text and binary types you can specify the column size by calling the size method. The valid sizes values are: tiny, small, normal, medium and big.

$db->schema()->create('users', function($table){

    $table->integer('id')->size('big');

});

Type	Size	Description
integer	tiny	Adds an TINYINT equivalent column to the table
integer	small	Adds an SMALLINT equivalent column to the table
integer	normal	Adds an INTEGER equivalent column to the table
integer	medium	Adds an MEDIUMINT equivalent column to the table
integer	big	Adds an BIGINT equivalent column to the table
text	tiny	Adds an TINYTEXT equivalent column to the table
text	small	Adds an TINYTEXT equivalent column to the table
text	small	Adds an TINYTEXT equivalent column to the table
text	medium	Adds an MEDIUMTEXT equivalent column to the table
text	big	Adds an LONGTEXT equivalent column to the table
binary	tiny	Adds an TINYBLOB equivalent column to the table
binary	small	Adds an TINYBLOB equivalent column to the table
binary	normal	Adds an BLOB equivalent column to the table
binary	medium	Adds an MEDIUMBLOB equivalent column to the table
binary	big	Adds an LONGBLOB equivalent column to the table

Column properties

You can specify that an integer column contains an unsigned integer value by using the unsigned method.

$table->integer('id')->unsigned();

You can provide a default value for column by using the defaultValue method.

$table->string('role', 32)->defaultValue('user');

Specifying that a column in not nullable id bone by using the notNull method.

$table->string('email')->notNull();

Column constraints

Adding a primary key constraint is done by using the primary method. The name of the constraint will be the same as the column’s name.

$table->integer('id')->primary();

Adding an unique constraint is done by using the unique method. The name of the constraint will be the same as the column’s name

$table->string('email')->unique();

For integer column types you can specify that the column’s value will be incremented automatically, when a new row is inserted into the table, by using the autoincrement method. When using this feature, a primary key constraint will be automatically added to the column.

$table->integer('id')->autoincrement();

Indexing a column

You can specify that the current column should be indexed, by calling the index method. The name of the newly added index will be the same as the column’s name.

$table->string('username', 32)->index();

Primary key

Adding a primary key for a newly created table is done by using the primary method. This method takes as an argument the name of the column on which you want to add the primary key constraint. The primary key’s name will be then derived from the table’s name and the name of the column on which the primary key was added.

$db->schema()->create('users', function($table){

    $table->integer('id');
    // ... Add other columns
    //Add primary key
    $table->primary('id');

});

You can add only one primary key per table.

If you want add a composite primary key, you simply have to pass an array of column names to the primary method.

$db->schema()->create('users', function($table){

    $table->integer('id');
    $table->integer('group');
    // ... Add other columns
    //Add a composite primary key on `id` and `group` columns
    $table->primary(['id', 'group']);
    
});

Specifying a custom name for a primary key is also possible.

$db->schema()->create('users', function($table){

    $table->integer('id');
    // ... Add other columns
    //Add a primary key named `my_custom_pk` on column `id`
    $table->primary('id', 'my_custom_pk');
    
});

Unique keys

Adding a unique key for a newly created table is done using the unique method. The name of the unique key will be derived in the same manner as the name of the primary key is.

$db->schema()->create('users', function($table){

    $table->string('email');
    // ... Add other columns
    //Add unique key
    $table->unique('email');

});

Composite unique keys are supported as well.

$db->schema()->create('users', function($table){

    $table->string('email');
    $table->string('username');
    // ... Add other columns
    //Add a unique key on `email` and `uesername` columns
    $table->unique(['email', 'username']);

});

And, of course, you can always add a custom name for a unique key.

$db->schema()->create('users', function($table){

    $table->string('email');
    $table->string('username');
    // ... Add other columns
    //Add a unique key named `uk_email` on column `email`
    $table->unique('email', 'uk_email');
    //Add a unique key named `uk_composite` on `email` and `uesername` columns
    $table->unique(['email', 'username'], 'uk_composite');
    
});

Foreign keys

Adding a foreign key to a newly created table is done using the foreign method in conjunction with the references method. The foreign method takes as arguments the column on which the foreign key will be added, and the references method, takes as arguments the referenced table and column.

$db->schema()->create('users', function($table){

    $table->integer('profile_id');
    // ... Add other columns
    //Add a foreign key
    $table->foreign('profile_id')
          ->references('profiles', 'id');

});

You may also specify options for the on delete and on update actions by using the onDelete and onUpdate methods. These methods accepts as an argument only the following string values: cascade, restrict, no action and set null.

$db->schema()->create('users', function($table){

    $table->integer('profile_id');
    // ... Add other columns
    //Add a foreign key
    $table->foreign('profile_id')
          ->references('profiles', 'id')
          ->onDelete('cascade')
          ->onUpdate('cascade');

});

Adding a foreign key that has a custom name can be done by passing a second argument to the foreign method

$db->schema()->create('users', function($table){

    $table->integer('profile_id');
    // ... Add other columns
    //Add a foreign key named `fk_custom_name`
    $table->foreign('profile_id', 'fk_custom_name')
          ->references('profiles', 'id')
          ->onDelete('cascade')
          ->onUpdate('cascade');

});

Indexes

Indexes are added by using the index method.

$db->schema()->create('users', function($table){

    $table->string('name');
    // ... Add other columns
    //Add index
    $table->index('name');

});

You can set an index on multiple columns

$db->schema()->create('users', function($table){

    $table->string('name');
    $table->string('email');
    // ... Add other columns
    //Add an index on `email` and `name` columns
    $table->index(['name', 'email']);

});

Adding a custom named index is simply a matter of passing a second argument to the index method.

$db->schema()->create('users', function($table){

    $table->string('name');
    // ... Add other columns
    //Add an index named `idx_name` on column `name`
    $table->index('name', 'idx_name');

});