Model from database tables with Doctrine ORM

Sometimes it is required to use some kind of reverse engineer with Doctrine ORM to extract models from an existing database, this time we have a MySQL database and we want to extract the models.

Before start, we need to install Composer and the Doctrine, easiest way to install this packages is the following:

1 – Create your project folder, i.e. “DoctrineSample”
2 – Go to your project folder and execute following command from console “php -r “readfile(‘https://getcomposer.org/installer’);” | php”, this will install composer inside your project folder
3 – Create a file to stablish project depedencies and name it “composer.json” with the following content:

{
    "require": {
        "doctrine/orm": "*"
    }
}

4 – Then we must install it by using “php composer.phar install”, this will download the package inside “vendor” folder
5 – Now we are ready to start, and that means to follow Doctrine configuration manual, you can follow it here (http://doctrine-orm.readthedocs.org/en/latest/reference/configuration.html) or keep reading this article for an quicker solution.

Next step: Project and Database setup

1 – For this sample I have created a simple database with just one table and no relationships, it’s up to you to add complexity as you need, take into account final notes in this article about limitations of Doctrine, database schema is the following:

CREATE TABLE IF NOT EXISTS `table1` (
`field1` int(11) NOT NULL,
  `field2` date DEFAULT NULL,
  `field3` text NOT NULL,
  `field4` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `table1`
 ADD PRIMARY KEY (`field1`);

ALTER TABLE `table1`
MODIFY `field1` int(11) NOT NULL AUTO_INCREMENT;

2 – Create a file named “bootstrap.php” with the following content (take into account that database is named ‘test), fill it with your database credentials:

<?php
// bootstrap.php
require_once "vendor/autoload.php";

use Doctrine\ORM\Tools\Setup;
use Doctrine\ORM\EntityManager;

$paths = array("/path/to/entity-files");
$isDevMode = false;

// the connection configuration
$dbParams = array(
    'driver'   => 'pdo_mysql',
    'user'     => 'root',
    'password' => '',
    'dbname'   => 'test',
);

$config = Setup::createAnnotationMetadataConfiguration($paths, $isDevMode);
$entityManager = EntityManager::create($dbParams, $config);

Now we are ready to get the models from the tables by using reverse engineer provided by Doctrine, i.e. if we want to get the models in YAML format we need an additional step, we should go back again to composer.json and add the following dependency “symfony/yaml”: “*”, then we need to update the dependencies with “php composer.phar update” command.

After that we are able to execute our reverse engineer command, create a folder under your project structure in which will be saved the YAML models, i.e. “src”, then, execute the command:

First create a file named “cli-config.php” with following content:

<?php
// cli-config.php
require_once "bootstrap.php";

return \Doctrine\ORM\Tools\Console\ConsoleRunner::createHelperSet($entityManager);

vendor\bin\doctrine orm:convert-mapping –from-database yaml src/

Once executed we can see tables mapping as YAML have been created inside “src” folder and content for this sample is:

Table1:
    type: entity
    table: table1
    id:
        field1:
            type: integer
            nullable: false
            options:
                unsigned: false
            id: true
            generator:
                strategy: IDENTITY
    fields:
        field2:
            type: date
            nullable: true
        field3:
            type: text
            nullable: false
            length: 65535
            options:
                fixed: false
        field4:
            type: string
            nullable: false
            length: 50
            options:
                fixed: false
    lifecycleCallbacks: {  }

Command for reverse engineer, from database to php models (xml, yaml, annotations):
vendor\bin\doctrine orm:convert-mapping –from-database annotation api/src/
vendor\bin\doctrine orm:convert-mapping –from-database xml api/src/
vendor\bin\doctrine orm:convert-mapping –from-database yaml api/src/

Note: This mapping could be affected by an Doctrine known issue, it’s not allowed to create a mapping with N:M relationships because doctrine does not support N:M relationships with PRIMARY KEYS as FOREIGN KEYS

Leave a Comment Cancel Reply