📏 Schema

Hector Schema is the schema introspection and metadata module of Hector ORM. It provides a simple and consistent API to explore and manage your database structure. While tightly integrated with Hector ORM, this package is entirely independent and can be used on its own.

DBMS compatibility

Usage

Generate a schema

The schema generator reads your DB structure and returns rich PHP objects that describe it:

use Hector\Connection\Connection;
use Hector\Schema\Generator\MySQL;
use Hector\Schema\Generator\Sqlite;

// MySQL / MariaDB
$connection = new Connection('mysql:host=localhost;dbname=mydb', 'user', 'pass');
$generator = new MySQL($connection);

// SQLite
$connection = new Connection('sqlite:/path/to/database.db');
$generator = new Sqlite($connection);

$schema = $generator->generateSchema('schema_name');
// Returns a `Hector\Schema\Schema` object

$container = $generator->generateSchemas('schema1_name', 'schema2_name');
// Returns a `Hector\Schema\SchemaContainer` object

Available generators:

• Hector\Schema\Generator\MySQL — for MySQL and MariaDB
• Hector\Schema\Generator\Sqlite — for SQLite

Caching schemas

Schema generation can be expensive for large databases. This library does not include built-in caching, as caching strategies vary widely.

Instead, you can serialize schema objects and restore them later:

$serialized = serialize($schema);
file_put_contents('cache/schema.ser', $serialized);

// Later...
$schema = unserialize(file_get_contents('cache/schema.ser'));

Inheritance and references between objects are preserved during (de)serialization. This makes caching simple and flexible — ideal for integrating into your own caching logic 🌟

API reference

This section covers the main classes used to explore database schemas.

Hector\Schema\SchemaContainer

A container for multiple schemas. Useful when your application interacts with multiple databases or schemas.

Key methods:

• getSchemas(?string $connection = null): Generator
• hasSchema(string $name, ?string $connection = null): bool
• getSchema(string $name, ?string $connection = null): Schema
• hasTable(string $name, ?string $schemaName = null, ?string $connection = null): bool
• getTable(string $name, ?string $schemaName = null, ?string $connection = null): Table

// Get table from default schema
$usersTable = $container->getTable('users');

// Get table from specific schema
$usersTable = $container->getTable('users', 'my_database');

// Get table from specific schema and connection
$usersTable = $container->getTable('users', 'my_database', 'replica');

This class is iterable: foreach ($container as $schema) will yield Hector\Schema\Schema objects.

Hector\Schema\Schema

Represents a single schema (i.e., database).

Key methods:

• getName(bool $quoted = false): string
• getCharset(): string
• getCollation(): string
• getTables(): Generator
• hasTable(string $name): bool
• getTable(string $name): Table
• getContainer(): ?SchemaContainer

Deprecated: The $quoted parameter on getName() is deprecated. Use Hector\Query\Statement\Quoted for driver-aware identifier quoting.

This class is also iterable: foreach ($schema as $table) yields Hector\Schema\Table objects.

Hector\Schema\Table

Represents a table and its structure.

Key methods:

• Identification:

  • getSchemaName(bool $quoted = false): string
  • getName(bool $quoted = false): string
  • getFullName(bool $quoted = false): string

• Metadata:

  • getType(): string
  • getCharset(): ?string
  • getCollation(): ?string
  • getDefinition(): ?string — native CREATE TABLE/VIEW SQL when available (since 1.5)
  • getTriggerDefinitions(): ?array — attached trigger name => native CREATE TRIGGER SQL (since 1.5)

• Columns:

  • getColumns(): Generator
  • getColumnsName(bool $quoted = false, ?string $tableAlias = null): array
  • hasColumn(string $name): bool
  • getColumn(string $name): Column
  • getAutoIncrementColumn(): ?Column

• Indexes:

  • getIndexes(?string $type = null): Generator
  • hasIndex(string $name): bool
  • getIndex(string $name): Index
  • getPrimaryIndex(): ?Index

• Foreign Keys:

  • getForeignKeys(Table $table = null): Generator

• Other:

  • getSchema(): Schema

• CHECK constraints (since 1.5):

  • hasCheckMetadata(): bool
  • getChecks(): Generator<CheckConstraint>
  • hasCheck(string $name): bool
  • getCheck(string $name): CheckConstraint

