Schema

Database schema builder for creating and modifying tables

Overview

The Schema class provides a fluent interface for creating and modifying database tables. It works with a Blueprint class to define table structures with methods inspired by Laravel’s schema builder.

Class Reference

Lyger\Database\Schema

Schema builder for database table operations.

Location: Lyger/Database/Schema.php

Schema Methods

Constructor

public function __construct(?string $driver = null)

Create a new Schema instance.

driver · string

Database driver (‘sqlite’, ‘mysql’, ‘pgsql’). Auto-detected if not provided.

Example:

$schema = new Schema();
// or with explicit driver
$schema = new Schema('sqlite');

create()

public function create(string $table, callable $callback): void

Create a new database table.

table · string · required

Name of the table to create

callback · callable · required

Closure that receives a Blueprint instance

Example:

$schema->create('users', function ($table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamps();
});

table()

public function table(string $table, callable $callback): void

Modify an existing database table.

table · string · required

Name of the table to modify

callback · callable · required

Closure that receives a Blueprint instance

Example:

$schema->table('users', function ($table) {
    $table->string('phone')->nullable();
    $table->integer('age');
});

drop()

public function drop(string $table): void

Drop a database table.

table · string · required

Name of the table to drop

Example:

$schema->drop('old_users');

dropIfExists()

public function dropIfExists(string $table): void

Drop a table if it exists.

table · string · required

Name of the table to drop

Example:

$schema->dropIfExists('users');

Blueprint Class

Lyger\Database\Blueprint

Fluent interface for defining table structure.

Location: Lyger/Database/Schema.php:186

Column Types

id()

public function id(string $column = 'id'): self

Create an auto-incrementing INTEGER primary key column.

column · string · default: "'id'"

Column name

Example:

$table->id(); // Creates 'id' column
$table->id('user_id'); // Creates 'user_id' column

bigId()

public function bigId(string $column = 'id'): self

Create an auto-incrementing big INTEGER primary key column.

column · string · default: "'id'"

Column name

Example:

$table->bigId();

string()

public function string(string $column, int $length = 255): self

Create a VARCHAR column.

column · string · required

Column name

length · int · default: "255"

Maximum string length

Example:

$table->string('name');
$table->string('email', 100);

text()

public function text(string $column): self

Create a TEXT column.

column · string · required

Column name

Example:

$table->text('description');
$table->text('content');

integer()

public function integer(string $column, bool $unsigned = false): self

Create an INTEGER column.

column · string · required

Column name

unsigned · bool · default: "false"

Whether the integer is unsigned

Example:

$table->integer('age');
$table->integer('quantity', true); // unsigned

bigInteger()

public function bigInteger(string $column, bool $unsigned = false): self

Create a big INTEGER column.

column · string · required

Column name

unsigned · bool · default: "false"

Whether the integer is unsigned

Example:

$table->bigInteger('population');

decimal()

public function decimal(string $column, int $precision = 8, int $scale = 2): self

Create a DECIMAL column (stored as REAL in SQLite).

column · string · required

Column name

precision · int · default: "8"

Total number of digits

scale · int · default: "2"

Number of decimal places

Example:

$table->decimal('price', 10, 2);
$table->decimal('tax_rate', 5, 3);

float()

public function float(string $column): self

Create a FLOAT column (stored as REAL in SQLite).

column · string · required

Column name

Example:

$table->float('rating');

boolean()

public function boolean(string $column): self

Create a BOOLEAN column (stored as INTEGER in SQLite).

column · string · required

Column name

Example:

$table->boolean('is_active');
$table->boolean('email_verified');

date()

public function date(string $column): self

Create a DATE column.

column · string · required

Column name

Example:

$table->date('birth_date');

datetime()

public function datetime(string $column): self

Create a DATETIME column.

column · string · required

Column name

Example:

$table->datetime('published_at');

timestamp()

public function timestamp(string $column): self

Create a TIMESTAMP column.

column · string · required

Column name

Example:

$table->timestamp('created_at');

timestamps()

public function timestamps(): self

Add created_at and updated_at TIMESTAMP columns.

Example:

$table->timestamps();
// Equivalent to:
// $table->timestamp('created_at');
// $table->timestamp('updated_at');

softDeletes()

public function softDeletes(): self

Add a deleted_at TIMESTAMP column for soft deletes.

Example:

$table->softDeletes();

json()

public function json(string $column): self

Create a JSON column (stored as TEXT in SQLite).

column · string · required

Column name

Example:

$table->json('metadata');
$table->json('settings');

uuid()

public function uuid(string $column = 'uuid'): self

Create a UUID column (36 character string).

column · string · default: "'uuid'"

Column name

Example:

$table->uuid();
$table->uuid('external_id');

Column Modifiers

These methods modify the most recently defined column.

nullable()

public function nullable(): self

Mark the column as nullable.

Example:

$table->string('middle_name')->nullable();
$table->text('bio')->nullable();

default()

public function default(mixed $value): self

Set a default value for the column.

value · mixed · required

Default value

Example:

$table->boolean('is_active')->default(1);
$table->string('status')->default('pending');
$table->integer('count')->default(0);

unsigned()

public function unsigned(): self

