Schema manager

Introspecting database names

Retrieve a list of the names of available databases:

<?php$databaseNames = $schemaManager->introspectDatabaseNames();

Introspecting sequences

Retrieve a list of Doctrine\DBAL\Schema\Sequence instances that exist in the current database:

<?php$sequences = $schemaManager->introspectSequences();

Now you can loop over the list inspecting each sequence object:

<?phpforeach ($sequences as $sequence) {    echo $sequence->getObjectName()->toString() . PHP_EOL;}

Introspecting table columns

Retrieve a list of Doctrine\DBAL\Schema\Column instances that exist for the given table:

<?php$columns = $schemaManager->introspectTableColumnsByUnquotedName('user');

Or if the table name should be represented as a quoted identifier:

<?php$columns = $schemaManager->introspectTableColumnsByQuotedName('user');

Now you can loop over the list inspecting each column object:

<?phpforeach ($columns as $column) {    echo $column->getObjectName()->toString() . ': ' . $column->getType() . PHP_EOL;}

Introspecting a table

Retrieve a single Doctrine\DBAL\Schema\Table instance that encapsulates the definition of the given table:

<?php$table = $schemaManager->introspectTableByUnquotedName('user');

Or if the table name should be represented as a quoted identifier:

<?php$columns = $schemaManager->introspectTableByQuotedName('user');

Now you can call methods on the table to manipulate the in memory schema for that table. For example we can add a new column:

<?php$table->addColumn('email_address', 'string');

Introspecting foreign key constraints of a table

Retrieve a list of Doctrine\DBAL\Schema\ForeignKeyConstraint instances that exist for the given table:

<?php$foreignKeyConstraints = $schemaManager->introspectTableForeignKeyConstraintsByUnquotedName('user');

Or if the table name should be represented as a quoted identifier:

<?php$foreignKeyConstraints = $schemaManager->introspectTableForeignKeyConstraintsByQuotedName('user');

Now you can loop over the list inspecting each foreign key constraint object:

<?phpforeach ($foreignKeyConstraints as $foreignKeyConstraint) {    echo $foreignKeyConstraint->getObjectName()->toString() . PHP_EOL;}

Introspecting table indexes

Retrieve a list of Doctrine\DBAL\Schema\Index instances that exist for the given table:

<?php$indexes = $schemaManager->introspectTableIndexesByUnquotedName('user');

Or if the table name should be represented as a quoted identifier:

<?php$indexes = $schemaManager->introspectTableIndexesByQuotedName('user');

Now you can loop over the list inspecting each index object:

<?phpforeach ($indexes as $index) {    echo $index->getObjectName()->toString() . ': ' . match($index->getType()) {        IndexType::REGULAR  => 'regular',        IndexType::UNIQUE   => 'unique',        IndexType::FULLTEXT => 'fulltext',        IndexType::SPATIAL  => 'spatial',    } . PHP_EOL;}

Introspecting all tables in the database

Retrieve a list of Doctrine\DBAL\Schema\Table instances that exist in the current database:

<?php$tables = $schemaManager->introspectTables();

Each Doctrine\DBAl\Schema\Table instance is populated with information provided by all the above methods. So it encapsulates a list of Doctrine\DBAL\Schema\Column instances that can be retrieved with the getColumns() method:

<?phpforeach ($tables as $table) {    echo $table->getObjectName()->toString() . " columns:" . PHP_EOL;    foreach ($table->getColumns() as $column) {        echo ' - ' . $column->getObjectName()->toString() . PHP_EOL;    }}

Introspecting all views in the database

Retrieve a list of Doctrine\DBAL\Schema\View instances that exist in the current database:

<?php$views = $schemaManager->introspectViews();

Now you can loop over the list inspecting each view object:

<?phpforeach ($views as $view) {    echo $view->getObjectName()->toString() . ': ' . $view->getSql() . PHP_EOL;}

Introspecting the database schema

For a complete representation of the schema of current database you can use the introspectSchema() method which returns an instance of Doctrine\DBAL\Schema\Schema, which you can use in conjunction with a schema comparator.

<?php$fromSchema = $schemaManager->introspectSchema();

Now we can clone the $fromSchema to $toSchema and drop a table:

<?php$toSchema = clone $fromSchema;$toSchema->dropTable('user');

Now we can compare the two schema instances in order to calculate the differences between them and return the SQL required to make the changes on the database:

<?php$statements = $schemaManager->createComparator()    ->compareSchemas($fromSchema, $toSchema)    ->toSql($conn->getDatabasePlatform());

The $statements list should give you the SQL statements to drop the user table:

<?phpprint_r($sql);/*array(  0 => 'DROP TABLE user')*/

Creating a schema comparator

To create a comparator that can be used to compare two schemas use the createComparator() method which returns an instance of Doctrine\DBAL\Schema\Comparator.

<?php$comparator = $schemaManager->createComparator();$schemaDiff = $comparator->compareSchemas($fromSchema, $toSchema);

To change the configuration of the comparator, you can pass a Doctrine\DBAL\Schema\ComparatorConfig object to the method:

<?php$config = (new ComparatorConfig())->withDetectRenamedColumns(false);$comparator = $schemaManager->createComparator($config);$schemaDiff = $comparator->compareSchemas($fromSchema, $toSchema);

Overriding the schema manager

All schema manager classes can be overridden, for instance if your application needs to modify SQL statements emitted by the schema manager or the comparator. If you want your own schema manager to be returned by Connection::createSchemaManager() you need to configure a factory for it.

<?phpuse Doctrine\DBAL\Configuration;use Doctrine\DBAL\DriverManager;use Doctrine\DBAL\Platforms\AbstractMySQLPlatform;use Doctrine\DBAL\Schema\DefaultSchemaManagerFactory;use Doctrine\DBAL\Schema\MySQLSchemaManager;use Doctrine\DBAL\Schema\SchemaManagerFactory;class MyCustomMySQLSchemaManager extends MySQLSchemaManager{    // .. your custom logic.}final class MySchemaManagerFactory implements SchemaManagerFactory{    private readonly SchemaManagerFactory $defaultFactory;    public function __construct()    {        $this->defaultFactory = new DefaultSchemaManagerFactory();    }    public function createSchemaManager(Connection $connection): AbstractSchemaManager    {        $platform = $connection->getDatabasePlatform();        if ($platform instanceof AbstractMySQLPlatform) {            return new MyCustomMySQLSchemaManager($connection, $platform);        }        return $this->defaultFactory->createSchemaManager($connection);    }}$configuration = new Configuration();$configuration->setSchemaManagerFactory(new MySchemaManagerFactory());$connection = DriverManager::getConnection([/* your connection parameters */], $configuration);