How to get SQL from Doctrine to INSERT an entity into another database / connection?

Question

I am working on a Symfony 3.4 + Doctrine based project. The production database has grown quite large and I would like to be able to copy some of the data / entities to a second database which can be used as sandbox for running tests, evaluations, etc. on the data.

Adding a second database connection to the project was no problem. Second step was query the database schema / table structure form Doctrine to re-create the exact same tables for some entities in this second DB.

Now I would like to query entities from the production DB using the entity manager as usual and persist them to the second DB. Since the second DB holds only some of the data/entities/tables I cannot use a second entity manager here but have to insert the data manually.

I am looking for something like:

// Load entity from production DB via entity manager
$repo = $this->em->getRepository(SomeEntity::class);
$entity = $repo->findOneById('xy12');

// Pseudocode(!): Get SQL code to save the entity
$saveQuery = $repo->getSaveQueryForEntity(entity);   <<< HOW TO DO THIS?
$saveSql = saveQuery->getSql();

// Run SQL on sandbox connection
$sandboxConnection = $doctrine->getConnection('sandbox');
$sandboxConnection->executeQuery($saveSql);

Of course I could create the INSERT query completely manually. However, this would be quite cumbersome and error prone. On the other hand creating the SQL code already build into Doctrine and all I need is a way to access/get this code to run it on a different connection?

Or is this approach completely wrong and there is a better way to get an entity from one DB to the other?

---

EDIT:

Dumping the complete database and importing it into a sandbox DB is not an option. The database holds data of many registered users and each user can decide if and when he wants to transfer some data to the sandbox. Copying a several GB large database with all user data to a sandbox because User 123 wants to run some tests on entities A and B is not very effective, is it?

I do not want to describe the complete internal logic of the project here, since this does not really help the question. So the question is how to copy / move a single entity to another database by getting the SQL from doctrine :-)

Answer 1

You said:

Since the second DB holds only some of the data/entities/tables I cannot use a second entity manager here but have to insert the data manually.

but you can still declare two different entity managers, that both map the same entity, but with different mapping options (maybe you don't want to map all fields with the other entity manager, for example).

You will need to have two distinct mappings that are bound to the same entity, so better go with separate YML files (instead of annotations). You can have something like:

config/orm/default/User.orm.yml   // User mapping for the default EM
config/orm/other/User.orm.yml     // User mapping for the other EM

Also, loading the entity with the default entity manager and persisting with the other will not work as expected. You will have to use merge() instead of persist(), since the entity will be managed by the default entity manager:

$user = $defaultEntityManager->getRepository(User::class)->find(1);

$otherEntityManager->merge($user);
$otherEntityManager->flush();

Answer 2

Given it's absolutely unsuitable to do it on a database level using mysqldump, I would probably enable two entity managers for the project and maintain exactly the same data schema for both of them. It gives an opportunity to persist similar objects when needed. When your user would select entities to copy on a web page, we can pass those ids to a handler to fetch entity from the main entity manager and sync it into a sandbox one. It should be pretty straightforward and less hacky than getting insert SQL queries from Doctrine. Here is a simple example to give you a starting point.

<?php

declare(strict_types=1);

namespace App\Sync;

use Doctrine\ORM\EntityManagerInterface;

class SandboxDbSyncHandler
{
    /** @var EntityManagerInterface */
    private EntityManagerInterface $em;

    /** @var EntityManagerInterface */
    private EntityManagerInterface $sandboxEm;

    public function __construct(EntityManagerInterface $em, EntityManagerInterface $sandboxEm)
    {
        $this->em        = $em;
        $this->sandboxEm = $sandboxEm;
    }

    public function sync(string $class, array $ids): void
    {
        $repo        = $this->em->getRepository($class);
        $sandBoxRepo = $this->sandboxEm->getRepository($class);
        $entities    = $repo->findBy(['id' => $ids]);

        foreach ($entities as $entity) {
            if (!$entity instanceof UpdatableFromEntity) {
                continue;
            }

            $sandBoxEntity = $sandBoxRepo->find($entity->getId());
            if (!$sandBoxEntity) {
                $sandBoxEntity = $class()::createFromEntity();
            }

            $sandBoxEntity->updateFromEntity($entity);
            $this->sandboxEm->persist($entity);
        }

        $this->sandboxEm->flush();
    }
}

<?php

declare(strict_types=1);

namespace App\Entity;

interface UpdatableFromEntity
{
    public static function createFromEntity($entity);
    public function updateFromEntity($entity): void;
}

<?php

declare(strict_types=1);

namespace App\Entity;

class SomeEntity implements UpdatableFromEntity
{
    private string $id;
    private ?string $name;

    public function __construct(string $id)
    {
        $this->id = $id;
    }

    public function getId(): string
    {
        return $this->id;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function updateFromEntity($entity): void
    {
        if (!$entity instanceof SomeEntity::class) {
            throw new \Exception('Cannot update an entity from the entity of a different type');
        }

        $this->name = $entity->getName();
    }

    public static function createFromEntity($entity)
    {
        if (!$entity instanceof SomeEntity::class) {
            throw new \Exception('Cannot create an entity from the entity of a different type');
        }

        return new static($entity->getId());
    }
}