Mark the integer column as unsigned.

Example:

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

primary()

public function primary(): self

Mark the column as a primary key.

Example:

$table->string('uuid', 36)->primary();

unique()

public function unique(): self

Add a unique index to the column.

Example:

$table->string('email')->unique();
$table->string('username', 50)->unique();

index()

public function index(array $columns = []): self

Add an index to the column(s).

columns · array · default: "[]"

Array of column names. If empty, indexes the last defined column.

Example:

$table->string('email')->index();
$table->index(['user_id', 'created_at']); // Composite index

Usage Examples

Creating a Users Table

$schema = new Schema();

$schema->create('users', function ($table) {
    $table->id();
    $table->string('name', 100);
    $table->string('email')->unique();
    $table->string('password');
    $table->timestamp('email_verified_at')->nullable();
    $table->boolean('is_active')->default(1);
    $table->timestamps();
    $table->softDeletes();
});

Creating a Posts Table with Foreign Key

$schema->create('posts', function ($table) {
    $table->id();
    $table->integer('user_id')->unsigned();
    $table->string('title');
    $table->text('content');
    $table->string('status')->default('draft');
    $table->datetime('published_at')->nullable();
    $table->timestamps();
    
    // Add indexes
    $table->index(['user_id']);
    $table->index(['status', 'published_at']);
});

Creating a Pivot Table

$schema->create('role_user', function ($table) {
    $table->integer('role_id')->unsigned();
    $table->integer('user_id')->unsigned();
    $table->timestamps();
    
    // Composite index for faster lookups
    $table->index(['role_id', 'user_id']);
});

Adding Columns to Existing Table

$schema->table('users', function ($table) {
    $table->string('phone', 20)->nullable();
    $table->date('birth_date')->nullable();
    $table->json('preferences')->nullable();
});

Complex Table with All Column Types

$schema->create('products', function ($table) {
    $table->id();
    $table->uuid('external_id')->unique();
    $table->string('sku', 50)->unique();
    $table->string('name');
    $table->text('description')->nullable();
    $table->decimal('price', 10, 2);
    $table->float('rating')->nullable();
    $table->integer('stock_quantity')->unsigned()->default(0);
    $table->boolean('is_available')->default(1);
    $table->json('attributes')->nullable();
    $table->date('release_date')->nullable();
    $table->timestamps();
    $table->softDeletes();
    
    // Add indexes
    $table->index(['is_available', 'stock_quantity']);
});

E-commerce Example

$schema = new Schema();

// Categories
$schema->create('categories', function ($table) {
    $table->id();
    $table->string('name');
    $table->string('slug')->unique();
    $table->text('description')->nullable();
    $table->integer('parent_id')->nullable();
    $table->timestamps();
});

// Products
$schema->create('products', function ($table) {
    $table->id();
    $table->integer('category_id')->unsigned();
    $table->string('name');
    $table->text('description');
    $table->decimal('price', 10, 2);
    $table->integer('stock')->unsigned()->default(0);
    $table->boolean('featured')->default(0);
    $table->timestamps();
    
    $table->index(['category_id']);
    $table->index(['featured', 'created_at']);
});

// Orders
$schema->create('orders', function ($table) {
    $table->id();
    $table->integer('user_id')->unsigned();
    $table->decimal('total', 10, 2);
    $table->string('status')->default('pending');
    $table->json('shipping_address');
    $table->datetime('shipped_at')->nullable();
    $table->timestamps();
    
    $table->index(['user_id', 'status']);
});

Dropping Tables

$schema = new Schema();

// Drop if exists (safe)
$schema->dropIfExists('old_products');

// Direct drop
$schema->drop('temporary_table');

Column Type Reference

Show Complete Column Type List

Method	SQL Type	Description
id()	INTEGER	Auto-incrementing primary key
bigId()	INTEGER	Big auto-incrementing primary key
string()	VARCHAR	Variable-length string
text()	TEXT	Long text content
integer()	INTEGER	Standard integer
bigInteger()	INTEGER	Large integer
decimal()	REAL	Fixed-point decimal
float()	REAL	Floating-point number
boolean()	INTEGER	Boolean (0 or 1)
date()	TEXT	Date value
datetime()	TEXT	Date and time
timestamp()	TEXT	Timestamp
json()	TEXT	JSON data
uuid()	VARCHAR(36)	UUID string

Best Practices

• Use id() for most tables to create a standard auto-incrementing primary key
• Always add timestamps() to track record creation and updates
• Use unique() on columns that must have distinct values (emails, usernames, etc.)
• Add index() to columns frequently used in WHERE clauses and JOINs
• Use nullable() for optional fields
• Use default() to set sensible defaults for columns
• Use descriptive column names in snake_case

Database Drivers

The Schema builder supports multiple database drivers:

Show Supported Drivers

• SQLite - Default driver, automatically detected if PDO SQLite extension is loaded
• MySQL - Used if PDO MySQL extension is loaded
• PostgreSQL - Used if PDO PostgreSQL extension is loaded

The driver is auto-detected based on available PDO extensions, with SQLite as the fallback.

Related

• Model - Eloquent-style ORM models
• QueryBuilder - Fluent query builder
• Migration - Database migration system