Database Schema Introspection

Spiral/Database layer provides the ability to read and analyze the basic properties of a given database or a given table. The DBAL layer includes a set of "abstract" types assigned to each column, based on DBMS specific mapping in order to unify different engines.

List of database tables

To check if the database has a specific table use hasTable:

if ($database->hasTable('users')) {
    //...
}

Read how to get Database instances here.

Receive all database tables (array of Spiral\Database\Table):

foreach ($database->getTables() as $table) {
    dump($table->getName());
}

Only tables specific to the database prefix (if any) are returned.

Schema Reader/Builder (AbstractTable) is available using getSchema method:

foreach ($database->getTables() as $table) {
    dump($table->getSchema());
}

Reading table properties using AbstractTable

The AbstractTable provides low-level access to table information, such as column types (internal and abstract), indexes, foreign keys, etc. You can use this information to perform a database export, or to build your own ORM or migration mechanism (see schema declaration).

Table primary keys:

dump($schema->getPrimaryKeys());

Table indexes:

foreach ($schema->getIndexes() as $index) {
    dump($index->getName());
    dump($index->getColumns());
    dump($index->isUnique());
}

Table foreign keys (references):

foreach ($schema->getForeigns() as $foreign) {
    dump($foreign->getColumn());       //Local column name
    dump($foreign->getForeignTable()); //Global table name!
    dump($foreign->getForeignKey());

    dump($foreign->getDeleteRule());   //NO ACTION, CASCADE
    dump($foreign->getUpdateRule());   //NO ACTION, CASCADE
}

Attention, getForeignTable returns full table name ignoring db prefix.

Table columns:

foreach ($schema->getColumns() as $column) {
    dump($column->getName());

    dump($column->getType());          //Internal database type
    dump($column->abstractType());     //Abstract type like string, bigInt, enum, text and etc.
    dump($column->phpType());          //PHP type: int, float, string, bool

    dump($column->getDefaultValue());  //Can be instance of SqlFragment

    dump($column->getSize());          //Only for strings and decimal values

    dump($column->getPrecision());     //Decimals only
    dump($column->getScale());         //Decimals only

    dump($column->isNullable());
    dump($column->getEnumValues());    //Only for enums

    dump($column->getConstraints());

    dump($column->sqlStatement());     //Column creation syntax
}

Some types can be mapped incorrectly if the table was created outside migrations or ORM.

You can find a complete list of available abstract types here.

Edit this page