Database: Migration
The Schema and Structure classes provide a fluent, expressive interface for creating and modifying database tables. It allows you to define table columns, indexes, and foreign key constraints using a clean PHP syntax.
Introduction
The Schema builder is responsible for all database schema operations. It allows you to create tables, drop them, and manage their structure. The Schema facade provides a static interface to these operations, while the Structure class gives you a fluent way to define the table's columns and constraints.
Schema Methods
The Ecommerce\Supports\Facades\Schema facade provides the following methods for managing database tables.
Create Table
Create a new table in the database.
Description:
The create() method is used to create a new database table. It accepts the table name and a Closure (anonymous function) as arguments. The closure receives a Ecommerce\Core\Database\Schema\Structure instance, which you can use to define the table's columns and indexes.
Method Signature:
public static function create(string $table, Closure $callback): void
Parameters:
$table- The name of the table to create.$callback- A closure that defines the table structure. Receives aStructureinstance.
Examples:
use Ecommerce\Supports\Facades\Schema;
use Ecommerce\Core\Database\Schema\Structure;
// Create a 'products' table
Schema::create('products', function (Structure $table) {
$table->id();
$table->string('name');
$table->decimal('price', 8, 2);
$table->boolean('is_active')->default(true);
$table->timestamps();
});
// Create an 'orders' table with specific engine
Schema::create('orders', function (Structure $table) {
$table->engine('InnoDB');
$table->id();
$table->unsigned_big_integer('user_id');
$table->decimal('total', 10, 2);
$table->string('status')->default('pending');
$table->timestamps();
});
SQL Output:
CREATE TABLE IF NOT EXISTS `droip_ecommerce_products` (
`id` bigint unsigned auto_increment not null,
`name` varchar(255) not null,
`price` decimal(8, 2) not null,
`is_active` tinyint(1) not null default '1',
`created_at` timestamp not null default CURRENT_TIMESTAMP,
`updated_at` timestamp null,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Drop Table If Exists
Drop a table from the database if it exists.
Description:
The drop_if_exists() method creates a DROP TABLE IF EXISTS statement. This is useful in migration down methods or when you want to ensure a table is removed without throwing an error if it's already gone. It temporarily disables foreign key checks during the operation.
Method Signature:
public static function drop_if_exists(string $table): void
Parameters:
$table- The name of the table to drop.
Examples:
use Ecommerce\Supports\Facades\Schema;
// safely drop the products table
Schema::drop_if_exists('products');
// safely drop the orders table
Schema::drop_if_exists('orders');
SQL Output:
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `droip_ecommerce_products`;
SET FOREIGN_KEY_CHECKS = 1;
Drop Table
Drop a table from the database.
Description:
The drop() method creates a DROP TABLE statement. Unlike drop_if_exists, this will throw an error if the table does not exist. It temporarily disables foreign key checks during the operation.
Method Signature:
public static function drop(string $table): void
Parameters:
$table- The name of the table to drop.
Examples:
use Ecommerce\Supports\Facades\Schema;
// Force drop the products table
Schema::drop('products');
SQL Output:
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE `droip_ecommerce_products`;
SET FOREIGN_KEY_CHECKS = 1;
Column Types
The Ecommerce\Core\Database\Schema\Structure class (passed as $table to the closure) provides a wide variety of methods to create table columns.
id()
Create an auto-incrementing primary key.
Description:
The id() method creates a BIGINT UNSIGNED equivalent column that is auto-incrementing and acts as the primary key. This is an alias for $table->big_increment('id').
Method Signature:
public function id(string $name = 'id'): Definition
Parameters:
$name- (Optional) The name of the column. Defaults to'id'.
Examples:
$table->id();
$table->id('product_id');
SQL Output:
`id` bigint unsigned auto_increment not null
-- and adds PRIMARY KEY (`id`) constraint
integer()
Create an integer column.
Description:
The integer() method creates an INT column. It can optionally be set as auto-incrementing or unsigned.
Method Signature:
public function integer(string $name, bool $auto_increment = false, bool $unsigned = false): Definition
Parameters:
$name- The name of the column.$auto_increment- (Optional) Set to true to make it auto-increment. Defaults tofalse.$unsigned- (Optional) Set to true to make it unsigned. Defaults tofalse.
Examples:
$table->integer('votes');
$table->integer('position', false, true); // Unsigned
SQL Output:
`votes` int not null
`position` int unsigned not null
big_integer()
Create a big integer column.
Description:
The big_integer() method creates a BIGINT column. Useful for storing large numbers or foreign keys referencing big increments.
Method Signature:
public function big_integer(string $name, bool $auto_increment = false, bool $unsigned = false): Definition
Parameters:
$name- The name of the column.$auto_increment- (Optional) Set to true for auto-increment.$unsigned- (Optional) Set to true for unsigned.
Examples:
$table->big_integer('views');
$table->big_integer('user_id', false, true); // Unsigned, good for FKs
SQL Output:
`views` bigint not null
`user_id` bigint unsigned not null
string()
Create a string (VARCHAR) column.
Description:
The string() method creates a VARCHAR column with a specified length.
Method Signature:
public function string(string $name, int $length = null): Definition
Parameters:
$name- The name of the column.$length- (Optional) The maximum length of the string. Defaults to 255.
Examples:
$table->string('name');
$table->string('email', 100);
$table->string('sku', 50);
SQL Output:
`name` varchar(255) not null
`email` varchar(100) not null
text()
Create a text column.
Description:
The text() method creates a standard TEXT column.
Method Signature:
public function text(string $name): Definition
Parameters:
$name- The name of the column.
Examples:
$table->text('description');
$table->text('comments');
SQL Output:
`description` text not null
boolean()
Create a boolean column.
Description:
The boolean() method creates a TINYINT(1) column, which is the standard way to store booleans in MySQL.
Method Signature:
public function boolean(string $name): Definition
Parameters:
$name- The name of the column.
Examples:
$table->boolean('confirmed');
$table->boolean('is_admin');
SQL Output:
`confirmed` tinyint(1) not null
decimal()
Create a decimal column.
Description:
The decimal() method creates a DECIMAL column with specified precision (total digits) and scale (decimal places). This is best for monetary values.
Method Signature:
public function decimal(string $name, int $total = 8, int $places = 2): Definition
Parameters:
$name- The name of the column.$total- (Optional) Total number of digits. Defaults to 8.$places- (Optional) Number of decimal places. Defaults to 2.
Examples:
$table->decimal('amount'); // decimal(8, 2)
$table->decimal('latitude', 10, 8);
$table->decimal('precise_score', 5, 4);
SQL Output:
`amount` decimal(8, 2) not null
`latitude` decimal(10, 8) not null
timestamp()
Create a timestamp column.
Description:
The timestamp() method creates a TIMESTAMP column.
Method Signature:
public function timestamp(string $name, int $precision = 0): Definition
Parameters:
$name- The name of the column.$precision- (Optional) The fractional seconds precision.
Examples:
$table->timestamp('published_at');
$table->timestamp('last_login_at');
SQL Output:
`published_at` timestamp not null
timestamps()
Create creation and update timestamps.
Description:
The timestamps() method is a helper that creates two columns: created_at (TIMESTAMP, default CURRENT_TIMESTAMP) and updated_at (TIMESTAMP, nullable).
Method Signature:
public function timestamps(int $precision = 0): void
Parameters:
$precision- (Optional) The fractional seconds precision.
Examples:
$table->timestamps();
SQL Output:
`created_at` timestamp not null default CURRENT_TIMESTAMP,
`updated_at` timestamp null
uuid()
Create a UUID column.
Description:
The uuid() method creates a CHAR(36) column suitable for storing UUIDs.
Method Signature:
public function uuid(string $name): Definition
Parameters:
$name- The name of the column.
Examples:
$table->uuid('uuid');
$table->uuid('batch_id');
SQL Output:
`uuid` char(36) not null
Column Modifiers
Every column definition method returns a Definition object, which provides fluent methods to modify the column's properties.
nullable()
Allow NULL values for the column.
Description:
The nullable() modifier alters the column definition to allow NULL values. By default, columns are created as NOT NULL.
Method Signature:
public function nullable(): Definition
Examples:
$table->string('notes')->nullable();
$table->big_integer('parent_id')->nullable();
SQL Output:
`notes` varchar(255) null
default()
Set a default value for the column.
Description:
The default() modifier sets a default value for the column.
Method Signature:
public function default(mixed $value): Definition
Parameters:
$value- The default value.
Examples:
$table->boolean('active')->default(true);
$table->integer('count')->default(0);
$table->string('type')->default('standard');
SQL Output:
`active` tinyint(1) not null default '1'
`type` varchar(255) not null default 'standard'
comment()
Add a comment to the column.
Description:
The comment() modifier adds a database-level comment to the column.
Method Signature:
public function comment(string $comment): Definition
Parameters:
$comment- The comment text.
Examples:
$table->string('status')->comment('Current order status');
SQL Output:
`status` varchar(255) not null comment 'Current order status'
use_current()
Set default value to CURRENT_TIMESTAMP.
Description:
The use_current() modifier sets the default value of a timestamp or datetime column to the current timestamp.
Method Signature:
public function use_current(): Definition
Examples:
$table->timestamp('created_at')->use_current();
SQL Output:
`created_at` timestamp not null default CURRENT_TIMESTAMP
Indexes
The Structure class provides methods to define table indexes.
primary()
Define a primary key.
Description:
The primary() method defines a primary key constraint on one or more columns.
Method Signature:
public function primary(string|array $keys, string $key_name = null): Structure
Parameters:
$keys- A column name or array of column names.$key_name- (Optional) Custom name for the primary key constraint.
Examples:
$table->string('uuid', 36);
$table->primary('uuid');
// Composite primary key
$table->primary(['order_id', 'product_id']);
SQL Output:
PRIMARY KEY (`uuid`)
-- or
PRIMARY KEY (`order_id`, `product_id`)
unique()
Define a unique index.
Description:
The unique() method defines a unique constraint on one or more columns.
Method Signature:
public function unique(string|array $keys, string $key_name = null): Structure
Parameters:
$keys- A column name or array of column names.$key_name- (Optional) Custom name for the unique key. If null, generated automatically.
Examples:
$table->string('email');
$table->unique('email');
// Composite unique key
$table->unique(['provider', 'provider_id']);
SQL Output:
UNIQUE KEY `tablename_email_unique` (`email`)
index()
Define a standard index.
Description:
The index() method defines a standard index on one or more columns to improve query performance.
Method Signature:
public function index(string|array $keys, string $key_name = null): Structure
Parameters:
$keys- A column name or array of column names.$key_name- (Optional) Custom name for the index. If null, generated automatically.
Examples:
$table->index('status');
$table->index(['category_id', 'created_at']);
SQL Output:
KEY `tablename_status_index` (`status`)
Foreign Keys
The schema builder allows you to easily define foreign key constraints to enforce referential integrity.
foreign()
Define a foreign key constraint.
Description:
The foreign() method starts the definition of a foreign key constraint. It returns a ForeignKeyDefinition instance that you chain methods onto to define the reference details.
Method Signature:
public function foreign(string $column, string $name = null): ForeignKeyDefinition
Parameters:
$column- The column name on the current table.$name- (Optional) Custom name for the foreign key constraint.
Examples:
// Basic Foreign Key
$table->unsigned_big_integer('user_id');
$table->foreign('user_id')->references('id')->on('users');
// With Cascade Actions
$table->foreign('product_id')
->references('id')
->on('products')
->on_delete('cascade')
->on_update('cascade');
SQL Output:
FOREIGN KEY (`user_id`) REFERENCES `droip_ecommerce_users` (`id`)
FOREIGN KEY (`product_id`) REFERENCES `droip_ecommerce_products` (`id`) ON UPDATE cascade ON DELETE cascade
Chained Methods:
references(string $column)
Specify the column on the referenced table (usually 'id').
on(string $table)
Specify the referenced table name.
on_delete(string $action)
Specify the action for ON DELETE (e.g., 'cascade', 'set null', 'restrict').
on_update(string $action)
Specify the action for ON UPDATE.
cascade_on_delete()
Shortcut for on_delete('cascade').
null_on_delete()
Shortcut for on_delete('set null').