Deprecated: The $quoted parameter on getSchemaName(), getName(), getFullName(), and getColumnsName() is deprecated. Use Hector\Query\Statement\Quoted for driver-aware identifier quoting.

Hector\Schema\Column

Represents a column in a table.

Key methods:

• Identification:

  • getName(bool $quoted = false, ?string $tableAlias = null): string
  • getFullName(bool $quoted = false): string

• Metadata:

  • getPosition(): int
  • getDefault(): mixed
  • isNullable(): bool
  • getType(): string
  • isAutoIncrement(): bool
  • getMaxlength(): ?int
  • getNumericPrecision(): ?int
  • getNumericScale(): ?int
  • getOnUpdate(): ?string — database-side update expression, e.g. CURRENT_TIMESTAMP(6) (since 1.5)
  • getDatetimePrecision(): ?int — fractional seconds precision from MySQL/MariaDB metadata (since 1.5)
  • getGenerationExpression(): ?string — generated-column SQL expression (since 1.5)
  • isGenerated(): bool — whether the column is generated (since 1.5)
  • isGeneratedStored(): bool — whether a generated column is STORED (since 1.5)
  • isUnsigned(): bool
  • getCharset(): ?string
  • getCollation(): ?string

• Other:

  • getTable(): Table
  • isPrimary(): bool

Deprecated: The $quoted parameter on getName() and getFullName() is deprecated. Use Hector\Query\Statement\Quoted for driver-aware identifier quoting.

For MySQL/MariaDB, getOnUpdate() returns a normalized expression (CURRENT_TIMESTAMP or CURRENT_TIMESTAMP(n)) when a column has an automatic update clause, otherwise null. getDatetimePrecision() preserves the distinction between zero fractional digits (0) and absent metadata (null). Both properties survive serialization; caches created before these properties existed deserialize with null values.

SQLite introspection returns null for both properties. In particular, a migration using useCurrentOnUpdate does not create this property in SQLite: the introspected schema describes what the database actually stores.

Generated column metadata

Both MySQL/MariaDB and SQLite generators expose generated columns through the usual table column collection:

$column = $schema->getTable('order_lines')->getColumn('stored_total');

$column->isGenerated();              // true
$column->getGenerationExpression();  // SQL expression reported by the database
$column->isGeneratedStored();        // true for STORED, false for VIRTUAL
$column->hasDefault();               // false for generated columns, even when nullable

For an ordinary column, the expression is null and both generated flags return false. An expression such as 0 or NULL still identifies a generated column. MySQL/MariaDB may normalize the expression’s formatting and identifier quoting, so its text need not be identical to the migration input.

SQLite uses PRAGMA table_xinfo to find generated columns, then extracts their expressions from the table’s SQL, respecting quoted identifiers, strings, comments and nested parentheses. Hidden virtual-table implementation columns remain excluded. An expression that cannot be extracted raises a SchemaException rather than reporting an ordinary column. Older SQLite versions without table_xinfo retain ordinary-column introspection through table_info.

The expression and storage mode survive serialization. Older schema caches deserialize with null / false metadata; regenerate those caches to discover generated columns. Schema\Column stores metadata independently of the Plan\Generated declaration object.

SQLite column collations

SQLite introspection reports explicitly declared column collations through getCollation(), for example BINARY, NOCASE or a custom collation declared with collation. Columns without an explicit collation return null. SQLite has no per-column character set: getCharset() keeps reporting the database encoding for text columns. Collations of view results and expressions are not inferred as column declarations. Collations survive serialization and table rebuilds.

Hector\Schema\Index

Represents a table index.

Key methods:

• getName(): string
• getType(): string
• getColumnsName(bool $quoted = false, ?string $tableAlias = null): array
• getTable(): Table
• getColumns(): array
• hasColumn(Column $column): bool
• getDefinition(): ?string — native CREATE INDEX SQL when available (since 1.5)
• isImplicit(): bool — index created by a table constraint (since 1.5)

Hector\Schema\CheckConstraint

Represents a SQL CHECK constraint, independently of the plan operations.

$table = $schema->getTable('products');
if ($table->hasCheckMetadata()) {
    foreach ($table->getChecks() as $check) {
        echo ($check->getName() ?? '(anonymous)') . ': ' . $check->getExpression() . "\n";
    }
}

