Tips and tricks to reverse engineer your existing database with Doctrine for Symfony

So in the Symfony documentation there is a page that describes how to reverse engineer an existing database using Doctrine.

Rule #1 No DB Vendor custom types

The only problem is this technique will only work with databases that are  generically designed. Meaning you can’t use some of the DB vendor specific types etc.

This means if your database uses tinyint or enum for example you should undo that before trying to reverse engineer it with the link above. And god bless you if you have used tiny ints as foreign keys. You must delete all foreign keys first, then recreate them.

Seriously trying to figure out how to work around TinyInts and Enums is a real pain in the ass, I’d advise against it. This documentation page is written poorly it leaves lots of questions to be asked.

Like WTF does this line even mean?

In this case however Schema-Tool update will have a hard time not to request changes for this column on each call.

Anyone want to have a stab at translating  WTF that means?

Use a backup

Don’t just backup your database. Start by dumping your current database structure, no data. Save this to your repository so you can restore if you need to. Next import that into a new Schema. You can do that with one of the doctrine migration commands see this article.

Once you have the database structure imported. Open a terminal and do a quick doctrine migration diff as talked about here. Once you have the diff look over the migration to see what doctrine is wanting to change in your database schema. For me, It is easier to make those changes in something like MySQL Workbench with the added bonus that it can create an Entity Relationship diagram as part of my documentation.

Working with a backup of the data-structure lets you see what needs to be changed in your real database first. Follow the techniques in this article about reverse engineering the database.

Do the doctrine orm mapping import after you do the first diff. The purpose of the first diff is just to see the changes. You will see stuff like problems with TinyInts, Enums etc. Any vendor specific column type will generally be more trouble than it is worth.

Delete Migrations

In the process of doing the diff and creating the mapping and doing the diff again and again, you end up with lots of migrations. Delete all of the migrations except for the very last diff migration.

You do the final diff after making final changes to the entity, mapping annotations. This way Doctrine can view your entities and compare them to your actual Schema. When you run this migration it will sync your database to the Entities.

No TinyInts

Sorry database designers you are not allowed to have these in your database design as they are mostly MySQL specific the Doctrine code does not allow them.

The Answer

Convert your tinyint columns to smallint. Doctrine understands those. Sure you are using like, a little more space each row now, but you really need Doctrine. LOL You can create a custom tinyint type, but wiring it in and using it is more of a pain than it is worth unless you really need it.

NO ENUMS

And the docs are wrong on this one and need updating. MySQL no longer requires an entire table rebuild costing hours.However Enums are not very useful because NULL and an empty string can be acceptable values under normal MySql configuration. Also with enums users can choose multiple values, so Doctrine uses php array type. You can use Enums, but it is more of a pain. See the doctrine docs here. You have to create a custom type and wire it in.

The Answer

Just use varchar or string as doctrine calls it. Make a check in your code (entity methods) to restrict values that are able to be accepted or make a table that holds the values. I personally use a table when there will be 10 or more values or I don’t know for certain how many there will be. If you use a table use a foreign link id to it in your main tables.

Forget your foreign key names

There is no solution for this. I have no idea why but Doctrine will absolutely rename,or suggest to rename every last foreign key.

I think it tries to make sure each is unique or maybe it needs it in a specific format. I usually like to put the table name in it to be a hint in error output. But it also seems to rename keys it has previously renamed too.

The Answer

Like the leg humping dog in National Lampoon… “it’s best to let him finish”

I’ll post more as I find it.

Your Entities have no repositories

It is awesome that doctrine can reverse engineer a database and even create the repositories too. What I don’t understand is why it does not link the damn repository  to the Entity. When you use the Entity maker it does this. When you reverse engineer with Doctrine it only creates the Entity mappings.

Answer

Use this Doctrine/Symfony maker I created. It can loop through your Entities and create Repositories for them.

There is still one catch though. You will have much fun like I did, opening 100+ Entity files and adding the proper annotations for the matching Repository to it. I still have another maker to put on github that fixes some issues with entity/repository mapping.

For example my app has a PageUrls Entity and it uses… PageUrlsRepository so to fix PageUrls so that I can use custom Repository methods I must now add this in the annotations.

/**
 * PageUrls
 *
 * @ORM\Table(name="page_urls", indexes={@ORM\Index(name="userOwnerId", columns={"owner_user_id"})})
 * @ORM\Entity(repositoryClass=PageUrlsRepository::class)
 */

So I must do this 100+ times now. I hope you have less tables in your database schema

I could add more code to the RepositoryMaker to update the Entities with the proper Repository tag. But then it is doing more than one thing. Maybe I should make a maker that updates the Entities Repository definition line to match. I’ve done this I just need to create the github repo add some stuff and publish it.

FullText ignored

I found that Doctrine ignored several things, fulltext indexes is one of them. It seems to me it doesn’t do cascading updates/deletes either, this may have changed by now or maybe I wasn’t paying close attention.

Anyways this isn’t documented yet I think it is a new feature to cover this issue. You must manually add the fulltext info to each index definition inside your Entity with a flags=fulltext section like so.

, flags={"fulltext"}

This goes in your Table indexes section like this

/**
 * ImageCollections
 *
 * @ORM\Table(name="image_collections",
 * uniqueConstraints={@ORM\UniqueConstraint(name="uniqueCollection",
 *     columns={"collection_name", "owner_user_id"})},
 *      indexes={@ORM\Index(name="collection", columns={"collection_name"}, flags={"fulltext"}),
 *     @ORM\Index(name="ownerIndex", columns={"owner_user_id"})})
 * @ORM\Entity(repositoryClass=ImageCollectionsRepository::class)
 */

Imagine having to go through all of this typing to create 100+ Entities! Yeah for me it was just easier to create my entire database in a GUI like MySQLWorkbench then reverse engineer with Doctrine. Otherwise I’d have to spend months becoming a doctrine wizard before I could dare to create a database design.