Schema - Lyger PHP Framework

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.

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

Core

Database

Components

​Overview

​Class Reference

​Lyger\Database\Schema

​Schema Methods

​Constructor

​create()

​table()

​drop()

​dropIfExists()

​Blueprint Class

​Lyger\Database\Blueprint

​Column Types

​id()

​bigId()

​string()

​text()

​integer()

​bigInteger()

​decimal()

​float()

​boolean()

​date()

​datetime()

​timestamp()

​timestamps()

​softDeletes()

​json()

​uuid()

​Column Modifiers

​nullable()

​default()

​unsigned()

​primary()

​unique()

​index()

​Usage Examples

​Creating a Users Table

​Creating a Posts Table with Foreign Key

​Creating a Pivot Table

​Adding Columns to Existing Table

​Complex Table with All Column Types

​E-commerce Example

​Dropping Tables

​Column Type Reference

​Best Practices

​Database Drivers

​Related

Overview

Class Reference

Lyger\Database\Schema

Schema Methods

Constructor

create()

table()

drop()

dropIfExists()

Blueprint Class

Lyger\Database\Blueprint

Column Types

id()

bigId()

string()

text()

integer()

bigInteger()

decimal()

float()

boolean()

date()

datetime()

timestamp()

timestamps()

softDeletes()

json()

uuid()

Column Modifiers

nullable()

default()

unsigned()

primary()

unique()

index()

Usage Examples

Creating a Users Table

Creating a Posts Table with Foreign Key

Creating a Pivot Table

Adding Columns to Existing Table

Complex Table with All Column Types

E-commerce Example

Dropping Tables

Column Type Reference

Best Practices

Database Drivers

Related