getChecks() yields numeric keys and retains all anonymous or duplicate-named constraints. getCheck() throws NotFoundException for a missing name, or SchemaException for an ambiguous name. All CHECK accessors except hasCheckMetadata() throw SchemaException when metadata is unavailable. An empty list means introspection confirmed that the table has no CHECKs; it does not mean “not collected”.

MySQL 8.0.16+ exposes names, expressions and ENFORCED / NOT ENFORCED state. MariaDB exposes column and table CHECKs when information_schema.CHECK_CONSTRAINTS exists; older MariaDB releases without that catalog return unknown metadata, even when the engine enforces CHECKs. The catalog was backported to MariaDB 10.2.22. SQLite extracts named and anonymous CHECKs from CREATE TABLE SQL, retaining expression text, whitespace and comments. Virtual table declarations retain unknown CHECK metadata. Views have a known empty CHECK list.

Enforcement metadata describes the constraint, not connection-level settings that can temporarily disable checks. MySQL/MariaDB may normalize expressions in their catalog. Unknown/unsupported engines are not assumed CHECK-free. Names, expressions, enforcement and table associations survive serialization. Older caches remain readable but have unknown CHECK metadata; regenerate them before a SQLite rebuild.

Native SQL definitions

SQLite introspection also retains definitions needed for faithful rebuilding:

• each table or view carries its native SQL in Table::getDefinition();
• each explicit index carries its own SQL in Index::getDefinition(); implicit indexes have no standalone SQL;
• each table/view carries its attached triggers as getTriggerDefinitions() (name => SQL);
• views remain schema objects, available through $schema->getTables(Table::TYPE_VIEW).

Definitions are strings, specific to their originating SQL dialect. They survive schema serialization. null indicates an unavailable definition or uncollected trigger metadata; [] confirms no triggers. Generators that do not collect these definitions leave them unavailable.

Hector\Schema\ForeignKey

Represents a foreign key constraint.

Key methods:

• getName(): string
• getColumnsName(bool $quoted = false, ?string $tableAlias = null): array
• getReferencedSchemaName(): string
• getReferencedTableName(): string
• getReferencedColumnsName(bool $quoted = false, ?string $tableAlias = null): array
• getUpdateRule(): string
• getDeleteRule(): string
• getTable(): Table
• getColumns(): Generator
• getReferencedTable(): ?Table
• getReferencedColumns(): Generator

Deprecated: The $quoted parameter on getColumnsName() and getReferencedColumnsName() is deprecated. Use Hector\Query\Statement\Quoted for driver-aware identifier quoting.

Example: basic schema introspection

$generator = new MySQL($connection);
$schema = $generator->generateSchema('my_database');

foreach ($schema as $table) {
    echo "Table: " . $table->getName() . "\n";
    
    // Columns
    foreach ($table->getColumns() as $column) {
        echo "  Column: " . $column->getName() . " (" . $column->getType() . ")";
        echo $column->isNullable() ? " NULL" : " NOT NULL";
        echo $column->isPrimary() ? " [PK]" : "";
        echo "\n";
    }
    
    // Indexes
    foreach ($table->getIndexes() as $index) {
        echo "  Index: " . $index->getName() . " (" . $index->getType() . ")";
        echo " on [" . implode(', ', $index->getColumnsName()) . "]\n";
    }
    
    // Foreign keys
    foreach ($table->getForeignKeys() as $fk) {
        echo "  FK: " . $fk->getName();
        echo " [" . implode(', ', $fk->getColumnsName()) . "]";
        echo " -> " . $fk->getReferencedTableName();
        echo " [" . implode(', ', $fk->getReferencedColumnsName()) . "]\n";
    }
    
    echo "\n";
}

This example will output the structure of your database with all tables, columns, indexes and foreign keys. Great for CLI tools, documentation generators or migration scripts! 📊

Installation

composer require hectororm/schema

See also

• Plan — the DDL migration module included in this package. Build CREATE TABLE, ALTER TABLE, DROP TABLE operations and compile them into SQL for MySQL/MariaDB and SQLite.
• Migration — orchestrate database migrations with providers, trackers and a runner.