Schema-Manager

A Schema Manager instance helps you with the abstraction of the generation of SQL assets such as Tables, Sequences, Foreign Keys and Indexes.

To instantiate a SchemaManager for your connection you can use the createSchemaManager() method:

1<?php $schemaManager = $conn->createSchemaManager();
2

Now with the SchemaManager instance in $schemaManager you can use the available methods to learn about your database schema:

Parameters containing identifiers passed to the SchemaManager methods are NOT quoted automatically! Identifier quoting is really difficult to do manually in a consistent way across different databases. You have to manually quote the identifiers when you accept data from user or other sources not under your control.

listDatabases()

Retrieve an array of databases on the configured connection:

1<?php $databases = $sm->listDatabases();
2

listSequences()

Retrieve an array of Doctrine\DBAL\Schema\Sequence instances that exist for a database:

1<?php $sequences = $sm->listSequences();
2

Or if you want to manually specify a database name:

1<?php $sequences = $sm->listSequences('dbname');
2

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

1<?php foreach ($sequences as $sequence) { echo $sequence->getName() . "\n"; }
2
3
4

listTableColumns()

Retrieve an array of Doctrine\DBAL\Schema\Column instances that exist for the given table:

1<?php $columns = $sm->listTableColumns('user');
2

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

1<?php foreach ($columns as $column) { echo $column->getName() . ': ' . $column->getType() . "\n"; }
2
3
4

introspectTable()

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

1<?php $table = $sm->introspectTable('user');
2

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:

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

listTableForeignKeys()

Retrieve an array of Doctrine\DBAL\Schema\ForeignKeyConstraint instances that exist for the given table:

1<?php $foreignKeys = $sm->listTableForeignKeys('user');
2

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

1<?php foreach ($foreignKeys as $foreignKey) { echo $foreignKey->getName() ."\n"; }
2
3
4

listTableIndexes()

Retrieve an array of Doctrine\DBAL\Schema\Index instances that exist for the given table:

1<?php $indexes = $sm->listTableIndexes('user');
2

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

1<?php foreach ($indexes as $index) { echo $index->getName() . ': ' . ($index->isUnique() ? 'unique' : 'not unique') . "\n"; }
2
3
4

listTables()

Retrieve an array of Doctrine\DBAL\Schema\Table instances that exist in the connections database:

1<?php $tables = $sm->listTables();
2

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

1<?php foreach ($tables as $table) { echo $table->getName() . " columns:\n\n"; foreach ($table->getColumns() as $column) { echo ' - ' . $column->getName() . "\n"; } }
2
3
4
5
6
7

listViews()

Retrieve an array of Doctrine\DBAL\Schema\View instances that exist in the connections database:

1<?php $views = $sm->listViews();
2

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

1<?php foreach ($views as $view) { echo $view->getName() . ': ' . $view->getSql() . "\n"; }
2
3
4

introspectSchema()

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

1<?php $fromSchema = $sm->introspectSchema();
2

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

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

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:

1<?php $sql = $fromSchema->getMigrateToSql($toSchema, $conn->getDatabasePlatform());
2

The $sql array should give you a SQL query to drop the user table:

1<?php print_r($sql); /* array( 0 => 'DROP TABLE user' ) */
2
3
4
5
6
7
8

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.

1<?php use 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);
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37