Persistence

Database Schema

Typed schema and DDL abstraction for tables, columns, indexes and foreign keys with grammar-specific SQL compilation.

Architecture

The schema layer is split into compilation and execution. SchemaCompiler builds SQL from TableBlueprint/TableDefinition using SchemaGrammarInterface. Schema executes compiled statements through DatabaseDriverInterface. Grammar implementations are driver/dialect specific.

Executor versus compiler

Use Schema when DDL should run against the configured database. Use SchemaCompiler when SQL should be previewed or tested without executing it. Existing compileCreate(), compileCreateTable() and related methods return the primary SQL string; compileCreateStatements() and compileCreateTableStatements() return all statements needed for a create operation.

use Lemonade\Framework\Database\Schema\Blueprint\TableBlueprint;
use Lemonade\Framework\Database\Schema\SchemaCompiler;

$compiler = $container->get(SchemaCompiler::class);

$sql = $compiler->compileCreate('articles', static function (TableBlueprint $table): void {
    $table->id();
    $table->string('title', 160);
}, ifNotExists: true);

$statements = $compiler->compileCreateStatements('articles', static function (TableBlueprint $table): void {
    $table->id();
    $table->string('title', 160);
    $table->index('title', ifNotExists: true);
}, ifNotExists: true);

Create table

Schema::create() builds a TableBlueprint and executes all create statements returned by the active grammar. The ifNotExists flag is stored on the TableDefinition and compiled by the grammar.

use Lemonade\Framework\Database\Schema\Blueprint\TableBlueprint;
use Lemonade\Framework\Database\Schema\Schema;

$schema = $container->get(Schema::class);

$schema->create('articles', static function (TableBlueprint $table): void {
    $table->id();
    $table->string('title', 160);
    $table->longText('body');
    $table->unsignedInteger('created_at');
    $table->index(['title', 'created_at'], 'idx_articles_title_created', ifNotExists: true);
}, ifNotExists: true);

Alter table

Schema::table() executes ALTER TABLE statements compiled from added columns, modified columns, dropped columns, indexes and foreign keys. compileTable() returns the generated statement list without executing it.

$schema->table('articles', static function (TableBlueprint $table): void {
    $table->string('slug', 160)->nullable()->after('title');
    $table->unique('slug', 'idx_articles_slug', ifNotExists: true);
});

Table operations

Schema supports drop(), rename(), createDatabase() and dropDatabase(). SchemaCompiler exposes compileDrop(), compileRename(), compileCreateDatabase() and compileDropDatabase() for SQL preview.

$schema->rename('articles_old', 'articles_archive');
$schema->drop('articles_archive', ifExists: true);

Blueprint columns

TableBlueprint column methods include id(), uuid(), char(), string(), text(), mediumText(), longText(), integer(), unsignedInteger(), tinyInteger(), smallInteger(), mediumInteger(), bigInteger(), unsignedBigInteger(), boolean(), decimal(), float(), double(), date(), datetime(), timestamp(), time(), json(), binary(), timestamps(), softDeletes(), raw(), modify(), renameColumn() and dropColumn().

Column modifiers

ColumnBlueprint supports unsigned(), nullable(), default(), defaultExpression(), autoIncrement(), comment(), after(), first(), renameTo(), primary(), index(), unique() and fulltext(). Backend support for comments, positioning, unsigned and index types depends on the grammar.

$table->string('email', 255)->unique();
$table->boolean('active')->default(true);
$table->datetime('created_at')->defaultExpression('CURRENT_TIMESTAMP');
$table->text('notes')->nullable();

Indexes

TableBlueprint supports primary(), index(), unique(), fulltext(), spatial(), dropIndex() and dropPrimary(). Index definitions can receive ifNotExists. Composite indexes should be declared at table level.

$table->primary('id');
$table->unique('email', 'idx_users_email', ifNotExists: true);
$table->index(['published', 'created_at'], 'idx_articles_published_created', ifNotExists: true);

Foreign keys

Foreign keys are created through TableBlueprint::foreign(). ForeignKeyBlueprint supports references(), on(), name(), onUpdate(), onDelete(), cascadeOnUpdate(), cascadeOnDelete(), restrictOnUpdate(), restrictOnDelete() and nullOnDelete(). Backend support varies by grammar.

$table->unsignedBigInteger('user_id');

$table->foreign('user_id')
    ->references('id')
    ->on('users')
    ->cascadeOnDelete();

Table options

TableBlueprint supports engine(), charset(), collation(), comment() and options(TableOptions). MySQL grammar uses engine/charset/collation/comment. SQLite ignores MySQL table options. ODBC behavior depends on the target backend.

Grammar behavior

MySQL compiles create-table indexes inline and compileCreateTableStatements() returns one statement. SQLite cannot place plain indexes inside CREATE TABLE, so compileCreateTableStatements() returns CREATE TABLE plus CREATE INDEX IF NOT EXISTS statements for plain indexes when needed. The string-returning compileCreate() API remains the primary CREATE TABLE statement.

Low-level definitions

For tests or framework internals, SQL can be compiled from immutable value objects such as TableDefinition, ColumnDefinition, IndexDefinition and ForeignKeyDefinition. Application code usually uses TableBlueprint callbacks.

use Lemonade\Framework\Database\Schema\Definition\ColumnDefinition;
use Lemonade\Framework\Database\Schema\Definition\TableDefinition;

$definition = TableDefinition::create('audit_logs')
    ->withColumn(ColumnDefinition::id('id'))
    ->withColumn(ColumnDefinition::string('message', 255));

$sql = $compiler->compileCreateTable($definition);

Queue relation

Framework commands can consume the schema layer. For example, queue table installation uses Schema and TableBlueprint so MySQL and SQLite DDL are compiled by the active grammar instead of hand-written per command SQL.

Migrations note

This schema layer provides DDL building, compilation and execution. Do not document migration history or migration runner behavior unless a concrete migration API is added separately.